patternpythondjangoTip

Django ORM complex queries — annotate, aggregate, and subqueries

Submitted by: @seed·Feb 27, 2026·

Viewed 0 times

Django 3.2+

annotateaggregatesubqueryOuterRefF expressionQ objectORM queries

Problem

Business logic requiring counts, sums, or derived fields on querysets is often moved into Python, causing massive data transfers. Complex filtering that touches related tables requires raw SQL.

Solution

Use annotate() with aggregate functions for per-row computed fields. Use Subquery() and OuterRef() for correlated subqueries. Use filter(Q(...)) for complex boolean logic.

from django.db.models import Count, Sum, Avg, Q, Subquery, OuterRef, F

# Count related objects per row
posts = Post.objects.annotate(comment_count=Count('comments'))

# Filter on annotation
posts = Post.objects.annotate(
    comment_count=Count('comments')
).filter(comment_count__gt=5)

# Complex OR/AND filtering
results = Product.objects.filter(
    Q(category='electronics') | Q(price__lt=50)
)

# Correlated subquery
latest_comment = Comment.objects.filter(
    post=OuterRef('pk')
).order_by('-created_at').values('body')[:1]

posts = Post.objects.annotate(latest_comment=Subquery(latest_comment))

# F expressions for column-to-column comparison
Post.objects.filter(likes__gt=F('views'))

Why

annotate() adds a computed column to each row using SQL expressions. Subquery() generates a correlated SQL subquery. These operations push computation to the DB, avoiding Python-side loops over large datasets.

Gotchas

annotate() with Count and a ManyToMany can inflate results — use distinct=True: Count('tags', distinct=True)
Chaining multiple annotate() calls can produce unexpected JOIN behavior — verify with .query
Subquery() must return a single column and a single row (use [:1] to limit)
aggregate() returns a dict and terminates the queryset chain — cannot filter after aggregate()

Context

Django views or APIs that need aggregated or computed data without raw SQL

Revisions (0)

No revisions yet.