MySQL递归CTE查询：获取用户关注链接对应的最优产品节点

数据表结构 #

• products（无限嵌套结构）
  • id
  • parent_id
• links
  • id
  • product_id
• user_follows_link
  • user_id
  • link_id（注：原字段名linkd_id疑似笔误）

核心需求 #

查询返回拥有用户关注链接的产品，需遵循递归逻辑：

• 若某父产品及其后代的被关注链接总数高于其子产品，返回父产品而非子产品；
• 最终仅展示每个关注链路中尽可能深的节点——仅当父产品与后代的被关注链接数相同时，选择最深的节点。

尝试的Laravel查询（存在问题） #

原代码因whereDoesntHave闭包内使用havingRaw无法正常运行：

return Product::query()
            ->withCount([
                'allLinks' => function ($q) use ($user) {
                    $q->whereHas('isFollowedBy', function ($q) use ($user) {
                        $q->where('id', $user->id);
                    });
                }
            ])
            ->whereDoesntHave('parent as p', function ($q) use ($user) {
                $q->withCount([
                    'allLinks' => function ($q) use ($user) {
                        $q->whereHas('isFollowedBy', function ($q) use ($user) {
                            $q->where('id', $user->id);
                        });
                    }
                ])
                    ->havingRaw('all_links_count > p.all_links_count');
            })
            ->havingRaw('all_links_count > 0')
            ->get();

1. 修复Laravel查询逻辑 #

whereDoesntHave无法直接在闭包内关联当前模型的统计值进行对比，改用递归CTE+子查询实现需求：

$userId = $user->id;

// 递归统计每个产品自身+所有后代的被关注链接总数
$productFollowStats = Product::withRecursive([
    'descendants' => function ($query) {
        $query->select('id', 'parent_id');
    }
])
->selectRaw('products.id, COUNT(DISTINCT l.id) as total_follows')
->join('products as descendant', 'descendant.id', '=', 'products.id')
->leftJoin('links as l', 'descendant.id', '=', 'l.product_id')
->leftJoin('user_follows_link as ufl', 'l.id', '=', 'ufl.link_id')
->where('ufl.user_id', $userId)
->groupBy('products.id');

// 主查询：筛选无父产品总关注数更高的节点
return Product::query()
    ->select('products.*', 'stats.total_follows')
    ->joinSub($productFollowStats, 'stats', 'products.id', '=', 'stats.id')
    ->where('stats.total_follows', '>', 0)
    ->whereNotExists(function ($query) use ($userId) {
        $query->select(DB::raw(1))
            ->from('products as parent')
            ->joinSub($productFollowStats, 'parent_stats', 'parent.id', '=', 'parent_stats.id')
            ->whereRaw('parent.id = products.parent_id')
            ->whereRaw('parent_stats.total_follows > stats.total_follows');
    })
    ->orderByRaw('(SELECT MAX(depth) FROM product_tree WHERE descendant_id = products.id) DESC')
    ->get();

逻辑说明 #

• 用withRecursive生成产品的递归层级树，统计每个产品及其所有后代的总关注链接数；
• 通过whereNotExists排除存在父产品且父产品总关注数更高的节点；
• 最后按节点深度降序排序，确保当父/子节点关注数相同时，返回最深的节点。

2. 持久化优化方案（高频查询场景） #

如果递归查询性能瓶颈明显，可通过以下方式持久化统计数据：

方案A：实时维护产品后代关注数字段 #

1. 在products表新增total_descendant_follows字段，存储该产品自身+所有后代的被关注链接总数；
2. 当用户关注/取消链接时，触发递归更新：遍历该链接所属产品的所有祖先节点，同步更新total_descendant_follows的增减；
3. 查询时直接基于该字段筛选，无需每次递归统计：

   return Product::query()
       ->where('total_descendant_follows', '>', 0)
       ->whereNotExists(function ($query) {
           $query->select(DB::raw(1))
               ->from('products as parent')
               ->whereRaw('parent.id = products.parent_id')
               ->whereRaw('parent.total_descendant_follows > products.total_descendant_follows');
       })
       ->orderBy('depth', 'desc') // 需提前维护depth字段记录节点深度
       ->get();
   

方案B：预生成层级快照与统计 #

1. 定期（或触发式）生成product_hierarchy快照表，包含ancestor_id、descendant_id、depth三个字段，存储所有产品的祖先-后代关系；
2. 基于快照表和user_follows_link统计每个祖先节点的总关注数，存入product_follow_stats表；
3. 查询时直接关联统计表，快速筛选符合条件的产品，性能远高于实时递归查询。

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