Multilevel annotation/aggregation using django ORM

Asked Feb 28 '18 at 07:58

Active Mar 01 '18 at 03:42

Viewed 154 times

I have been trying to implement pretty much the same thing as in query-for-total-score

Does Django support multilevel aggregation? I can get the max score for each user each challenge, but I don't know how to drill down my results to fit in.

s = Submission.objects.values('challenge','user').annotate(Max('score'),Max('total_score'))

This gives me list of dict with user, challenge, total_score and score

What I want is sum of scores for each user. When I use aggregate on this I am getting the sum of all scores

s.aggregate(Sum('total_score__max'),Sum(score__max))

Postgres sql query looks something like this

select "user_id", SUM("score__max") as "score", SUM("total_score__max") as "total_score", COUNT("challenge_id") as "count" from 
(SELECT "submission"."user_id", "submission"."challenge_id", MAX("submission"."score")
AS "score__max", MAX("submission"."total_score") AS "total_score__max" FROM "submission" 
GROUP BY "submission"."user_id", "submission"."challenge_id")t GROUP BY "user_id" ORDER BY "user_id";

edited Mar 01 '18 at 03:42

asked Feb 28 '18 at 07:58

Bhavani Ravi

2,130
3
18
41

Postgresql or mysql? – Anup Yadav Feb 28 '18 at 09:43
I am using postgresql – Bhavani Ravi Feb 28 '18 at 09:48
Your core query would be like this right? `SELECT 'challenge','user',Max('score'), Max('total_score'), FROM submission GROUP BY Max('score'), Max('total_score'), user, challenge` – Anup Yadav Feb 28 '18 at 09:59
Yup that's right – Bhavani Ravi Feb 28 '18 at 12:36
Hey! I forgot what I wanted to text here for you, anyways now what exactly you need? – Anup Yadav Feb 28 '18 at 12:44
I want that sql query into django ORM statement. Is it possible ? If not how to do it efficiently. If I use a aggregate on above statement it gives me total score of all questions and user. – Bhavani Ravi Mar 01 '18 at 02:23
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166010/discussion-between-bhavani-ravi-and-anup-yadav). – Bhavani Ravi Mar 01 '18 at 03:43

Multilevel annotation/aggregation using django ORM

0 Answers0