Hi there,
I’ve been trying to solve “N+1 Queries” problem as suggested by “Django for Professionals” book, by using prefetch_related().
I suspect that the issue is related to the fact that I introduced an additional “uuid” field in order to securely display URLs for my book objects, and I am simultaneously using a standard auto-increment “id” for foreign-key relationship whereas Django book’s author substituted standard “id” with “uuid”.
I made an experiment - while keeping an additional “uuid” field, I switched back to showing URL’s with simple “id”, and in this case there’s no “N+1 Queries” problem - I have only 5 queries to display the book and associated reviews. When I am using uuid-based URL, I’ve got 14 queries - and 10 of them are just duplicates, and prefetch_related() does not help to solve it.
I read Django’s documentation, googled here and on StackOverFlow, tried to introduce queryset into get_object() method, tried to use combination of select_related and prefetch_related, - nothing really helps. It might also be the case that the cached results of prefetch_related are simply deleted as I’m using “uuid” in get_object(), but I can’t figure out how to solve it. So any help/ideas are appreciated.
Here’s my code and debug-toolbar’s log:
Models:
class Book(models.Model):
uuid = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
title = models.CharField(max_length=200)
author = models.CharField(max_length=200)
price = models.DecimalField(max_digits=6, decimal_places=2)
cover = models.ImageField(upload_to="covers/", blank=True)
class Meta:
permissions = [
("special_status", "Can read all books"),
]
def __str__(self):
return self.title
def get_absolute_url(self):
return reverse("book_detail", args=[str(self.uuid)])
class Review(models.Model):
book = models.ForeignKey(
Book,
on_delete=models.CASCADE,
related_name="reviews",
)
review = models.CharField(max_length=255)
author = models.ForeignKey(
get_user_model(),
on_delete=models.CASCADE,
)
def __str__(self):
return self.review
Views:
class BookListView(LoginRequiredMixin, ListView):
model = Book
context_object_name = "book_list"
template_name = "books/book_list.html"
login_url = "account_login"
class BookDetailView(LoginRequiredMixin, PermissionRequiredMixin, DetailView):
model = Book
context_object_name = "book"
template_name = "books/book_detail.html"
login_url = "account_login"
permission_required = "books.special_status"
queryset = Book.objects.select_related("reviews").prefetch_related(
"reviews__author_id",
)
def get_object(self, queryset=None):
return Book.objects.get(uuid=self.kwargs.get("uuid"))
URL:
urlpatterns = [
path("", BookListView.as_view(), name="book_list"),
path("<uuid:uuid>/", BookDetailView.as_view(), name="book_detail"),
path("search/", SearchResultsListView.as_view(), name="search_results"),
]
Template:
{% block content %}
<div class="book-detail">
{% if book.cover %}
<img class="bookcover" src="{{ book.cover.url }}" alt="{{ book.title }}">
{% endif %}
<h2><a href="">{{ book.title }}</a></h2>
<p>Author: {{ book.author }}</p>
<p>Price: {{ book.price }}</p>
<div>
<h3>Reviews</h3>
<ul>
{% for review in book.reviews.all %}
<li>{{ review.review }} (by {{ review.author }})</li>
{% endfor %}
</ul>
</div>
</div>
{% endblock content %}
debug-toolbar evidence of “N+1 Queries” problem:
**SELECT "books_book"."id",
"books_book"."uuid",
"books_book"."title",
"books_book"."author",
"books_book"."price",
"books_book"."cover"
FROM "books_book"
WHERE "books_book"."uuid" = 'ca65fdd62bc84d2eadee92a930b53b6c'::uuid
LIMIT 21 0.52
/code/books/views.py in get_object(27)
return Book.objects.get(uuid=self.kwargs.get("uuid"))
+ **SELECT "books_review"."id",
"books_review"."book_id",
"books_review"."review",
"books_review"."author_id"
FROM "books_review"
WHERE "books_review"."book_id" = 1 0.40
{% for review in book.reviews.all %}
+ **SELECT "accounts_customuser"."id",
"accounts_customuser"."password",
"accounts_customuser"."last_login",
"accounts_customuser"."is_superuser",
"accounts_customuser"."username",
"accounts_customuser"."first_name",
"accounts_customuser"."last_name",
"accounts_customuser"."email",
"accounts_customuser"."is_staff",
"accounts_customuser"."is_active",
"accounts_customuser"."date_joined"
FROM "accounts_customuser"
WHERE "accounts_customuser"."id" = 2
LIMIT 21 11 similar queries. Duplicated 10 times.
<li>{{ review.review }} (by {{ review.author }})</li>
**11 similar queries.** **Duplicated 10 times.** 0.27
+ **SELECT** ••• **FROM** "accounts_customuser" **WHERE** "accounts_customuser"."id" = 2 **LIMIT** 21
**11 similar queries.** **Duplicated 10 times.** 0.23
+ **SELECT** ••• **FROM** "accounts_customuser" **WHERE** "accounts_customuser"."id" = 2 **LIMIT** 21
**11 similar queries.** **Duplicated 10 times.**
..........
