Django - Annotate with count across ManytoMany relationships

Question

I am not able to find the way to annotate a queryset with a count of how many times an element is used in a many-to-many relationship.

class Profile(models.Model):
    [...]
    # Profile can have multiple roles
    roles = models.ManyToManyField('Role', blank=True)
    [...]

class Role(models.Model):
    company = models.ForeignKey(Company, on_delete=models.CASCADE)
    name = models.CharField(blank=True, max_length=30)
    description = models.CharField(blank=True, max_length=300)
    [...]   

For example I would have 5 roles:

• Role1
• Role2
• Role3
• Role4
• Role5

And 2 profiles with following roles assigned:

• Profile1
  • Role 1
  • Role 2
• Profile2
  • Role 1
  • Role 3
  • Role 4

I want to query the Role model and annotate with the number of profile that have that role.

So return a queryset like

Role1: company, name, description, profile_count=2
Role2: company, name, description, profile_count=1

etc...

I have tried that but it does not work:

Role.objects.annotate(profile_count=Count('profile__roles'))

It seems to return an overall count and not a count per role.

Any idea if that can be done natively in Django or if a raw SQL request is necessary?

Thanks!

Answer 1

If i understand you correctly you just want this:

Role.objects.annotate(profile_count=Count('profile'))

Which is almost what you wrote.