CODEWITHSUNDEEP
djangodjango-queryset
modbender
modbender

Reputation: 419

How to write this raw SQL query using django querysets?

I have 2 models in defferent apps:

app1/model.py

class BookRatings(models.Model):
    book = models.ForeignKey('app2.Book', on_delete=models.CASCADE)
    user = models.ForeignKey(User, on_delete=models.CASCADE, default=User)
    rating = models.PositiveSmallIntegerField(default=0, help_text='Book Rating out of 10')

app2/model.py

class Book(models.Model):
    title = models.CharField(max_length=250, unique=True)
    slug = models.SlugField(max_length=50)
    description = models.TextField(max_length=500)

I wanted to get the average ratings of each book in descending order with all the details of the book and also be able to access the methods in Book model like get_absolute_url(), so I started with trying to use the below code BookRatings.objects.values('book').annotate(rating_avg=Avg('rating')).order_by('-rating_avg')

This indeed gave me the right values (book id and rating_avg) but it was not possible to get all the fields of the foreign key model book. Like book title or description.

I searched a little and found out that I could just add more columns in values and made it like BookRatings.objects.values('book','book__title','book__description').annotate(rating_avg=Avg('rating')).order_by('-rating_avg').

But still this would not let me access the methods in the book model like get_absolute_url. I tried book__get_absolute_url in values and it gave an error that it is not a field of book.

Since I already knew SQL directly I successfully created a raw sql statement that gets everything that I need.

Book.objects.raw('select rb.*,(select avg(rating) from app1_bookratings as ab where ab.book_id=rb.id group by ab.book_id) as rating_avg from app2_book as rb order by rating_avg desc')

This indeed gives me all the values that I need and I am even able to access the get_absolute_url because im using Book.objects but still I thought it would be better if I learn of a way to do this using django queryset.

By the way, the reason why I am grouping book in BookRatings is because, many users can give ratings to one single book.

Upvotes: 0

Views: 47

Answers (1)

Sergey Pugach
Sergey Pugach

Reputation: 5669

Please try to add distinct=True to your query and remove values() and it's better to call Book model annotation.

Book.objects.annotate(rating_avg=Avg('bookratings__rating', distinct=True)).order_by('-rating_avg')

Similar case but with Count you can find in Django docs.

Upvotes: 1

Related Questions