Django 3.2联合查询集执行Sum聚合触发DatabaseError求解决

使用Django 3.2，需要操作两个不同模型的查询，需求如下：

• 重命名返回列，使查询集可执行union操作
• 使用annotate重命名字段后，将两个查询集union
• 对联合后的查询集执行Sum聚合函数（此步骤报错）

模型代码 #

class EventCategory(models.Model):
    name = models.CharField(max_length=16)


class Event(models.Model):
    name = models.CharField(max_length=32)
    category = models.ForeignKey(EventCategory, on_delete=models.CASCADE)


class Tournament(models.Model):
    name = models.CharField(max_length=32)
    category = models.ForeignKey(EventCategory, on_delete=models.CASCADE)
    prize_money = models.IntegerField()


class TournamentAward(models.Model):
    awardee =  models.ForeignKey(setting.AUTH_USER_MODEL, on_delete=models.CASCADE)
    tournament = models.ForeignKey(Tournament, on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now_add=True)


class Game(models.Model):
    player =  models.ForeignKey(setting.AUTH_USER_MODEL, on_delete=models.CASCADE)
    event = models.ForeignKey(Event, on_delete=models.CASCADE)
    payment = models.SmallPositiveInteger()
    created_at = models.DateTimeField(auto_now_add=True)

查询代码 #

payment_earned_today = Game.objects.filter(
                                      player=user,
                                      created_at__year=year, 
                                      created_at__month=month, 
                                      created_at__day=day
                                        ).annotate(category=models.F('event__category'))\
                                   .only('category','payment')
        
        
tournament_prize_today = TournamentAward.objects.filter(
                                      awardee=user,
                                      created_at__year=year, 
                                      created_at__month=month, 
                                      created_at__day=day
                                  ).select_related('tournament__category')\                                            
                          .annotate(category=models.F('tournament__category'))\                                            
                          .annotate(payment=models.F('tournament__prize_money'))\
                          .only('category','payment')


# Union the two querysets ...
union_queryset = payment_earned_today.union( tournament_prize_today )
results = union_queryset.aggregate(total=models.Sum('payment'))

执行聚合计算时触发错误：

django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements

错误根源是Django默认会给查询集添加排序规则（比如模型的ordering元选项、隐式主键排序），而复合查询（如union）的子查询中不允许出现ORDER BY子句。解决核心是先清空两个原始查询集的默认排序，再执行后续操作。

修改步骤 #

1. 在两个查询集末尾添加.order_by()，显式清空默认排序规则（only()方法不会移除默认排序，必须手动调用）
2. 确保两个查询集的字段数量、数据类型完全匹配（你的代码已通过annotate对齐category和payment字段，这部分无需调整）

修改后的代码 #

payment_earned_today = Game.objects.filter(
                                      player=user,
                                      created_at__year=year, 
                                      created_at__month=month, 
                                      created_at__day=day
                                        ).annotate(category=models.F('event__category'))\
                                   .only('category','payment')\
                                   .order_by()  # 清空默认排序
        
        
tournament_prize_today = TournamentAward.objects.filter(
                                      awardee=user,
                                      created_at__year=year, 
                                      created_at__month=month, 
                                      created_at__day=day
                                  ).select_related('tournament__category')\                                            
                          .annotate(category=models.F('tournament__category'))\                                            
                          .annotate(payment=models.F('tournament__prize_money'))\
                          .only('category','payment')\
                          .order_by()  # 清空默认排序


# Union the two querysets ...
union_queryset = payment_earned_today.union(tournament_prize_today)
results = union_queryset.aggregate(total=models.Sum('payment'))

如果不需要对联合结果去重，可使用union(tournament_prize_today, all=True)，但这不会影响聚合计算的最终结果。

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