Django’s Subquery expression is one of the most powerful tools in the ORM. The documentation does a good job of explaining it at a high level, but u

Complex Django filters with Subquery · Better Simple

submited by

Style Pass

2025-01-07 20:00:05

Django’s Subquery expression is one of the most powerful tools in the ORM. The documentation does a good job of explaining it at a high level, but unless you have a decent understanding of SQL, you may not fully understand it’s power.

The primary use case that I’d like to cover is filtering a model based on a very nested relationship’s state. The purpose here is to show something complicated. If it doesn’t make sense, give it a little time, play around with it in your local environment and ask me follow-up questions!

Note: I’ll be using ipython and django-extensions to use python manage.py shell_plus --print-sql, then formatting that SQL manually with sqlformat.org.

That works and for small cases it’s great. Plus the SQL is pretty straightforward. However, it’s a bit inefficient because we’re fetching ALL comments then making them unique in the application layer via a set.

This gets us the same result, and it’s an actual QuerySet instance rather than a set. This can be useful if we need to do some other ORM operation. However, looking at that SQL, we can see we’re selecting on "myapp_comment" twice (and there’s another join to "myapp_post"). This may not be problematic depending on your database’s resources, but it sure would be great if we could avoid that.