Filter concepts by levelShowing all levels.

Django · Section 20

Advanced ORM Queries

Level
advanced
Read
24 min
Concepts
3

Correlated subqueries via Subquery/OuterRef (embedding a per-row subquery that references the outer query) and Exists (the faster, presence-only alternative), window functions via Window(expression, partition_by, order_by, frame) that compute per-row values without collapsing rows the way values()+annotate() grouping does, and the three raw SQL escape hatches — .raw() for a whole query, RawSQL for one embedded fragment, and the deprecated, legacy-only extra() — with the real, live SQL-injection risk of skipping parameterized params.

What is true here

  1. OuterRef bridges an inner (subquery) queryset back to a field on the outer query — it only has meaning inside something wrapped in Subquery()/Exists().
  2. Subquery returns an actual value (usually via .values(...)[:1]); Exists returns just True/False and is the faster, correct tool whenever only presence/absence matters — stopping at the first match instead of scanning or counting every row.
  3. Window() computes a value per row within a partition without collapsing rows, unlike values()+annotate() grouping — partition_by sets the context, order_by is required for order-sensitive functions, frame narrows further.
  4. .raw() replaces an entire query and returns real model instances; RawSQL embeds one fragment inside an otherwise normal query — both require params as a parameterized tuple/list.
  5. extra() is legacy — Django's own docs steer new code toward annotate()/F()/RawSQL instead; recognizing extra() in old code is a real skill, writing it in new code is not recommended.

What you will be able to do

  • Write a correlated subquery with Subquery/OuterRef, and choose Exists() when only presence matters
  • Use Window() for a per-row computation that must keep every row intact
  • Choose the right raw SQL escape hatch for a given gap in the ORM's expressiveness
  • Parameterize raw SQL correctly, avoiding a live SQL-injection vulnerability

Subquery, OuterRef, and Exists

Embedding a per-row subquery that references the outer query, and the faster Exists() alternative for presence checks.

Subquery, OuterRef, and Exists

coreadvanced

Subquery(inner_queryset) embeds a genuine correlated SQL subquery, computed per-row of the outer query, returning an actual value (e.g. the newest comment's email per post). OuterRef("field") is how the INNER queryset references a field from the OUTER query — it works like F(), but resolution is deferred until the outer QuerySet actually runs. Exists(inner_queryset) is a Subquery subclass returning only True/False, and stops scanning at the first match — faster than a Subquery when only presence matters, not an actual value.

Think of it as

A correlated subquery is one whose result depends on the CURRENT ROW of the outer query — "this post's newest comment," not "the newest comment overall." OuterRef is the pointer back to that current row, used inside the inner QuerySet's own .filter() exactly where a normal field value would go. Exists is the specialized, cheaper case of this pattern — the database only needs to find ONE matching row to answer "does at least one exist," and can stop scanning immediately, whereas a full Subquery returning values has to actually determine and return a real value, so the same shape costs more when only a yes/no answer is needed.

python
inner = Model.objects.filter(related=OuterRef("pk"))
Outer.objects.annotate(x=Subquery(inner.values("field")[:1]))
Outer.objects.annotate(y=Exists(inner))

What we're doing: Find every post that has at least one comment posted in the last day, without loading any comment data at all.

blog/views.pypython
recent_comments = Comment.objects.filter(
    post=OuterRef("pk"), created_at__gte=timezone.now() - timedelta(days=1),
)
posts_with_recent_activity = Post.objects.filter(Exists(recent_comments))
1
OuterRef("pk") is a placeholder — it means "whatever Post.pk this row of the OUTER query happens to be," resolved once the whole query runs, not when this line executes.
4
Exists(recent_comments) as a filter() condition works directly — no .values() needed, since Exists never returns actual comment data, just a per-post True/False.

Why this works: A naive alternative — fetching every recent comment, then computing which post IDs appear — pulls real comment data across the wire just to answer a yes/no question; Exists() pushes the entire check into the database as a single EXISTS subquery per post, stopping at the first match and never transferring comment content at all.

Forgetting OuterRef only works inside the inner queryset, not the outer one

Wrong

python
Post.objects.filter(pk=OuterRef("pk"))   # OuterRef used on the OUTER queryset itself — meaningless

Better

python
inner = Comment.objects.filter(post=OuterRef("pk"))   # OuterRef on the INNER queryset
Post.objects.filter(Exists(inner))

What you see: ValueError: This queryset contains a reference to an outer query and may only be used in a subquery — raised because OuterRef only has meaning INSIDE something wrapped in Subquery()/Exists(), never as a standalone filter condition on the query it appears to belong to.

Why: OuterRef is specifically a bridge FROM an inner (subquery) queryset BACK TO its outer query — using it directly on what would be the outer queryset itself has no outer query to refer back to, which is exactly the error Django raises to catch this mistake early.

OuterRef bridges the inner subquery back to each outer row
current rowused in.filter()annotatedback

Post.objects (outer)

one row at a time

OuterRef("pk")

points back to that row

Comment.objects.filter(post=OuterRef("pk"))

the inner queryset

Subquery(...) or Exists(...)

value, or True/False

  • Post.objects (outer) — one row at a time
    • leads to OuterRef("pk") (current row)
  • OuterRef("pk") — points back to that row
    • leads to Comment.objects.filter(post=OuterRef("pk")) (used in .filter())
  • Comment.objects.filter(post=OuterRef("pk")) — the inner queryset
    • leads to Subquery(...) or Exists(...)
  • Subquery(...) or Exists(...) — value, or True/False
    • leads to Post.objects (outer) (annotated back)

Subquery vs Exists

Subquery vs Exists
ToolReturnsUse when
Subquery(qs.values(...)[:1])an actual scalar value per outer rowyou need the VALUE (e.g. newest comment's email)
Exists(qs)True / False per outer rowyou only need to know IF a match exists
~Exists(qs)True / False, negated"has none" queries — NOT EXISTS

Together

python
newest_comment = Comment.objects.filter(post=OuterRef("pk")).order_by("-created_at")
Post.objects.annotate(newest_commenter_email=Subquery(newest_comment.values("email")[:1]))

recent_comments = Comment.objects.filter(post=OuterRef("pk"), created_at__gte=one_day_ago)
Post.objects.annotate(has_recent_comment=Exists(recent_comments))

Remember: OuterRef bridges an inner (subquery) queryset back to a field on the outer query — it only works inside something wrapped in Subquery()/Exists(). Subquery returns an actual value (usually via .values(...)[:1]); Exists returns just True/False and is the faster, correct tool whenever only presence/absence matters.

See also: window functions · raw sql escape hatches · conditional aggregation and distinct

Advertisement

Window functions

Computing a value per row within a partition, without collapsing rows the way grouping does.

Window functions

standardadvanced

Window(expression, partition_by=[...], order_by=..., frame=...) computes a value across a set of related rows (a "partition") WITHOUT collapsing them into one row — unlike annotate()+values() grouping, every original row survives, each annotated with its own window-computed value (e.g. "this movie's rating, plus the average rating of every movie from the same studio and genre," on the SAME row).

Think of it as

A regular aggregate (via values()+annotate()) collapses many rows into one row per group — you lose the individual rows entirely. A window function keeps every row exactly as it was, and adds ONE MORE computed column to each — "this row's value, evaluated in the context of its partition." partition_by defines what "context" means (movies from the same studio+genre); order_by matters when the computation is order-sensitive (a running total, a rank); frame narrows the window further, to only some rows within the partition (e.g. 2 before and 2 after, for a moving average) rather than the whole partition.

python
Model.objects.annotate(
    x=Window(expression=Avg("field"), partition_by=[F("group_field")], order_by="order_field"),
)

What we're doing: Rank each employee's salary within their own department, keeping every employee as an individual row (unlike a GROUP BY, which would collapse the department into one row).

hr/reports.pypython
from django.db.models import F, Window
from django.db.models.functions import Rank

Employee.objects.annotate(
    salary_rank=Window(expression=Rank(), partition_by=[F("department")], order_by="-salary"),
).order_by("department", "salary_rank")
3
partition_by=[F("department")] means the rank RESETS for each department — the highest-paid employee in EVERY department gets rank 1, not just the single highest-paid employee company-wide.
4
order_by="-salary" is what Rank() actually ranks by — descending, so the highest salary in each department gets rank 1.

Why this works: A regular values("department").annotate(max_salary=Max("salary")) would answer "what's the top salary per department" but collapse every employee into one row per department, losing individual employee data — Window() keeps every Employee row intact while still computing the per-department rank, which is exactly what a "show me every employee, ranked within their department" report actually needs.

Using values()+annotate() grouping when individual rows needed to survive

Wrong

python
Employee.objects.values("department").annotate(max_salary=Max("salary"))
# one row PER DEPARTMENT — individual employees are gone

Better

python
Employee.objects.annotate(
    dept_max_salary=Window(expression=Max("salary"), partition_by=[F("department")]),
)
# every employee keeps their own row, each showing their department's max

What you see: A report meant to list every employee alongside their department's highest salary instead returns only ONE row per department, with all individual employee data lost — the wrong shape of result entirely.

Why: values("department").annotate(...) is a real GROUP BY — it fundamentally collapses rows sharing the same department value into one output row, which is correct for "one row per department" reports but wrong the moment individual employee rows need to survive alongside a department-level computed value; Window() is specifically the tool that keeps rows ungrouped while still computing per-group context.

Window() arguments

Window() arguments
ArgumentPurpose
expressionthe aggregate/function to compute (Avg, Sum, Rank, RowNumber, ...)
partition_bywhich rows form each computation's context — like GROUP BY, but rows stay ungrouped
order_byordering within the partition — required for order-sensitive functions
framenarrows the partition further (e.g. N rows before/after) — defaults to the whole partition

Together

python
Movie.objects.annotate(
    avg_rating_for_studio_genre=Window(
        expression=Avg("rating"),
        partition_by=[F("studio"), F("genre")],
        order_by="released__year",
    ),
)
# every movie keeps its own row — each also shows the average for its studio+genre group

Remember: Window() computes a value per row within a partition WITHOUT collapsing rows — unlike values()+annotate() grouping, every original row survives. partition_by sets the grouping context; order_by is required for order-sensitive functions (Rank, running totals); frame narrows further to a sub-range of the partition.

See also: subquery outerref and exists · grouping with values · conditional expressions

Advertisement

Raw SQL escape hatches

.raw(), RawSQL, and the legacy extra() — and the real security stakes of skipping parameterization.

Raw SQL: .raw(), RawSQL, and legacy extra()

standardadvanced

.raw("SELECT ...") runs a full raw SQL statement and maps each result row to a model instance — the ORM equivalent of a raw query for whole objects. RawSQL(sql, params) embeds a raw SQL fragment inside a normal Django query (a filter/annotation), for one piece the ORM can't express. extra() is Django's older, now-deprecated way to inject raw SQL fragments into select/where/order_by — legacy code awareness only; new code should use annotate()/F()/RawSQL instead.

Think of it as

These three are ordered from least to most 'raw.' .raw() replaces an ENTIRE query but still gives back real model instances — the escape hatch of last resort when a query genuinely can't be expressed with the QuerySet API at all. RawSQL is a scalpel — one raw SQL fragment embedded INSIDE an otherwise normal Django query, when just one piece (not the whole query) needs raw SQL. extra() tried to do both at once through a single confusing multi-parameter method (select=, where=, params=, tables=, order_by=) and Django's own docs now steer new code away from it entirely — reading it in old code is a real skill (it still runs), writing new code with it is not.

python
Model.objects.raw("SELECT ... FROM ... WHERE col = %s", [value])
queryset.annotate(x=RawSQL("some_sql_expression(%s)", (param,), output_field=SomeField()))

What we're doing: Use RawSQL for one database-specific computation the ORM has no expression for, parameterized safely.

analytics/queries.pypython
Order.objects.annotate(
    similarity_score=RawSQL(
        "similarity(customer_notes, %s)", (search_term,), output_field=FloatField(),
    ),
).filter(similarity_score__gt=0.3)
2
similarity() here stands in for a database-specific function (e.g. PostgreSQL's pg_trgm extension) that has no equivalent Django ORM expression — RawSQL is the correct, narrow tool for exactly this gap.
3
(search_term,) — a tuple, passed as params, not string-interpolated into the SQL text directly. This is what keeps the query safely parameterized rather than vulnerable to injection.

Why this works: The rest of the query (annotate/filter/output_field) stays entirely normal Django ORM code — RawSQL is scoped to exactly the one piece (a database-specific similarity function) the ORM genuinely cannot express, rather than dropping to raw SQL for the whole query via .raw() when only one small piece actually needed it.

String-interpolating a value directly into raw SQL instead of using params

Wrong

python
Order.objects.raw(f"SELECT * FROM orders_order WHERE customer_name = '{customer_name}'")
# customer_name = "x'; DROP TABLE orders_order; --" is a real, working SQL injection here

Better

python
Order.objects.raw("SELECT * FROM orders_order WHERE customer_name = %s", [customer_name])

What you see: Any raw SQL built with an f-string or string concatenation containing user-controlled input is a live SQL injection vulnerability — a specially crafted value can alter the query's meaning entirely, up to and including destructive statements.

Why: params (passed separately, as a list/tuple, using %s placeholders) is what makes Django hand the actual substitution off to the underlying DB-API driver's parameterized query mechanism — the driver escapes/quotes the value correctly for that specific database. String-interpolating a value directly into the SQL text bypasses that mechanism entirely, and no amount of manual escaping in application code is a reliable substitute for real parameterization.

The three raw SQL tools, by scope

The three raw SQL tools, by scope
ToolScopeReturns
.raw(sql, params)an entire querya RawQuerySet of real model instances
RawSQL(sql, params)one fragment inside a normal queryused as an annotate()/filter() expression
extra() — legacyselect/where/order_by fragmentsa modified QuerySet — avoid in new code

Together

python
Order.objects.raw("SELECT * FROM orders_order WHERE total > %s", [100])

Order.objects.annotate(
    discounted=RawSQL("total * %s", (Decimal("0.9"),)),
)

Remember: .raw() replaces a whole query, returning real model instances; RawSQL embeds one fragment inside an otherwise normal query. Both require params as a parameterized tuple/list — never string-interpolate a value into the SQL text. extra() is legacy — recognize it in old code, but reach for annotate()/F()/RawSQL in new code instead.

See also: subquery outerref and exists · database functions · f and q expressions

Advertisement