In Django, why does Permit.objects.exclude(violations__paid=False, violations__issued_on__year=2026) not mean 'no unpaid 2026 violation', and what is the fix?

## Single-valued versus multi-valued paths A lookup path such as `zone__code` follows a **single-valued** relation: each permit has one zone, so there is exactly one value to test. A path such as `violations__paid` follows a **multi-valued** relation, a reverse `ForeignKey` from `Violation` to `Permit` (or a `ManyToManyField`): one permit can have many violations, and a condition is really "some related row satisfies this". Combining several such conditions raises a question: must the **same** violation satisfy all of them? ## How filter() answers Django's documented rule for `filter()`: 1. Conditions in **one** `filter()` call on the same multi-valued relation must be satisfied by the **same related row**. `filter(violations__paid=False, violations__issued_on__year=2026)` finds permits with at least one violation that is both unpaid and from 2026. One join is used. 2. Conditions in **chained** `filter()` calls may be satisfied by **different** related rows. `filter(violations__paid=False).filter(violations__issued_on__year=2026)` finds permits with some unpaid violation and some 2026 violation. Each call adds its own join, so a permit can appear several times; `.distinct()` removes the duplicates. ## How exclude() answers `exclude()` is **not** implemented as the mirror image. The docs state that conditions in a single `exclude()` call "will not necessarily refer to the same item". For each condition on a multi-valued path Django builds a subquery, so `Permit.objects.exclude(violations__paid=False, violations__issued_on__year=2026)` removes permits that have **any** unpaid violation **and any** violation in 2026, possibly two different violations. A permit with an unpaid 2025 fine and a paid 2026 fine is excluded, although it has no unpaid 2026 violation. The query answers a different question from the one it seems to ask, and nothing errors. | Query | Permits returned | |---|---| | `filter(violations__paid=False, violations__issued_on__year=2026)` | with one violation both unpaid and from 2026 | | `filter(violations__paid=False).filter(violations__issued_on__year=2026)` | with some unpaid and some 2026 violation, duplicates possible | | `exclude(violations__paid=False, violations__issued_on__year=2026)` | without the combination "some unpaid" and "some 2026" | | `exclude(violations__in=Violation.objects.filter(paid=False, issued_on__year=2026))` | without any unpaid 2026 violation (the intended meaning) | ## Writing the intended query Describe the related rows you mean, then exclude permits linked to them: - **`__in` subquery.** `Permit.objects.exclude(violations__in=Violation.objects.filter(paid=False, issued_on__year=2026))`. This is the form Django's documentation gives. - **Correlated `Exists`.** `Permit.objects.exclude(Exists(Violation.objects.filter(permit=OuterRef('pk'), paid=False, issued_on__year=2026)))`, which many databases plan efficiently. - **Filter the other side.** `Permit.objects.exclude(pk__in=Violation.objects.filter(paid=False, issued_on__year=2026).values('permit'))`. All three express "no single violation with both properties". ## Why Django behaves this way For `filter()`, the rule "one call, one related row" maps naturally onto SQL: a single join to `violations` with both conditions in the `WHERE` clause. For `exclude()`, a join cannot express "the permit has no related row matching this", because excluding joined rows would just remove some violation rows and leave the permit in the result. Django therefore turns each condition on the multi-valued path into a `NOT IN (subquery)` style test on its own. Tying several conditions to the same related row would require combining them into one subquery, which Django does not do automatically, and the documentation records the difference instead of hiding it. ## Related traps worth naming - **`~Q(...)` inside `filter()`** goes through the same negation machinery as `exclude()`, so the same looseness applies to several negated conditions on one multi-valued path. - **Counting after chained filters**: annotations with `Count` over duplicated join rows inflate the numbers; that is an aggregation concern, but it starts with the extra joins described here. - **Mixing a positive and a negative condition** on the same relation, such as unpaid violations that are not from 2026, is clearest as one subquery of violations with both conditions, then `filter(violations__in=...)`. ## Checking and testing 1. Print `str(qs.query)` and read the subqueries: separate subqueries per condition signal the loose meaning. 2. Build a fixture with the tricky case, one permit with an unpaid 2025 violation and a paid 2026 violation, and assert which query returns it. 3. Remember the NULL side: permits with **no** violations at all are kept by all the `exclude()` forms above, which is usually what a clean-record search wants. ## Interview framing Interviewers ask this because it separates people who have read the query semantics from people who assume `exclude()` is `filter()` with a NOT in front. A strong answer states the rule for one `filter()` call, the different rule for chained calls, the documented `exclude()` exception, and the subquery fix.