How to write this raw SQL query using django querysets?

Question

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.

Sergey Pugach · Accepted Answer · 2019-01-08 08:06:12Z

1

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.

edited Jan 8, 2019 at 8:06

answered Jan 8, 2019 at 8:00

Sergey Pugach

5,6691 gold badge18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

modbender Over a year ago

Oh lol thanks, that worked. I thought distinct will not include duplicate rows when calculating avg, I guess ill have to study more on how it works in here.

Collectives™ on Stack Overflow

How to write this raw SQL query using django querysets?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related