You need to enable JavaScript to run this app. Django:跨M2M关联用annotate为提交记录注解竞赛名称
优惠活动
大模型
产品
解决方案
定价
更多

Django:跨M2M关联用annotate为提交记录注解竞赛名称

从CompetitionSubmission查询集获取关联竞赛名称的实现

需求背景

  • 模型关联关系:Competition 与 Team 通过中间模型 CompetitionTeam 实现多对多关联,每一组 CompetitionTeam 关联对可对应任意数量的 CompetitionSubmission 提交记录。
  • 开发目标:制作仪表盘展示表格,呈现团队全量提交记录及对应竞赛名称,目标展示格式:
Submission NameSubmission Date etc.Competition Name
Sub012020-12-30 2000Competition01

现有模型定义

class Competition(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=30)

class CompetitionTeam(models.Model):
    competition_id = models.ForeignKey(
        'Competition',
        on_delete=models.CASCADE,
        to_field='id',
        db_column='competition_id'
    )
    team_id = models.ForeignKey(
        'Team',
        on_delete=models.CASCADE,
        to_field='id',
        null=True,
        db_column='team_id'
    )

class CompetitionSubmission(models.Model):
    competitionteam_id = models.ForeignKey(
        CompetitionTeam,
        on_delete=models.CASCADE,
        db_column='competitionteam_id'
    )

前期尝试的无效方案

  • 嵌套子查询注解写法:
    submissions.annotate(
        competition_name=Subquery(
            Competition.objects.filter(
                id=Subquery(CompetitionTeam.objects.get(id=OuterRef('competitionteam_id')).competition_id)
            ).values('name')
        )
    )
    
    运行报错:ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.
  • 预加载关联写法:
    CompetitionSubmission.objects.prefetch_related('competitionteam_id__competition_id')
    
    语句无运行报错,但无法正常从结果中提取对应竞赛名称。

最终可运行的实现方案

修正嵌套子查询的外部引用层级匹配逻辑后,可用注解写法如下:

submissions.annotate(
    competition_name=Subquery(
        Competition.objects.filter(
            id=Subquery(
                CompetitionTeam.objects.filter(
                    id=OuterRef(OuterRef('competitionteam_id_id'))
                ).values('competition_id')
            )
        ).values('name')
    )
)

内容的提问来源于stack exchange,提问作者Herougan No

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.16 18:06:03