beginnerintermediateadvanced

Django · Section 18

Query Expressions

Level: intermediate
Read: 24 min
Concepts: 3

Moving computation from Python into the database query itself: F() for race-safe field references and cross-field comparisons, Q() for OR/NOT logic filter() keyword arguments alone cannot express, Case/When for per-row conditional branching, ExpressionWrapper for declaring an ambiguous expression's output_field, and the database function families (Cast for real type conversion, Coalesce for a database-side fallback chain, plus date/string/numeric functions).

Django overview

Python Django Full Course for Beginners | Complete All-in-One Tutorial | 3 HoursDave Gray

What is true here

F("field") tells the database to compute using the field's own current value — avoiding the read-then-write race a plain += has, at the cost of needing refresh_from_db() to see the resolved value in Python afterward.
Q(...) wraps a lookup so it can combine with &/|/~ — the only way to express OR or NOT, since plain filter() keyword arguments always combine with AND.
Case(When(...), default=...) computes a conditional value per row inside the database — always set default=, or unmatched rows resolve to NULL.
ExpressionWrapper only tells the ORM how to interpret a combined expression's type in Python — it performs no actual database-level conversion, unlike Cast().
Coalesce(*expressions) is the database-side equivalent of a Python "or" fallback chain — it must be used inside the query (annotate/filter/order_by) to affect filtering or ordering, not applied after rows are already fetched.

What you will be able to do

Use F() for atomic, race-safe field updates and cross-field comparisons
Combine lookups with Q() for OR/NOT logic filter() kwargs cannot express
Compute a conditional, per-row value with Case/When, always covering the default case
Choose correctly between ExpressionWrapper and Cast, and use Coalesce inside a query rather than after fetching

F() and Q()

Database-side field references for race-safe updates and cross-field comparisons, and combining lookups with AND/OR/NOT.

F() and Q() expressions

coreintermediate

F("field_name") refers to a field's value inside the database itself, without pulling it into Python — used for race-safe increments (F("count") + 1) and for comparing two fields on the same row. Q(...) wraps a lookup so multiple lookups can be combined with & (AND), | (OR), and ~ (NOT) — needed the moment a filter needs OR logic or NOT logic, which filter()'s own keyword arguments cannot express.

Think of it as

F() moves a computation from Python into SQL — instead of reading a value, computing in Python, then writing it back (two round trips, with a race-condition window between them), F() tells the database "set this column to itself plus one," entirely inside one UPDATE statement, atomically. Q() exists because filter(a=1, b=2) always means AND — there is no keyword-argument syntax for OR or NOT, so Q objects give filter() something to combine with the boolean operators Python already has (&, |, ~), each Q wrapping one lookup.

python

Model.objects.update(count=F("count") + 1)
Model.objects.filter(Q(a=1) | Q(b=2))

What we're doing: Increment a view counter without a race condition between two concurrent requests, and query for orders in either of two statuses.

articles/views.pypython

def record_view(article_id):
    Article.objects.filter(pk=article_id).update(view_count=F("view_count") + 1)

def active_orders(customer):
    return Order.objects.filter(
        Q(status="PENDING") | Q(status="PROCESSING"), customer=customer,
    )

2: F("view_count") + 1 tells the database to compute the new value itself — two concurrent requests both calling this at once each correctly add 1, with no possibility of one overwriting the other's increment.
6: Q(status="PENDING") | Q(status="PROCESSING") is OR logic — combined with customer=customer as a normal kwarg, which still ANDs with the whole Q expression.

Why this works: article.view_count += 1; article.save() has a real race condition: two requests can both read the same starting value, both compute +1 from it, and the second save() silently overwrites the first's increment, losing a view — F() avoids this by never reading the value into Python at all, letting the database perform the read-and-increment as one atomic operation.

Reading an F()-updated attribute immediately after save() without refreshing

Wrong

python

article.view_count = F("view_count") + 1
article.save()
print(article.view_count)   # still an F() expression object, not a number!

Better

python

article.view_count = F("view_count") + 1
article.save()
article.refresh_from_db()
print(article.view_count)   # now the real, current integer

What you see: Printing or using article.view_count right after save() shows a CombinedExpression object (or raises when used in arithmetic), not the actual new integer — code that expects a number breaks immediately.

Why: F() never resolves to an actual number in Python — it is a reference the database evaluates when the SQL runs. save() sends that expression to the database and does not read the result back into the instance automatically; refresh_from_db() (or re-fetching the object) is required to load the real, current value into Python.

F() moves computation into SQL; Q() adds OR/NOT to filter()

F("count") + 1

+Computed inside the database, atomically
+Race-safe — no read-then-write window
+refresh_from_db() needed to see the real value

Q(a=1) | Q(b=2)

−filter() kwargs are always AND
−Q wraps a lookup for &, |, ~
−The only way to express OR/NOT

F() vs Q() — what each solves

F() vs Q() — what each solves
Need	Tool	Why filter() kwargs alone can't
Increment a field atomically	F("count") + 1	a plain += reads then writes — two steps, a race window
Compare two fields on the same row	filter(a__gt=F("b"))	kwargs compare a field to a Python value, not to another field
OR two conditions	Q(a=1) \| Q(b=2)	filter(a=1, b=2) is always AND
NOT a condition	~Q(a=1)	exclude(a=1) works for one condition, not for combining with OR

Together

python

from django.db.models import F, Q

Order.objects.filter(Q(status="PENDING") | Q(status="PROCESSING"))
Order.objects.filter(Q(total__gt=100) & ~Q(customer__is_vip=True))
Reporter.objects.filter(pk=1).update(stories_filed=F("stories_filed") + 1)

Remember: F("field") defers a computation to the database (race-safe increments, cross-field comparisons) — refresh_from_db() is required to see the real value in Python afterward. Q(...) wraps a lookup so filter() can express OR/NOT, which plain keyword arguments never can.

Value(), Case/When, and ExpressionWrapper

Per-row conditional branching computed inside the database, and declaring an expression's output type when Django can't infer it.

Value(), Case/When, and ExpressionWrapper

standardintermediate

Value(x) wraps a literal so it can participate in a database expression (Django usually infers this automatically, but explicit wrapping matters when the type can't be inferred). Case(When(condition, then=x), ..., default=y) is SQL CASE/WHEN as a Python expression — an if/elif/else computed inside the database. ExpressionWrapper wraps an expression to declare its output_field explicitly, needed whenever Django can't infer the combined result type on its own (e.g. adding a DateTimeField and a DurationField).

Think of it as

Case/When moves a branch of application logic into the query itself — instead of fetching rows and then classifying them in a Python loop, the database does the classifying and hands back an already-labeled column. ExpressionWrapper solves a narrower, more mechanical problem: Django infers a combined expression's type most of the time (int + int is still an int), but the moment two DIFFERENT field types combine (a DateTimeField plus a DurationField), Django genuinely cannot guess what type the result should be treated as in Python — ExpressionWrapper is where that guess is made explicit instead of left to fail.

python

Case(When(condition, then=Value(x)), default=Value(y), output_field=SomeField())
ExpressionWrapper(F("a") + F("b"), output_field=SomeField())

What we're doing: Label each order with a priority tier computed entirely inside the database, avoiding a Python-side loop over every row.

orders/views.pypython

orders = Order.objects.annotate(
    priority=Case(
        When(total__gte=1000, then=Value("high")),
        When(total__gte=100, then=Value("medium")),
        default=Value("low"),
        output_field=CharField(),
    )
).order_by("-priority")

3: Each When is checked in order, top to bottom — the first matching condition wins, same as a Python if/elif chain.
5: default=Value("low") covers every order matching neither prior When — without it, those rows would get NULL instead of a sensible fallback label.

Why this works: Computing priority in Python would require fetching every Order row first, then looping and re-assigning a Python attribute — Case/When computes the same label as part of the original query, so the database (not the application server) does the classification work, and the result is available for ordering/filtering in the same query too.

Omitting default= on a Case expression

Wrong

python

Case(
    When(total__gte=1000, then=Value("high")),
    When(total__gte=100, then=Value("medium")),
    output_field=CharField(),
)   # no default — orders under 100 get priority=None

Better

python

Case(
    When(total__gte=1000, then=Value("high")),
    When(total__gte=100, then=Value("medium")),
    default=Value("low"),
    output_field=CharField(),
)

What you see: Every order that matches none of the When conditions gets priority=None instead of an expected fallback value — code downstream that assumes priority is always one of "high"/"medium"/"low" breaks on the first low-value order.

Why: A Case expression with no default= behaves like a SQL CASE with no ELSE — any row matching none of the When clauses evaluates to NULL, not to some sensible fallback; default= is what supplies that fallback, and omitting it is easy to miss since Python's own if/elif has no equivalent silent-NULL failure mode to compare it against.

When each tool is needed

When each tool is needed
Situation	Tool
A literal needs to participate in an expression Django can't auto-wrap	Value(x)
A column's value should depend on a condition, computed in the database	Case(When(...), default=...)
Combining two different field types and Django can't infer the result type	ExpressionWrapper(..., output_field=...)
An actual database-level type conversion (not just a Python-side type hint)	Cast(..., output_field=...)

Together

python

from django.db.models import Case, When, Value, CharField, ExpressionWrapper, DateTimeField, F

Order.objects.annotate(
    priority=Case(
        When(total__gte=1000, then=Value("high")),
        When(total__gte=100, then=Value("medium")),
        default=Value("low"),
        output_field=CharField(),
    )
)

Ticket.objects.annotate(
    expires=ExpressionWrapper(F("active_at") + F("duration"), output_field=DateTimeField())
)

Remember: Case/When moves conditional branching into the database — always include default= or unmatched rows get NULL. ExpressionWrapper declares an expression's output_field when combining different field types makes the result type ambiguous; it does not itself perform a database-level type conversion (that's Cast()).

Database functions

Cast for real type conversion, Coalesce for a database-side fallback chain, and the date/string/numeric function families.

Database functions: Cast, Coalesce, and the function families

standardintermediate

Database functions run SQL functions (LOWER, UPPER, TRUNC, ROUND, ...) as part of a query instead of in Python after fetching. Cast(expression, output_field) performs a real database-level type conversion (not just a Python-side type hint, unlike ExpressionWrapper). Coalesce(*expressions) returns the first non-NULL value among its arguments — the database-side equivalent of a Python or-chain (a or b or c or default).

Think of it as

Every database function is the same trade as F()/Case — move a computation from "fetch rows, then compute in Python" to "let the database compute it as part of the query." Coalesce is the clearest example: `value if value is not None else default` in Python becomes `Coalesce(F("value"), Value(default))` in a query, computed per-row inside the database, filterable and orderable like any other annotated column. Cast is the one function in this group that actually changes what type the database stores/returns the value as — everything else in this family (date/string/numeric functions) just runs an existing SQL function against a column.

python

Model.objects.annotate(safe_value=Coalesce(F("maybe_null"), Value(default)))
Model.objects.annotate(converted=Cast("text_column", output_field=IntegerField()))

What we're doing: Display a customer's nickname if set, falling back to their first name — computed inside the query rather than with a Python-side "or" after fetching.

customers/views.pypython

def customer_list():
    return Customer.objects.annotate(
        display_name=Coalesce(F("nickname"), F("first_name")),
    ).order_by("display_name")

2: Coalesce(F("nickname"), F("first_name")) evaluates per row inside the database — a customer with no nickname gets their first_name instead, and the result (display_name) is a real annotated column, so it can be ordered/filtered like any other field.

Why this works: The Python equivalent — customer.nickname or customer.first_name — only works AFTER every Customer row has already been fetched, and cannot be used to order_by() or filter() on the combined value; Coalesce computes and exposes that same fallback logic as part of the query itself, so ordering by display_name orders by the ACTUAL displayed value, not by nickname alone (which would put every NULL-nickname customer in the wrong sort position).

Using Python's `or` on a fetched value instead of Coalesce in a query context

Wrong

python

customers = Customer.objects.all().order_by("nickname")
for c in customers:
    print(c.nickname or c.first_name)   # fallback only applied AFTER fetching and ordering

Better

python

customers = Customer.objects.annotate(
    display_name=Coalesce(F("nickname"), F("first_name")),
).order_by("display_name")

What you see: The list appears sorted incorrectly — every customer with no nickname clusters at the start (NULLs typically sort first) regardless of their first_name, even though the printed values (after the Python or fallback) look like they should be in a different order.

Why: order_by("nickname") sorts by the RAW nickname column, NULLs and all — applying a Python fallback afterward, only at print time, does nothing to fix the order the rows were already fetched in. Coalesce has to be part of the QUERY (used inside order_by(), not applied after the fact) for the sort to reflect the actual fallback value.

Database function families

Database function families
Family	Examples	Typical use
Type conversion	Cast	compare/store a value as a different DB type
Null handling	Coalesce	substitute a fallback for a NULL column, in the query
Date/time	TruncMonth, Extract, Now	group by month/year, filter by a date component
String	Lower, Upper, Concat, Length	case-insensitive comparisons, computed text columns
Numeric	Round, Abs, Ceil, Floor	computed numeric columns, usable in filter()/order_by()

Together

python

from django.db.models.functions import Coalesce, Lower, TruncMonth, Cast
from django.db.models import CharField, IntegerField

Customer.objects.annotate(display_name=Coalesce(F("nickname"), F("first_name")))
Article.objects.annotate(month=TruncMonth("published_at")).values("month").annotate(count=Count("id"))
Product.objects.filter(sku_lower=Lower("sku"))
Order.objects.annotate(reference_int=Cast("reference", output_field=IntegerField()))

Remember: Coalesce is the database-side equivalent of a Python "or" fallback chain — put it INSIDE the query (annotate/filter/order_by), not applied to already-fetched values, or ordering/filtering on the fallback breaks. Cast performs a real database-level type conversion; ExpressionWrapper does not.