Group by users in django ORM

user_19517727 0
Viewed 54

I've a StudentAnswer model which stores the answer id given by the student(User) in a quiz. If the answer is correct then 1 marks else 0. I'm also ranking the users based on their marks in a particular quiz.

The model looks like this:

class StudentAnswer(models.Model):
user = models.ForeignKey(User, related_name='user_question_answer', 
            on_delete=models.SET_NULL,null=True, blank=True)
answer = models.ForeignKey(QuizQuestionAnswer, related_name='user_answer', 
            on_delete=models.CASCADE)
quiz = models.ForeingKey(Quiz, on_delete=models.CASCADE)
marks = models.IntegerField()

This is the query I'm using to rank my users:

StudentAnswer.objects.filter(
        quiz__start_date__month=date.today().month).annotate(
        rank=Window(expression=DenseRank(), order_by=[F('marks').desc(),])
    )

A user will have multiple entries in table(number of questions he attempted in a month). I want to group by each user, add their marks and then rank them for that particular month and send the response.

How can I do this?

django-views django-orm django-models django python
1 Answers

You can order the Users by the sum of the marks in reverse order, so:

from django.db.models import Sum
from django.utils.timezone import now

User.objects.filter(
    user_question_answer__quiz__start_date__month=now().month
).annotate(
    total_marks=Sum('user_question_answer__marks')
).order_by('-total_marks')

or for a specific Quiz:

from django.db.models import Sum

User.objects.filter(
    user_question_answer__quiz_id=quiz_id
).annotate(
    total_marks=Sum('user_question_answer__marks')
).order_by('-total_marks')

Note: It is normally better to make use of the settings.AUTH_USER_MODEL [Django-doc] to refer to the user model, than to use the User model [Django-doc] directly. For more information you can see the referencing the User model section of the documentation.

user_675790
Related