Database query optimization with select_related in Django - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

Understanding select_related effect on query count

Given the Django models Author and Book where Book has a foreign key to Author, what is the difference in database queries between these two code snippets?

1) books = Book.objects.all()
2) books = Book.objects.select_related('author').all()

What will be the number of queries executed when iterating over books and accessing book.author.name in each case?

Django

for book in books:
    print(book.author.name)

A 1) Executes N+1 queries (N = number of books); 2) Executes 1 query

B 1) Executes 1 query; 2) Executes N queries (N = number of books)

C 1) Executes 1 query; 2) Executes 1 query

D 1) Executes N queries; 2) Executes N+1 queries

💡 Hint

Think about how select_related joins tables to reduce queries.

Without select_related, accessing book.author triggers a separate query per book (N+1 queries). Using select_related fetches related authors in one join query, so only 1 query runs.

📝 Syntax

intermediate

Correct usage of select_related with multiple fields

Which of the following is the correct way to use select_related to fetch related fields author and publisher in a Django query?

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

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

C Book.objects.select_related('author__publisher').all()

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

💡 Hint

Check the expected argument type for select_related.

select_related accepts either a single string or a list/tuple of strings for multiple related fields.

🔧 Debug

advanced

Diagnosing unexpected query count with select_related

A developer uses select_related('author') on a queryset but still sees multiple queries when accessing book.author.profile. Why does this happen?

A select_related does not work with foreign keys, only with many-to-many fields

B select_related only fetches one level of foreign keys; author.profile is a second level relation not included

C The developer must call select_related twice to fetch nested relations

D select_related causes Django to cache queries, so multiple queries are expected

💡 Hint

Think about how deep select_related fetches related objects.

select_related fetches related objects only one level deep unless nested relations are specified with double underscores.

🧠 Conceptual

advanced

When to prefer select_related over prefetch_related

Which situation is best suited for using select_related instead of prefetch_related in Django?

A Fetching many-to-many related objects where the related set is large

B Fetching reverse foreign key relations with many related objects

C Fetching unrelated models in separate queries

D Fetching foreign key related objects where the relation is one-to-one or many-to-one

💡 Hint

Consider the type of relationship and how joins work.

select_related uses SQL joins and is efficient for single-valued relationships like foreign keys or one-to-one fields. prefetch_related is better for many-to-many or reverse foreign keys.

❓ state_output

expert

Output of nested select_related query

Given these Django models:

class Country(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 Book(models.Model): title = models.CharField(max_length=100) author = models.ForeignKey(Author, on_delete=models.CASCADE)

What is the output of the following code snippet?

books = Book.objects.select_related('author__country').all() for book in books: print(f"{book.title} by {book.author.name} from {book.author.country.name}")

Assuming the database has 3 books with authors and countries properly set, how many database queries will this code execute?

Django

books = Book.objects.select_related('author__country').all()
for book in books:
    print(f"{book.title} by {book.author.name} from {book.author.country.name}")

A 3 queries

B 2 queries

C 1 query

D N+1 queries (N = number of books)

💡 Hint

select_related can follow nested foreign keys with double underscores.

The select_related('author__country') fetches books, authors, and countries in a single SQL join query. So only 1 query runs regardless of number of books.