In Django's ORM, when do you use an aggregate's filter= argument instead of QuerySet.filter(), and where do Case, When and Coalesce fit in?

## Two places a condition can go Suppose the report shows, per sales region, paid revenue and refunded amount side by side. In Django's ORM a condition can be applied in two places, and they mean different things. **`QuerySet.filter()` before `annotate()`** adds a `WHERE` clause to the whole query: ```python Region.objects.filter(customers__orders__status="paid").annotate( paid=Sum("customers__orders__amount") ) ``` - every aggregate in the query sees only the paid orders; - regions with **no paid orders vanish**, because no joined row survives the `WHERE`; - you cannot add a refund total in the same query, since refunded rows were removed. **The aggregate's `filter=` argument** applies the condition to that aggregate only: ```python from django.db.models import Q, Sum Region.objects.annotate( paid=Sum("customers__orders__amount", filter=Q(customers__orders__status="paid"), default=0), refunded=Sum("customers__orders__amount", filter=Q(customers__orders__status="refunded"), default=0), ) ``` - each aggregate has its own condition; - **every region stays** in the result, with `0` where nothing matched; - Django emits `SUM(...) FILTER (WHERE ...)` on databases that support the SQL standard syntax and an equivalent `SUM(CASE WHEN ... THEN ... ELSE NULL END)` elsewhere. The documentation adds a practical rule: for a **single** aggregate, `QuerySet.filter()` is more efficient because it removes rows early; `filter=` pays off when **two or more aggregates over the same relation need different conditions**. ## Filter after annotate: `HAVING` A `filter()` placed **after** `annotate()` that refers to the annotation, such as `.filter(paid__gt=10000)`, filters groups and becomes a `HAVING` clause. A `filter()` after `annotate()` on a related field (not the annotation) does not change what was already aggregated; `filter()` and `annotate()` do not commute. | Where the condition is | SQL | Effect | |---|---|---| | `filter(related__x=...)` before `annotate()` | `WHERE` | narrows rows for all aggregates, drops empty parents | | `Sum(..., filter=Q(...))` | `FILTER (WHERE ...)` or `CASE` | narrows rows for one aggregate | | `filter(annotation__gt=...)` after `annotate()` | `HAVING` | keeps or drops whole groups | ## A per-region scorecard in one query A realistic dashboard row per region might carry order count, paid revenue, refunded amount and a flag. Built step by step: 1. Start from `Region.objects` so every region appears, even one with no orders. 2. Add `orders=Count("customers__orders", filter=Q(customers__orders__placed_at__year=2026))` for the year's order count. 3. Add the paid and refunded `Sum`s with their own `filter=` and `default=0`. 4. Add a flag with `Case(When(paid__gte=100000, then=Value(True)), default=Value(False))`, which can reference the earlier annotation. 5. Filter groups with `.filter(paid__gt=0)` only if empty regions should be hidden, knowing this is a `HAVING`. Every aggregate here walks the **same** chain of joins (region → customers → orders), so there is no join multiplication; adding an aggregate over a second, independent relation would reintroduce it. ## `Case` and `When`: conditional values `Case(*cases, default=None, output_field=None)` works like `if/elif/else` in SQL. Each `When(condition, then=value)` is tested in order and the first true one wins: ```python from django.db.models import Case, Value, When Customer.objects.annotate(lifetime=Sum("orders__amount", default=0)).annotate( tier=Case( When(lifetime__gte=50000, then=Value("gold")), When(lifetime__gte=10000, then=Value("silver")), default=Value("standard"), ) ) ``` Typical uses: - **bucketing** rows into labels or scores; - **custom sort orders** with `order_by(Case(...))`; - **conditional updates**: `update(price=Case(When(...), default=F("price")))`; - conditional aggregation on old code that predates `filter=`, e.g. `Sum(Case(When(status="paid", then="amount"), default=0))`. `When` accepts keyword lookups, a `Q` object, or a boolean expression such as `Exists(...)`. ## Common mistakes with conditions - Putting the paid-only filter in `QuerySet.filter()` and then wondering why regions without paid orders vanished from a dashboard. - Writing `Count("customers__orders", filter=Q(status="paid"))`: the `Q` inside `filter=` is resolved against the **outer** model (`Region`), so the lookup must follow the relation, `Q(customers__orders__status="paid")`. - Expecting a `filter()` on a related field placed **after** `annotate()` to change the already computed aggregate; it only decides which parents appear, and because it adds a second join over the same multi-valued relation it can multiply a plain `Count`, which is why the documentation's example uses `distinct=True`. ## `Coalesce` and `default=`: taming `NULL` SQL aggregates over no rows return `NULL`, which Django shows as `None`: - `Sum`, `Avg`, `Min`, `Max` return `None` for an empty group unless you pass **`default=`**, which Django implements by wrapping the aggregate in **`Coalesce`**; - **`Coalesce(*expressions)`** from `django.db.models.functions` returns the first non-null argument, and needs at least two; use it for non-aggregate fallbacks too, e.g. `Coalesce("nickname", "name")`; - **`Count`** returns `0` for an empty group and raises `TypeError` if given `default`. Mixing types inside `Coalesce` (text with numbers) is a database error, and a `DecimalField` sum needs a decimal fallback, which `default=0` handles for you.