In Django's ORM, how do you use F() in filter() to compare two columns of the same row, such as permits over their vehicle limit?

middleshould knowfreq 50%

answer

reference, not a value
compared inside the database
arithmetic with constants and durations
can cross relations too

basics

~20 s

F('field') refers to a column inside the query instead of a Python value, so filter(vehicles_registered__gt=F('vehicles_allowed')) compares two columns of each row in SQL. F() supports arithmetic and relation paths, such as F('valid_from') + timedelta(days=365) or F('permit__valid_from').

solid answer

~40 s

F() from django.db.models is a reference to a model field that the database resolves per row. Permit.objects.filter(vehicles_registered__gt=F('vehicles_allowed')) becomes WHERE vehicles_registered > vehicles_allowed, with no rows loaded into Python. You can do arithmetic, as in F('vehicles_allowed') + 1, and date arithmetic, as in valid_until__gt=F('valid_from') + timedelta(days=365) for permits longer than a year; Django infers that a DateField plus a duration yields a datetime. F can follow relations: Violation.objects.filter(issued_on__lt=F('permit__valid_from')) joins to Permit. Any lookup works on the left, and comparisons with a NULL column are unknown, so such rows do not match. The same F() makes update() race-free, but that is a write-path concern.

`F()` from `django.db.models` is a reference to a model field that the database resolves per row. `Permit.objects.filter(vehicles_registered__gt=F('vehicles_allowed'))` becomes `WHERE vehicles_registered > vehicles_allowed`, with no rows loaded into Python. You can do arithmetic, as in `F('vehicles_allowed') + 1`, and date arithmetic, as in `valid_until__gt=F('valid_from') + timedelta(days=365)` for permits longer than a year; Django infers that a `DateField` plus a duration yields a datetime. F can follow relations: `Violation.objects.filter(issued_on__lt=F('permit__valid_from'))` joins to Permit. Any lookup works on the left, and comparisons with a NULL column are unknown, so such rows do not match. The same `F()` makes `update()` race-free, but that is a write-path concern.

code

python · 15 lines

python

from datetime import timedelta

from django.db.models import F

from permits.models import Permit, Violation

over_limit = Permit.objects.filter(vehicles_registered__gt=F('vehicles_allowed'))

longer_than_a_year = Permit.objects.filter(
    valid_until__gt=F('valid_from') + timedelta(days=365),
)

bad_dates = Permit.objects.filter(valid_until__lt=F('valid_from'))   # NULL valid_until never matches

before_permit = Violation.objects.filter(issued_on__lt=F('permit__valid_from'))

go deeper

for a junior

Recall that F('field') lets a filter compare one column with another column of the same row.

for a middle

Explain that the comparison happens in SQL, how arithmetic and relation paths work inside F(), and how NULL affects matches.

for a senior

Use F() to push rule checks into the database, handle type inference with ExpressionWrapper where needed, and watch multi-valued joins for duplicates.

for a principal

Decide which business rules are enforced as queries with F() versus check constraints, so invalid rows are prevented rather than merely found.

What F() is

django.db.models.F creates an expression that names a field. Where a filter would normally take a Python value, you can pass F('other_field'), and Django puts the column reference into the SQL instead of a bound parameter. The comparison then happens inside the database, row by row, and nothing has to be fetched into Python first.

Without F(), comparing two columns means loading every row and filtering in a loop, which is slower and reads data that may already be stale.

Comparing columns of the same row

For a Permit with vehicles_allowed, vehicles_registered, valid_from and a nullable valid_until:

filter(vehicles_registered__gt=F('vehicles_allowed')) finds permits over their vehicle limit.
filter(valid_until__lt=F('valid_from')) finds data-entry errors where the end precedes the start.
filter(vehicles_registered=F('vehicles_allowed')) finds permits exactly at the limit.

Any lookup can take an F() on the right: __gt, __lte, __exact, __iexact and others.

Arithmetic

F() supports +, -, *, /, % and ** with constants and with other expressions:

Numbers. filter(vehicles_registered__gte=F('vehicles_allowed') - 1) finds permits one slot from the limit.
Dates and durations. filter(valid_until__gt=F('valid_from') + timedelta(days=365)) finds permits longer than a year. Django resolves DateField + DurationField to a DateTimeField output on its own; when mixing types it cannot infer, wrap the expression in ExpressionWrapper(..., output_field=...).
Two columns. F('vehicles_allowed') - F('vehicles_registered') gives free slots, usable in a filter or an annotation.

Crossing relations

The double-underscore path works inside F() too:

Violation.objects.filter(issued_on__lt=F('permit__valid_from')) finds violations dated before their permit started, joining Violation to Permit.
Permit.objects.filter(valid_from__lt=F('zone__opened_on')) compares a permit column with its zone's column.

When the path goes through a multi-valued relation, the comparison is made per joined row, so a permit can appear once per matching related row.

NULL and F()

A comparison that involves NULL is unknown in SQL, and unknown rows do not match a filter. So filter(valid_until__lt=F('valid_from')) silently skips open-ended permits whose valid_until is NULL, which here is desired. If NULL should count, add an explicit Q(valid_until__isnull=True) branch or wrap the column in Coalesce.

What F() is not

It is not evaluated in Python: F('vehicles_allowed') > 3 in plain Python code does not give a boolean you can use.
It does not load the field's current value into the instance; it only appears in SQL.
It is the same class used in update(vehicles_registered=F('vehicles_registered') + 1), which avoids read-modify-write races; that write-side use is a separate topic from filtering.

F() in other query methods

The same field reference works outside filter():

exclude(): exclude(vehicles_registered__gt=F('vehicles_allowed')) keeps permits within their limit, with the usual NULL handling for negated lookups.
annotate(): annotate(free_slots=F('vehicles_allowed') - F('vehicles_registered')) adds a computed attribute to each permit, which later filters can use: .filter(free_slots__lt=2).
order_by(): order_by(F('valid_until').asc(nulls_last=True)) sorts open-ended permits last, something a plain field name cannot express.
Q objects: Q(vehicles_registered__gt=F('vehicles_allowed')) | Q(status='suspended') mixes column comparisons with ordinary conditions.

Type inference

When Django combines expressions it must know the result type to convert values and choose SQL. It infers common combinations itself: integer with integer gives integer, integer with float gives float, a date plus a duration gives a datetime. When it cannot infer the type it raises FieldError asking for an output_field, and ExpressionWrapper supplies it.

Comparison with doing it in Python

Approach	Rows transferred	Correct under concurrent writes
`[p for p in Permit.objects.all() if p.vehicles_registered > p.vehicles_allowed]`	all	only as of the read
`Permit.objects.filter(vehicles_registered__gt=F('vehicles_allowed'))`	only matches	evaluated in one statement

The F() version is shorter, lets the database use indexes and statistics, and moves only the permits that actually break the rule.

## What F() is `django.db.models.F` creates an **expression** that names a field. Where a filter would normally take a Python value, you can pass `F('other_field')`, and Django puts the column reference into the SQL instead of a bound parameter. The comparison then happens **inside the database**, row by row, and nothing has to be fetched into Python first. Without `F()`, comparing two columns means loading every row and filtering in a loop, which is slower and reads data that may already be stale. ## Comparing columns of the same row For a `Permit` with `vehicles_allowed`, `vehicles_registered`, `valid_from` and a nullable `valid_until`: - `filter(vehicles_registered__gt=F('vehicles_allowed'))` finds permits over their vehicle limit. - `filter(valid_until__lt=F('valid_from'))` finds data-entry errors where the end precedes the start. - `filter(vehicles_registered=F('vehicles_allowed'))` finds permits exactly at the limit. Any lookup can take an `F()` on the right: `__gt`, `__lte`, `__exact`, `__iexact` and others. ## Arithmetic `F()` supports `+`, `-`, `*`, `/`, `%` and `**` with constants and with other expressions: 1. **Numbers.** `filter(vehicles_registered__gte=F('vehicles_allowed') - 1)` finds permits one slot from the limit. 2. **Dates and durations.** `filter(valid_until__gt=F('valid_from') + timedelta(days=365))` finds permits longer than a year. Django resolves `DateField + DurationField` to a `DateTimeField` output on its own; when mixing types it cannot infer, wrap the expression in `ExpressionWrapper(..., output_field=...)`. 3. **Two columns.** `F('vehicles_allowed') - F('vehicles_registered')` gives free slots, usable in a filter or an annotation. ## Crossing relations The double-underscore path works inside `F()` too: - `Violation.objects.filter(issued_on__lt=F('permit__valid_from'))` finds violations dated before their permit started, joining `Violation` to `Permit`. - `Permit.objects.filter(valid_from__lt=F('zone__opened_on'))` compares a permit column with its zone's column. When the path goes through a **multi-valued** relation, the comparison is made per joined row, so a permit can appear once per matching related row. ## NULL and F() A comparison that involves NULL is **unknown** in SQL, and unknown rows do not match a filter. So `filter(valid_until__lt=F('valid_from'))` silently skips open-ended permits whose `valid_until` is NULL, which here is desired. If NULL should count, add an explicit `Q(valid_until__isnull=True)` branch or wrap the column in `Coalesce`. ## What F() is not - It is **not** evaluated in Python: `F('vehicles_allowed') > 3` in plain Python code does not give a boolean you can use. - It does **not** load the field's current value into the instance; it only appears in SQL. - It is the same class used in `update(vehicles_registered=F('vehicles_registered') + 1)`, which avoids read-modify-write races; that write-side use is a separate topic from filtering. ## F() in other query methods The same field reference works outside `filter()`: - **`exclude()`**: `exclude(vehicles_registered__gt=F('vehicles_allowed'))` keeps permits within their limit, with the usual NULL handling for negated lookups. - **`annotate()`**: `annotate(free_slots=F('vehicles_allowed') - F('vehicles_registered'))` adds a computed attribute to each permit, which later filters can use: `.filter(free_slots__lt=2)`. - **`order_by()`**: `order_by(F('valid_until').asc(nulls_last=True))` sorts open-ended permits last, something a plain field name cannot express. - **`Q` objects**: `Q(vehicles_registered__gt=F('vehicles_allowed')) | Q(status='suspended')` mixes column comparisons with ordinary conditions. ## Type inference When Django combines expressions it must know the result type to convert values and choose SQL. It infers common combinations itself: integer with integer gives integer, integer with float gives float, a date plus a duration gives a datetime. When it cannot infer the type it raises `FieldError` asking for an `output_field`, and `ExpressionWrapper` supplies it. ## Comparison with doing it in Python | Approach | Rows transferred | Correct under concurrent writes | |---|---|---| | `[p for p in Permit.objects.all() if p.vehicles_registered > p.vehicles_allowed]` | all | only as of the read | | `Permit.objects.filter(vehicles_registered__gt=F('vehicles_allowed'))` | only matches | evaluated in one statement | The `F()` version is shorter, lets the database use indexes and statistics, and moves only the permits that actually break the rule.

How would you find permits whose free slots are below 10% of the allowance?
Compare expressions on both sides: `Permit.objects.filter(vehicles_registered__gt=F('vehicles_allowed') * 0.9)`. Django infers the result type of integer times float as a float, so no wrapper is needed; `ExpressionWrapper(..., output_field=...)` is only required when mixing types Django cannot infer, which it reports with a `FieldError` asking for an `output_field`.
Why is filtering with F() safer than comparing values you read earlier in Python?
The F() comparison runs inside one SQL statement against the values the database holds at that moment. Reading `permit.vehicles_allowed` first and filtering later compares against a value that another request may already have changed. For reads the difference is freshness; for writes, the same idea is what makes `update()` with `F()` race-free.

saying these in an interview costs you the question

F('field') loads the field's current value into Python first
Comparing two columns requires raw SQL in Django
F() cannot follow foreign keys with double underscores
A NULL column compared with F() counts as a match
F() only works inside update(), not in filter()

In Django's ORM, how do you use F() in filter() to compare two columns of the same row, such as permits over their vehicle limit?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

What F() is

Comparing columns of the same row

Arithmetic

Crossing relations

NULL and F()

What F() is not

F() in other query methods

Type inference

Comparison with doing it in Python

▾common follow-ups2 questions

▾red flags5 flags

What F() is

Comparing columns of the same row

Arithmetic

Crossing relations

NULL and F()

What F() is not

F() in other query methods

Type inference

Comparison with doing it in Python

answer

go deeper