python 31 lines · 6 steps

Building an efficient article list in Django

A ListView that fights N+1 queries with select_related, a custom Prefetch, and an annotated comment count.

Explained by highlit
Django Intermediate orm query-optimization prefetching n+1 annotations
1from django.db.models import Prefetch, Count
2from django.views.generic import ListView
3 
4from .models import Article, Comment
5 
6 
7class ArticleListView(ListView):
8 model = Article
9 template_name = "blog/article_list.html"
10 context_object_name = "articles"
11 paginate_by = 25
12 
13 def get_queryset(self):
14 approved_comments = Comment.objects.filter(is_approved=True).select_related(
15 "author"
16 )
17 
18 return (
19 Article.objects.filter(status=Article.Status.PUBLISHED)
20 .select_related("author", "category")
21 .prefetch_related(
22 "tags",
23 Prefetch(
24 "comments",
25 queryset=approved_comments.order_by("-created_at"),
26 to_attr="visible_comments",
27 ),
28 )
29 .annotate(comment_count=Count("comments", filter=Q(comments__is_approved=True)))
30 .order_by("-published_at")
31 )
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1select_related handles forward foreign keys with a JOIN, while prefetch_related handles reverse and many-to-many relations with a second query.
  2. 2A custom Prefetch lets you filter and order related objects and stash them on a named attribute via to_attr.
  3. 3Annotating a filtered Count computes per-row aggregates in SQL instead of counting in Python.

Related explainers

python 
from django.db import models
from django.utils.text import slugify
 
 

Auto-generating unique slugs in Django

slugs orm model-methods
Intermediate 7 steps
python 
from functools import lru_cache
import math
 
 

Memoizing number theory with lru_cache

memoization number-theory caching
Intermediate 8 steps
python 
import re
from collections import defaultdict
from pathlib import Path
 

Summarizing log files by date in Python

regex parsing aggregation
Intermediate 7 steps
python 
import threading
import logging
 
logger = logging.getLogger(__name__)

A self-rescheduling periodic task in Python

threading scheduling concurrency
Intermediate 6 steps
python 
from collections import ChainMap
from functools import reduce
from typing import Any, Iterable, Mapping
 

Five ways to merge dicts in Python

dictionaries merging recursion
Intermediate 8 steps
python 
from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy import create_engine
from sqlalchemy.orm import Session, sessionmaker
 

Wiring SQLAlchemy sessions into FastAPI

dependency-injection orm sessions
Intermediate 7 steps

Share this explainer

Here's the card — post it anywhere.

Building an efficient article list in Django — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code