DJANGO-按类别分类的小计,按项目分类的小计

晚上好 !

假设我有一个数据库:

Category | Item | Price
Cat1 | Item1 | $1
Cat1 | Item2 | $2
Cat2 | Item3 | $3
Cat2 | Item3 | $3
Cat2 | Item4 | $4

我想按类别显示总销售额,并按项目在销售明细组下方显示

Cat1 - Total Price = $3
  Item1 - $1
  Item2 - $2
Cat2 - Total Price = $10
  Item3 - $6 (the sum of the two item3)
  Item4 - $4

我几乎成功了,但是我不知道如何按类别进行小计。 我的代码如下:

模型

class Category(models.Model):
Category= models.CharField(max_length=100)

class Item(models.Model):
Category= models.ForeignKey(Category, on_delete=models.CASCADE)
Item= models.CharField(max_length=100)

class Order(models.Model):
Category= models.ForeignKey(Category, on_delete=models.SET_NULL, null=True)
Item=  models.ForeignKey(Item, on_delete=models.SET_NULL, null=True)
Price=  models.DecimalField()

Views.py

def summary(request):
metrics =  {
'total': Sum('Price'),
}

orders=Order.objects.values('Category','Item').annotate(**metrics))

Template.html

 {% for order in orders%}
       {% ifchanged order.Item%}

       <tr>
           <td>{{ order.Item}} </td>
           <td>{{order.total}} </td>
        </tr>

       {% endifchanged %}
        <tr>
        <td>{{order.Item}}</td>
        <td> {{order.total}}</td>
        </tr>


    {% endfor %}
评论
  • 萌了个兔
    萌了个兔 回复

    You can make use of a Prefetch(..) object [Django-doc] to make aggregates per Category and per Item:

    从django.db.models导入预取,总和

    def summary(request):
        metrics =  {
            'total': Sum('item__order__Price'),
        }
    
        categories = Category.objects.annotate(
            total=Sum('item__order__Price')
        ).prefetch_related(
            Prefetch(
                'Item_set',
                Item.objects.annotate(total=Sum('Price')),
                to_attr='items_with_price'
            )
        )
        return render(request, 'template.html', {'categories': categories})

    然后,可以在模板中使用以下命令呈现此内容:

    {% for category in categories %}
        <tr>
            <td><b>{{ category.Category }}</b></td>
            <td><b>{{ category.total }}</b></td>
        </tr>
        {% for item in category.items_with_price %}
            <tr>
                <td>{{ item.Item }}</td>
                <td>{{ item.total }}</td>
            </tr>
        {% endfor %}
    {% endfor %}