使用总和进行多重注释并在管理员中显示数据 - 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

在每个 GoodsItem 的 stock_available 中，我想显示 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.

作为解决这个特定问题的方法，我们可以使用子查询表达式.这是我在 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.

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