TopolearnExpert ORM use means predicting exactly which SQL runs, and when.
A QuerySet is a thin wrapper around a `Query` object, a tree describing tables, joins, WHERE conditions, annotations and ordering. Each chained method (filter, annotate, order_by) clones the QuerySet and modifies the clone's query, so QuerySets are immutable from the caller's point of view and safe to reuse as bases.
When evaluated, the Query is handed to a SQLCompiler for the database backend, which produces SQL and parameters; str(qs.query) shows an approximation (parameters are not properly quoted), and qs.explain() asks the database for its plan.
Evaluation happens on iteration, len(), list(), bool(), repr(), pickling and slicing with a step. Results are stored in the QuerySet's result cache: iterating the same QuerySet twice runs one query, but building a new one (qs.filter(...), qs.all()) starts fresh. Indexing an unevaluated QuerySet (qs[0]) runs a LIMIT 1 query each time.
count(), exists() and aggregate() run their own queries unless the cache is already filled (count() then uses len() of the cache).
The multi-valued relationship gotcha: conditions inside one filter() call on a multi-valued relation must match the same related row; conditions in separate filter() calls may match different rows:
Post.objects.filter(comments__text="spam", comments__approved=True) # same comment
Post.objects.filter(comments__text="spam").filter(comments__approved=True) # any commentsThe second form also adds a second join. With DEBUG=True, connection.queries lists executed SQL; CaptureQueriesContext does the same in tests.
Example
from django.db import connection
from django.test.utils import CaptureQueriesContext
from blog.models import Post
base = Post.objects.filter(status="pub")
recent = base.order_by("-created") # a clone; base is unchanged
with CaptureQueriesContext(connection) as ctx:
posts = Post.objects.all()
list(posts); list(posts) # 1 query: second pass uses the result cache
posts.filter(views__gt=0).exists() # new QuerySet -> new query
posts.all()[0] # new clone -> LIMIT 1 query
print(len(ctx)) # 3
# Same comment must match both conditions vs. any comments matching each:
same = Post.objects.filter(comments__text="spam", comments__approved=True)
anyc = Post.objects.filter(comments__text="spam").filter(comments__approved=True)
print(same.count(), anyc.count()) # e.g. 1 2
print(str(same.query).count("JOIN"), str(anyc.query).count("JOIN")) # 1 2
print(base.explain()) # the database's query planRemember
- Chaining clones the QuerySet; each clone holds a Query tree
- Evaluated QuerySets cache results; new clones query again
- Conditions in one filter() on a multi-valued relation must match the same related row
- str(qs.query), explain() and CaptureQueriesContext reveal the SQL
Watch out for
- Splitting conditions across filter() calls on a reverse/M2M relation and getting broader results than intended.