Django如何通过Contest ID查询对应Submission的QuerySet

关联关系梳理 #

三个模型没有直接从Submission指向Contest的外键，关联路径为：

• Contest 通过多对多字段 problems 关联 Problem
• Problem 通过反向关联字段 submissions 关联 Submission

涉及模型定义 #

problem/models.py #

User = get_user_model()

class Problem(models.Model):
    name = models.CharField(max_length=50)
    description = models.CharField(max_length=1000)
    writer = models.ForeignKey(User,models.CASCADE)
    score = models.PositiveIntegerField(default=100) # my perfer is PositiveSmallIntegerField
    
class Submission(models.Model):
    submitted_time = models.DateTimeField(auto_now_add=True)
    participant = models.ForeignKey(User,related_name="submissions",on_delete=models.CASCADE)
    problem = models.ForeignKey(Problem,related_name="submissions",on_delete=models.CASCADE)
    code = models.URLField(max_length=200)
    score = models.PositiveIntegerField(default=0)

contest/models.py #

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

需求 #

传入Contest ID，返回纯Submission类型的QuerySet，结果格式示例：

<QuerySet [<Submission: Submission object (12)>, <Submission: Submission object (6)>, <Submission: Submission object (16)>, <Submission: Submission object (5)>, <Submission: Submission object (11)>, <Submission: Submission object (15)>, <Submission: Submission object (14)>, <Submission: Submission object (4)>, <Submission: Submission object (10)>, <Submission: Submission object (9)>, <Submission: Submission object (3)>, <Submission: Submission object (8)>, <Submission: Submission object (2)>, <Submission: Submission object (13)>, <Submission: Submission object (1)>, <Submission: Submission object (7)>]>

实现代码 #

基础实现（仅匹配比赛包含题目下的所有提交） #

通过problem__in匹配当前比赛关联的所有题目，返回结果就是纯Submission类型的QuerySet，完全符合格式要求：

from problem.models import Submission
from contest.models import Contest

def get_contest_submissions(contest_id: int):
    # 先获取目标比赛实例
    contest = Contest.objects.get(id=contest_id)
    # 筛选题目属于比赛题目的所有提交
    return Submission.objects.filter(problem__in=contest.problems.all())

生产环境增强版（符合比赛业务规则） #

实际比赛场景下需要额外过滤无效提交，避免统计非比赛时段、非参赛人员的提交，按需添加过滤条件即可：

from problem.models import Submission
from contest.models import Contest

def get_contest_submissions(contest_id: int):
    contest = Contest.objects.get(id=contest_id)
    # 提前取出比赛关联的题目、参赛人员ID列表，减少不必要的字段查询
    contest_problem_ids = contest.problems.values_list('id', flat=True)
    contest_participant_ids = contest.participants.values_list('id', flat=True)
    
    return Submission.objects.filter(
        # 题目属于比赛题目
        problem_id__in=contest_problem_ids,
        # 提交人属于参赛名单
        participant_id__in=contest_participant_ids,
        # 提交时间在比赛时段内
        submitted_time__gte=contest.start_time,
        submitted_time__lte=contest.finish_time
    ).select_related('problem', 'participant') # 提前缓存关联对象，避免N+1查询

注意：如果传入的contest_id不存在，Contest.objects.get()会抛出Contest.DoesNotExist异常，业务代码中需要按需捕获，接口场景可以替换为get_object_or_404(Contest, id=contest_id)直接返回404响应。
可以在filter后追加order_by()方法自定义排序规则，比如order_by('-submitted_time')按提交时间倒序排列，order_by('-score')按得分倒序排列。

内容的提问来源于stack exchange，提问作者Nova