晚上好!
假设我有一个数据库,其中包含:
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
我几乎成功了,但我不知道如何按类别进行小计。我下面的代码:
型号.py
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 %}
您可以使用Prefetch(..)
对象[Django doc]来根据Category
和Item
:进行聚合
from django.db.models import Prefetch, Sum
def summary(request):
categories = Category.objects.annotate(
total=Sum('item__order__Price')
).prefetch_related(
Prefetch(
'item_set',
Item.objects.annotate(total=Sum('order__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 %}