Django QuerySet: how to aggregate repeated elements and add quantity field to it?

Question

I have a feeling the solution to this is very simple, but as new to Django I am not able to figure it out...

Given the following 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')}]>

I want to aggregate the repeated elements (based on id) e produce the following QuerySet, adding the field poQty_ to represent the quantity of repeated elements (products in my case...):

<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}]>

What I tried so far with annotate() in views.py is not working, and the results of orders_aggris the same original QuerySet:

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)

Would anyone give please some help? Thanks !! Further information:

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)

Answer 1

You can also use a combination of values() and annotate(), so:

from django.db.models import Count

def display_orders(request):
    orders = Order.objects.values(
        'id',
        'orderTable',
        'menuQuery'
    ).annotate(
        poQty_=Count('id')
    ).order_by(
        'id',
        'orderTable',
        'menuQuery'
    )

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

This code will group the orders by the specified fields (id, orderTable, menuQuery), count the distinct occurrences of each group, and annotate the count onto each result.