Django N+1 Queries

# ======================================================================
# Q: How do you detect and prevent N+1 queries?
# ======================================================================

def test_order_list_queries(api_client, django_assert_num_queries):
    OrderFactory.create_batch(20, items=3)
    with django_assert_num_queries(3):   # auth + orders + items
        api_client.get('/api/orders/')

# ======================================================================
# Q: How do you fix an N+1 query problem in Django?
# ======================================================================

from django.db.models import Prefetch

orders = (Order.objects
          .select_related('customer')                       # JOIN (FK / OneToOne)
          .prefetch_related(
              Prefetch('items',
                       queryset=OrderItem.objects.select_related('product')))
          [:100])
# Total: 2 queries (orders JOIN customer, items JOIN product WHERE order_id IN (...))

# ======================================================================
# Q: What does an N+1 query problem look like in Django code?
# ======================================================================

# models: Order -> Customer (FK), Order -> OrderItem (reverse FK) -> Product (FK)

orders = Order.objects.all()[:100]          # 1 query
for o in orders:
    print(o.customer.name)                  # +100 queries (1 per order)
    for item in o.items.all():              # +100 queries
        print(item.product.title)           # +1 per item (e.g. +500)
# Total: ~701 queries