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)

the Query,

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 %}

renders the model to the template, but the django debug toolbar shows as Duplicated n times where n is the number of objects in the list. when I use queryset caching, its normal.

whats going on behind, how can I fix this?

thanks.

Answer 1

Didn't test but you should definitely prefetch rating_set to not make additional database hit for each book 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 as they may cause an additional db hit. Besides, you don't need to call .first because we already know 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 to selec first rate