Django Admin中Sum注解计算库存可用量异常问题求助

Hey there! Let's work through this Django Admin annotation problem together—since you're new to Django and Python, I'll keep this straightforward so you can follow along easily.

First, let's address why your current annotate approach is giving incorrect values: when you directly use Sum with related models, Django joins the tables behind the scenes, which can create duplicate rows in the queryset. This duplication makes the sum calculations inflate because the same parent GoodsItem gets counted multiple times for each related FinishedGoodsItem or SoldGoodsItem.

Fixing the Calculation with Subqueries #

The solution is to use subqueries to calculate the total finished and sold quantities for each GoodsItem individually, avoiding duplicate row issues. Here's how to implement this in your admin.py:

First, let's assume your models have the correct foreign key relationships (adjust if your field names differ):

# models.py
from django.db import models

class GoodsItem(models.Model):
    name = models.CharField(max_length=100)
    # Add any other fields you have for GoodsItem

class FinishedGoodsItem(models.Model):
    goods_item = models.ForeignKey(GoodsItem, on_delete=models.CASCADE, related_name="finished_entries")
    quantity = models.IntegerField()
    # Other fields for finished goods

class SoldGoodsItem(models.Model):
    goods_item = models.ForeignKey(GoodsItem, on_delete=models.CASCADE, related_name="sold_entries")
    quantity = models.IntegerField()
    # Other fields for sold goods

Now update your admin.py to use subqueries for accurate sums:

# admin.py
from django.contrib import admin
from django.db.models import Sum, Subquery, OuterRef
from .models import GoodsItem, FinishedGoodsItem, SoldGoodsItem

class GoodsItemAdmin(admin.ModelAdmin):
    # Display these fields in the admin list view
    list_display = ('name', 'total_finished', 'total_sold', 'stock_available')

    def get_queryset(self, request):
        # Subquery to calculate total finished quantity per GoodsItem
        finished_total_subquery = FinishedGoodsItem.objects.filter(
            goods_item=OuterRef('pk')
        ).values('goods_item').annotate(
            total=Sum('quantity')
        ).values('total')

        # Subquery to calculate total sold quantity per GoodsItem
        sold_total_subquery = SoldGoodsItem.objects.filter(
            goods_item=OuterRef('pk')
        ).values('goods_item').annotate(
            total=Sum('quantity')
        ).values('total')

        # Attach the subquery results to the GoodsItem queryset
        queryset = super().get_queryset(request).annotate(
            _total_finished=Subquery(finished_total_subquery, output_field=models.IntegerField()),
            _total_sold=Subquery(sold_total_subquery, output_field=models.IntegerField())
        )
        return queryset

    # Helper method to display total finished (handle None values)
    def total_finished(self, obj):
        return obj._total_finished or 0
    total_finished.short_description = 'Total Finished'

    # Helper method to display total sold (handle None values)
    def total_sold(self, obj):
        return obj._total_sold or 0
    total_sold.short_description = 'Total Sold'

    # Calculate available stock
    def stock_available(self, obj):
        return self.total_finished(obj) - self.total_sold(obj)
    stock_available.short_description = 'Available Stock'

# Register your models with the custom admin classes
admin.site.register(GoodsItem, GoodsItemAdmin)
admin.site.register(FinishedGoodsItem)
admin.site.register(SoldGoodsItem)

Why This Works #

• Subqueries calculate the sum for each GoodsItem independently, so there's no duplication from table joins.
• We handle None values (for items with no finished or sold entries) using or 0 to avoid errors in calculations.
• The get_queryset method ensures these calculations are done efficiently at the database level, not in Python.

Just adjust the related_name values and field names to match your actual model setup, and this should give you the correct stock_available values in the Django Admin.

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