用总和多次注释并在admin中显示数据-Django [英] Multiple annotate with sum and display data in admin - Django

问题描述

我是Django和Python的新手。目前，我正在尝试通过Django Admin。

I'm new to both Django and Python. Currently I'm trying the Django Admin by doing.

我为Django应用提供了三种模型，分别是 GoodsItem ， SoldGoodsItem 和 FinishedGoodsItem 。 models.py是：

I've three models for a Django app, which are GoodsItem, SoldGoodsItem and FinishedGoodsItem. The models.py is:

from django.db import models


class GoodsItem(models.Model):
    name = models.CharField(max_length=255)
    size = models.DecimalField(max_digits=4, decimal_places=2)
    INCHES = 'IN'
    NUMBER = 'NUM'
    GOODS_ITEM_SIZE_UNITS = (
        (INCHES, 'Inches'),
        (NUMBER, '#'),
    )
    size_unit = models.CharField(
        max_length=4,
        choices=GOODS_ITEM_SIZE_UNITS,
        default=INCHES,
    )

    def __str__(self):
        if(self.size_unit == self.NUMBER):
            return "%s #%s" % (self.name, (self.size).normalize())
        else:
            return "%s %s\"" % (self.name, (self.size).normalize())


class FinishedGoodsItem(models.Model):
    date = models.DateField()
    goods_item = models.ForeignKey(GoodsItem, on_delete=models.CASCADE, related_name="finished_name")
    weight = models.DecimalField(max_digits=6, decimal_places=3)

    def __str__(self):
        return str(self.goods_item)


class SoldGoodsItem(models.Model):
    goods_item = models.ForeignKey(GoodsItem, on_delete=models.CASCADE, related_name="sold_name")
    date = models.DateField()
    weight = models.DecimalField(max_digits=6, decimal_places=3)

    def __str__(self):
        return str(self.goods_item)

这是admin.py：

And here is admin.py:

from django.contrib import admin
from django.db.models import Sum

from .models import GoodsItem, FinishedGoodsItem, SoldGoodsItem

@admin.register(SoldGoodsItem)
@admin.register(FinishedGoodsItem)
class FinishedGoodsItemAdmin(admin.ModelAdmin):
    fields = ('date', 'goods_item', 'weight')
    list_display = ('date', 'goods_item', 'weight')

@admin.register(GoodsItem)
class GoodsItemAdmin(admin.ModelAdmin):
    list_display = ('__str__', 'finished_good', 'sold_good', 'stock_available')

    def get_queryset(self, request):
        qs = super(GoodsItemAdmin, self).get_queryset(request)
        qs = qs.annotate(
            finished_good=Sum('finished_name__weight'),
            sold_good=Sum('sold_name__weight'),
            stock_available=Sum('finished_name__weight') - Sum('sold_name__weight'),
        )
        return qs

    def finished_good(self, obj):
        return obj.finished_good

    def sold_good(self, obj):
        return obj.sold_good

    def stock_available(self, obj):
        return obj.stock_available

在 stock_available 中，每个 GoodsItem ，我要显示 FinishedGoodsItem 的所有条目和 SoldGoodsItem的所有条目。现在，我得到的所有三个带注释的字段的值都不正确，分别是 finished_good ， sold_good 和 stock_available 。我找不到原因。在Django Debug Toolbar中，建议执行重复的查询。

In stock_available for each GoodsItem, I want to display the difference between all entries of FinishedGoodsItem and all entries of SoldGoodsItem. For now, I'm getting incorrect value for all three annotated fields which are finished_good, sold_good and stock_available. I'm unable to find the reason for that. In Django Debug Toolbar suggest that duplicate queries are being executed.

推荐答案

这是已知问题，当我们尝试组合多个聚合时会发生，如 docs 中所述。

This is known issue and occurs when we try to combine multiple aggregation, as mentioned in docs.

作为解决此特定问题的方法，我们可以使用Subquery表达式。这是我在 GoodsItemAdmin 的 get_queryset 方法中使用子查询表达式更新的admin.py。

As a workaround for this particular problem, we can use Subquery expression. Here is my updated admin.py using Subquery expression in get_queryset method of GoodsItemAdmin.

from django.contrib import admin
from django.db.models import Subquery, Sum, OuterRef

from .models import GoodsItem, FinishedGoodsItem, SoldGoodsItem

@admin.register(SoldGoodsItem)
class SoldGoodsItemAdmin(admin.ModelAdmin):
    fields = ('date', 'goods_item', 'weight')
    list_display = ('date', 'goods_item', 'weight')

@admin.register(FinishedGoodsItem)
class FinishedGoodsItemAdmin(admin.ModelAdmin):
    fields = ('date', 'goods_item', 'weight')
    list_display = ('date', 'goods_item', 'weight')

@admin.register(GoodsItem)
class GoodsItemAdmin(admin.ModelAdmin):
    list_display = ('__str__', 'finished_good', 'sold_good', 'stock_available')

    def get_queryset(self, request):
        qs = super(GoodsItemAdmin, self).get_queryset(request)
        qs = qs.annotate(
            finished_good = Subquery(FinishedGoodsItem.objects.filter(goods_item=OuterRef('pk'))\
                .values('goods_item_id').annotate(sum=Sum('weight')).values('sum')[:1]),
            sold_good = Subquery(SoldGoodsItem.objects.filter(goods_item=OuterRef('pk'))\
                .values('goods_item_id').annotate(sum=Sum('weight')).values('sum')[:1])
        )
        return qs

    def finished_good(self, obj):
        return obj.finished_good

    def sold_good(self, obj):
        return obj.sold_good

    def stock_available(self, obj):
        finished_good = 0 if self.finished_good(obj) is None else self.finished_good(obj)
        sold_good = 0 if self.sold_good(obj) is None else self.sold_good(obj)
        return '-' if (finished_good == 0 and sold_good == 0) else finished_good - sold_good

希望有人觉得这很有用。

Hope someone finds this useful.

这篇关于用总和多次注释并在admin中显示数据-Django的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！