如何在Laravel/SQL中获取带权限关联的任务分类子孙节点

问题背景 #

现有三张数据表：

• task_categories：id, name, parent_id, _lft, _rgt（嵌套集结构）
• team_members：id, name
• task_category_team_member：id, task_category_id, team_member_id（关联表）

需求：获取指定task_categories节点的所有子孙节点，要求这些节点自身存在task_category_team_member关联记录，或者其子孙节点中存在该关联记录。

示例场景：

• 任务1
• 任务1.2（父节点为任务1）
• 任务1.2.1（父节点为任务1.2）
• 任务2

其中task_category_team_member中仅存在任务1.2.1的关联记录。当前Laravel代码仅返回任务1.2.1，但实际需要返回任务1.2和任务1.2.1（因为任务1.2的子孙节点有权限关联）。

现有代码的问题 #

现有代码使用orWhereHas仅筛选了自身存在关联的子孙节点，没有考虑该节点的后代是否存在关联记录，因此无法返回那些自身无关联但后代有关联的父节点。

Laravel解决方案 #

我们需要筛选出指定节点的子孙中，自身有权限、后代有权限、或祖先（在指定节点范围内）有权限的所有节点，具体实现如下：

子查询简化版 #

$targetTask = Task::where('id', 1)->first();
$teamMemberId = $this->taskCategoryPolicy->teamMember->id;

$results = Task::whereBetween('_lft', [$targetTask->_lft, $targetTask->_rgt])
    ->where(function ($query) use ($targetTask, $teamMemberId) {
        // 自身存在关联权限
        $query->whereHas('taskCategoryTeamMember', function ($q) use ($teamMemberId) {
            $q->where('team_member_id', $teamMemberId);
        })
        // 后代中存在有权限的节点
        ->orWhereExists(function ($q) use ($teamMemberId) {
            $q->select(DB::raw(1))
                ->from('task_categories as child')
                ->join('task_category_team_member as tctm', 'child.id', '=', 'tctm.task_category_id')
                ->whereRaw('child._lft > task_categories._lft')
                ->whereRaw('child._rgt < task_categories._rgt')
                ->where('tctm.team_member_id', $teamMemberId);
        })
        // 祖先（在指定节点子孙范围内）存在有权限的节点
        ->orWhereExists(function ($q) use ($targetTask, $teamMemberId) {
            $q->select(DB::raw(1))
                ->from('task_categories as parent')
                ->join('task_category_team_member as tctm', 'parent.id', '=', 'tctm.task_category_id')
                ->whereRaw('parent._lft < task_categories._lft')
                ->whereRaw('parent._rgt > task_categories._rgt')
                ->whereBetween('parent._lft', [$targetTask->_lft, $targetTask->_rgt])
                ->where('tctm.team_member_id', $teamMemberId);
        });
    })
    ->distinct()
    ->get();

对应的SQL示例 #

假设指定节点的_lft为1、_rgt为10（对应任务1的嵌套集范围），团队成员ID为123，SQL如下：

SELECT DISTINCT tc.*
FROM task_categories tc
WHERE tc._lft BETWEEN 1 AND 10
AND (
    -- 自身存在关联记录
    EXISTS (
        SELECT 1
        FROM task_category_team_member tctm
        WHERE tctm.task_category_id = tc.id
        AND tctm.team_member_id = 123
    )
    -- 后代中存在关联记录
    OR EXISTS (
        SELECT 1
        FROM task_categories child
        JOIN task_category_team_member tctm ON child.id = tctm.task_category_id
        WHERE child._lft > tc._lft
        AND child._rgt < tc._rgt
        AND tctm.team_member_id = 123
    )
    -- 祖先（在指定节点范围内）存在关联记录
    OR EXISTS (
        SELECT 1
        FROM task_categories parent
        JOIN task_category_team_member tctm ON parent.id = tctm.task_category_id
        WHERE parent._lft < tc._lft
        AND parent._rgt > tc._rgt
        AND parent._lft BETWEEN 1 AND 10
        AND tctm.team_member_id = 123
    )
);

SQL逻辑解释 #

1. 外层查询先限定范围：只处理指定节点的所有子孙节点（通过_lft BETWEEN 1 AND 10）。
2. 第一个EXISTS筛选自身有权限的节点。
3. 第二个EXISTS筛选后代中有权限的节点（即该节点是某个有权限节点的祖先）。
4. 第三个EXISTS筛选祖先中有权限的节点（即该节点是某个有权限节点的子孙）。
5. 用DISTINCT去重，避免重复返回同一节点。

内容的提问来源于stack exchange，提问作者Stanislau Karaliou