MySQL 5.7无CTE递归查询父级节点方案咨询

针对你的场景（MySQL 5.7无CTE支持、Laravel框架、SPA API需求），以下是几种可行的非递归CTE方案：

方案1：Laravel模型层循环查询（最简实现） #

直接在模型中编写递归逻辑，循环查询父节点直到根节点（parent_id为null/0）。这种方式无需修改数据库结构，适合层级不深的场景。

实现代码 #

在Task模型中添加方法：

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Task extends Model
{
    // 获取当前任务的所有父级节点（从直接父级到根节点）
    public function getAncestors()
    {
        $ancestors = collect();
        $current = $this->parent;

        while ($current) {
            $ancestors->prepend($current); // 前置插入，保证顺序从根到当前父级
            $current = $current->parent;
        }

        return $ancestors;
    }

    // 定义父级关联
    public function parent()
    {
        return $this->belongsTo(Task::class, 'parent_id');
    }
}

控制器中调用（供API返回）：

public function showAncestors($taskId)
{
    $task = Task::findOrFail($taskId);
    return response()->json([
        'task' => $task,
        'ancestors' => $task->getAncestors()
    ]);
}

优缺点：

• 优点：零数据库改动，代码实现简单直观
• 缺点：层级过深时会产生多次DB查询，性能下降

方案2：物化路径（Path Enumeration）优化查询 #

在tasks表新增path字段（VARCHAR类型，长度按需设置），存储从根节点到当前节点的ID链（例如1,3,7，表示节点7的父级是3，根节点是1）。查询时直接拆分path即可获取所有父级ID，再批量查询。

步骤1：修改数据库表 #

ALTER TABLE tasks ADD COLUMN path VARCHAR(255) DEFAULT NULL AFTER parent_id;

步骤2：Laravel模型维护path字段 #

利用模型事件自动维护path值：

class Task extends Model
{
    protected static function booted()
    {
        static::creating(function ($task) {
            if ($task->parent_id) {
                $parent = self::find($task->parent_id);
                $task->path = $parent->path ? "{$parent->path},{$task->parent_id}" : "{$task->parent_id}";
            } else {
                $task->path = null;
            }
        });

        static::updating(function ($task) {
            if ($task->isDirty('parent_id')) {
                $oldParentId = $task->getOriginal('parent_id');
                $newParent = self::find($task->parent_id);
                
                // 更新当前节点的path
                $task->path = $newParent ? ($newParent->path ? "{$newParent->path},{$task->parent_id}" : "{$task->parent_id}") : null;
                
                // 递归更新所有子节点的path（可选，根据业务需求）
                $children = self::where('path', 'LIKE', "%{$oldParentId},{$task->id}%")->orWhere('parent_id', $task->id)->get();
                foreach ($children as $child) {
                    $child->path = str_replace($task->getOriginal('path') . ",{$task->id}", $task->path . ",{$task->id}", $child->path);
                    $child->save();
                }
            }
        });
    }

    // 获取所有父级节点
    public function getAncestors()
    {
        if (!$this->path) {
            return collect();
        }
        $parentIds = explode(',', $this->path);
        return self::whereIn('id', $parentIds)->orderByRaw("FIELD(id, {$this->path})")->get();
    }
}

查询示例 #

-- 直接查询某节点的所有父级ID
SELECT path FROM tasks WHERE id = ?;
-- 批量查询父级节点
SELECT * FROM tasks WHERE id IN (1,3,7) ORDER BY FIELD(id, '1,3,7');

优缺点：

• 优点：查询仅需1-2次DB请求，性能优异；支持快速获取任意节点的完整路径
• 缺点：需要维护path字段，节点移动时需同步更新子节点的path，逻辑稍复杂

方案3：嵌套集模型（Nested Set） #

嵌套集通过lft（左值）和rgt（右值）字段标记节点的层级范围，适合层级复杂、需要频繁查询祖先/后代的场景。Laravel生态有成熟的包可快速实现。

步骤1：安装依赖包 #

composer require kalnoy/nestedset

步骤2：修改数据库表 #

ALTER TABLE tasks ADD COLUMN lft INT UNSIGNED NOT NULL, ADD COLUMN rgt INT UNSIGNED NOT NULL;

步骤3：配置模型 #

use Kalnoy\Nestedset\NodeTrait;

class Task extends Model
{
    use NodeTrait;

    // 包已内置获取祖先的方法：$task->ancestors
}

控制器调用 #

public function showAncestors($taskId)
{
    $task = Task::findOrFail($taskId);
    return response()->json([
        'task' => $task,
        'ancestors' => $task->ancestors
    ]);
}

优缺点：

• 优点：查询祖先/后代的性能极高，支持复杂层级操作
• 缺点：节点的创建、移动、删除逻辑较复杂，依赖第三方包；数据迁移时需初始化lft/rgt值

内容的提问来源于stack exchange，提问作者Nick Davies