CODEWITHSUNDEEP
pythondjangodjango-orm
Deadly
Deadly

Reputation: 2094

Django GROUP_BY through .annotate()

I have table invoices with field customer_id and some others fields. I need select count of purchases, taken by each user. In SQL it's should looks like this:

SELECT username, COUNT('customer_id') FROM `invoices`
LEFT JOIN `auth_user` ON `auth_user`.id =  `invoices`.customer_id
GROUP BY `customer_id`, username

In Django i try:

Invoice.objects.annotate(buy_count=Count('customer')).all()

But this code groups by invoices.id instead of invoices.customer_id and returns wrong result.

Upvotes: 0

Views: 388

Answers (1)

jpic
jpic

Reputation: 33420

I think you should turn it around, something like:

Customer.objects.annotate(buy_count=Count('invoice')).all()

There you'd get a list of Customer with their count of invoice.

Upvotes: 1

Related Questions