ThinkPHP 嵌套集合模型（Nested Set Model）适配用户邀请关系

CREATE TABLE wg_user_invite ( id int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', user_id int(11) DEFAULT NULL COMMENT '用户ID', lft int(11) DEFAULT NULL COMMENT '左节点', rgt int(11) DEFAULT NULL COMMENT '右节点', root int(11) DEFAULT NULL COMMENT '根节点', depth int(11) DEFAULT NULL COMMENT '深度', parent_id int(11) DEFAULT NULL COMMENT '邀请人', PRIMARY KEY (id), KEY user_id (user_id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户邀请人变更日志';

/** * 新增根节点（无上级的用户）. * @param int $userId 用户ID * @return bool */ public function addRootNode($userId) { $exists = $this->where('user_id', $userId)->find(); if ($exists) { return true; } return $this->save([ 'user_id' => $userId, 'lft' => 1, 'rgt' => 2, 'root' => $userId, 'depth' => 0, 'parent_id' => 0, ]); }

/** * 新增子节点（邀请下级）. * @param int $parentUserId 邀请人用户ID * @param int $childUserId 被邀请人用户ID * @return bool */ public function addChildNode($childUserId = null, $parentUserId = null) { if (!$parentUserId) { return $this->addRootNode($parentUserId); } $parent = $this->where('user_id', $parentUserId)->find(); if (!$parent) { return false; } $parentRgt = $parent['rgt']; $root = $parent['root']; $depth = $parent['depth'] + 1; // 更新节点lft/rgt $this->where('root', $root)->where('lft', '>', $parentRgt)->inc('lft', 2)->update(); $this->where('root', $root)->where('rgt', '>=', $parentRgt)->inc('rgt', 2)->update(); return $this->save([ 'user_id' => $childUserId, 'lft' => $parentRgt, 'rgt' => $parentRgt + 1, 'root' => $root, 'depth' => $depth, 'parent_id' => $parentUserId, ]); }

/** * 删除节点（含所有下级）. * @param int $userId 要删除的用户ID * @return bool */ public function deleteNode($userId) { $node = $this->where('user_id', $userId)->find(); if (!$node) { return true; } $lft = $node['lft']; $rgt = $node['rgt']; $root = $node['root']; $diff = $rgt - $lft + 1; $this->where('root', $root)->where('lft', '>=', $lft)->where('rgt', '<=', $rgt)->delete(); $this->where('root', $root)->where('lft', '>', $rgt)->dec('lft', $diff)->update(); $this->where('root', $root)->where('rgt', '>', $rgt)->dec('rgt', $diff)->update(); return true; }

/** * 修改节点的父节点（变更邀请人）. * @param int $userId 要修改的用户ID * @param int $newParentId 新的邀请人ID * @return bool */ public function updateParentNode($userId, $newParentId) { $user = $this->where('user_id', $userId)->find(); $newParent = $this->where('user_id', $newParentId)->find(); if (!$user || !$newParent) { return false; } $this->deleteNode($userId); return $this->addChildNode($userId, $newParentId); }

/** * 获取指定用户的节点深度. * @param int $userId 用户ID * @return null|int 深度（根节点为0） */ public function getNodeDepth(int $userId): ?int { $node = $this->where('user_id', $userId)->value('depth'); return null === $node ? null : (int) $node; }

/** * 获取指定用户的邀请节点数结构（层级+数量）. * @param int $userId 用户ID * @return array 示例：[0 => 1, 1 => 5, 2 => 10] */ public function getInviteNodeCount(int $userId): array { $node = $this->where('user_id', $userId)->find(); if (!$node) { return []; } $lft = $node['lft']; $rgt = $node['rgt']; $root = $node['root']; $baseDepth = $node['depth']; $list = $this->where('root', $root) ->where('lft', '>', $lft) ->where('rgt', '<', $rgt) ->column('depth'); $count = []; $count[0] = 1; foreach ($list as $depth) { $level = $depth - $baseDepth; $count[$level] = ($count[$level] ?? 0) + 1; } ksort($count); return $count; }

/** * 获取上级邀请链路（从自己到最顶层）. * @param int $userId 用户ID * @return array 示例：[1001, 1000, 999] */ public function getParentChain(int $userId): array { $chain = [$userId]; $currentId = $userId; while (true) { $parentId = $this->where('user_id', $currentId)->value('parent_id'); if (!$parentId || 0 == $parentId) { break; } $chain[] = $parentId; $currentId = $parentId; } return $chain; } public function getParentChainV2(int $userId): array { $currentUser = $this->where('user_id', $userId)->find(); if (empty($currentUser)) { return []; } $chain = $this->where('root', $currentUser['root']) ->where('lft', '<', $currentUser['lft']) ->where('rgt', '>', $currentUser['rgt']) ->order('lft', 'asc') ->column('user_id'); $chain[] = $userId; return array_reverse($chain); }

/** * 获取所有邀请下级（含所有层级）. * @param int $userId 用户ID * @return array 下级用户ID列表 */ public function getAllChildren(int $userId): array { $node = $this->where('user_id', $userId)->find(); if (!$node) { return []; } return $this->where('root', $node['root']) ->where('lft', '>', $node['lft']) ->where('rgt', '<', $node['rgt']) ->column('user_id'); }

/** * 获取所有上级（按从近到远顺序）. * @param int $userId 用户ID * @return array 上级用户ID列表 */ public function getAllParents(int $userId): array { $chain = $this->getParentChain($userId); array_shift($chain); return $chain; }

/** * 获取所有独立的邀请链路（每个根节点一条）. * @return array 示例：[[999, 1000, 1001], [1002, 1003]] */ public function getAllIndependentChains(): array { $rootNodes = $this->where('parent_id', 0)->column('user_id'); $chains = []; foreach ($rootNodes as $root) { $nodes = $this->where('root', $root)->order('lft')->column('user_id'); $chains[] = $nodes; } return $chains; }

/** * 获取指定用户的下级树形结构 * @param int $userId 根用户ID * @param bool $withSelf 是否包含自己（默认包含） * @return array 树形结构数组 */ public function getChildrenTree(int $userId, bool $withSelf = true): array { $currentNode = $this->where('user_id', $userId)->find(); if (!$currentNode) { return []; } $lft = $currentNode['lft']; $rgt = $currentNode['rgt']; $root = $currentNode['root']; $baseDepth = $currentNode['depth']; $nodes = $this->where('root', $root) ->where('lft', '>=', $lft) ->where('rgt', '<=', $rgt) ->field('user_id, depth, parent_id') ->select() ->toArray(); $nodeMap = []; foreach ($nodes as $node) { $nodeMap[$node['user_id']] = [ 'user_id' => $node['user_id'], 'depth' => $node['depth'] - $baseDepth, 'parent_id' => $node['parent_id'], 'children' => [] ]; } foreach ($nodeMap as $id => $node) { $parentId = $node['parent_id']; if ($parentId != 0 && isset($nodeMap[$parentId])) { $nodeMap[$parentId]['children'][] = &$nodeMap[$id]; } } if ($withSelf) { return $nodeMap[$userId]; } else { return $nodeMap[$userId]['children']; } }

/** * 获取所有独立邀请树（所有根节点的树形结构） * @return array 所有独立树的列表 */ public function getAllRootTrees(): array { $rootUserIds = $this->where('parent_id', 0)->column('user_id'); $trees = []; foreach ($rootUserIds as $rootId) { $trees[] = $this->getChildrenTree($rootId); } return $trees; }

• 查询效率极高：查询子节点、路径、层级结构时，无需递归，单条 SQL 即可完成
• 适合读多写少场景：如商品分类、网站导航栏等
• 易于统计：可快速计算某节点下的子节点总数（公式：(rgt - lft - 1) / 2）

• 写操作复杂：添加、删除、移动节点时，需要批量更新其他节点的 lft/rgt 值
• 数据一致性要求高：更新 lft/rgt 时需用事务，否则容易出现数据错乱
• 不适合高频修改场景：如频繁新增、删除的评论树

• PHP 嵌套集合模型的核心是通过深度优先遍历给节点标记 lft/rgt 值，将树形结构转化为二维表，实现高效查询
• 核心优势是查询效率高（无递归），核心劣势是写操作复杂（需批量更新 lft/rgt）
• 适用场景：读多写少的树形结构（分类、导航），不适用高频修改的场景（评论、动态列表）