Laravel/SQL获取指定分类的所有父级及自身数据问题求助

我有一个categories表（实际表名为forum_categories），通过parent_id字段做自关联外键，想要获取指定分类的所有父级数据（包含自身）。

示例数据 #

id     title     parent_id
1      test      null
2      test1     1
3      test2     2

期望结果（查询id=3时） #

[
 {
   "id": 1,
   "title": "test"
 },
 {
   "id": 2,
   "title": "test1"
 },
 {
   "id": 3,
   "title": "test2"
 }
]

之前尝试固定层级的join写法只能拿到部分父级；用Laravel关联loopParents能获取完整数据，但无法指定只查询特定字段，求可行的实现方案。

解决方案 #

方法1：MySQL递归CTE（推荐，MySQL 8.0+支持） #

递归CTE可以动态遍历所有层级的父级，一次性查询出结果，效率远高于循环查询。

Laravel查询构造器实现：

$targetId = $this->main->id;

$allParents = DB::table('forum_categories')
    ->withRecursive(['cte' => function ($query) use ($targetId) {
        // 初始查询：获取目标分类自身
        $query->select('id', 'title', 'parent_id')
            ->from('forum_categories')
            ->where('id', $targetId);

        // 递归关联父级，直到parent_id为null
        $query->unionAll(function ($query) {
            $query->select('fc.id', 'fc.title', 'fc.parent_id')
                ->from('forum_categories as fc')
                ->join('cte', 'fc.id', '=', 'cte.parent_id');
        });
    }])
    ->select('id', 'title')
    ->from('cte')
    ->orderBy('id') // 按层级排序，根分类在前
    ->get();

该方法直接返回包含指定字段的完整父级集合，无需多次查询。

方法2：改进Laravel关联关系，支持指定字段 #

如果坚持用关联关系loopParents，可以通过指定查询字段实现需求：

1. 先在Category模型中定义递归关联：

public function loopParents()
{
    return $this->belongsTo(Category::class, 'parent_id')
                ->with('loopParents'); // 开启递归关联
}

2. 查询时指定字段（注意必须包含关联所需的id和parent_id）：

$category = Category::where('id', $this->main->id)
    ->with(['loopParents' => function ($query) {
        $query->select('id', 'title', 'parent_id'); // 必须带parent_id才能继续递归
    }])
    ->select('id', 'title', 'parent_id') // 自身也要保留parent_id
    ->first();

// 整理自身和所有父级为指定字段的数组
$allParents = collect();
$current = $category;
while ($current) {
    $allParents->push($current->only('id', 'title'));
    $current = $current->loopParents;
}

// 可选：反转数组让根分类排在最前面
$allParents = $allParents->reverse()->values();

方法3：不推荐的固定层级join写法 #

你之前的join写法是固定了两层关联，只能适配固定层级的分类结构，无法处理动态变化的层级深度，因此不推荐使用。

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