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?

Thanks in advance.

CodePudding user response：

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.