How to group by with annotate in django?
I tried absolutely everything with values().annotate() or aggregate() , or by following tutorials with Subquery I cant get what I want, let me explain :
There is what I get with QuizzUserItem.objects.filter(examuser=user_exam).values('item__question', 'item')
{'item': 759, 'item__question': 429}
{'item': 762, 'item__question': 430}
{'item': 763, 'item__question': 430}
{'item': 767, 'item__question': 431}
What I want is to group by item__question and get this result :
{'item': 759, 'item__question': 429}
{'item': 762,763 'item__question': 430}
{'item': 767, 'item__question': 431}
I start to think there is absolutely no way :/
What I tried :
QuizzUserItem.objects.filter(examuser=user_exam).values('item__question','item').annotate(items_number=Count('item__question'))
I got :
{'item': 759, 'item__question': 429, 'items_number': 1}
{'item': 762, 'item__question': 430, 'items_number': 1}
{'item': 763, 'item__question': 430, 'items_number': 1}
{'item': 767, 'item__question': 431, 'items_number': 1}
or
QuizzUserItem.objects.filter(examuser=user_exam).values('item__question').annotate(items_number=Count('item__question'))
This one is the closest result that I got :
{'item__question': 429, 'items_number': 1}
{'item__question': 430, 'items_number': 2}
{'item__question': 431, 'items_number': 1}
But I have not the item_ids 762,763...
Thank you for reading
You can work with the ArrayAgg
aggregate [Django-doc] to obtain a list of items:
from django.contrib.postgres.aggregates import ArrayAgg
QuizzUserItem.objects.filter(
examuser=user_exam
).values(
'item__question'
).annotate(
items=ArrayAgg('item__question')
).order_by('item__question')