Django aggregate()计算订单总额错误，实际应为1200却得300求修复

问题背景 #

在Django项目的ReportView视图中，对店铺订单数据做聚合查询时出现异常：手动遍历店铺计算符合状态的订单subtotal总和为1200，但通过aggregate方法得到的all_orders结果仅为300。同时需要后续扩展计算净金额、佣金、利润等聚合指标。

相关代码与打印结果 #

视图代码

class ReportView(AdminOnlyMixin, ListView):
    model = homecleaners
    template_name = 'home_clean/report/store_list.html'
    context_object_name = 'stores'
    paginate_by = 20
    ordering = ['-id']
    valid_statuses = [2, 3, 5]

    def get_queryset(self):
        queryset = super().get_queryset()
        search_text = self.request.GET.get('search_text')
        picked_on = self.request.GET.get('picked_on', None)

        if search_text:
            queryset = queryset.filter(store_name__icontains=search_text)
        if picked_on:
            date_range = picked_on.split(' to ')
            start_date = parse_date(date_range[0])
            end_date = parse_date(date_range[1]) if len(date_range) > 1 else None
            date_filter = {'orders__timeslot__date__range': [start_date, end_date]} if end_date else {'orders__timeslot__date': start_date}
            queryset = queryset.filter(**date_filter)

        status_filter = Q(orders__status__in=self.valid_statuses)

        queryset = queryset.prefetch_related('orders').annotate(
            orders_count=Count('orders__id', filter=status_filter),
            subtotal=Sum('orders__subtotal', filter=status_filter),
            store_discount=Sum(
                Case(
                    When(Q(orders__promocode__is_store=True) & status_filter, then='orders__discount'),
                    default=Value(0),
                    output_field=FloatField()
                )
            ),
            admin_discount=Sum(
                Case(
                    When(Q(orders__promocode__is_store=False) & status_filter, then='orders__discount'),
                    default=Value(0),
                    output_field=FloatField()
                )
            ),
            total_sales=Sum(
                F('orders__subtotal') - Case(
                    When(Q(orders__promocode__is_store=True), then=F('orders__discount')),
                    default=Value(0),
                    output_field=FloatField()
                ),
                filter=status_filter
            ),
            commission=Sum(
                (F('orders__subtotal') - Case(
                    When(Q(orders__promocode__is_store=True), then=F('orders__discount')),
                    default=Value(0),
                    output_field=FloatField()
                )) * F('earning_percentage') / 100,
                filter=status_filter
            )
        )

        return queryset.distinct()

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        context['count'] = context['paginator'].count

        # Calculate store-level aggregates
        status_filter = Q(orders__status__in=self.valid_statuses)
        store_totals = {}
        for store in self.object_list:
            total = store.orders.filter(status__in=self.valid_statuses).aggregate(
                subtotal=Sum('subtotal')
            )['subtotal'] or 0
            store_totals[store.store_name] = total

        print("Store Totals------:", store_totals)
        store_total_aggr = self.object_list.aggregate(all_orders=Sum('orders__subtotal', filter=status_filter, default=0))
        print("Store Total Aggregates---------:", store_total_aggr)
        
        return context

打印结果

Store Totals------: {'aminu1': 600.0, 'Golden Touch': 0, 'hm': 100.0,
'Silk Hospitality': 0, 'Test clean': 0, 'Razan Hospitality': 0,
'Enertech Cleaning': 0, 'Bait Al Karam Hospitality': 0, 'Clean pro':
0, 'Dr.Home': 0, 'Dust Away': 0, 'Al Kheesa General Cleaning': 0,
'test': 0, 'fresho': 0, 'justmop': 0, 'cleanpro': 0, 'Test Store':500.0}
Store Total Aggregates---------: {'all_orders': 300.0}

模型代码

class HomecleanOrders(models.Model):
    user = models.ForeignKey(Customer, on_delete=models.CASCADE, null=True, blank=True)
    store = models.ForeignKey(homecleaners, on_delete=models.CASCADE, related_name='orders')
    service_area = models.ForeignKey(HomeCleanServiceArea, on_delete=models.SET_NULL, null=True, blank=True)
    timeslot = models.ForeignKey(HomecleanSlots, on_delete=models.SET_NULL, null=True)
    address = models.ForeignKey(Address, on_delete=models.SET_NULL, null=True, blank=True)
    duration = models.IntegerField(default=4)
    ref_id = models.UUIDField(default=uuid.uuid4, editable=False, null=True, blank=True)
    date = models.DateField(null=True, blank=True)
    no_of_cleaners = models.IntegerField()
    cleaner_type = models.CharField(max_length=4, choices=CLEANER_TYPES, null=True, blank=True)
    material = models.BooleanField()
    material_charges = models.FloatField(default=0)
    credit_card = models.ForeignKey('customer.CustomerCard', on_delete=models.SET_NULL, null=True, blank=True)
    payment_type = models.CharField(choices=PAYMENT_CHOICES, default='COD1', max_length=10)
    wallet = models.BooleanField(default=False)
    deducted_from_wallet = models.FloatField(default=0)
    status = models.IntegerField(choices=STATUS_CHOICES, default=WAITING)
    state = models.IntegerField(choices=STATE_CHOICES, null=True, blank=True)
    subtotal = models.FloatField(default=0)
    grand_total = models.FloatField(default=0)
    extra_charges = models.FloatField(default=0)
    discount = models.FloatField(default=0)
    to_pay = models.FloatField(default=0)
    is_paid = models.BooleanField(default=False)
    is_refunded = models.BooleanField(default=False)
    is_payment_tried = models.BooleanField(default=False)
    vendor_confirmed = models.BooleanField(default=False)
    promocode = models.ForeignKey(PromoCodeV2, on_delete=models.SET_NULL, null=True, blank=True)
    vendor_settled = models.BooleanField(default=False)
    cancel_reason = models.TextField(null=True, blank=True)
    workers = models.ManyToManyField(HomeCleanWorker, blank=True)
    created_on = models.DateTimeField(auto_now_add=True)
    invoice_id = models.CharField(max_length=200, null=True, blank=True)
    bill_id = models.CharField(max_length=200, null=True, blank=True)

    instructions = models.CharField(max_length=255, null=True, blank=True)
    voice_instructions = models.FileField(upload_to='voice_instructions', null=True, blank=True)
    dont_call = models.BooleanField(default=False)
    dont_ring = models.BooleanField(default=False)
    leave_at_reception = models.BooleanField(default=False)

    def save(self, *args, **kwargs):
        super(HomecleanOrders, self).save(*args, **kwargs)
        self.create_transaction_if_needed()

    def create_transaction_if_needed(self):
        """Creates a transaction for the order if it is marked as DONE, payment type is COD, and is paid."""
        if self.is_paid and self.status == DONE and self.payment_type == 'COD1' and not hasattr(self, 'transaction'):
            amount = self.subtotal - (self.discount + self.deducted_from_wallet)
            HCTransaction.objects.create(order=self, amount=amount, store=self.store)

    def __str__(self):
        return f'{self.id}-{self.store}-{self.user}'

    def process_totals(self, auto_order=False, custom_price=0):
        hours_charge = self.service_area.hours_charge if self.service_area else self.store.hours_charge
        weekday = self.date.strftime("%w")
        price = HCWeekdayOffer.objects.get(vendor=self.store).get_weekday_price(week_num=int(weekday))
        price = hours_charge - price if price and price > 0 else hours_charge
        if auto_order:
            price = custom_price if int(custom_price) > 0 else self.store.recurring_price or hours_charge
        self.subtotal = (self.duration * price) * self.no_of_cleaners + self.extra_charges
        if self.material:
            total_material_charge = self.material_charges * self.no_of_cleaners
            self.subtotal += (self.duration * total_material_charge)
        offer_amount = 0
        if self.promocode:
            is_free_delivery, is_cash_back, offer_amount = self.promocode.get_offer_amount(self.subtotal)
            if is_cash_back:
                offer_amount = 0
        grand_total = self.subtotal - offer_amount
        self.grand_total = grand_total
        self.to_pay = grand_total
        self.discount = offer_amount
        self.save()

    def get_cleaner_profit(self):
        a = self.subtotal
        result = a * 0.7
        result_round = round(result, 2)
        return result_round

    @cached_property
    def order_type(self):
        return 'home_clean'

class homecleaners(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, limit_choices_to={'user_type': 5})
    store_name = models.CharField(max_length=88)
    store_name_arabic = models.CharField(max_length=88)
    description = models.CharField(max_length=150, null=True, blank=True)
    description_arabic = models.CharField(max_length=150, null=True, blank=True)
    cleaner_type = models.CharField(max_length=4, choices=CLEANER_TYPES, default=BOTH)
    earning_percentage = models.FloatField(default=30)
    image = models.ImageField(upload_to='homecleaners/logos')
    address = models.TextField()

问题原因 #

核心是多表关联后的分组聚合与全局聚合冲突：

1. 在get_queryset中，已对homecleaners进行annotate分组聚合，每个店铺对应一行数据，且包含该店铺的订单聚合字段（如subtotal）。
2. 调用self.object_list.aggregate(Sum('orders__subtotal'))时，Django会再次关联orders表执行求和，但此时QuerySet已分组去重，导致SQL逻辑错误，最终计算结果异常。

修复方案 #

方案一：复用已annotate的聚合字段求和 #

直接对get_queryset中已计算好的店铺级subtotal字段求和，无需再次关联订单表：

# 替换原aggregate代码
store_total_aggr = self.object_list.aggregate(all_orders=Sum('subtotal', default=0))

方案二：直接从订单模型做全局聚合 #

绕过店铺QuerySet的关联问题，直接从订单模型查询并应用过滤条件：

# 获取当前筛选后的店铺ID集合
store_ids = self.object_list.values_list('id', flat=True)
status_filter = Q(status__in=self.valid_statuses)
date_filter = Q()

# 复用日期过滤条件
picked_on = self.request.GET.get('picked_on', None)
if picked_on:
    date_range = picked_on.split(' to ')
    start_date = parse_date(date_range[0])
    end_date = parse_date(date_range[1]) if len(date_range) > 1 else None
    date_filter = Q(timeslot__date__range=[start_date, end_date]) if end_date else Q(timeslot__date=start_date)

# 直接从订单模型计算全局总和
store_total_aggr = HomecleanOrders.objects.filter(
    store_id__in=store_ids,
    **status_filter,
    **date_filter
).aggregate(all_orders=Sum('subtotal', default=0))

后续扩展：计算净金额、佣金、利润等指标 #

基于修复方案，可扩展以下全局聚合计算：

基于annotate字段扩展 #

# 在get_context_data中添加
global_aggregates = self.object_list.aggregate(
    all_total_sales=Sum('total_sales', default=0),
    all_commission=Sum('commission', default=0),
    all_store_discount=Sum('store_discount', default=0),
    all_admin_discount=Sum('admin_discount', default=0)
)
# 计算店铺利润：总销售额 - 佣金
global_aggregates['all_profit'] = global_aggregates['all_total_sales'] - global_aggregates['all_commission']
context['global_aggregates'] = global_aggregates

直接从订单模型扩展 #

global_aggregates = HomecleanOrders.objects.filter(
    store_id__in=store_ids,
    **status_filter,
    **date_filter
).annotate(
    store_earning=F('store__earning_percentage')
).aggregate(
    all_subtotal=Sum('subtotal', default=0),
    all_store_discount=Sum(
        Case(When(Q(promocode__is_store=True), then='discount'), default=0, output_field=FloatField()),
        default=0
    ),
    all_admin_discount=Sum(
        Case(When(Q(promocode__is_store=False), then='discount'), default=0, output_field=FloatField()),
        default=0
    ),
    all_total_sales=Sum(
        F('subtotal') - Case(When(Q(promocode__is_store=True), then=F('discount')), default=0, output_field=FloatField()),
        default=0
    ),
    all_commission=Sum(
        (F('subtotal') - Case(When(Q(promocode__is_store=True), then=F('discount')), default=0, output_field=FloatField())) * F('store_earning') / 100,
        default=0
    )
)
global_aggregates['all_profit'] = global_aggregates['all_total_sales'] - global_aggregates['all_commission']
context['global_aggregates'] = global_aggregates

完整修复后的get_context_data代码 #

def get_context_data(self, **kwargs):
    context = super().get_context_data(**kwargs)
    context['count'] = context['paginator'].count

    # Calculate store-level aggregates
    status_filter = Q(orders__status__in=self.valid_statuses)
    store_totals = {}
    for store in self.object_list:
        total = store.orders.filter(status__in=self.valid_statuses).aggregate(
            subtotal=Sum('subtotal')
        )['subtotal'] or 0
        store_totals[store.store_name] = total

    print("Store Totals------:", store_totals)
    # 修复后的全局总和计算
    store_total_aggr = self.object_list.aggregate(all_orders=Sum('subtotal', default=0))
    print("Store Total Aggregates---------:", store_total_aggr)

    # 扩展全局聚合指标
    global_aggregates = self.object_list.aggregate(
        all_total_sales=Sum('total_sales', default=0),
        all_commission=Sum('commission', default=0),
        all_store_discount=Sum('store_discount', default=0),
        all_admin_discount=Sum('admin_discount', default=0)
    )
    global_aggregates['all_profit'] = global_aggregates['all_total_sales'] - global_aggregates['all_commission']
    context['global_aggregates'] = global_aggregates
    
    return context

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