Django annotate count with a distinct field
PythonDjangoPython Problem Overview
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 InformationUnit
s 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
s participate.
That is, count how many distinct username
s are there in the InformationUnit
s 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!
Python Solutions
Solution 1 - Python
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.
Solution 2 - Python
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()
Solution 3 - Python
Project.objects.all().annotate(Count('informationunit__username',
distinct=True))
Solution 4 - Python
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()