The Django ORM Can Do Way More Than .filter()

Q objects, F objects, annotate, and aggregate: the four tools that let you stop dropping to raw SQL, and the one distinction everyone gets wrong.

Most Django developers use maybe 20% of the ORM. They know .filter(), .get(), .all(), .order_by(). The moment a query needs anything more interesting (an OR condition, comparing two columns, counting related rows, a GROUP BY), they either write a clumsy loop in Python or drop to raw SQL, convinced the ORM "can't do that."

It almost always can. The ORM has four tools that cover the vast majority of "complex" queries, and they're not advanced or obscure. They're just under-taught.

This article walks all four in plain English:

• Q objects (OR/NOT logic)
• F objects (comparing fields to each other)
• annotate (per-row computed columns)
• aggregate (whole-table summaries)

And it clears up the single most confused point in the entire ORM: the difference between annotate and aggregate. Everything here is current for Django 6.x and straight from the official docs.

---

Tool 1: Q Objects, for OR, NOT, and Complex Logic

The plain .filter() has a hidden limitation: multiple arguments are always combined with AND. filter(is_staff=True, is_active=True) means staff AND active. There's no way, with keyword arguments alone, to express OR.

So the moment you need "staff OR superuser," people give up and write two queries or a Python loop.

The tool for this is the Q object, which represents a single condition you can combine with boolean operators:

from django.db.models import Q

# OR - the | operator
User.objects.filter(Q(is_staff=True) | Q(is_superuser=True))

# NOT - the ~ operator
User.objects.filter(~Q(is_active=True))     # users who are NOT active

# Complex combination: (staff OR superuser) AND joined this year
User.objects.filter(
    (Q(is_staff=True) | Q(is_superuser=True)) & Q(date_joined__year=2026)
)

The operators map exactly to SQL logic:

Operator	Meaning
`\	`
&	AND
~	NOT

Q objects nest, so you can build arbitrarily complex boolean conditions (Q(Q(a=1) | Q(b=2)) & Q(c=3)), just like grouping with parentheses in SQL.

One rule to remember: if you mix a Q object with regular keyword arguments in the same filter(), the Q objects must come first.

Anytime you catch yourself thinking "I need an OR here," that's the Q object's cue.

---

Tool 2: F Objects, for Comparing a Field to Another Field

Here's a question that stumps people: how do you find all products where the quantity_sold is greater than the quantity_in_stock? Both values are columns in the same row.

You can't write filter(quantity_sold__gt=quantity_in_stock). Django would think quantity_in_stock is a Python variable, not a column.

The F object solves this. It's a reference to a database field's value, so you can compare one column to another, or do arithmetic between columns, entirely in the database:

from django.db.models import F

# Find rows where one column exceeds another
Product.objects.filter(quantity_sold__gt=F("quantity_in_stock"))

# Do math between columns
Company.objects.annotate(chairs_needed=F("num_employees") - F("num_chairs"))

# Update a column based on its own value (the atomic, race-safe increment)
Article.objects.filter(id=1).update(view_count=F("view_count") + 1)

The key idea: an F object means "the value of this field, in the database," so the whole comparison or calculation happens in SQL rather than by pulling rows into Python.

That's why F is both the tool for field-to-field logic and the tool for safe, atomic updates (no reading a value into Python where it can go stale). Anytime a query involves two columns relating to each other, reach for F.

---

Tools 3 & 4: annotate vs aggregate, the Distinction Everyone Gets Wrong

This is the heart of the article, because these two are the most powerful tools and the most confused pair in the ORM. They sound similar, they both compute things with functions like Count, Sum, Avg, Max, Min, and they do completely different things.

Here's the distinction in one sentence:

aggregate() collapses the whole queryset into a single summary. annotate() adds a computed value to each row.

Let's make that concrete.

aggregate(): one answer for the entire set

It's a terminal clause. You give it a computation, it runs over all matching rows, and it returns a plain dictionary: a single summary. "What's the average price of all books?"

from django.db.models import Avg, Max, Min

Book.objects.aggregate(Avg("price"))
# → {'price__avg': 34.35}   ← one number for the whole table

# Multiple summaries at once, with custom names:
Book.objects.aggregate(
    average_price=Avg("price"),
    highest=Max("price"),
    lowest=Min("price"),
)
# → {'average_price': 34.35, 'highest': 81.20, 'lowest': 9.99}

Because it returns a dict (not a queryset), aggregate() is the end of the line. You can't chain .filter() or .order_by() after it. It's the answer, not a step.

annotate(): one computed value per row

This is a SQL GROUP BY. It adds a new attribute to each object in the queryset, computed from related data. "How many books does each publisher have?"

from django.db.models import Count

pubs = Publisher.objects.annotate(num_books=Count("book"))
# Each publisher object now HAS a .num_books attribute:
for p in pubs:
    print(p.name, p.num_books)   # Penguin 42, O'Reilly 128, ...

Every publisher comes back with a num_books attribute glued on. And crucially, unlike aggregate, annotate() returns a queryset, so you can keep chaining: filter on the computed value, order by it, annotate again.

# Publishers with more than 5 books, most-prolific first
Publisher.objects.annotate(num_books=Count("book")) \
    .filter(num_books__gt=5) \
    .order_by("-num_books")

That's a query that would be genuinely awkward in raw SQL and a nightmare as a Python loop, and it's three readable lines in the ORM. You can even filter and order by the annotated value, because after annotate you still have a queryset to work with.

The mental model that fixes the confusion for good

You want...	Tool	Returns
One number for the whole set	aggregate	A dict (terminal, the final answer)
One number per row or group	annotate	A queryset (chainable)

Ask "do I want one number for everything, or one number per group/row?" That question picks the tool every time.

New in Django 6: AnyValue helps in GROUP BY queries where you want a representative value from each group without caring which specific row it comes from. It's a small but handy addition to the aggregate toolkit.

---

Putting Them Together

The real power shows up when you combine these. Say you want each active author, annotated with their book count and average rating, keeping only prolific ones, best-rated first:

from django.db.models import Count, Avg

Author.objects.filter(is_active=True) \
    .annotate(
        book_count=Count("book"),
        avg_rating=Avg("book__rating"),
    ) \
    .filter(book_count__gte=3) \
    .order_by("-avg_rating")

One database query. Q for complex conditions, F for field relationships, annotate for per-row computations, aggregate for final summaries. Combined, they express an enormous range of "complex" queries directly in Python, and the database does all the heavy lifting in a single optimized statement.

That last point matters. These tools push the computation into the database, where it belongs, instead of dragging thousands of rows into Python to loop over. That's both far faster and the difference between an ORM query and an N+1 disaster.

---

When You Should Still Reach for Raw SQL

To be fair and complete: the ORM isn't infinitely capable, and knowing its edge is part of using it well.

For genuinely exotic queries (some complex window functions, hyper-specific database features, or heavily-tuned analytical queries where you need exact control over the SQL), raw SQL via .raw() or a direct cursor is the right tool. Reaching for it there is a sign of experience, not failure.

The point of this article isn't "never write SQL." It's that the line where you need raw SQL is much further out than most developers think. Q, F, annotate, and aggregate cover the vast majority of what people wrongly assume requires dropping down. Learn these four, and raw SQL becomes the rare exception it should be, not a weekly reflex.

---

Why Understanding This Makes You Better at Django

The developers who think "the ORM can't do complex queries" are almost always just missing these four tools, and the cost of that gap is real:

• They write Python loops that pull thousands of rows into memory to count or sum them (slow, and often an N+1 problem).
• Or they scatter raw SQL through the codebase where a readable, database-agnostic, injection-safe ORM query would have done the job.

The developer who knows Q, F, annotate, and aggregate looks at "count each user's published posts and show the ones with more than ten, most-active first" and writes it as one chained queryset that runs as one efficient SQL statement. No loop, no raw SQL, no N+1.

And the tools aren't hard once they're named and separated:

• Q for the boolean logic keyword arguments can't express.
• F for when two columns relate to each other.
• annotate for a computed value on each row (chainable, GROUP BY).
• aggregate for one summary of the whole set (terminal dict).

The annotate-vs-aggregate distinction is the one that unlocks the most power, and it comes down to a single question: one row each, or one answer total?

Hold these four, and the ORM stops feeling like a toy you outgrow and starts being what it actually is: a genuinely powerful query builder that keeps you in readable Python for far longer than you'd expect.

---

The 30-Second Takeaway

• The ORM does far more than .filter(). Four tools cover most "complex" queries, no raw SQL needed.
• Q objects handle OR / NOT / complex boolean logic that keyword arguments can't express (they only AND). Use | (OR), & (AND), ~ (NOT). They nest. Put Q args before keyword args.
• F objects reference a field's value in the database, so you can compare one column to another (filter(sold__gt=F("stock"))), do column arithmetic, and run atomic, race-safe updates (F("view_count") + 1).
• annotate() adds a computed value to each row (a SQL GROUP BY), e.g. Publisher.objects.annotate(num_books=Count("book")). It returns a queryset, so you can .filter()/.order_by() on the computed value and keep chaining.
• aggregate() collapses the whole set into one summary dict, e.g. Book.objects.aggregate(Avg("price")). It's terminal: it returns a dict, not a queryset, so you can't chain after it.
• The distinction everyone confuses: one number per row → annotate (chainable); one number for everything → aggregate (final dict). Ask "one answer per row, or one answer total?"
• These push work into the database (fast, one query) instead of looping in Python (slow, N+1). Reach for raw SQL only for genuinely exotic cases. The line is much further out than people think.

---

Your Turn

Take the ugliest Python loop in your codebase that counts or sums database rows, and try rewriting it as one annotate() or aggregate() query. Nine times out of ten it collapses into a single readable line that runs a single fast query, and that's the ORM you didn't know you had.

Which of the four do you reach for least? Tell me in the comments 👇

---

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.