How to get values of a django query from attributes of a many to many relation?

user_15323452 0
Viewed 26

I have these two models:

class Company(models.Model):
    name = models.CharField(max_length=100)

class User(models.Model):
    ACCES_TYPE_CHOICES = (
        ('ADMIN', 'Admin'),
        ('GUEST', 'Guest')
    )
    company = models.ForeignKey(Company, on_delete=models.CASCADE, related_name='users')
    access_type = models.CharField(max_length=5, choices=ACCES_TYPE_CHOICES)

I want a query that would return the values of the number of users with access_type == GUEST and the number of users with access_type == ADMIN for each company, like this:

<QuerySet [{'id': 1, 'name': 'Company name', 'admin_users': 3, 'guest_users': 1}]>

I tried some approaches with annotate but couldn't find a solution.

Thanks for your help!

django-queryset django
1 Answers

You can use the filter=… parameter [Django-doc] in a Count expression [Django-doc]:

from django.db.models import Count, Q

Company.objects.annotate(
    admin_users=Count('users', filter=Q(access_type='ADMIN')),
    guest_users=Count('users', filter=Q(access_type='GUEST'))
)

The Company objects that arise from this will have two extra attributes: .admin_users and .guest_users.

user_675790
Related