Database query optimization with select_related in Django - Mini Project: Build & Apply

Database query optimization with select_related

📖 Scenario: You are building a simple Django app to display a list of books with their authors. Each book has one author. You want to show the book title and the author's name efficiently.

🎯 Goal: Learn how to optimize database queries in Django by using select_related to fetch related author data along with books in a single query.

📋 What You'll Learn

Create Django models for Author and Book with a foreign key relationship

Write a query to get all books

Add a variable to hold the optimized query using select_related

Use the optimized query in a Django view to fetch books with authors efficiently

💡 Why This Matters

🌍 Real World

Optimizing database queries is important in real apps to make pages load faster and reduce server load.

💼 Career

Knowing how to use select_related is a key skill for Django developers to write efficient, scalable web applications.

1

Create Django models for Author and Book

Create two Django models: Author with a name field, and Book with a title field and a foreign key author to Author.

Django

# Define Author and Book models with the specified fields
# Your code here

Hint

Use models.CharField for text fields and models.ForeignKey for the relationship.

2

Write a query to get all books

Write a Django query to get all Book objects and assign it to a variable called books.

Django

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

books = Book.objects.all()  # Your code here

Hint

Use Book.objects.all() to get all book records.

3

Add a variable for optimized query using select_related

Create a variable called optimized_books that uses select_related('author') on Book.objects to fetch books with their authors in one query.

Django

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

books = Book.objects.all()

# Your code here

Hint

Use select_related('author') to join author data in the same query.

4

Use optimized_books in a Django view

In a Django view function called book_list, return a render with template 'books.html' and context containing optimized_books as 'books'.

Django

from django.shortcuts import render

# Your code here

Hint

Use render(request, 'books.html', {'books': optimized_books}) to pass data to the template.

Practice

1. What is the main purpose of using select_related in Django queries?

easy

A. To reduce the number of database queries by joining related tables

B. To create new database tables automatically

C. To delete related objects from the database

D. To update multiple records at once

Solution

1. Step 1: Understand what select_related does

   select_related is used to fetch related objects in a single database query by joining tables.

2. Step 2: Identify the main benefit

   This reduces the number of queries and improves performance when accessing related data.

3. Final Answer:

   To reduce the number of database queries by joining related tables -> Option A

4. Quick Check:

   select_related reduces queries = D [OK]

Hint: Remember: select_related joins tables to reduce queries [OK]

Common Mistakes:

• Thinking select_related creates or deletes tables
• Confusing select_related with update or delete operations
• Assuming select_related works for many-to-many relations

2. Which of the following is the correct syntax to use select_related to fetch related author objects in a Book model query?

easy

A. Book.objects.select_related['author'].all()

B. Book.objects.select_related.author().all()

C. Book.objects.select_related('author')()

D. Book.objects.select_related('author').all()

Solution

1. Step 1: Recall the correct method call syntax

   select_related is a queryset method that takes related field names as string arguments inside parentheses.

2. Step 2: Check each option

   Book.objects.select_related('author').all() uses correct method call with parentheses and string argument. Others misuse dot notation, brackets, or call syntax.

3. Final Answer:

   Book.objects.select_related('author').all() -> Option D

4. Quick Check:

   Method call with string arg = C [OK]

Hint: Use parentheses and quotes: select_related('field') [OK]

Common Mistakes:

• Using dot notation instead of parentheses
• Using square brackets instead of parentheses
• Calling select_related without parentheses

3. Given these models:

class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

What will this code print?

books = Book.objects.select_related('author').all()
for book in books:
    print(book.author.name)

medium

A. Prints author names but runs one query per book

B. Raises an error because select_related is used incorrectly

C. Prints all author names with only one database query

D. Prints book titles instead of author names

Solution

1. Step 1: Understand select_related effect on queries

   Using select_related('author') fetches books and their related authors in one query.

2. Step 2: Analyze the loop output

   The loop prints book.author.name for each book, showing author names without extra queries.

3. Final Answer:

   Prints all author names with only one database query -> Option C

4. Quick Check:

   select_related joins tables = A [OK]

Hint: select_related fetches related data in one query [OK]

Common Mistakes:

• Thinking select_related causes multiple queries
• Confusing select_related with prefetch_related
• Expecting book titles instead of author names

4. What is wrong with this Django query?

books = Book.objects.select_related('publisher').all()

Assuming Book has no publisher foreign key field.

medium

A. It will run but ignore the 'publisher' argument

B. It will raise a FieldError because 'publisher' is not a valid related field

C. It will fetch all books and publishers anyway

D. It will cause a syntax error

Solution

1. Step 1: Check if 'publisher' is a related field on Book

   Since Book has no publisher foreign key, this field does not exist.

2. Step 2: Understand select_related behavior with invalid fields

   Using an invalid field name in select_related raises a FieldError.

3. Final Answer:

   It will raise a FieldError because 'publisher' is not a valid related field -> Option B

4. Quick Check:

   Invalid field in select_related = FieldError = B [OK]

Hint: Check related field names exist before using select_related [OK]

Common Mistakes:

• Assuming invalid fields are ignored
• Expecting silent failure or warnings
• Confusing syntax errors with runtime FieldErrors

5. You have these models:

class Publisher(models.Model):
    name = models.CharField(max_length=100)

class Author(models.Model):
    name = models.CharField(max_length=100)
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

How do you optimize a query to get all books with their authors and authors' publishers in the fewest queries?

hard

A. Book.objects.select_related('author', 'author__publisher').all()

B. Book.objects.select_related('author').select_related('publisher').all()

C. Book.objects.prefetch_related('author', 'author__publisher').all()

D. Book.objects.select_related('publisher').all()

Solution

1. Step 1: Identify the related fields to join

   We want to fetch author and the publisher related to that author in one query.

2. Step 2: Use nested select_related syntax

   Use select_related('author', 'author__publisher') to join both foreign keys in one query.

3. Step 3: Evaluate other options

   Book.objects.select_related('author').select_related('publisher').all() is invalid because publisher is not directly on Book. Book.objects.prefetch_related('author', 'author__publisher').all() uses prefetch_related which is less efficient here. Book.objects.select_related('publisher').all() misses author relation.

4. Final Answer:

   Book.objects.select_related('author', 'author__publisher').all() -> Option A

5. Quick Check:

   Nested select_related joins = A [OK]

Hint: Chain related fields with double underscores in select_related [OK]

Common Mistakes:

• Trying to select_related unrelated fields directly
• Using prefetch_related instead of select_related for foreign keys
• Missing nested relation syntax with double underscores