1

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!

Kamil Niski
  • 4,580
  • 1
  • 11
  • 24
shitzuu
  • 119
  • 2
  • 9

1 Answers1

2

If i understand you correctly you just want this:

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

Which is almost what you wrote.

Kamil Niski
  • 4,580
  • 1
  • 11
  • 24
  • 1
    Thanks for that! I guess I was overcomplicating things! After reading your response I realized that a ManyToMany relationship is both way and if I had declared it in the Role model instead of the Profile one, then I could have visualised the answer more easily. Anyway thanks for the quick answer, I spent way too much time on this! :) – shitzuu Oct 10 '18 at 11:16