Django:跨M2M关联用annotate为提交记录注解竞赛名称
从CompetitionSubmission查询集获取关联竞赛名称的实现
需求背景
- 模型关联关系:Competition 与 Team 通过中间模型 CompetitionTeam 实现多对多关联,每一组 CompetitionTeam 关联对可对应任意数量的 CompetitionSubmission 提交记录。
- 开发目标:制作仪表盘展示表格,呈现团队全量提交记录及对应竞赛名称,目标展示格式:
| Submission Name | Submission Date etc. | Competition Name |
|---|---|---|
| Sub01 | 2020-12-30 2000 | Competition01 |
现有模型定义
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
相关产品推荐
相关产品推荐

