CODEWITHSUNDEEP
djangodjango-querysetdjango-orm
Rorschach_88
Rorschach_88

Reputation: 13

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

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)

Upvotes: 1

Views: 334

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476699

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.

Upvotes: 2

Related Questions