Django generate group by different than id

Question

I want to count amount of Users for same Product

models.py

class Product(models.Model):
   pass
class User(models.Model):
   product = models.ForeignKey(Product)
   age = models.IntegerField(blank=True, null=True)

User.objects.filter(age__gt=18).annotate(product_count=Count('product_id'))

output sql

SELECT
  "user"."product_id"
  COUNT("user"."product_id") AS "product_count"
FROM "user"
WHERE "user"."age" > 18
GROUP BY "user"."id";

desired sql:

SELECT
  "user"."product_id"
  COUNT("user"."product_id") AS "product_count"
FROM "user"
WHERE "user"."age" > 18
GROUP BY "user"."product_id";

Isherwood

Answer 1

I don't think that makes any sense. What you want is probably this:

Product.objects.annotate(user_count=Count('user'))

Abutting

more specifically, Product.objects.annotate(user_count=Count('user')).values('id').order_by('id') – Namtar

I don't know if django makes any sense, but query makes a lot of sense, what if I need to filter object somehow, in your case it will perform the inner join. Check my edits, please – Isherwood

@Abutting check my edits please, how do I filter user by age using your answer? Furthermore, I need to select user fields, not product, why would I perform operations on Product.objects and then join user, when I don't need to fetch product fields at all? – Isherwood

You can filter users by age using filter parameter : Product.objects.annotate(cnt=Count('user', filter=Q(user__age__gt=18))). – Fredelia

Answer 2

It is possible to generate the intended query if you explicitly select product using the method values(), like this:

User.objects.filter(age__gt=19).values('product').annotate(product_count=Count('product'))

This query will return a list of dictionaries containing product (that is, its ID) and product_count.

When you use values(), you restrict the query to select only the columns you required. Otherwise, Django will implicitly select all the attributes of the model.

In your example, that means user_id will be selected along with product_id and age, and they will be used in the GROUP BY clause. Since user_id is unique, it will seem as if annotate() is not working.

Almeida