Django query - Is it possible to group elements by common field at database level?

Question

I have a database model as shown below. Consider the data as 2 different books each having 3 ratings.

class Book(models.Model):
    name    = models.CharField(max_length=50)

class Review(models.Model):
    book    = models.ForeignKey(Book)           
    review  = models.CharField(max_length=1000)            
    rating  = models.IntegerField()

Question : Is it possible to group all the ratings in a list, for each book with a single query. I'm looking to do this at database level, without iterating over the Queryset in my code. Output should look something like :

{
 'book__name':'book1', 
 'rating'    : [3, 4, 4], 
 'average'   : 3.66,
 'book__name':'book2', 
 'rating     : [2, 1, 1] ,
 'average'   : 1.33
}

I've tried this query, but neither are the ratings grouped by book name, nor is the average correct :

Review.objects.annotate(average=Avg('rating')).values('book__name','rating','average')

Edit : Added clarification that I'm looking for a method to group the elements at database level.

@KevinBrown : You are making trivial edits. In the process you removed a clarification from the question. I rolled it back and you did it again. If you have any doubts, please put that in comments instead of being disrespectful by repeatedly doing the same edits. Regarding the duplicate tag, I'll remove it when there are some comments/answers on your Meta SO question. — user, Dec 25 '14 at 05:57
My apologies, it occurred to me that I was reverting a revert almost immediately after the edit was made, but by that time (I think?) it was too late. I'm not interested in starting an edit war over retagging, and I'll wait on the Meta SO question before continuing. — Kevin Brown-Silva, Dec 25 '14 at 13:01

Anish Shah · Accepted Answer · 2014-03-12T13:11:17.357

11

You can do this. Hope this helps.

Review.objects.values('book__name').annonate(average=Avg('rating'))

UPDATE:

If you want all the ratings of a particular book in a list, then you can do this.

from collections import defaultdict
ratings = defaultdict(list)
for result in Review.objects.values('book__name', 'rating').order_by('book__name', 'rating'):
    ratings[result['book__name']].append(result['rating'])

You will get a structure like this :

[{ book__name: [rating1, rating2, ] }, ]

UPDATE:

q = Review.objects.values('book__name').annonate(average=Avg('rating')).filter().prefetech_related('rating')
q[0].ratings.all() # gives all the ratings of a particular book name
q[0].average # gives average of all the ratings of a particular book name

Hope this works (I'm not sure, sorry), but you need to add related_ name attribute

class Review(models.Model):
     book = models.ForeignKey(Book, related_name='rating')

UPDATE:

Sorry to say, but you need something called as GROUP_CONCAT in SQL , but it is not supported in Django ORM currently.

You can use Raw SQL or itertools

from django.db import connection
sql = """
    SELECT name, avg(rating) AS average, GROUP_CONCAT(rating) AS rating
    FROM book JOIN review on book.id = review.book_id
    GROUP BY name
     """
cursor = connection.cursor()
cursor.execute(sql)
data = cursor.fetchall()

DEMO

edited Mar 12 '14 at 13:11

answered Mar 12 '14 at 06:32

Anish Shah

7,669
8
29
40

Yes, that's working for average. But how do I get the rating as a list grouped by book name. Is that even possible? – user Mar 12 '14 at 06:40
It is correct, but I should have been more explicit about asking if it's possible 'in Database abstraction API' instead of in the views. I've emphasized the word `single query` but maybe it wasn't obvious that I'm trying to avoid looping through the Queryset in my `view` – user Mar 12 '14 at 09:01
This is an interesting approach. I'll read further. It's not working in the current form. Further the Django docs say `prefetch_related does a separate lookup for each relationship, and does the ‘joining’ in Python` - Probably doing the same thing I'm trying to avoid. There's a mention of `select_related` that I'll try. Appreciate your help. – user Mar 12 '14 at 11:27
I read up on select_related & prefetch_related. They are primarily for following ForeignKey 'type' relations. It won't work in this case. The data's already there when average is computed. How do I tell the DB to group that per field? – user Mar 12 '14 at 11:52
1

Hi-- I have updated the answer. It uses a **single query**. :) If it works, please mark it as accepted :) – Anish Shah Mar 12 '14 at 13:06

Django query - Is it possible to group elements by common field at database level?

1 Answers1