Django:如何以非可怕的方式查询孩子、孙子等记录

Question

我正在尝试在我的员工表中返回与用户具有嵌套关系的用户列表/过滤器。例如，我有与其经理绑定的员工，我希望能够查询该经理下的所有员工(这包括主经理下的任何其他经理下的任何员工(。因此，如果用户Bob有 2 个直接下属，则Sally和Brian。Brian有 2 个直接下属，Sally有 3 个直接下属。我希望鲍勃能够看到所有 7 名员工。现在，我能让它工作的唯一方法是通过一个可怕的序列，如下所示。我希望有一种更简单/更有效的方法。

manager = Employees.objects.filter(manager_id=request.user.id).values('manager')
employee_ids = list(Employees.objects.filter(manager=manager.first()['manager']).values_list('employee', flat=True))
employees = [User.objects.get(id=i).username for i in employee_ids]
grandchildren = []
for i in employees:
user_id = User.objects.get(username=i).id
child = list(Employees.objects.filter(manager=user_id).values_list('employee', flat=True))
grandchildren.append(child)
children = list(chain.from_iterable(grandchildren))
for i in children:
user_id = User.objects.get(id=i).id
child = list(Employees.objects.filter(manager=user_id).values_list('employee', flat=True))
grandchildren.append(child)
grandchildren = list(chain.from_iterable(grandchildren))
for i in grandchildren:
employees.append(User.objects.get(id=i).username)
employees = list(set(employees))

Answer 1

抱歉，您的代码看起来非常糟糕。首先，我的意思是太多的数据库查询(其中大多数都非常未优化甚至不需要(。

根据您的描述，我建议尝试这样的事情：

manager_id = request.user.id
children_ids = list(
Employees.objects.filter(manager_id=manager_id).values_list('employee', flat=True)
)
grandchildren_ids = list(
Employees.objects.filter(manager_id__in=children_ids).values_list('employee', flat=True)
)
# If you want to go deeper, do this in a loop and stop once an empty list of IDs is fetched 
# (which means that there are no descendants anymore)
# Combine all IDs and finally fetch the actual users 
# (do it only once, and fetch all the users in a single query, not one by one)
employees_ids = children_ids + grandchildren_ids
employees = User.objects.filter(id__in=employees_ids)

PS：这是开玩笑吗user_id = User.objects.get(id=i).id？ :)