Django annotate count with a distinct field


Question

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 'username' participate. That is, count how many distinct 'username' 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!

1
46
5/22/2018 10:30:51 PM

Accepted Answer

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.

89
10/30/2012 6:26:11 PM

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()

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon