CODEWITHSUNDEEP
pythondjangodjango-templatesdjango-queryset
user5170375
user5170375

Reputation:

Django SQL query duplicated n times

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.

enter image description here

whats going on behind, how can I fix this?

thanks.

Upvotes: 12

Views: 8234

Answers (2)

Ozgur Vatansever
Ozgur Vatansever

Reputation: 52213

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

Upvotes: 9

Tomasz Jakub Rup
Tomasz Jakub Rup

Reputation: 10680

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..

Upvotes: 4

Related Questions