using aggregate with values : group by : django

Question

i want to return a product names in distinct , and also i've used aggregate to some calculation as well

this is my models.py

class CustomerInvoice(models.Model):
    customer = models.CharField(max_length=50)
    items = models.ManyToManyField(Product,through='ProductSelecte')
    date = models.DateTimeField(auto_now_add=True)

class ProductSelecte(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    products= models.ForeignKey(CustomerInvoice,on_delete=models.CASCADE,related_name='product')
    qnt= models.IntegerField(default=1)
    price = models.IntegerField()
    cash = models.IntegerField()

i want to make table to track customers activity

and this is my query

context['clients'] = ProductSelecte.objects.filter(
    products__customer=self.object.name).values('product').annotate(quantity=Sum(F('qnt'))).order_by('product')

till here works fine but i also need to aggregate price using this

.aggregate(
        total_price=Sum(F('price')))

it raise this error

'dict' object has no attribute 'order_by'

thanks for your helping

UPDATE

for example at in first invoice of john 4th june john bought two pen price:20$ with 3 book price : 30$ total price for this invoice :50$ , and for second invoice 5th july he buy 1 pen price:10$ with 2 copybook price:20$ total price:30 , i need a result like this

client : john , activities :  3 pen(total pens he bought till now),price pens : 30  (total pens price he bought till now) , 3 book(total books he bought till now) ; price:30(total books price he bought till now) ,2 copybook ;price:20 
 and then total price of both invoice : 30+50 = 80

Answer 1

You should make the annotate per client per product and then perform some "grouping" at the Django/Python level:

from django.db.models import F, Sum
from itertools import groupby
from operator import itemgetter

qs = ProductSelecte.objects.values(
    customer=F('products__customer')
    product=F('product')
).annotate(quantity=Sum('qnt')).order_by('customer', 'product')

data = {
    k: list(vs)
    for k, vs in groupby(qs, itemgetter('customer'))
}

Here data is a dictionary that maps the name of the customer to a list of dictionaries that contain the id of the product, the customer name and the quantity.

If you pass it to the template, you can for example render this with:

<ul>
{% for c, vs in data.items %}
    <li>{{ c }}</li>
    <ul>
    {% for v in vs %}
        <li>{{ v.product }}: {{ v.quantity }}</li>
    {% endfor %}
    </ul>
{% endfor %}
</ul>

EDIT: based on the updated request, you should make an annotate and aggregate request:

class ProfileClientDetailView(LoginRequiredMixin,DetailView):
    # …

    def get_object(self):
        return get_object_or_404(Client,pk=self.kwargs['pk'])

    def get_context_data(self,*args,**kwargs):
        data = super().get_context_data(*args,**kwargs)
        data['products'] = ProductSelecte.objects.filter(
            products__customer=self.object.name
        ).values('product').annotate(
            quantity=Sum('qnt'),
            total_price=Sum('price')
        ).order_by('product')
        data['total'] = ProductSelecte.objects.filter(
            products__customer=self.object.name
        ).aggregate(
            quantity=Sum('qnt'),
            total_price=Sum('price')
        )
        return data

Then it can be rendered as:

<ul>
{% for pr in products %}
    <li>{{ pr.product }}:  {{ pr.total_price }} ({{ pr.quantity }} items)</li>
</ul>
TOTAL: {{ total.total_price }} ({{ total.quantity }} items)