从 django orm 中特定用户的最高分中查找总分

Question

我有一个函数，得到两个参数(contest_id,user_id)，我如何获得给定用户在给定竞赛中每个问题的最大分数，然后将所有这些最大分数相加?每个问题可以有0或多个提交的分数。

for example:(problem_id,submitted_score)——>(80)、(100)、(150)、(200)、(220)、(300)

这个例子的预期结果应该是600,100 + 200 + 300。

<标题>模型:

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)

Answer 1

可以用相关Submissions的最大score来标注Problems，然后求和，即:

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')
)

这会生成如下的查询:

SELECTSUM(max_score)
FROM (
SELECTMAX(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

如果在给定的上下文中没有与任何Problem相关的Submission，它将返回NULL/None而不是0。