时间戳

首页 > 解决方案 > 从 django orm 中特定用户的最高分中查找总分

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

问题描述

我有一个函数，它获取两个参数（比赛 ID，用户ID），我怎样才能获得给定用户在给定比赛中每个问题的最高分，然后总结所有这些最高分？每个问题可以有零个或多个提交分数。

例如：(problem_id,submitted_score) --> (1, 80), (1, 100), (2, 150), (2, 200), (3, 220), (3, 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)

标签： djangodjango-querysetdjango-orm

解决方案

您可以用相关sProblem的最大值来注释s，然后将它们总结起来，所以：scoreSubmission

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

这将进行如下查询：

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

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

推荐阅读