Django Count() in multiple annotations

Question

Say I have a simple forum model:

class User(models.Model):     username = models.CharField(max_length=25)     ...  class Topic(models.Model):     user = models.ForeignKey(User)     ...  class Post(models.Model):     user = models.ForeignKey(User)     ... 

Now say I want to see how many topics and posts each users of subset of users has (e.g. their username starts with "ab").

So if I do one query for each post and topic:

User.objects.filter(username_startswith="ab")             .annotate(posts=Count('post'))             .values_list("username","posts") 

Yeilds:

[('abe', 5),('abby', 12),...] 

and

User.objects.filter(username_startswith="ab")             .annotate(topics=Count('topic'))             .values_list("username","topics") 

Yields:

[('abe', 2),('abby', 6),...] 

HOWEVER, when I try annotating both to get one list, I get something strange:

User.objects.filter(username_startswith="ab")             .annotate(posts=Count('post'))             .annotate(topics=Count('topic'))             .values_list("username","posts", "topics") 

Yields:

[('abe', 10, 10),('abby', 72, 72),...] 

Why are the topics and posts multiplied together? I expected this:

[('abe', 5, 2),('abby', 12, 6),...] 

What would be the best way of getting the correct list?

Answer 1

I think Count('topics', distinct=True) should do the right thing. That will use COUNT(DISTINCT topic.id) instead of COUNT(topic.id) to avoid duplicates.

User.objects.filter(     username_startswith="ab").annotate(     posts=Count('post', distinct=True)).annotate(     topics=Count('topic', distinct=True)).values_list(     "username","posts", "topics")