Django计算匹配的分数(在并集后对查询进行注释的变通方法)

Question

我需要计算拳击比赛中运动员的排名。在我的模型中，我跟踪每一场比赛的结果，以及每一场结果的得分。

class Member(models.Model):
surname = models.CharField(max_length=200)
last_name = models.CharField(max_length=200)
class Tournament(models.Model):
name = models.CharField(max_length=200)
class TrophyRule(models.Model):
win = models.IntegerField()
loose = models.IntegerField()
draw = models.IntegerField()
class Ring(models.Model):
code = models.CharFieldmax_length=1)
tournament = models.ForeignKey(Tournament, on_delete=models.CASCADE)
class Match(models.Model):
ring = models.ForeignKey(Ring, null=True, on_delete=models.SET_NULL)
winner = models.CharField(max_length=20, null=True, blank=True)
trophy_rule = models.ForeignKey(TrophyRule, on_delete=models.SET_NULL, null=True)
red_member = models.ForeignKey(Member, related_name='reds', on_delete=models.SET_NULL, null=True)
red_count_ranking = models.BooleanField(default=True)
blue_member = models.ForeignKey(Member, related_name='blues', on_delete=models.SET_NULL, null=True)
blue_count_ranking = models.BooleanField(default=True)

基于这个模型，我需要将运动员在红角时获得的分数与运动员在蓝角时获得获得的分数相加。结果应该是一个包含所有成员及其总点数的查询集。

为了实现这一点，我首先计算运动员在红角获得的分数：

from apps.members.models import Member
from django.db.models import Case, Sum, When, Q
red = Member.objects.filter(reds__ring__tournament_id=11402).annotate(
points=Case(
When(Q(reds__winner='red') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__win'),
When(Q(reds__winner='draw') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__draw'),
When(Q(reds__winner='blue') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__loose'),
),
)

我也对蓝角的运动员获得的分数做了同样的处理：

blue = Member.objects.filter(blues__ring__tournament_id=11402).annotate(
points=Case(
When(Q(blues__winner='blue') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__win'),
When(Q(blues__winner='draw') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__draw'),
When(Q(blues__winner='red') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__loose'),
),
)

现在，我需要将这两个查询结合起来，并对每个运动员的积分求和。这是我目前陷入困境的部分。

我尝试使用union((，它可以转换为SQL union:

red.union(blue)

如果我有4个匹配项，使用union((我会得到一个包含8个成员(4个红色和4个蓝色(的查询集，这正是我想要的。不幸的是，当我试图计算最终的点数(运动员为红色时的点数+运动员为蓝色时的点数(时，我触发了错误：不支持在union((之后调用QuerySet.annotate(((根据文档(。

red.union(blue).annotate(Sum('points'))

Django ORM还有其他方法可以实现这一点吗？如果没有必要，我宁愿不恢复到原始SQL。

Answer 1

这可能在单个请求(未经测试的代码(中实现：

from django.db.models import Case, When, Q, F
members = Member.objects.filter(Q(reds__ring__tournament_id=11402)|Q(blues__ring__tournament_id=11402)).annotate(
red_points=Case(
When(Q(reds__winner='red') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__win'),
When(Q(reds__winner='draw') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__draw'),
When(Q(reds__winner='blue') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__loose'),
),
blue_points=Case(
When(Q(blues__winner='blue') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__win'),
When(Q(blues__winner='draw') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__draw'),
When(Q(blues__winner='red') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__loose'),
),
points=F('red_points') + F('blue_points')
)