Fixing N+1 queries on a Django list endpoint without changing the contract.

orders  naive_queries  fixed_queries
     5             11               2
    25             51               2
    50            101               2

"""Public-safe demo schema: customers place orders, orders have lines.

The shape is deliberately small. The case is not the schema; it is the query
plan of the list endpoint that serializes all three tables.
"""
from django.db import models


class Customer(models.Model):
    account_ref = models.CharField(max_length=32, unique=True)
    display_name = models.CharField(max_length=120)

    def __str__(self) -> str:
        return self.account_ref


class Order(models.Model):
    class Status(models.TextChoices):
        DRAFT = "draft", "Draft"
        SUBMITTED = "submitted", "Submitted"
        FULFILLED = "fulfilled", "Fulfilled"

    customer = models.ForeignKey(Customer, related_name="orders", on_delete=models.CASCADE)
    reference = models.CharField(max_length=32, unique=True)
    status = models.CharField(max_length=16, choices=Status.choices, default=Status.DRAFT)
    placed_on = models.DateField()

    class Meta:
        ordering = ["reference"]

    def __str__(self) -> str:
        return self.reference


class OrderLine(models.Model):
    order = models.ForeignKey(Order, related_name="lines", on_delete=models.CASCADE)
    sku = models.CharField(max_length=32)
    quantity = models.PositiveIntegerField()
    unit_price_cents = models.PositiveIntegerField()

    class Meta:
        ordering = ["sku"]

    @property
    def line_total_cents(self) -> int:
        return self.quantity * self.unit_price_cents

"""Serialization shared by both endpoints so the contract stays identical.

Keeping one serializer is the point of the case: the fixed view changes the
query plan, not the response shape, so the deploy is release-safe.
"""


def serialize_order(order) -> dict:
    lines = list(order.lines.all())
    return {
        "reference": order.reference,
        "status": order.status,
        "placed_on": order.placed_on.isoformat(),
        "customer": {
            "account_ref": order.customer.account_ref,
            "display_name": order.customer.display_name,
        },
        "lines": [
            {
                "sku": line.sku,
                "quantity": line.quantity,
                "unit_price_cents": line.unit_price_cents,
                "line_total_cents": line.line_total_cents,
            }
            for line in lines
        ],
        "total_cents": sum(line.line_total_cents for line in lines),
    }

"""Two list endpoints with the same contract and different query plans.

`order_list_naive` walks orders and touches customer and lines per row: one
query for the orders, then two queries per order. `order_list` fetches the
same graph with select_related + prefetch_related: a constant two queries.
"""
from django.http import HttpRequest, JsonResponse

from catalog.models import Order
from catalog.serializers import serialize_order


def order_list_naive(request: HttpRequest) -> JsonResponse:
    orders = Order.objects.all()
    return JsonResponse({"orders": [serialize_order(order) for order in orders]})


def order_list(request: HttpRequest) -> JsonResponse:
    orders = Order.objects.select_related("customer").prefetch_related("lines")
    return JsonResponse({"orders": [serialize_order(order) for order in orders]})

"""Query-count guardrails.

The naive endpoint issues 1 + 2 queries per order: one for the order list,
then a customer lookup and a lines lookup per row. The fixed endpoint is a
constant two queries (select_related join + lines prefetch) at any size.
These tests pin both facts so a regression fails CI.
"""
from django.test import TestCase

from catalog.seed import seed_orders


class QueryCountTest(TestCase):
    def test_naive_queries_grow_with_row_count(self):
        seed_orders(order_count=5)
        with self.assertNumQueries(11):  # 1 + 2 * 5
            self.client.get("/api/orders/naive/")

    def test_fixed_queries_constant_at_small_size(self):
        seed_orders(order_count=5)
        with self.assertNumQueries(2):
            self.client.get("/api/orders/")

    def test_fixed_queries_constant_at_large_size(self):
        seed_orders(order_count=50)
        with self.assertNumQueries(2):
            self.client.get("/api/orders/")

    def test_naive_at_large_size_shows_the_growth(self):
        seed_orders(order_count=50)
        with self.assertNumQueries(101):  # 1 + 2 * 50
            self.client.get("/api/orders/naive/")