Skillversitas

QuerySets and Related Lookups

The QuerySet is the full: the filter the chain (the 1 the SQL, the lazy), the aggregate (the COUNT, the SUM, the Avg the SQL), the annotation (the compute the row, the group_by), and the select_related plus the prefetch_related (the N+1 the fix). The related lookup is the filter (the author__name="x", the join the auto), and the no is the 2 (the select_related the FK, the prefetch the M2M). This chapter covers the filter (the 15 the lookup, the chain), the aggregate (the 5 the function), the annotation (the row, the group), and the N+1 (the select_related, the prefetch_related, the explain).

The filter: The 15 lookup

# the LOOKUP (the 15, the where's the
#   full):
Note.objects.filter(
    title__icontains="x")      #   the LIKE
Note.objects.filter(
    title__startswith="x")     #   the prefix
Note.objects.filter(
    created__year=2024)        #   the extract
Note.objects.filter(
    created__date=date(
        2024, 1, 1))           #   the day
Note.objects.filter(
    pk__in=[1, 2, 3])          #   the IN
Note.objects.filter(
    title__isnull=True)        #   the IS NULL
Note.objects.filter(
    views__gte=10)             #   the >=
Note.objects.filter(
    body__contains="text")     #   the substring
Note.objects.exclude(
    published=False)           #   the NOT
Note.objects.order_by(
    "views", "-created")       #   the 2 col
#   the the 3: the chain (the 1 SQL),
#     the Q (the OR the AND, the no
#     the the no), the raw (the no,
#     the no, the no)
#   the Q (the OR, the no):
from django.db.models import Q
Note.objects.filter(
    Q(title__icontains="a") |
    Q(body__icontains="b"))

The filter is the 15 lookup: the exact (the =), the icontains (the LIKE the case the no), the startswith, the year/month/day (the extract), the date, the in (the IN), the isnull, the gte/lte/gt/lt, the range, and the contains - the chain is the 1 the SQL (the filter the order_by the 1 the WHERE the ORDER BY, the no the 2), the exclude is the NOT, and the Q is the OR (the |, the &, the ~, the no the filter the OR) - the aggregate is the next: the COUNT the SUM the Avg the SQL the 1, the no the Python the loop.

The aggregate, the annotation, and the N+1

# the AGGREGATE (the 5, the SQL's):
from django.db.models import (
    Avg, Count, Max, Min, Sum)
Note.objects.aggregate(
    n=Count("id"),
    avg=Avg("views"),
    max=Max("views"),
    min=Min("views"),
    total=Sum("views"))
#   the 1 (the COUNT, the 5 the 1
#     the SQL, the no the loop)

# the ANNOTATION (the row, the compute):
Note.objects.annotate(
    word_count=Length("body"))
#   the SELECT body, LENGTH(body) (the
#     the row, the no the Python)
Note.objects.annotate(
    day=TruncDate("created")).values(
    "day").annotate(n=Count("id"))
#   the group by (the day, the count,
#     the 1 the SQL)

# the N+1 (the for, the related):
#   the BAD (the 1 + the N the SQL):
for post in Post.objects.all():
    print(post.author.name)   #   the 1
                              #     the author per
#   the GOOD (the 2 the SQL):
for post in Post.objects.select_related(
    "author").all():
    print(post.author.name)   #   the no
                              #     the extra
#   the M2M (the prefetch):
for post in Post.objects.prefetch_related(
    "tags").all():
    print([t.name for t in
        post.tags.all()])

The aggregate is the 5: the Count, the Sum, the Avg, the Max, the Min (the 1 the SQL, the no the Python the loop, the no the load the row). The annotation is the row: the Length("body") the compute (the LENGTH the SQL), and the TruncDate plus the values plus the annotate is the group_by (the day the count, the 1 the SQL, the no the Python the dict). The N+1 is the for the related: the 1 the post plus the N the author (the 500 the 501 the SQL), and the fix is the 2: the select_related (the FK the O2O, the JOIN the 1 the SQL) and the prefetch_related (the M2M, the 2 the SQL the IN, the Python the match) - the explain is the check (the Query log the no the SELECT, the N+1 the see), and the rule is the one: the filter the chain the 1 the SQL, the aggregate the SQL the no the loop, the select_related the FK, the prefetch the M2M - the next chapter's admin is the CRUD the auto the 1 the line.

The Working Rules

  • The filter is the 15 lookup, the chain the 1 SQL. The exact the =, the icontains the LIKE (the case the no), the startswith, the year the extract, and the in the IN - the chain the 1 (the WHERE, the ORDER BY), the exclude the NOT, and the Q the OR (the | the & the ~, the no the filter the OR).
  • The aggregate is the 5, the SQL the no loop. The Count the Sum the Avg the Max the Min (the 1 the SQL, the no the Python) - the values the no the object (the dict, the fast, the no the row), and the only the field (the no the *, the index the use).
  • The annotation is the row, the group the values. The Length the compute (the SQL, the no the Python), and the TruncDate plus the values plus the annotate the group_by (the day the count, the 1 the SQL) - the no the aggregate the row (the 2 the no, the separate the 1).
  • The N+1 is the for, the select_related the prefetch. The 1 + the N the SQL (the 500 the 501), and the select_related the FK (the JOIN, the 1) - the prefetch_related the M2M (the 2 the SQL, the IN, the Python the match), and the explain the check (the Query log, the no the SELECT the see).

Summary

The QuerySet is the full: the filter the 15 lookup (the exact, the icontains, the startswith, the year the extract, the in, the isnull, the gte, the contains, the chain the 1 the SQL, the Q the OR), the aggregate the 5 (the Count the Sum the Avg the Max the Min, the SQL the no the loop), the annotation the row (the Length the compute, the TruncDate plus the values the group_by, the 1 the SQL), and the N+1 the fix (the select_related the FK the JOIN, the prefetch_related the M2M the 2 the SQL, the explain the check). The rule is the one: the filter the chain, the aggregate the SQL, the annotation the row, and the select_related the prefetch_related the N+1 - the next chapter's admin is the CRUD the auto: the register the 1 line, the ModelAdmin the 3 option, the search the filter, and the permission the require the superuser the no the 403.