python - Django annotate count with a distinct field

78
votes

I have two models defined loosely like this:

class InformationUnit(models.Model):
    username = models.CharField(max_length=255)
    project = models.ForeignKey('Project')
    ...

class Project(models.Model):
    name = models.CharField(max_length=255)

Now, in a view, I want to annotate all the InformationUnits that belong to a project, so I do this:

p = Project.objects.all().annotate(Count('informationunit')

which works just ok.

Furthermore, I want to know, in each project, how many distinct usernames participate. That is, count how many distinct usernames are there in the InformationUnits that compose one project. I have tried the following, but it simply counts the number of InformationUnit, regardless of the username:

p = Project.objects.all().annotate(Count('informationunit__username')

Note that username is not an object, it is a string. Is there a clean way to do this or should I create a more complicated code based on loops and spaghetti code :P

Thanks a lot!

4
pythondjango

4 Answers

158
votes

Count can take a distinct argument, like so:

p = Project.objects.all().annotate(Count('informationunit__username', 
                                         distinct=True))

This doesn't seem to be documented, but you can find it in the source for Count.

27
votes

If you just want to count the distinct values, you can use the distinct() and count() functions:

count = Project.objects.values('informationunit__username').distinct().count()
12
votes
Project.objects.all().annotate(Count('informationunit__username', 
                                     distinct=True))
6
votes

SQL SELECT field1, COUNT(DISTINCT(pk)) FROM project GROUP BY field1 ORDER BY NULL;

QuerySet

Project.objects.all().values(field1).annotate(count=Count('pk', distinct=True)).order_by()