QuerySets and filtering

A QuerySet is how you read data with the ORM — Django's way of expressing "give me these rows" in Python instead of SQL. Learning to think in querysets is learning to use Django, because every view, API and report you write pulls its data through one. This lesson covers the core operations and, crucially, the one property that surprises everyone and explains most queryset confusion: querysets are lazy.

The manager and the queryset

Every model has an objects manager — the entry point to querying it. From it you get querysets:

Patient.objects.all()                       # every patient — a QuerySet
Patient.objects.filter(city="Pune")         # patients in Pune — a QuerySet
Patient.objects.get(id=1)                    # exactly ONE patient (not a QuerySet)

filter(), exclude(), all() return querysets (zero or more rows). get() returns a single object and is different: it raises DoesNotExist if nothing matches and MultipleObjectsReturned if more than one does. Use get() when you expect exactly one (by primary key, say) and want a clear error otherwise; use filter() when you expect a set.

Filtering, and field lookups

filter() narrows to rows matching conditions; exclude() does the opposite. Conditions use field lookups — a double-underscore syntax that maps to SQL operators:

Patient.objects.filter(city="Pune").count()                 # verified: 3
Patient.objects.exclude(city="Pune").count()                # verified: 2  (everyone not in Pune)
Patient.objects.filter(name__icontains="3").count()         # verified: 1  (name contains "3", case-insensitive)
Appointment.objects.filter(status=Appointment.Status.BOOKED).count()   # verified: 5

The field__lookup=value form is the ORM's query language. The lookups you will use constantly:

You can also follow relationships with __: Appointment.objects.filter(patient__city="Pune") filters appointments by their patient's city, generating the SQL join for you. Multiple keyword arguments to one filter() are combined with AND: filter(city="Pune", status="booked") means both.

Querysets are lazy — the thing that surprises everyone

This is the single most important queryset fact. A queryset does not hit the database when you create it. It hits the database only when you actually need the data. So this line runs no SQL:

pune = Patient.objects.filter(city="Pune")     # NO database query yet

The query runs when the queryset is evaluated — when you iterate it, call list() on it, index it, len()/count() it, or check it in a boolean context:

for p in pune:        # NOW the query runs
    print(p.name)
count = pune.count()  # a query (a SQL COUNT)
first = pune.first()  # a query (LIMIT 1)

Laziness is a feature: it lets you build up a query in stages without paying for intermediate steps. Each filter returns a new queryset and still runs nothing:

qs = Patient.objects.all()          # no query
qs = qs.filter(city="Pune")         # no query — chaining is free
qs = qs.exclude(phone="")           # no query
qs = qs.order_by("name")            # no query
results = list(qs)                  # ONE query, with all of the above combined

All those refinements collapse into one efficient SQL statement when finally evaluated. This is why you can pass a queryset around, add filters conditionally, and compose it freely — nothing happens until the end. The flip side, and a real performance trap: evaluating the same queryset repeatedly re-runs the query each time. If you need the results more than once, evaluate once into a list (or slice), rather than iterating the queryset three times and firing three queries.

Ordering, slicing, and getting one

Appointment.objects.order_by("-scheduled_for")       # newest first (- means descending)
Appointment.objects.order_by("-scheduled_for").first().patient.name   # verified: "Patient4"
Patient.objects.all()[:10]                            # first 10 (SQL LIMIT — efficient, still lazy)
Patient.objects.filter(city="Pune").exists()          # cheap boolean: does any match?

order_by sorts (prefix - for descending). Slicing maps to SQL LIMIT/OFFSET, so [:10] fetches only ten rows, not all-then-trim. first()/last() return one object or None. exists() is the efficient way to ask "is there any?" — cheaper than count() or loading rows when you only need yes/no.

Creating and updating through the ORM

Reading is most of it, but the ORM writes too:

p = Patient.objects.create(name="Asha", phone="09812345678", city="Pune")  # create + save
p.city = "Mumbai"; p.save()                          # update one object
Patient.objects.filter(city="Pune").update(city="Pune City")  # bulk update — one SQL UPDATE
Patient.objects.filter(status="cancelled").delete()  # bulk delete

create() makes and saves in one step. .save() persists changes to an object you hold. .update() on a queryset is a bulk operation — one SQL UPDATE for all matching rows, far more efficient than looping and saving each (and the ORM-in-depth module returns to why bulk operations matter for performance).

Check your work

Manager versus queryset versus get. objects is the manager; filter/exclude/all return querysets (sets of rows); get returns one object and raises DoesNotExist/MultipleObjectsReturned.

Field lookups. field__lookup=value maps to SQL: icontains, gte/lte, in, isnull, etc.; __ also follows relationships (patient__city); multiple kwargs in one filter are AND.

Why querysets are lazy. Creating/chaining a queryset runs no SQL; it executes only on evaluation (iteration, list(), count(), first(), boolean). Chained filters collapse into one query.

The repeated-evaluation trap. Re-evaluating the same queryset re-runs the query; if you need results more than once, evaluate once into a list.

Ordering and slicing. order_by("-field") sorts descending; [:10] is SQL LIMIT (efficient); exists() is the cheap "is there any?".

Writing through the ORM. create() (make+save), .save() (one object), .update()/.delete() on a queryset (bulk, one SQL statement).

Practice

1. In the shell, create several patients across cities. Reproduce the verified counts: filter(city="Pune") → 3, exclude(city="Pune") → 2.
2. Try Patient.objects.get(city="Pune") when three match; read MultipleObjectsReturned. Then get(id=…) for a real id and a missing one; read DoesNotExist.
3. Assign qs = Patient.objects.filter(city="Pune") and, using django.db.connection.queries (with DEBUG=True), confirm no query ran until you iterate qs.
4. Chain filter().exclude().order_by() into one queryset; evaluate once and confirm (via query logging) it is a single SQL statement.
5. Follow a relationship: Appointment.objects.filter(patient__city="Pune"). Confirm the count matches the Pune patients' appointments.
6. Use .update() to change a field on all Pune patients in one call, and confirm via query logging it was one UPDATE, not several.

Official documentation

• Django — Making queries — The narrative guide to querysets.
• Django — QuerySet API reference — Every method and when the query is evaluated.
• Django — Field lookups — The full lookup list.

Next: select_related, prefetch_related and the N+1 problem.