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.

Answer 1

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