Laravel单表自关联场景下如何统计所有层级嵌套子节点总数

下面提供两种不同场景的实现方式：

方案1：递归访问器（适合小数据量、层级浅的场景） #

实现简单无需调整数据库结构，仅需在User模型中扩展逻辑：

1. 新增一级子用户基础关联

// User模型中新增
public function directChildren()
{
    return $this->hasMany(self::class, 'referrer_id');
}

2. 新增递归统计访问器

// User模型中新增
public function getTotalChildrenAttribute()
{
    $count = $this->directChildren->count();
    foreach ($this->directChildren as $child) {
        $count += $child->total_children;
    }
    return $count;
}

3. 控制器调用

$user = User::findOrFail($this->request->UserId);
// 直接获取统计结果
$totalChildren = $user->total_children;

注意：该方案会产生N+1查询，数据量较大时性能较差

方案2：递归CTE查询（生产环境首选，高性能） #

基于MySQL 8.0+支持的递归公共表表达式实现，仅需一次查询即可拿到结果，性能不受层级、数据量影响：

1. 直接在控制器中调用查询

use Illuminate\Support\Facades\DB;

$userId = $this->request->UserId;
$totalChildren = DB::select(
    'WITH RECURSIVE user_hierarchy AS (
        SELECT id FROM users WHERE referrer_id = ?
        UNION ALL
        SELECT u.id FROM users u
        INNER JOIN user_hierarchy uh ON u.referrer_id = uh.id
    )
    SELECT COUNT(*) AS total FROM user_hierarchy',
    [$userId]
)[0]->total;

2. 也可以封装为模型静态方法复用

// User模型中新增
public static function countAllNestedChildren(int $userId): int
{
    return DB::select(
        'WITH RECURSIVE user_hierarchy AS (
            SELECT id FROM users WHERE referrer_id = ?
            UNION ALL
            SELECT u.id FROM users u
            INNER JOIN user_hierarchy uh ON u.referrer_id = uh.id
        )
        SELECT COUNT(*) AS total FROM user_hierarchy',
        [$userId]
    )[0]->total;
}

// 控制器调用
$totalChildren = User::countAllNestedChildren($this->request->UserId);

低版本数据库兼容方案 #

如果使用的是不支持CTE的数据库版本，可以在users表新增path字段冗余存储上级链（比如用户ID为3、上级是1的path存1,3，下级用户4的path存1,3,4），统计时直接执行模糊查询即可：

$totalChildren = User::where('path', 'like', $userId.',%')->count();

内容的提问来源于stack exchange，提问作者Bilal sagheer Awan