django:在带有注释的 Sum 函数中使用 if else 或 while else 吗?无法计算 Sum('<CombinedExpression:...') 是一个聚合 [英] django : using if else or while else inside Sum function with annotation? Cannot compute Sum('<CombinedExpression:..') is an aggregate

问题描述

我想在 annotate 中为我的 Sum 函数设置一个条件，我尝试使用 Case When> 但它在我的情况下不起作用

i want to set a condition to my Sum function inside annotate , and i tried to use Case When but it didnt work in my case

这是我的models.py

this is my models.py

class MyModel(models.Model):
    name = models.ForeignKey(Product, on_delete=models.CASCADE)
    order = models.IntegerField()
    price = models.IntegerField()

class Prodcut(models.Model):
    name = models.CharField(max_lenth=20)
    cost = models.IntegerField()
    price = models.IntegerField()

我想要这样的东西

total = F('price')*F('order')
base = (F(name__cost')+F('name__price')) * F('order')
if  total> base:
    income = Sum(F('total') - F('base'))

我试过了

MyModel.objects.values('name__name').annotate(total=(Sum(F('price') * F('order'),output_field=IntegerField())),
                        base=(Sum((F('name__price')+F('name__cost'))*F('order'),output_field=IntegerField())
                            ),
                        income=Sum(
                            Case(When(total__gt=F('base') , then=Sum(F('total') - F('base'))),default=0),output_field=IntegerField()),)

但这会引发此错误:

无法计算 Sum(''): ''是一个集合

Cannot compute Sum('<CombinedExpression: F(total) - F(base)>'): '<CombinedExpression: F(total) - F(base)>' is an aggregate

我不想使用 .filter(income__gt=0) 因为它会阻止 quantity 计数我不想将收入计入那些失去销售的产品例如

i dont want to use .filter(income__gt=0) because it stops quantity from counting and i dont want to counting income to those products which loss its sold for example

我在 MyModel(name=mouse ,order=2,price=20) 上发帖，在我的产品模型中，我有鼠标产品的这些信息 Product(name=mouse,cost=4,price=10) ，当我计算这个产品的收入时:(2 *20) - ((4+10)*2) =>40 - 28 = 12 ，但有时(2*10) - ((4+10)*2) =>20 - 28 = -8

i make a post on MyModel(name=mouse ,order=2,price=20) and in my Product model i have these information for mouse product Product(name=mouse,cost=4,price=10) , when i calculate to find income for this product : (2 *20) - ((4+10)*2) => 40 - 28 = 12 , but sometimes happen the result will be a negative price when (2*10) - ((4+10)*2) => 20 - 28 = -8

*我使用 mysql v:8 作为数据库

*i use mysql v:8 for database

我想防止负数添加到我的 income 相对于其他列 quantity

i want to prevent negative numbers to add to my income with respect the other columns quantity

推荐答案

问题是您不能在同一查询中的另一个聚合内使用聚合(总计和基数).只有一个 GROUP BY 子句，Django 无法在此处自动生成有效查询.据我了解，您需要先计算总和基数，找到每个 MyModel 收入，然后才生成聚合:

The problem is that you cannot use an aggregate (total and base) inside yet another aggregate in the same query. There is only one GROUP BY clause and Django cannot automatically produce a valid query here. As far as I've understood, you need to firstly calculate total and base, find each MyModel income, and only then produce an aggregate:

MyModel.objects.annotate(
    total=F('price') * F('order'),
    base=(F('name__price') + F('name__cost')) * F('order'),
    income=Case(
        When(total__gt=F('base'), then=F('total') - F('base')),
        default=0,
        output_field=IntegerField()
    )
).values('name__name').annotate(income=Sum('income'))

附言请格式化您的代码，以便人们可以毫无困难地阅读它:)

P.S. Please, format your code so people can read it without difficulties :)

P.P.S 我可能可以看到另一种方式，您不需要 Sum() 来获取收入，因为 total 和 base 已经是总和了

P.P.S I can probably see another way, you don't need Sum() for the income because total and base are sums already

MyModel.objects.values('name__name').annotate(
    total=Sum(F('price') * F('order')),
    base=Sum((F('name__price') + F('name__cost')) * F('order')),
).annotate(
    income=Case(
        When(total__gt=F('base'), then=F('total') - F('base')),
        default=0,
        output_field=IntegerField()
    )
)

这篇关于django:在带有注释的 Sum 函数中使用 if else 或 while else 吗?无法计算 Sum('&lt;CombinedExpression:...') 是一个聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！