Django annotate count with a distinct field

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 😛

Thanks a lot!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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

Method 3

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

Method 4

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0