1

i want to return a ManyToMany fields data , and also i've used aggregate to some calculation , now i need to return products 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()

and this is my query

context['clients'] = CustomerInvoice.objects.filter(
        customer=self.object.name).aggregate(
            total_order=Sum(F('product__qnt')),
            total_price=Sum(F('product__price')))

i want to make table to track customers activity : for example at 4th june he bought two pen with 3 book , and for 5th july he buy 1 pen with 2 copybook , i need a result like this : 3; pen , 3;book ,2;copybook

i know i should use distinct and i dont know why dont have any output {{clients.items.all|join:','}} ? thanks for your helping

updated

now i changed the query to this

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'

art_cs
  • 683
  • 1
  • 8
  • 17

1 Answers1

1

Flip your approach. Instead of going from CustomerInvoice to ProductSelecte, do it from ProductSelecte to CustomerInvoice

ProductSelecte.objects.filter(products__customer=self.object.name)

Now you have every purchase from a customer with the name of the item and the quantity

Now use aggregation on this query to get them per-invoice if you really need that

Aayush Agrawal
  • 1,354
  • 1
  • 12
  • 24
  • thanks i will try it and let you know if it success – art_cs Jul 05 '20 at 13:36
  • but where we should use distinct in order to prevent duplicated product name? – art_cs Jul 05 '20 at 13:40
  • no brother , the problem is the aggregate , it only allow us to iterate through aggregate fields , doesnt work – art_cs Jul 05 '20 at 13:43
  • Look at this answer: https://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django – Aayush Agrawal Jul 05 '20 at 13:44
  • 1
    ProductSelecte.objects.filter(products__customer=self.object.name) .values('product').annotate(count=Sum(F('qnt'))) I wrote that without testing so might need to check the syntax – Aayush Agrawal Jul 05 '20 at 13:45
  • i dont want to use group by , the query inside my DetailView – art_cs Jul 05 '20 at 13:45
  • 1
    The above is equivalent to this SQL query: SELECT "yourapp_productselecte"."product_id", SUM("yourapp_productselecte"."qnt") AS "count" FROM "yourapp_productselecte" INNER JOIN "yourapp_customerinvoice" ON ("yourapp_productselecte"."products_id" = "yourapp_customerinvoice"."id") WHERE "yourapp_customerinvoice"."customer" = val GROUP BY "yourapp_productselecte"."product_id – Aayush Agrawal Jul 05 '20 at 13:52
  • it iterates , for example , we have 2 pen in one invoice and 1 pen in another invoice , it show 2 : pen , 1 : pen – art_cs Jul 05 '20 at 14:11
  • 1
    @art_cs Are you using the second query i gave you? – Aayush Agrawal Jul 05 '20 at 14:16
  • 1
    ProductSelecte.objects.filter(products__customer=self.object.name) .values('product').annotate(count=Sum(F('qnt'))) – Aayush Agrawal Jul 05 '20 at 14:16
  • in order to get distinct values we should use order_by('product') , thanks alot you helped me alot – art_cs Jul 05 '20 at 14:25
  • 1
    @art_cs Happy to help :) Please accept the answer if your problem is resolved – Aayush Agrawal Jul 05 '20 at 14:27
  • but sorry i also need to use aggregate , it doesnt work with aggregate – art_cs Jul 05 '20 at 14:36
  • using aggregate to get total price of all products – art_cs Jul 05 '20 at 14:40