如何在Django ORM中查询商品维度的库存与营收数据？

需求背景 #

正在开发库存看板功能，需要展示商品维度的库存与营收数据。目前已通过Django ORM实现了总库存与总营收的计算，但需要修改为按商品（InvItem）维度进行统计。

现有视图代码 #

class RevenueStockDashboardViewset(ViewSet):

    sale = InvSaleDetail.objects.filter(sale_main__sale_type='SALE')
    sale_returns = InvSaleDetail.objects.filter(sale_main__sale_type='RETURN')
    purchase = InvPurchaseDetail.objects.filter(purchase_main__purchase_type='PURCHASE')
    purchase_returns = InvPurchaseDetail.objects.filter(purchase_main__purchase_type='RETURN')

    def list(self, request):

        total_revenue = (self.sale.annotate(total=Sum('sale_main__grand_total'))
                        .aggregate(total_revenue=Sum('total'))
                        .get('total_revenue') or 0) - (self.sale_returns.annotate(total=Sum('sale_main__grand_total'))
                        .aggregate(total_revenue=Sum('total'))
                        .get('total_revenue') or 0) - (self.purchase.annotate(total=Sum('purchase_main__grand_total'))
                        .aggregate(total_revenue=Sum('total'))
                        .get('total_revenue') or 0) + (self.purchase_returns.annotate(total=Sum('purchase_main__grand_total'))
                        .aggregate(total_revenue=Sum('total'))
                        .get('total_revenue') or 0)
        
        total_stocks = (self.purchase.annotate(total=Sum('qty'))
                    .aggregate(total_stock=Sum('total'))
                    .get('total_stock') or 0) - (self.purchase_returns.annotate(total=Sum('qty'))
                    .aggregate(total_stocks=Sum('total'))
                    .get('total_stock') or 0) - (self.sale.annotate(total=Sum('qty'))
                    .aggregate(total_stock=Sum('total'))
                    .get('total_stock') or 0) - (self.sale_returns.annotate(total=Sum('qty'))
                    .aggregate(total_stock=Sum('total'))
                    .get('total_stock') or 0)

        
        return Response(        
            {
                'total_revenue': total_revenue,
                'total_stocks': total_stocks,
                # 'item_wise_stocks': items,
            }
        )

相关模型定义 #

Item Model #

class InvItem(CreatedInfoModel):

    name = models.CharField(
        max_length=100,
        unique=True,
        help_text="Item name should be max. of 100 characters",
    )
    code = models.CharField(
        max_length=10,
        unique=True,
        blank=True,
        help_text="Item code should be max. of 10 characters",
    )

Sale Models #

class InvSaleDetail(CreatedInfoModel):

    sale_main = models.ForeignKey(
        InvSaleMain, related_name="inv_sale_details", on_delete=models.PROTECT
    )
    item = models.ForeignKey(InvItem, on_delete=models.PROTECT)
    item_category = models.ForeignKey(InvItemCategory, on_delete=models.PROTECT)
    cost = models.DecimalField(
        max_digits=12,
        decimal_places=2,
        help_text="cost can have max value upto=9999999999.99 and default=0.0",
    )
    qty = models.DecimalField(
        max_digits=12,
        decimal_places=2,
        help_text="Purchase quantity can have max value upto=9999999999.99 and min_value=0.0",
    )

    sale_qty = models.DecimalField(
        max_digits=12,
        decimal_places=2,
        help_text="Sale quantity can be max value upto 9999999999.99",
    )

Purchase Models #

class InvPurchaseDetail(CreatedInfoModel):
    purchase_main = models.ForeignKey(
        InvPurchaseMain,
        related_name="purchase_details",
        on_delete=models.PROTECT,
    )
    item = models.ForeignKey(InvItem, on_delete=models.PROTECT)
    item_category = models.ForeignKey(InvItemCategory, on_delete=models.PROTECT)
    purchase_cost = models.DecimalField(
        max_digits=12,
        decimal_places=2,
        default=0.0,
        help_text="purchase_cost can be max value upto 9999999999.99 and default=0.0",
    )
    sale_cost = models.DecimalField(
        max_digits=12,
        decimal_places=2,
        help_text="sale_cost can be max value upto 9999999999.99 and default=0.0",
    )
    qty = models.DecimalField(
        max_digits=12,
        decimal_places=2,
        help_text="Purchase quantity can be max value upto 9999999999.99",
    )

需求说明 #

• 按商品（InvItem）维度，复用现有总营收/总库存的计算逻辑：
  • 营收=销售额-销售退回-采购额+采购退回
  • 库存=采购量-采购退回-销售量-销售退回
• 在接口返回中添加商品维度的库存与营收字段。

修改后的实现方案 #

优化后视图代码 #

from django.db.models import Sum, F
from rest_framework.response import Response
from rest_framework.viewsets import ViewSet

class RevenueStockDashboardViewset(ViewSet):

    def list(self, request):
        # 定义查询集（移至方法内避免类级别缓存问题）
        sale = InvSaleDetail.objects.filter(sale_main__sale_type='SALE')
        sale_returns = InvSaleDetail.objects.filter(sale_main__sale_type='RETURN')
        purchase = InvPurchaseDetail.objects.filter(purchase_main__purchase_type='PURCHASE')
        purchase_returns = InvPurchaseDetail.objects.filter(purchase_main__purchase_type='RETURN')

        # 总营收计算（保留原有逻辑）
        total_revenue = (
            sale.annotate(total=Sum('sale_main__grand_total')).aggregate(total_revenue=Sum('total'))['total_revenue'] or 0
        ) - (
            sale_returns.annotate(total=Sum('sale_main__grand_total')).aggregate(total_revenue=Sum('total'))['total_revenue'] or 0
        ) - (
            purchase.annotate(total=Sum('purchase_main__grand_total')).aggregate(total_revenue=Sum('total'))['total_revenue'] or 0
        ) + (
            purchase_returns.annotate(total=Sum('purchase_main__grand_total')).aggregate(total_revenue=Sum('total'))['total_revenue'] or 0
        )
        
        # 总库存计算（保留原有逻辑）
        total_stocks = (
            purchase.annotate(total=Sum('qty')).aggregate(total_stock=Sum('total'))['total_stock'] or 0
        ) - (
            purchase_returns.annotate(total=Sum('qty')).aggregate(total_stock=Sum('total'))['total_stock'] or 0
        ) - (
            sale.annotate(total=Sum('qty')).aggregate(total_stock=Sum('total'))['total_stock'] or 0
        ) - (
            sale_returns.annotate(total=Sum('qty')).aggregate(total_stock=Sum('total'))['total_stock'] or 0
        )

        # 获取所有有交易记录的商品（若需包含所有商品，改为InvItem.objects.all()）
        item_ids = set()
        item_ids.update(sale.values_list('item_id', flat=True))
        item_ids.update(sale_returns.values_list('item_id', flat=True))
        item_ids.update(purchase.values_list('item_id', flat=True))
        item_ids.update(purchase_returns.values_list('item_id', flat=True))

        # 按商品维度统计数据
        item_wise_data = []
        for item in InvItem.objects.filter(id__in=item_ids):
            # 库存计算
            purchase_qty = purchase.filter(item=item).aggregate(total=Sum('qty'))['total'] or 0
            purchase_return_qty = purchase_returns.filter(item=item).aggregate(total=Sum('qty'))['total'] or 0
            sale_qty = sale.filter(item=item).aggregate(total=Sum('qty'))['total'] or 0
            sale_return_qty = sale_returns.filter(item=item).aggregate(total=Sum('qty'))['total'] or 0
            item_stock = purchase_qty - purchase_return_qty - sale_qty - sale_return_qty

            # 营收计算（复用原有逻辑）
            sale_revenue = sale.filter(item=item).annotate(total=Sum('sale_main__grand_total')).aggregate(total=Sum('total'))['total'] or 0
            sale_return_revenue = sale_returns.filter(item=item).annotate(total=Sum('sale_main__grand_total')).aggregate(total=Sum('total'))['total'] or 0
            purchase_cost = purchase.filter(item=item).annotate(total=Sum('purchase_main__grand_total')).aggregate(total=Sum('total'))['total'] or 0
            purchase_return_cost = purchase_returns.filter(item=item).annotate(total=Sum('purchase_main__grand_total')).aggregate(total=Sum('total'))['total'] or 0
            item_revenue = sale_revenue - sale_return_revenue - purchase_cost + purchase_return_cost

            item_wise_data.append({
                'item_id': item.id,
                'item_name': item.name,
                'item_code': item.code,
                'stock': item_stock,
                'revenue': item_revenue
            })

        return Response(        
            {
                'total_revenue': total_revenue,
                'total_stocks': total_stocks,
                'item_wise_data': item_wise_data,
            }
        )

关键优化点 #

1. 查询集移至方法内：避免Django类级别查询集的缓存问题，确保每次请求获取最新数据。
2. 商品维度统计逻辑：遍历每个商品，复用原有总统计的公式计算单个商品的库存和营收。
3. 灵活的商品范围：可选择仅统计有交易记录的商品，或包含所有商品（修改遍历的查询集即可）。

营收计算逻辑修正（可选） #

原代码中使用Sum('sale_main__grand_total')会导致重复计算（同一订单的多个商品明细会重复累加订单总金额）。若要准确统计商品维度的营收，建议替换为基于商品明细的计算：

# 替换item_revenue相关计算代码
sale_revenue = sale.filter(item=item).aggregate(total=Sum(F('cost') * F('sale_qty')))['total'] or 0
sale_return_revenue = sale_returns.filter(item=item).aggregate(total=Sum(F('cost') * F('sale_qty')))['total'] or 0
purchase_cost = purchase.filter(item=item).aggregate(total=Sum(F('purchase_cost') * F('qty')))['total'] or 0
purchase_return_cost = purchase_returns.filter(item=item).aggregate(total=Sum(F('purchase_cost') * F('qty')))['total'] or 0
item_revenue = sale_revenue - sale_return_revenue - purchase_cost + purchase_return_cost

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