Django · Lesson 8 of 15

The ORM in Depth

select_related, aggregations, F expressions, transactions and indexes.

Intermediate
17 min read
3 objectives

Before this lessonLesson 7: Class-Based Views

What you will learn

Kill N+1 queries
Aggregate and annotate
Wrap a transaction

Your Progress

0 of 15 lessons 0%

Lessons0 / 15
Completed0
Est. time left~ 4 hours

Create a free account to keep your progress on every device.

The ORM will happily issue a query per row if you let it. Production Django is mostly about asking for the right related data, aggregating in the database, and wrapping writes in transactions.

select_related and prefetch_related

# ForeignKey / OneToOne: JOIN in the same query
Post.objects.select_related("author").all()

# ManyToMany / reverse FK: a second query, then stitch in Python
Post.objects.prefetch_related("tags", "comment_set")

Looping over post.author.name without select_related is the classic N+1.

Aggregate, annotate, F

from django.db.models import Count, F, Q

Post.objects.aggregate(n=Count("id"))
Post.objects.annotate(n_comments=Count("comment")).filter(n_comments__gt=0)
Post.objects.filter(Q(title__icontains="django") | Q(body__icontains="orm"))
Product.objects.update(price=F("price") * 11 / 10)   # 10% rise in SQL

Transactions

from django.db import transaction

@transaction.atomic
def transfer(from_id, to_id, amount):
    a = Account.objects.select_for_update().get(id=from_id)
    b = Account.objects.select_for_update().get(id=to_id)
    a.balance -= amount
    b.balance += amount
    a.save()
    b.save()

Indexes

class Post(models.Model):
    slug = models.SlugField(unique=True)
    published = models.BooleanField(db_index=True)
    class Meta:
        indexes = [models.Index(fields=["author", "-created"])]

# Write your solution here

Up next · Lesson 9Middleware, Signals and SettingsThe request pipeline, decoupling with signals and splitting settings.