python / expert
Snippet
Django ORM Prefetch Objects with Nested Relations Optimization
This snippet showcases Django's advanced Prefetch objects for optimizing complex nested queries. Using `Prefetch` with custom `queryset` filters and `prefetch_related` chaining, we reduce N+1 queries to exactly 3 queries regardless of data depth. The `to_attr` parameter stores pre-filtered results in a custom attribute, enabling efficient template rendering without additional database hits.
snippet.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
from django.db.models import Prefetchfrom myapp.models import Category, Product, Reviewdef get_categories_with_products_and_reviews():reviews_prefetch = Prefetch('reviews',queryset=Review.objects.filter(is_approved=True).select_related('author').order_by('-created_at'),to_attr='approved_reviews')products_prefetch = Prefetch('products',queryset=Product.objects.filter(is_active=True).prefetch_related(reviews_prefetch),to_attr='active_products')return Category.objects.prefetch_related(products_prefetch).all()for category in get_categories_with_products_and_reviews():print(f"Category: {category.name}")for product in category.active_products:print(f" Product: {product.name}")for review in product.approved_reviews:print(f" Review: {review.content[:50]}")
django
Breakdown
1
from django.db.models import Prefetch
Imports Prefetch object for custom relationship prefetching control
2
queryset=Review.objects.filter(...)
Custom queryset applies server-side filtering before prefetching
3
select_related('author')
Joins author table in same query to avoid additional hits
4
to_attr='approved_reviews'
Stores filtered results in named attribute, bypassing model filtering
5
prefetch_related(reviews_prefetch)
Chains nested prefetch for multi-level optimization