Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 77 ยท Models and the ORM

Lookups, Q Objects and F Expressions

Filter precisely with Django lookups such as __contains, __in and __range, combine conditions with Q objects, and compare or update columns with F expressions.

In QuerySets: Create, Read, Update, Delete you filtered by equality: filter(author="Jane Austen"). Real questions are richer: books whose title contains a word, products cheaper than 25 or in the "home" category, items whose stock has fallen below their own reorder level. Django answers these with three tools that build on each other.

  1. Field lookups change how a field is compared: price__lt=10, name__icontains="desk".
  2. Q objects combine conditions with OR and NOT, which keyword arguments alone cannot express.
  3. F expressions refer to the value of a column inside the database, so you can compare two columns or update a value without reading it into Python first.

All three become SQL, so the database does the work instead of your Python loop. The examples use a Product model with a name, category, price, stock, a reorder level, an added date and an optional note. As before, each editor contains its own setup so you can run it alone.

Field lookups

A lookup is written as the field name, two underscores, and the lookup name: field__lookup=value. Without a lookup, exact is assumed. The lookups you will use most:

Lookup Meaning SQL idea
exact, iexact Equal, case-insensitive equal =, LIKE
contains, icontains Substring, case-insensitive substring LIKE '%x%'
startswith, endswith (and i versions) Prefix or suffix LIKE 'x%'
in Value is in a list IN (...)
gt, gte, lt, lte Comparisons >, >=, <, <=
range Between two values, inclusive BETWEEN a AND b
isnull Is or is not NULL IS NULL
year, month, day, date Parts of date fields extracted parts
regex, iregex Regular expression database regex

Chained keyword arguments in one filter() call are joined with AND, and exclude() negates the whole condition. The first example runs many of these lookups on the same six products:

Python A tour of field lookups

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A few observations:

  • contains and icontains give different answers in some databases, but SQLite's LIKE ignores case for ASCII letters, so both found the same kind of matches here. On PostgreSQL contains is case-sensitive. Use the i versions when you mean "ignore case" and the result will be the same everywhere.
  • range=(8, 25) includes both ends, so the 8.00 mug and the 24.50 lamp are both in. For dates, the pair gte / lt (the half-open interval) is the safest way to select a month or a year, because it works for datetimes too.
  • isnull=True is how you ask "has no value". Comparing with None through exact works too, but isnull is clearer.
  • date__year=2024 style lookups (here added__year) work on DateField and DateTimeField. On datetimes, they respect the time zone when USE_TZ is on.
  • The printed SQL fragment at the end shows both conditions joined with AND, and price__range turned into BETWEEN.

Lookups are also how you cross relationships (author__name__startswith="J"), which you will meet in Relationships.

Q objects: OR and NOT

Keyword arguments in filter() can only be ANDed together. To say "category is home or price is below 10", wrap each condition in a Q object and combine them with the operators | (OR), & (AND) and ~ (NOT). A Q is just a reusable description of a condition: it runs nothing until you hand it to filter(), exclude() or get().

flowchart TD
    Q["Q(category='office') | Q(category='home')"] --> AND1["&"]
    N["~Q(stock=0)"] --> AND1
    AND1 --> AND2["&"]
    P["Q(price__lt=100)"] --> AND2
    AND2 --> F["Product.objects.filter(...)"]
    F --> S["WHERE (office OR home)<br/>AND NOT stock = 0 AND price below 100"]

Q objects form a tree of conditions that Django translates into one WHERE clause.

The order of operations follows Python: & binds tighter than |, so use parentheses (as the diagram does) whenever you mix them. Q objects may be mixed with keyword arguments in the same call, but the Q objects must come first.

Python Combining conditions with Q

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The most useful pattern is the last one: starting from an empty Q() and adding conditions with &= only when the user filled in a search field. That is how a search form with optional filters becomes a few clear lines instead of a tangle of if statements and repeated .filter() calls. An empty Q() is neutral, so search() with no arguments returned all six products.

Be careful with exclude() and ~Q() when a column can be NULL. SQL treats NOT (category = 'office') as unknown for a row where the category is NULL, and Django adds extra IS NOT NULL handling in most cases to behave as you would expect. When results look wrong, print str(queryset.query) and read the SQL.

F expressions: columns as values

Sometimes the value you want to compare or write is not a Python value but another column. F("reorder_level") is a placeholder that means "the value of this column, in the database". It unlocks four things:

  1. Comparing columns: filter(stock__lte=F("reorder_level")).
  2. Computing on the fly: annotate(shortfall=F("reorder_level") - F("stock")) adds a calculated attribute to each result.
  3. Updating from the current value: update(price=F("price") + 2) is a single UPDATE with no read.
  4. Race-free changes: the database performs the arithmetic atomically, so two requests cannot overwrite each other.

The fourth point is the one that matters in production. A classic bug is read-modify-write: your code loads an object, adds one to a counter in Python and saves it. If another request changes the counter between your read and your write, your save writes a stale number and the other change is lost.

Python F expressions and the lost update

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Here is how to read it:

  • The first filter compared two columns of each row. Monitor Arm sits exactly at its reorder level, which is why lte includes it.
  • The annotation shortfall is not stored anywhere; it was computed by the database for each row and appears as an attribute on the results. The Mug is out of stock and needs 10 more.
  • update(price=F("price") + Decimal("2.00")) raised all office prices in one statement.
  • The read-modify-write block shows the race on purpose: after another request sold one mug (23 left), saving the stale object wrote 25, so the sale vanished. With F("stock") + 1 the database added to its current value and the answer was correct, 24.
  • When you assign an expression to an instance attribute and save it, the attribute holds an expression rather than a number. Call refresh_from_db() to load the real value (newer Django versions on SQLite and PostgreSQL refresh it for you, but calling refresh_from_db() is always safe).
  • F also works in ordering. F("note").asc(nulls_last=True) sorts the products with a note first, in note order, and the products without one last.

For counters that can be hit by many requests (page views, stock, likes), reach for F() or, when logic gets complex, a transaction with row locking (see Transactions and Concurrency). Counting and summarizing rows are covered next, in Aggregation and Annotation.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which filter finds products whose name contains 'desk' regardless of case?

    icontains is a case-insensitive substring match. iexact requires the whole value to match, and contains may be case-sensitive depending on the database.
  2. 2. How do you express 'category is home OR price is below 10'?

    Keyword arguments and chained filters are ANDed. OR requires Q objects combined with |. Python's 'or' would evaluate before Django sees it.
  3. 3. What is the benefit of stock=F('stock') - 1 over loading the object, subtracting 1 in Python and saving it?

    F() makes the database compute the new value in a single UPDATE, avoiding the read-modify-write race where a stale Python value overwrites someone else's change.
  4. 4. Which statement about Q objects and keyword arguments in the same filter() call is correct?

    Positional Q objects come first, followed by keyword arguments; all of them are ANDed together.
  5. 5. What does price__range=(8, 25) select?

    range translates to BETWEEN, which includes both end values.

Summary

  • A lookup is field__lookup=value. Common ones are exact, icontains, startswith, in, gt/lt, range, isnull and date parts such as year.
  • Several conditions in one filter() call are ANDed; exclude() negates; chained filter() calls on single-valued fields are ANDed as well.
  • Q objects combine conditions with |, & and ~, can be nested with parentheses, and are ideal for building optional filters dynamically from Q().
  • F("column") refers to a column's value inside the database: compare columns, annotate calculations and update fields atomically.
  • Prefer F() to read-modify-write in Python for counters and stock levels, and print str(qs.query) when you are unsure what SQL a filter produces.

Next, connect models to each other with Relationships: ForeignKey, ManyToMany, OneToOne.