Bài 3.2: Advanced QuerySets - select_related() vs prefetch_related()

**Series Navigation:** - [Bài 3.1: QuerySet Fundamentals & Lazy Loading](/django-advance/advanced-querysets-optimization-phan-1) - **Bài 3.2 (bài này): select_related() vs prefetch_related()** 👈 - [Bài 3.3: N+1 Problem và Solutions](/django-advance/advanced-querysets-optimization-phan-3) - [Bài 3.4: Aggregation, Annotation & F/Q Objects](/django-advance/advanced-querysets-optimization-phan-4)

Mục Tiêu Bài 3.2

Sau khi hoàn thành Bài 3.2 này, bạn sẽ:

✅ Hiểu cách select_related() hoạt động với SQL JOINs
✅ Hiểu cách prefetch_related() hoạt động với separate queries
✅ Biết khi nào dùng select_related() vs prefetch_related()
✅ Sử dụng Prefetch objects cho advanced scenarios
✅ Implement nested prefetching
✅ Optimize ForeignKey và ManyToMany relationships

Scenario Without Optimization

Models:

class Author(models.Model):    name = models.CharField(max_length=100)    email = models.EmailField() class Article(models.Model):    title = models.CharField(max_length=200)    content = models.TextField()    author = models.ForeignKey(Author, on_delete=models.CASCADE)

Inefficient Code:

# Fetch articlesarticles = Article.objects.all()  # Query 1: SELECT * FROM article # Access related authorsfor article in articles:    print(f"{article.title} by {article.author.name}")    # Query 2-101: SELECT * FROM author WHERE id=?    # One query PER article! (N+1 problem)

Result: 101 queries for 100 articles! 💥

Why This Happens:

Initial query fetches articles only
Accessing article.author triggers separate query
Django doesn't know you'll need authors ahead of time

Solution: Tell Django to fetch related objects upfront!

select_related() - SQL JOIN Strategy

What is select_related()?

select_related() performs SQL JOIN to fetch related objects in single query.

Use Case: Forward ForeignKey và OneToOne relationships.

Basic Usage

Without select_related:

articles = Article.objects.all()  # Query 1 for article in articles:    print(article.author.name)  # Query 2, 3, 4, ...

Total: 101 queries (1 + 100)

With select_related:

articles = Article.objects.select_related('author').all()  # Query 1 (with JOIN) for article in articles:    print(article.author.name)  # No additional queries!

Total: 1 query only! ✅

SQL Generated

Without select_related:

-- Query 1: Get articlesSELECT * FROM article; -- Query 2-101: Get each authorSELECT * FROM author WHERE id = 1;SELECT * FROM author WHERE id = 2;SELECT * FROM author WHERE id = 3;-- ... 100 more queries

With select_related:

-- Single query with JOINSELECT article.*, author.*FROM articleINNER JOIN author ON article.author_id = author.id;

Following Multiple Relationships

Models with Multiple ForeignKeys:

class Category(models.Model):    name = models.CharField(max_length=100) class Author(models.Model):    name = models.CharField(max_length=100)    country = models.ForeignKey('Country', on_delete=models.CASCADE) class Article(models.Model):    title = models.CharField(max_length=200)    author = models.ForeignKey(Author, on_delete=models.CASCADE)    category = models.ForeignKey(Category, on_delete=models.CASCADE)

Select Multiple Relations:

articles = Article.objects.select_related('author', 'category').all() for article in articles:    print(f"{article.title}")    print(f"Author: {article.author.name}")  # No query    print(f"Category: {article.category.name}")  # No query

SQL Generated:

SELECT article.*, author.*, category.*FROM articleINNER JOIN author ON article.author_id = author.idINNER JOIN category ON article.category_id = category.id;

Nested Relationships (Double Underscore)

Follow ForeignKey Chain:

# Fetch article → author → country (nested)articles = Article.objects.select_related('author__country').all() for article in articles:    print(f"{article.title}")    print(f"Author: {article.author.name}")  # No query    print(f"Country: {article.author.country.name}")  # No query

SQL Generated:

SELECT article.*, author.*, country.*FROM articleINNER JOIN author ON article.author_id = author.idINNER JOIN country ON author.country_id = country.id;

Multiple Nested Relations:

articles = Article.objects.select_related(    'author__country',    'category__parent'  # If Category has self-referencing FK).all()

OneToOneField with select_related

Models:

class User(models.Model):    username = models.CharField(max_length=150) class UserProfile(models.Model):    user = models.OneToOneField(User, on_delete=models.CASCADE)    bio = models.TextField()    avatar = models.ImageField()

Usage:

# Forward direction (from UserProfile to User)profiles = UserProfile.objects.select_related('user').all() for profile in profiles:    print(profile.user.username)  # No additional query # Reverse direction (from User to UserProfile)users = User.objects.select_related('userprofile').all() for user in users:    print(user.userprofile.bio)  # No additional query

When select_related() Works

✅ Works With:

ForeignKey (forward direction)
OneToOneField (both directions)
Can follow chains with __

❌ Doesn't Work With:

Reverse ForeignKey (one-to-many)
ManyToManyField
GenericForeignKey

Why? JOINs would create duplicate rows for one-to-many relationships.

prefetch_related() - Separate Query Strategy

What is prefetch_related()?

prefetch_related() performs separate queries for related objects and joins them in Python.

Use Case:

Reverse ForeignKey (one-to-many)
ManyToManyField
Any relationship where JOIN would duplicate rows

Basic Usage - Reverse ForeignKey

Models:

class Author(models.Model):    name = models.CharField(max_length=100) class Article(models.Model):    title = models.CharField(max_length=200)    author = models.ForeignKey(Author, related_name='articles')

Without prefetch_related:

authors = Author.objects.all()  # Query 1 for author in authors:    # Query 2, 3, 4, ... (one per author)    articles = author.articles.all()    print(f"{author.name}: {articles.count()} articles")

With prefetch_related:

authors = Author.objects.prefetch_related('articles').all() for author in authors:    # No additional queries!    articles = author.articles.all()    print(f"{author.name}: {articles.count()} articles")

SQL Generated

Without prefetch_related:

-- Query 1: Get authorsSELECT * FROM author; -- Query 2-11: Get articles for each authorSELECT * FROM article WHERE author_id = 1;SELECT * FROM article WHERE author_id = 2;-- ... one per author

With prefetch_related:

-- Query 1: Get authorsSELECT * FROM author; -- Query 2: Get all related articles in one querySELECT * FROM article WHERE author_id IN (1, 2, 3, 4, 5, ...);

Key Difference:

select_related: 1 query with JOIN
prefetch_related: 2 queries, joined in Python

ManyToManyField with prefetch_related

Models:

class Tag(models.Model):    name = models.CharField(max_length=50) class Article(models.Model):    title = models.CharField(max_length=200)    tags = models.ManyToManyField(Tag, related_name='articles')

Forward Direction (Article → Tags):

articles = Article.objects.prefetch_related('tags').all() for article in articles:    # No additional queries    tag_names = [tag.name for tag in article.tags.all()]    print(f"{article.title}: {', '.join(tag_names)}")

Reverse Direction (Tag → Articles):

tags = Tag.objects.prefetch_related('articles').all() for tag in tags:    # No additional queries    print(f"{tag.name}: {tag.articles.count()} articles")

SQL Generated:

-- Query 1: Get articlesSELECT * FROM article; -- Query 2: Get all tag relationshipsSELECT * FROM article_tags WHERE article_id IN (...); -- Query 3: Get tag detailsSELECT * FROM tag WHERE id IN (...);

Nested Prefetching

Models:

class Author(models.Model):    name = models.CharField(max_length=100) class Article(models.Model):    title = models.CharField(max_length=200)    author = models.ForeignKey(Author, related_name='articles') class Comment(models.Model):    article = models.ForeignKey(Article, related_name='comments')    text = models.TextField()

Prefetch Articles with Their Comments:

# Get authors with their articles and each article's commentsauthors = Author.objects.prefetch_related(    'articles',              # Author → Articles    'articles__comments'     # Articles → Comments).all() for author in authors:    for article in author.articles.all():  # No query        comments = article.comments.all()   # No query        print(f"{article.title}: {comments.count()} comments")

Queries:

-- Query 1: Get authorsSELECT * FROM author; -- Query 2: Get all articlesSELECT * FROM article WHERE author_id IN (...); -- Query 3: Get all commentsSELECT * FROM comment WHERE article_id IN (...);

Total: 3 queries for all authors, articles, and comments! ✅

Combining select_related and prefetch_related

Best Practice: Use both together for optimal performance.

Scenario:

class Country(models.Model):    name = models.CharField(max_length=100) class Author(models.Model):    name = models.CharField(max_length=100)    country = models.ForeignKey(Country)  # Forward FK class Article(models.Model):    title = models.CharField(max_length=200)    author = models.ForeignKey(Author, related_name='articles')  # Forward FK class Comment(models.Model):    article = models.ForeignKey(Article, related_name='comments')  # Reverse FK    text = models.TextField()

Optimized Query:

articles = Article.objects.select_related(    'author',           # Forward FK → JOIN    'author__country'   # Nested forward FK → JOIN).prefetch_related(    'comments'          # Reverse FK → Separate query).all() for article in articles:    print(f"Title: {article.title}")    print(f"Author: {article.author.name}")  # No query (JOINed)    print(f"Country: {article.author.country.name}")  # No query (JOINed)    print(f"Comments: {article.comments.count()}")  # No query (prefetched)

Queries:

-- Query 1: Articles with author and country (JOINs)SELECT article.*, author.*, country.*FROM articleINNER JOIN author ON article.author_id = author.idINNER JOIN country ON author.country_id = country.id; -- Query 2: All commentsSELECT * FROM comment WHERE article_id IN (...);

Total: 2 queries for everything! 🎯

Advanced Prefetching with Prefetch Objects

The Prefetch() Object

Purpose: Custom control over prefetched querysets.

Import:

from django.db.models import Prefetch

Basic Prefetch Object

Syntax:

Prefetch(    lookup='related_name',    queryset=CustomQuerySet,    to_attr='custom_attribute_name')

Use Case 1: Filter Prefetched Objects

Without Prefetch:

# Prefetches ALL comments (including deleted)articles = Article.objects.prefetch_related('comments').all()

With Prefetch - Filter Only Active Comments:

from django.db.models import Prefetch articles = Article.objects.prefetch_related(    Prefetch(        'comments',        queryset=Comment.objects.filter(is_deleted=False),        to_attr='active_comments'    )).all() for article in articles:    # Access filtered comments    for comment in article.active_comments:  # Only non-deleted        print(comment.text)

Use Case 2: Order Prefetched Objects

Prefetch with Ordering:

articles = Article.objects.prefetch_related(    Prefetch(        'comments',        queryset=Comment.objects.order_by('-created_at'),        to_attr='latest_comments'    )).all() for article in articles:    # Comments already ordered by latest first    for comment in article.latest_comments[:5]:  # Top 5 latest        print(comment.text)

Use Case 3: Prefetch with select_related

Nested Optimization:

# Prefetch comments WITH their authors (via select_related)articles = Article.objects.prefetch_related(    Prefetch(        'comments',        queryset=Comment.objects.select_related('author'),        to_attr='comments_with_authors'    )).all() for article in articles:    for comment in article.comments_with_authors:        # No query for comment.author!        print(f"{comment.author.name}: {comment.text}")

Queries:

-- Query 1: Get articlesSELECT * FROM article; -- Query 2: Get comments with authors (JOIN)SELECT comment.*, author.*FROM commentINNER JOIN author ON comment.author_id = author.idWHERE comment.article_id IN (...);

Use Case 4: Multiple Prefetch Variations

Different Filters for Same Relation:

from django.db.models import Prefetch articles = Article.objects.prefetch_related(    # All comments    'comments',        # Only approved comments    Prefetch(        'comments',        queryset=Comment.objects.filter(is_approved=True),        to_attr='approved_comments'    ),        # Only recent comments (last 7 days)    Prefetch(        'comments',        queryset=Comment.objects.filter(            created_at__gte=timezone.now() - timedelta(days=7)        ),        to_attr='recent_comments'    )).all() for article in articles:    print(f"All: {article.comments.count()}")    print(f"Approved: {len(article.approved_comments)}")    print(f"Recent: {len(article.recent_comments)}")

Decision Matrix: select_related vs prefetch_related

Quick Reference

Relationship Type	Direction	Method	Why
ForeignKey	Forward	`select_related()`	Single JOIN, no duplicates
ForeignKey	Reverse (one-to-many)	`prefetch_related()`	JOIN would duplicate rows
OneToOneField	Both	`select_related()`	Single JOIN, one-to-one
ManyToManyField	Both	`prefetch_related()`	JOIN would duplicate rows
GenericForeignKey	-	`prefetch_related()`	Can't JOIN across content types

Examples by Relationship

1. Forward ForeignKey:

# Article → Author (many-to-one)Article.objects.select_related('author')  # ✅

2. Reverse ForeignKey:

# Author → Articles (one-to-many)Author.objects.prefetch_related('articles')  # ✅

3. OneToOneField:

# User → Profile (one-to-one)User.objects.select_related('profile')  # ✅Profile.objects.select_related('user')  # ✅ (reverse also works)

4. ManyToManyField:

# Article → Tags (many-to-many)Article.objects.prefetch_related('tags')  # ✅Tag.objects.prefetch_related('articles')  # ✅ (reverse)

5. Nested Mixed:

# Article → Author (forward FK) → Country (forward FK) + Comments (reverse FK)Article.objects.select_related(    'author',    'author__country').prefetch_related(    'comments')

Performance Comparison

Benchmark Example

Setup:

100 authors
Each author has 10 articles
Each article has 5 comments

Method 1: No Optimization

authors = Author.objects.all()for author in authors:    for article in author.articles.all():        for comment in article.comments.all():            print(comment)

Queries: 1 + 100 + 1000 = 1,101 queries 💥

Method 2: prefetch_related Only

authors = Author.objects.prefetch_related('articles', 'articles__comments')# Same loop

Queries: 1 + 1 + 1 = 3 queries ✅

Method 3: prefetch_related with Prefetch

authors = Author.objects.prefetch_related(    Prefetch(        'articles',        queryset=Article.objects.prefetch_related('comments')    ))

Queries: 1 + 1 + 1 = 3 queries ✅

Common Pitfalls

Pitfall 1: Subsequent Filters Break Prefetch

Problem:

authors = Author.objects.prefetch_related('articles') for author in authors:    # ❌ This IGNORES prefetch and queries again!    published = author.articles.filter(status='published')

Solution:

# Use Prefetch object with filterauthors = Author.objects.prefetch_related(    Prefetch(        'articles',        queryset=Article.objects.filter(status='published')    ))

Pitfall 2: Chaining all() Breaks Prefetch

Problem:

authors = Author.objects.prefetch_related('articles') for author in authors:    # ❌ .all() creates new queryset, loses prefetch    articles = author.articles.all()    articles = articles.order_by('-created_at')  # New query!

Solution:

authors = Author.objects.prefetch_related(    Prefetch(        'articles',        queryset=Article.objects.order_by('-created_at')    ))

Pitfall 3: Accessing Individual Objects

Problem:

articles = Article.objects.select_related('author').all() # ✅ Good: Using prefetched authorfor article in articles:    print(article.author.name) # ❌ Bad: Fetching individual article loses optimizationarticle = Article.objects.get(id=1)print(article.author.name)  # NEW query!

Solution:

# Always use select_related even for single objectsarticle = Article.objects.select_related('author').get(id=1)print(article.author.name)  # No additional query

Tóm Tắt Bài 3.2

Trong Bài 3.2 này, bạn đã học:

✅ select_related():

SQL JOIN strategy (1 query)
For ForeignKey (forward) và OneToOneField
Follow chains với __ notation
Nested relationships

✅ prefetch_related():

Separate query strategy (2+ queries)
For reverse ForeignKey và ManyToManyField
Joins in Python, not database
Nested prefetching với __

✅ Prefetch() Objects:

Custom queryset control
Filter prefetched objects
Order prefetched objects
Combine with select_related
Multiple variations of same relation

✅ Decision Matrix:

Forward FK → select_related
Reverse FK → prefetch_related
OneToOne → select_related
ManyToMany → prefetch_related
Mixed → Combine both

✅ Performance:

1,101 queries → 3 queries with optimization
Massive performance improvement

Exercises

Exercise 1: Optimize Blog Query

Models:

class Category(models.Model):    name = models.CharField(max_length=100) class Author(models.Model):    name = models.CharField(max_length=100)    bio = models.TextField() class Post(models.Model):    title = models.CharField(max_length=200)    content = models.TextField()    author = models.ForeignKey(Author, related_name='posts')    category = models.ForeignKey(Category, related_name='posts')    class Comment(models.Model):    post = models.ForeignKey(Post, related_name='comments')    author = models.ForeignKey(Author, related_name='comments')    text = models.TextField()

Task: Write optimized query to:

Get all posts
Include author and category
Include all comments with comment authors
Minimize query count

Exercise 2: E-commerce Product Listing

Models:

class Brand(models.Model):    name = models.CharField(max_length=100) class Product(models.Model):    name = models.CharField(max_length=200)    brand = models.ForeignKey(Brand, related_name='products') class Review(models.Model):    product = models.ForeignKey(Product, related_name='reviews')    rating = models.IntegerField()    text = models.TextField()    class Image(models.Model):    product = models.ForeignKey(Product, related_name='images')    url = models.URLField()    is_primary = models.BooleanField(default=False)

Task: Optimize query for product listing page showing:

Product name and brand
Average rating
Primary image only
Reduce to minimum queries

Models:

class User(models.Model):    username = models.CharField(max_length=150) class Post(models.Model):    author = models.ForeignKey(User, related_name='posts')    content = models.TextField()    created_at = models.DateTimeField(auto_now_add=True) class Like(models.Model):    post = models.ForeignKey(Post, related_name='likes')    user = models.ForeignKey(User, related_name='liked_posts') class Comment(models.Model):    post = models.ForeignKey(Post, related_name='comments')    author = models.ForeignKey(User, related_name='comments')    text = models.TextField()

Task: Optimize feed query to:

Get last 20 posts
Include post authors
Include last 3 comments per post with comment authors
Count total likes per post

Hint: Use Prefetch with slicing for latest 3 comments.

Next Steps

Trong Bài 3.3, bạn sẽ học:

N+1 query problem deep dive
Detecting N+1 problems với tools
Solutions: select_related, prefetch_related, annotate
Real-world N+1 scenarios
Prevention strategies

Navigation:

← Bài 3.1: QuerySet Fundamentals
Bài 3.2: select_related() vs prefetch_related() (current)
Bài 3.3: N+1 Problem và Solutions →

#joins

Advanced QuerySets và Database Optimization - Phần 2 - select_related vs prefetch_related