Groupby and count number of children where each child has more than a specific number of children itself

I have three models, Business, Employee, and Client, where each business can have many employees and each employee can have many clients:

class Business(models.Model):
    name = models.CharField(max_length=128)
    menu = models.CharField(max_length=128, default="")
    slogan = models.CharField(max_length=128, default="")
    slug = models.CharField(max_length=128, default="")


class Employee(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    business = models.ForeignKey(
        Business,
        related_name="employees",
        on_delete=models.CASCADE
    )


class Client(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    employee = models.ForeignKey(
        Employee,
        related_name="clients",
        on_delete=models.CASCADE
    )

A sample data:

Business.objects.create(name="first company")
Business.objects.create(name="second company")
Business.objects.create(name="third company")
Employee.objects.create(first_name="f1", last_name="l1", business_id=1)
Employee.objects.create(first_name="f2", last_name="l2", business_id=1)
Employee.objects.create(first_name="f3", last_name="l3", business_id=2)
Employee.objects.create(first_name="f4", last_name="l4", business_id=3)
Employee.objects.create(first_name="f5", last_name="l5", business_id=3)
Employee.objects.create(first_name="f6", last_name="l6", business_id=3)
Client.objects.create(first_name="cf1", last_name="cl1", employee_id=1)
Client.objects.create(first_name="cf2", last_name="cl2", employee_id=1)
Client.objects.create(first_name="cf3", last_name="cl3", employee_id=2)
Client.objects.create(first_name="cf4", last_name="cl4", employee_id=2)
Client.objects.create(first_name="cf5", last_name="cl5", employee_id=3)
Client.objects.create(first_name="cf6", last_name="cl6", employee_id=3)
Client.objects.create(first_name="cf7", last_name="cl7", employee_id=4)
Client.objects.create(first_name="cf8", last_name="cl8", employee_id=5)
Client.objects.create(first_name="cf9", last_name="cl9", employee_id=6)

If I wanted to see how many employees each business has, I could run a query like this:

Business.objects.annotate(
    employee_count=Count("employees")
).values(
    "name", "employee_count"
).order_by("-employee_count")

<QuerySet [
    {'name': 'third company', 'employee_count': 3},
    {'name': 'first company', 'employee_count': 2},
    {'name': 'second company', 'employee_count': 1}
]>

Similarly, if I wanted to see how many clients each employee has, I could run a query like this:

Employee.objects.annotate(
    client_count=Count("clients")
).values(
    "first_name", "client_count"
).order_by("-client_count")

<QuerySet [
    {'first_name': 'f1', 'client_count': 2},
    {'first_name': 'f2', 'client_count': 2},
    {'first_name': 'f3', 'client_count': 2},
    {'first_name': 'f4', 'client_count': 1},
    {'first_name': 'f5', 'client_count': 1},
    {'first_name': 'f6', 'client_count': 1}
]>

But I want to see, for every business, the number of employees that have more than one clients. I'm expecting an output like this:

<QuerySet [
    {'name': 'first company', 'employee_count': 2},
    {'name': 'second company', 'employee_count': 1},
    {'name': 'third company', 'employee_count': 0}
]>

I tried to use Count with Subquery, but the result isn't what I expected.

employees_with_multiple_clients = Employee.objects.annotate(
    client_count=Count("clients")
).filter(client_count__gt=1)


Business.objects.annotate(
    employee_count=Count(Subquery(employees_with_multiple_clients.values('id')))
).values("name", "employee_count").order_by("-employee_count")


<QuerySet [
    {'name': 'first company', 'employee_count': 1},
    {'name': 'second company', 'employee_count': 1},
    {'name': 'third company', 'employee_count': 1}]>

How can I retrieve, for every business, the number of employees that have more than one clients?

For seeing how many businesses have employees more than one client, you can try:

employees_with_multiple_clients = Employee.objects.annotate(
    client_count=Count("clients")
).filter(business=OuterRef('pk'), client_count__gt=1)

employees = employees_with_multiple_clients.annotate(c=Count('*')).values('c')

Business.objects.annotate(
    employee_count=Subquery(employees).values("name", "employee_count").order_by("-employee_count")

I am taking the same approach here, I am filtering using the client_count based on business (using OuterRef) and then counting again when annotating using Subquery.

Back to Top