Django SQL query duplicated n times

Question

I have a book model and a rating model,

class Book(models.Model): title = models.CharField(max_length=255) slug = AutoSlugField(unique=True, populate_from='title') description = models.TextField() # more fields class Rating(models.Model): book = models.ForeignKey('library.Book') score = models.DecimalField(max_digits=2, decimal_places=1) 

Request,

 books = {'books': Book.objects.filter(pk__in=Rating.objects.all().order_by('-score' ).values_list('book__id', flat=True))[:10] } 

template,

 {% for i in books %} {{ i.title }}, {{ i.rating_set.all.first.score }} <br/> {% endfor %} 

displays the model in a template, but the django debug toolbar appears as Duplicated n times, where n is the number of objects in the list. when I use query caching, its normal.

What is happening, how can I fix it?

thanks.

Answer 1

I didn’t check, but you should definitely pre- rating_set so as not to make an additional hit in the database for each book in order to find their highest score:

 rated_books = Rating.objects.all().order_by('-score').values_list('book', flat=True) books = Book.objects.prefetch_related('rating_set').filter(pk__in=rated_books)[:10] 

In the template, I also suspect .first and .all how they can cause an additional blow to the database. In addition, you do not need to call .first because we already know that these rated books have at least one rating object.

 {% for book in books %} {{ book.title }}, {{ book.rating_set.all.0.score }} <br/> {% endfor %} 

Update: you need to use rating_set.all.0 instead of rating_set.0 so that rating_set.0 first rating

Answer 2

Read about select_related and prefetch_related .

 Book.objects.filter(pk__in=Rating.objects.all().order_by('-score').values_list('book__id', flat=True)).preferch_related('rating_set')[:10] 

In the template, you want to access the book rating {{ i.rating_set.all.0.score }} . Without select_related / prefetch_related Django in each row makes a new request. With prefetch_related Django made 1 request and received all ratings.

In your case, the problem may be in .first. ,