How to get max score of answers to a question by each user-CodePudding

I have these 2 models:

class Question(models.Model):
    title = models.CharField(max_length=200)
    # other fields

class Answer(models.Model):
    user = models.ForeignKey(User)
    question = models.ForeignKey(Question)
    score = models.IntegerField()

each user can answer a question multiple times.

Imagine I have these answers:

{
     "user": 1,
     "question": 1,
     "score": 50
},
{
     "user": 1,
     "question": 1,
     "score": 100
},
{
     "user": 2,
     "question": 1,
     "score": 100
},
{
     "user": 2,
     "question": 1,
     "score": 200
},
{
     "user": 2,
     "question": 2,
     "score": 100
},
{
     "user": 2,
     "question": 2,
     "score": 200
}

I want a query to give me this result:

{
     "user": 1,
     "question": 1,
     "max_score": 100
},
{
     "user": 2,
     "question": 1,
     "max_score": 200
},
{
     "user": 2,
     "question": 2,
     "max_score": 200
}

I want all of the max scores of each user to each answer.

CodePudding user response：

I'm not sure how to achieve your goal with Django ORM, but you can do it with RawSQL

Answer.objects.raw("""
select a1.* from answer a1 LEFT JOIN answer a2
    ON (
        a1.user_id = a2.user_id and a1.score < a2.score
    )
where a2.user_id isnull
""")

Explanation: you get only records from you table, that have no bigger score from same table for each user.

CodePudding user response：

Try this:

from django.db.models import Max

Answer.objects.all().values("user", "question").annotate(score=Max("score"))