Finding total score from specific user's maximum score in django orm

user_10637545 0
Viewed 192

I have a function that gets two parameters(contest_id,user_id), how could i get maximum score of each problem in given contest for the given user and then sum up all those max scores? each problem can have zero or many submitted scores.

for example : (problem_id,submitted_score) --> (1, 80), (1, 100), (2, 150), (2, 200), (3, 220), (3, 300)

expected result for this example should be 600, 100 + 200 + 300.

Models:

class Contest(models.Model):
    name = models.CharField(max_length=50)
    holder = models.ForeignKey(User, on_delete=models.CASCADE)
    start_time = models.DateTimeField()
    finish_time = models.DateTimeField()
    is_monetary = models.BooleanField(default=False)
    price = models.PositiveIntegerField(default=0)
    problems = models.ManyToManyField(Problem)
    authors = models.ManyToManyField(User, related_name='authors')
    participants = models.ManyToManyField(User, related_name='participants')

class Problem(models.Model):
    name = models.CharField(max_length=50)
    description = models.CharField(max_length=1000)
    writer = models.ForeignKey(User, on_delete=models.DO_NOTHING)
    score = models.PositiveIntegerField(default=100)

class Submission(models.Model):
    submitted_time = models.DateTimeField()
    participant = models.ForeignKey(User, related_name="submissions", on_delete=models.DO_NOTHING)
    problem = models.ForeignKey(Problem, related_name="submissions", on_delete=models.CASCADE)
    code = models.URLField(max_length=200)
    score = models.PositiveIntegerField(default=0)
django-queryset django-orm django
1 Answers

You can annotate the Problems with the maximum score of the related Submissions, and then sum these up, so:

from django.db.models import Max, Sum

Problem.objects.filter(
    contest__id=contest_id,
    submissions__participant_id=user_id
).annotate(
    max_score=Max('submissions__score')
).aggregate(
    total=Sum('max_score')
)

This will make a query that looks like:

SELECT SUM(max_score)
FROM (
    SELECT MAX(submission.score) AS max_score
    FROM problem
    INNER JOIN contest_problems ON problem.id = contest_problems.problem_id
    INNER JOIN submission ON problem.id = submission.problem_id
    WHERE contest_problems.contest_id = contest_id
      AND submission.participant_id = user_id
    GROUP BY problem.id
) subquery

If there are no related Submissions for any Problem for the given context, it will return NULL/None instead of 0.

user_675790
Related