Django Querysets Reduce number of queries on ManyToManyField (n+1 issue)
30% OFF - 9th Anniversary discount on Entity Framework Extensions until December 15 with code: ZZZANNIVERSARY9
Example
Problem
# models.py:
class Library(models.Model):
name = models.CharField(max_length=100)
books = models.ManyToManyField(Book)
class Book(models.Model):
title = models.CharField(max_length=100)
# views.py
def myview(request):
# Query the database.
libraries = Library.objects.all()
# Query the database on each iteration (len(author) times)
# if there is 100 librairies, there will have 100 queries plus the initial query
for library in libraries:
books = library.books.all()
books[0].title
# ...
# total : 101 queries
Solution
Use prefetch_related on ManyToManyField if you know that you will need to access later a field which is a ManyToManyField field.
# views.py
def myview(request):
# Query the database.
libraries = Library.objects.prefetch_related('books').all()
# Does not query the database again, since `books` is pre-populated
for library in libraries:
books = library.books.all()
books[0].title
# ...
# total : 2 queries - 1 for libraries, 1 for books
prefetch_related can also be used on lookup fields :
# models.py:
class User(models.Model):
name = models.CharField(max_length=100)
class Library(models.Model):
name = models.CharField(max_length=100)
books = models.ManyToManyField(Book)
class Book(models.Model):
title = models.CharField(max_length=100)
readers = models.ManyToManyField(User)
# views.py
def myview(request):
# Query the database.
libraries = Library.objects.prefetch_related('books', 'books__readers').all()
# Does not query the database again, since `books` and `readers` is pre-populated
for library in libraries:
for book in library.books.all():
for user in book.readers.all():
user.name
# ...
# total : 3 queries - 1 for libraries, 1 for books, 1 for readers
However, once the queryset has been executed, the data fetched can't be altered without hitting again the database. The following would execute extra queries for example:
# views.py
def myview(request):
# Query the database.
libraries = Library.objects.prefetch_related('books').all()
for library in libraries:
for book in library.books.filter(title__contains="Django"):
print(book.name)
The following can be optimized using a Prefetch object, introduced in Django 1.7:
from django.db.models import Prefetch
# views.py
def myview(request):
# Query the database.
libraries = Library.objects.prefetch_related(
Prefetch('books', queryset=Book.objects.filter(title__contains="Django")
).all()
for library in libraries:
for book in library.books.all():
print(book.name) # Will print only books containing Django for each library
