Laravel 5.5中如何统计用户自关联的5级子节点数量

嗨，针对你Laravel 5.5项目里的这个需求，我有两个高效的方案，完全不用加载任何用户模型实例，直接通过数据库查询拿到各层级的子节点数量，性能拉满！

方案一：使用递归CTE（推荐，MySQL 8+/PostgreSQL适用） #

递归CTE可以一次性查询出所有层级的子节点，然后按层级分组统计数量，非常高效。你可以直接用DB门面来实现：

$userId = $user->id;

$levels = DB::select("
    WITH RECURSIVE user_hierarchy AS (
        SELECT id, parent_id, 1 AS level
        FROM users
        WHERE parent_id = ?
        UNION ALL
        SELECT u.id, u.parent_id, uh.level + 1 AS level
        FROM users u
        JOIN user_hierarchy uh ON u.parent_id = uh.id
        WHERE uh.level < 5
    )
    SELECT level, COUNT(*) AS count
    FROM user_hierarchy
    GROUP BY level
    ORDER BY level
", [$userId]);

// 格式化输出
foreach ($levels as $level) {
    echo "Level {$level->level}: {$level->count}\n";
}

说明： #

• 这个CTE会从指定用户的直接子节点（Level 1）开始，递归查询到第5级为止
• 最后按层级分组统计数量，完全在数据库层面完成，不会加载任何User模型对象
• 如果你的数据库是MySQL 5.x不支持CTE，看下面的方案二

方案二：分层左连接（兼容MySQL 5.x） #

因为只需要统计到5级，我们可以通过多次左连接来分别查询每一层的节点，然后统计数量：

$userId = $user->id;

// Level 1：直接子节点
$level1 = DB::table('users')->where('parent_id', $userId)->count();

// Level 2：Level1节点的子节点
$level2 = DB::table('users as l1')
    ->join('users as l2', 'l2.parent_id', '=', 'l1.id')
    ->where('l1.parent_id', $userId)
    ->count();

// Level 3：Level2节点的子节点
$level3 = DB::table('users as l1')
    ->join('users as l2', 'l2.parent_id', '=', 'l1.id')
    ->join('users as l3', 'l3.parent_id', '=', 'l2.id')
    ->where('l1.parent_id', $userId)
    ->count();

// Level 4：Level3节点的子节点
$level4 = DB::table('users as l1')
    ->join('users as l2', 'l2.parent_id', '=', 'l1.id')
    ->join('users as l3', 'l3.parent_id', '=', 'l2.id')
    ->join('users as l4', 'l4.parent_id', '=', 'l3.id')
    ->where('l1.parent_id', $userId)
    ->count();

// Level 5：Level4节点的子节点
$level5 = DB::table('users as l1')
    ->join('users as l2', 'l2.parent_id', '=', 'l1.id')
    ->join('users as l3', 'l3.parent_id', '=', 'l2.id')
    ->join('users as l4', 'l4.parent_id', '=', 'l3.id')
    ->join('users as l5', 'l5.parent_id', '=', 'l4.id')
    ->where('l1.parent_id', $userId)
    ->count();

// 格式化输出
echo "Level 1: {$level1}\n";
echo "Level 2: {$level2}\n";
echo "Level 3: {$level3}\n";
echo "Level 4: {$level4}\n";
echo "Level 5: {$level5}\n";

说明： #

• 这个方法虽然写起来繁琐一点，但完全兼容不支持CTE的旧版本MySQL
• 每一层的查询都是直接在数据库统计数量，不会加载任何模型对象，性能同样优秀

为什么你的原有代码不行？ #

你之前用的User::with('parent')->where('id','=', $user->id)->get()其实只拿到了当前用户自己和它的父节点，根本没查询子节点；就算你用with('children')递归加载，也会把所有子节点的模型实例加载到内存里，数据量大的时候性能会很差，这两个方案完美解决了这个问题。

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