Advanced ORM: Subqueries, Bulk & Raw SQL

QuerySets & Relationships taught the everyday ORM: lookups, Q, F, annotate() and the N+1 fix. Real reports ask harder questions. "Who borrowed each book last?" "Which members have an overdue loan?" "Label every book as in stock, low or out." "Give every English book a 10% Diwali discount without 500 separate UPDATE statements." A developer who answers these with Python loops ends up with slow pages; one who knows the ORM's advanced tools answers them in one or two queries.

The examples use the library from earlier lessons, now with a loans app (Member, and Loan with book, member, borrowed_on, due_on and returned_on), as set up in Structuring Large Django Projects. Ten loans exist, some returned and some still out. "Today" is 2 November 2026.

Subquery, OuterRef and Exists

A subquery is a query inside another query. OuterRef("pk") means "the primary key of the row in the outer query", so the inner query runs once per outer row, inside the database:

from django.db.models import Exists, OuterRef, Subquery

latest = Loan.objects.filter(book=OuterRef("pk")).order_by("-borrowed_on")
books = Book.objects.annotate(
    last_borrower=Subquery(latest.values("member__name")[:1]),
    last_borrowed=Subquery(latest.values("borrowed_on")[:1]),
)
for b in books:
    print(f"{b.title:24} {b.last_borrower or '-':6} {b.last_borrowed or ''}")

overdue = Loan.objects.filter(member=OuterRef("pk"), returned_on__isnull=True,
                              due_on__lt=date(2026, 11, 2))
print(Member.objects.filter(Exists(overdue)).order_by("name").values_list("name", flat=True))
print(Member.objects.filter(~Exists(overdue)).values_list("name", flat=True))

Gitanjali                -      
Godaan                   Isha   2026-10-25
Malgudi Days             -      
Nirmala                  Isha   2026-10-06
Swami and Friends        Rohan  2026-10-29
The Blue Umbrella        Meera  2026-10-30
The Home and the World   -      
<QuerySet ['Aarav', 'Isha', 'Meera']>
<QuerySet ['Rohan']>

A Subquery must return one column (values("member__name")) and at most one row ([:1]). Books never borrowed get None. Exists() asks only "is there at least one matching row?", which the database can answer quickly, and ~Exists() gives the opposite. This is usually faster and clearer than filter(loans__...) with distinct(), which returns a member once per matching loan unless you add distinct().

Conditional values with Case and When

Case and When are the ORM's version of SQL CASE: the first When that matches wins. Aggregates also accept a filter= argument, so one query can count different things:

from django.db.models import Case, Count, Q, Value, When

books = Book.objects.annotate(
    stock=Case(
        When(available=0, then=Value("Out")),
        When(available__lte=1, then=Value("Low")),
        default=Value("OK"),
    ),
    times_borrowed=Count("loans"),
    still_out=Count("loans", filter=Q(loans__returned_on__isnull=True)),
).order_by("-times_borrowed", "title")

for b in books[:4]:
    print(f"{b.title:18} {b.available}/{b.copies} {b.stock:3}  borrowed {b.times_borrowed}, out {b.still_out}")

Swami and Friends  2/5 OK   borrowed 4, out 3
Godaan             1/3 Low  borrowed 3, out 2
Nirmala            0/2 Out  borrowed 2, out 2
The Blue Umbrella  3/4 OK   borrowed 1, out 1

Without filter=, you would need two queries or a loop in Python. You can also filter and sort on the annotation, for example .filter(stock="Out") for a restock report.

Prefetch objects and loading less

prefetch_related("loans") loads every loan of each book. A Prefetch object lets you choose which related rows to load, how to order them, and which attribute to put them in:

from django.db.models import Prefetch

open_loans = (Loan.objects.filter(returned_on__isnull=True)
              .select_related("member").order_by("due_on"))
hindi = Book.objects.filter(language="hi").prefetch_related(
    Prefetch("loans", queryset=open_loans, to_attr="open_loans"))

with CaptureQueriesContext(connection) as ctx:     # counts queries, as in the N+1 lesson
    for book in hindi:
        print(book.title, [(loan.member.name, str(loan.due_on)) for loan in book.open_loans])
print(len(ctx.captured_queries), "queries")

Godaan [('Meera', '2026-11-03'), ('Isha', '2026-11-08')]
Nirmala [('Aarav', '2026-10-19'), ('Isha', '2026-10-20')]
2 queries

Two queries in total, whatever the number of books: one for the books, one for their open loans with members joined in. to_attr stores a plain list on each book, so book.loans.all() still means "all loans".

When a table has large columns you do not need, only("title", "price") loads just those fields (plus the primary key), and defer("summary") loads everything except the named ones. Touching a deferred field later costs one extra query per object, so use these only when you are sure what the page reads.

Bulk writes and custom QuerySets

Saving 500 objects in a loop means 500 queries. The bulk methods do the work in a few:

from decimal import Decimal

with CaptureQueriesContext(connection) as ctx:
    english = list(Book.objects.filter(language="en"))
    for b in english:
        b.price = (b.price * Decimal("0.90")).quantize(Decimal("0.01"))   # Diwali: 10% off
    print(Book.objects.bulk_update(english, ["price"]), "rows updated")
    Author.objects.bulk_create([Author(name=n) for n in ["Kamala Das", "Mahasweta Devi"]])
print([q["sql"].split()[0] for q in ctx.captured_queries])

4 rows updated
['SELECT', 'BEGIN', 'UPDATE', 'COMMIT', 'INSERT']

The second line lists the SQL statements Django sent: one SELECT, one UPDATE for all four books (inside a transaction) and one INSERT for both authors. Remember the trade-off from the last lesson: bulk methods do not call save() and do not send pre_save/post_save signals. When the change is a simple formula, filter(...).update(price=F("price") * Decimal("0.90")) is even better, with no SELECT at all.

Filters that you repeat across views belong on a custom QuerySet. Its methods chain like built-in ones:

# catalog/models.py
class BookQuerySet(models.QuerySet):
    def on_shelf(self):
        return self.filter(available__gt=0)

    def in_language(self, code):
        return self.filter(language=code)

    def with_loan_counts(self):
        return self.annotate(times_borrowed=models.Count("loans"))


class Book(models.Model):
    # ... fields as before
    objects = BookQuerySet.as_manager()


print(Book.objects.on_shelf().in_language("en").with_loan_counts()
      .order_by("title").values_list("title", "times_borrowed"))
print(Book.objects.filter(year__lt=1940).on_shelf().count())

<BookQuerySet [('Malgudi Days', 0), ('Swami and Friends', 4), ('The Blue Umbrella', 1), ('The Home and the World', 0)]>
4

The explicit order_by("title") matters: since Django 3.1, a model's Meta.ordering is ignored in queries that use GROUP BY, such as this one with Count.

Indexes and constraints

Rules that must never be broken belong in the database, not only in forms. Add them in Meta and create a migration:

class Meta:
    ordering = ["title"]
    indexes = [models.Index(fields=["language", "year"])]
    constraints = [
        models.CheckConstraint(
            condition=models.Q(available__lte=models.F("copies")),
            name="available_not_more_than_copies",
        ),
    ]

IntegrityError: CHECK constraint failed: available_not_more_than_copies
['Constraint “available_not_more_than_copies” is violated.']

The first line came from update(available=10) on Godaan: the database refused it, whatever code sent it. The second came from book.full_clean(), because model validation (and therefore ModelForms) checks constraints too and shows a friendly error. The index speeds up filters such as filter(language="en", year__gte=1930); QuerySet.explain() prints the database's plan so you can confirm an index is used.

Raw SQL, safely

The ORM covers most needs, but sometimes you need SQL directly: a database feature the ORM lacks, or a hand-tuned report. Django gives you Manager.raw(), which returns model objects, and connection.cursor() for anything else. In both, pass values as parameters, never inside the string:

from django.db import connection

for b in Book.objects.raw("SELECT id, title, year FROM catalog_book WHERE language = %s ORDER BY year", ["en"]):
    print(b.title, b.year)

with connection.cursor() as cursor:
    cursor.execute(
        """SELECT m.name, COUNT(*) FROM loans_loan AS l
           JOIN loans_member AS m ON m.id = l.member_id
           WHERE l.returned_on IS NULL
           GROUP BY m.name HAVING COUNT(*) >= %s ORDER BY m.name""",
        [2],
    )
    print(cursor.fetchall())

attack = "en' OR '1'='1"
safe = Book.objects.raw("SELECT id, title FROM catalog_book WHERE language = %s", [attack])
unsafe = Book.objects.raw(f"SELECT id, title FROM catalog_book WHERE language = '{attack}'")
print(len(list(safe)), len(list(unsafe)))

The Home and the World 1916
Swami and Friends 1935
Malgudi Days 1943
The Blue Umbrella 1980
[('Aarav', 2), ('Isha', 2), ('Meera', 3)]
0 7

The last line is the whole lesson of SQL injection: with a parameter, the attack text is treated as a strange language code and matches nothing. Pasted into an f-string, it rewrote the query and returned all seven books. Write %s without quotes around it, whatever the database; the driver adds the quoting.

Common mistakes

• A Subquery that returns several rows or columns. Always use values("one_field") and [:1], or the database raises an error.
• Sorting a GROUP BY query by Meta.ordering. It is ignored there; add order_by() yourself.
• Using only() and then reading other fields in the template. Each deferred field read is a new query per object, which brings back N+1.
• Expecting bulk_update() or update() to run save() logic. They do not; put such logic in a service that also handles bulk cases.
• Building raw SQL with f-strings or % formatting. Always pass a parameter list.

Key points

• Subquery with OuterRef puts a per-row query inside one SQL statement; Exists tests for related rows efficiently.
• Case/When produce conditional values, and Count(..., filter=Q(...)) counts subsets in the same query.
• Prefetch(queryset=..., to_attr=...) controls which related rows are loaded; only() and defer() load fewer columns.
• bulk_create(), bulk_update() and update() replace loops of save(), but skip save() and signals.
• Custom QuerySets with as_manager() give reusable, chainable filters; CheckConstraint and Index belong in Meta.
• Raw SQL is fine when needed, with values passed as parameters, never formatted into the string.

from datetime import date
from django.db.models import Count, OuterRef, Q, Subquery

today = date(2026, 11, 2)
latest = Loan.objects.filter(member=OuterRef("pk")).order_by("-borrowed_on")
members = Member.objects.annotate(
    open_loans=Count("loans", filter=Q(loans__returned_on__isnull=True)),
    overdue=Count("loans", filter=Q(loans__returned_on__isnull=True, loans__due_on__lt=today)),
    last_title=Subquery(latest.values("book__title")[:1]),
).order_by("-overdue", "name")
for m in members:
    print(m.name, m.open_loans, m.overdue, m.last_title)

Aarav 2 1 Swami and Friends
Isha 2 1 Godaan
Meera 3 1 The Blue Umbrella
Rohan 1 0 Swami and Friends