Groupby和count子节点数，其中每个子节点本身的子节点数大于特定数量

Question

我有三个模型，Business,Employee和Client，其中每个企业可以有许多员工，每个员工可以有许多客户:

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
)

一个样本数据:

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)

如果我想知道每个企业有多少员工，我可以运行这样的查询:

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}
]>

同样，如果我想知道每个员工有多少个客户，我可以运行这样的查询:

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}
]>

但是我想看到，对于每个企业，拥有一个以上客户的员工数量。我期待这样的输出:

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

我尝试使用Count与Subquery，但结果不是我所期望的。

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}]>

如何为每个企业检索拥有多个客户的员工数量?

Answer 1

要查看有多少企业的员工不止一个客户，您可以尝试:

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('id',distinct=True)).values('c')
Business.objects.annotate(
employee_count=Subquery(employees)).values("name", "employee_count").order_by("-employee_count")

我在这里采取相同的方法，我使用基于业务的client_count进行过滤(使用OuterRef)，然后在使用Subquery进行注释时再次计数。

更新/不工作:另一种方法是简单地从Employee查询并使用GROUP BY。

Employee.objects.annotate(
client_count=Count("clients")
).values('business').filter=Q(client_count__gt=1).annotate(
employee_count=Count('business')
).values('business', "employee_count")