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

i have database model shown below. consider data 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 : possible group ratings in list, each book single query. i'm looking @ database level, without iterating on queryset in code. output should :

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

i've tried query, neither ratings grouped book name, nor average correct :

review.objects.annotate(average=avg('rating')).values('book__name','rating','average') 

edit : added clarification i'm looking method group elements @ database level.

you can this. hope helps.

review.objects.values('book__name').annonate(average=avg('rating')) 

update:

if want ratings of particular book in list, can this.

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

you structure :

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

update:

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

hope works (i'm not sure, sorry), need add related_ name attribute

class review(models.model):      book = models.foreignkey(book, related_name='rating') 

update:

sorry say, need called group_concat in sql , not supported in django orm currently.

you can use raw sql or itertools

from django.db import connection sql = """     select name, avg(rating) average, group_concat(rating) rating     book join review on book.id = review.book_id     group name      """ cursor = connection.cursor() cursor.execute(sql) data = cursor.fetchall() 

demo