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

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 template You want to access to book rating {{ i.rating_set.all.0.score }}. Without select_related/prefetch_related Django in each row make new query. With prefetch_related Django made 1 query and fetch all ratings.

In Your case the problem may be in .first..

Answer 2

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