Django+MSSQL外键关联统计对象数量异常问题求助

环境信息 #

• 数据库: MSSQL
• Django 3.2.20
• mssql-django 1.3

模型定义 #

class ChangeOrder(models.Model):
    # ...其他字段...

class GroupChange(models.Model):
    order = models.ForeignKey(
        ChangeOrder, related_name='groupchanges'
    )
    action = models.CharField(max_length=10, choices=['assignment', 'removal'])
    # ...其他字段...

class UserChange(models.Model):
    order = models.ForeignKey(
        ChangeOrder, related_name='userchanges'
    )
    action = models.CharField(max_length=10, choices=['assignment', 'removal'])
    # ...其他字段...

需求目标 #

为每个ChangeOrder对象添加注解，计算以下两个值:

• ant_assignment_count: GroupChange和UserChange中action为assignment的总数量。
• ant_removal_count: GroupChange和UserChange中action为removal的总数量。

当前查询语句 #

ChangeOrder.objects.annotate(
    ant_assignment_count=Sum(
       Case(
           When(userchanges__action='assignment', then=1),
           When(groupchanges__action='assignment', then=1),
           default=0, output_field=IntegerField()
       )
   ),
   ant_removal_count=Sum(
       Case(
           When(userchanges__action='removal', then=1),
           When(groupchanges__action='removal', then=1),
           default=0, output_field=IntegerField()
       )
   )
)

测试数据 #

co = ChangeOrder.objects.create()
GroupChange.objects.create(order=co, action='removal', ..)
GroupChange.objects.create(order=co, action='removal', ..)
UserChange.objects.create(order=co, action='assignment', ..)

问题现象 #

执行上述查询后，得到的结果为ant_assignment_count=2和ant_removal_count=2，但预期结果应为ant_assignment_count=1和ant_removal_count=2。尝试过注解、子查询、结合Case的Count语句等多种方法均无效，推测问题出在GroupChange和UserChange的LEFT OUTER JOIN操作导致的数据重复计算。

解决方案 #

问题核心是同时关联两张子表时，Django生成的SQL会产生笛卡尔积，导致单条子表记录被多次统计。以下两种方法可解决：

方法一：使用Count+Distinct（推荐，简洁高效） #

利用Count的distinct参数确保每个子表对象只被计数一次，避免重复：

from django.db.models import Count, Q

ChangeOrder.objects.annotate(
    ant_assignment_count=Count(
        'userchanges', 
        filter=Q(userchanges__action='assignment'), 
        distinct=True
    ) + Count(
        'groupchanges', 
        filter=Q(groupchanges__action='assignment'), 
        distinct=True
    ),
    ant_removal_count=Count(
        'userchanges', 
        filter=Q(userchanges__action='removal'), 
        distinct=True
    ) + Count(
        'groupchanges', 
        filter=Q(groupchanges__action='removal'), 
        distinct=True
    )
)

方法二：子查询分别统计后相加 #

通过子查询单独统计每张子表的符合条件数量，再在主查询中求和，彻底避免多表连接的笛卡尔积问题：

from django.db.models import Subquery, OuterRef, IntegerField, Sum, Case, When, F
from django.db.models.functions import Coalesce

# 子查询统计GroupChange各类操作数量
group_assignment_subq = GroupChange.objects.filter(
    order=OuterRef('pk'), action='assignment'
).values('order').annotate(count=Sum(Case(When(action='assignment', then=1), default=0))).values('count')

group_removal_subq = GroupChange.objects.filter(
    order=OuterRef('pk'), action='removal'
).values('order').annotate(count=Sum(Case(When(action='removal', then=1), default=0))).values('count')

# 子查询统计UserChange各类操作数量
user_assignment_subq = UserChange.objects.filter(
    order=OuterRef('pk'), action='assignment'
).values('order').annotate(count=Sum(Case(When(action='assignment', then=1), default=0))).values('count')

user_removal_subq = UserChange.objects.filter(
    order=OuterRef('pk'), action='removal'
).values('order').annotate(count=Sum(Case(When(action='removal', then=1), default=0))).values('count')

# 主查询注解并求和
ChangeOrder.objects.annotate(
    g_assign=Subquery(group_assignment_subq, output_field=IntegerField()),
    u_assign=Subquery(user_assignment_subq, output_field=IntegerField()),
    g_remove=Subquery(group_removal_subq, output_field=IntegerField()),
    u_remove=Subquery(user_removal_subq, output_field=IntegerField())
).annotate(
    ant_assignment_count=Coalesce(F('g_assign'), 0) + Coalesce(F('u_assign'), 0),
    ant_removal_count=Coalesce(F('g_remove'), 0) + Coalesce(F('u_remove'), 0)
)

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