Django QuerySet如何聚合重复元素并添加数量字段？

问题描述 #

我是Django新手，需要对包含重复商品的QuerySet进行聚合，新增poQty_字段统计每个商品的重复数量。

原始QuerySet #

<QuerySet [
{'id': 2, 'prodclassQuery_id': 1, 'prodDescription': 'Hofbräu Kellerbier 500 ml', 'prodPrice': Decimal('6.50')}, 
{'id': 1, 'prodclassQuery_id': 1, 'prodDescription': 'Tonic Water 300 ml', 'prodPrice': Decimal('4.50')}, 
{'id': 3, 'prodclassQuery_id': 2, 'prodDescription': 'Coxinha 6 unidades', 'prodPrice': Decimal('8.00')}, 
{'id': 3, 'prodclassQuery_id': 2, 'prodDescription': 'Coxinha 6 unidades', 'prodPrice': Decimal('8.00')}]>

期望的聚合结果 #

<QuerySet [
{'id': 2, 'prodclassQuery_id': 1, 'prodDescription': 'Hofbräu Kellerbier 500 ml', 'prodPrice': Decimal('6.50'), 'poQty_': 1}, 
{'id': 1, 'prodclassQuery_id': 1, 'prodDescription': 'Tonic Water 300 ml', 'prodPrice': Decimal('4.50'), 'poQty_': 1}, 
{'id': 3, 'prodclassQuery_id': 2, 'prodDescription': 'Coxinha 6 unidades', 'prodPrice': Decimal('8.00'), 'poQty_': 2}]>

尝试过的无效代码 #

views.py中的实现：

def display_orders(request):
    orders = Order.objects.all().order_by('id', 'orderTable', 'menuQuery')
    for j in orders:
        print(j.prodQuery.values(),) # original QuerySet
    orders_aggr = Order.objects.annotate(poQty_=Count('prodQuery__id')).order_by('id', 'orderTable', 'menuQuery')
    for j in orders_aggr:
        print(j.prodQuery.values(),)
    context = {
        'orders': orders,
        'orders_aggr': orders_aggr
    }
    return render(request, 'orders.html', context)

关联Model代码 #

models.py：

class Product(models.Model):
    prodclassQuery = models.ForeignKey(ProductClass, on_delete=models.PROTECT, verbose_name='Product Class', default=1)
    prodDescription = models.CharField(max_length=255, verbose_name='Product')
    prodPrice = models.DecimalField(max_digits=6, decimal_places=2, verbose_name='Price')

    class Meta:
        ordering = ['prodclassQuery', 'prodDescription']

    def __str__(self):
        return self.prodDescription

class Menu(models.Model):
    menuActive = models.BooleanField(verbose_name='Active?', default=False)
    menuDescription = models.CharField(max_length=255, verbose_name='Menu')
    prodQuery = models.ManyToManyField(Product, verbose_name='Product')
    
    class Meta:
        ordering = ['menuDescription',]
    
    def __str__(self):
        return self.menuDescription
    
class Order(models.Model):
    orderUser = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True)
    orderDtOpen = models.DateTimeField(auto_now_add=True)
    orderDtClose = models.DateTimeField(auto_now=True)
    orderOpen = models.BooleanField(default=True, verbose_name='Open?')
    orderTable = models.CharField(max_length=25, verbose_name='Table')
    menuQuery = models.ForeignKey(Menu, on_delete=models.PROTECT, verbose_name='Menu', default=1)
    prodQuery = models.ManyToManyField(Product, through='ProductOrder')

    class Meta:
        models.UniqueConstraint(fields=['orderTable'], condition=models.Q(orderOpen=True), name='unique_open_order_table',
                                violation_error_message='This table has already a open order')
        
    def save_model(self, request, obj, form, change):
        obj.orderuser = request.user
        super().save_model(request, obj, form, change)
    
class ProductOrder(models.Model):
    poOrder = models.ForeignKey(Order, on_delete=models.CASCADE, verbose_name='Order', default=1)
    poStatus = models.BooleanField(default=True, verbose_name='Order Status')
    prodQuery = models.ForeignKey(Product, on_delete=models.CASCADE, verbose_name='Product', default=1)

解决方案 #

之前的annotate无效是因为聚合对象错误：你对Order对象做统计，得到的是每个订单关联的商品总数，而非单个商品的重复次数。重复记录实际存在于中间表ProductOrder中，需从这里入手聚合。

方法一：直接聚合ProductOrder #

修改views.py的查询逻辑：

from django.db.models import Count

def display_orders(request):
    # 按商品分组，统计同一订单内的商品数量
    aggregated_items = ProductOrder.objects.values(
        'poOrder__id',
        'prodQuery__id',
        'prodQuery__prodclassQuery_id',
        'prodQuery__prodDescription',
        'prodQuery__prodPrice'
    ).annotate(poQty_=Count('id')).order_by('poOrder__id', 'prodQuery__prodclassQuery_id')

    # 按订单分组整理数据（方便模板展示）
    order_groups = {}
    for item in aggregated_items:
        order_id = item['poOrder__id']
        if order_id not in order_groups:
            order_groups[order_id] = {
                'order': Order.objects.get(id=order_id),
                'items': []
            }
        order_groups[order_id]['items'].append(item)

    context = {
        'order_groups': order_groups.values()
    }
    return render(request, 'orders.html', context)

方法二：通过Order预加载聚合结果 #

如果想从Order对象出发，用Prefetch预加载聚合后的商品项：

from django.db.models import Count, Prefetch

def display_orders(request):
    # 定义聚合后的ProductOrder查询集
    prod_order_agg = ProductOrder.objects.values(
        'prodQuery__id',
        'prodQuery__prodclassQuery_id',
        'prodQuery__prodDescription',
        'prodQuery__prodPrice'
    ).annotate(poQty_=Count('id'))

    # 预加载到Order对象中
    orders = Order.objects.prefetch_related(
        Prefetch('productorder_set', queryset=prod_order_agg, to_attr='aggregated_items')
    ).order_by('id', 'orderTable')

    context = {
        'orders': orders
    }
    return render(request, 'orders.html', context)

模板使用示例 #

在orders.html中遍历展示：

{% for group in order_groups %}
    <h4>Table {{ group.order.orderTable }} (Order #{{ group.order.id }})</h4>
    <table>
        <tr>
            <th>Product</th>
            <th>Price</th>
            <th>Quantity</th>
        </tr>
        {% for item in group.items %}
        <tr>
            <td>{{ item.prodQuery__prodDescription }}</td>
            <td>{{ item.prodQuery__prodPrice }}</td>
            <td>{{ item.poQty_ }}</td>
        </tr>
        {% endfor %}
    </table>
{% endfor %}

内容的提问来源于stack exchange，提问作者Eduardo