如何从数据库获取推荐链信息？递归CTE查询报错求助

问题场景 #

现有一张名为referral的物理表，包含两列：

• refer_by：存储推荐人ID
• refer_to：存储被推荐人ID

表中数据如下：

需求：获取refer_by=1的完整推荐链（包含所有下级推荐关系）。

报错信息 #

执行原查询时触发如下错误：

ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ERROR: recursive reference to query "referral" must not appear within its non-recursive term

错误原因 #

递归CTE的名称referral与物理表名完全重名，导致非递归部分的SELECT ref1.* FROM referral ref1被解析为引用递归CTE本身，而非物理表，违反了递归CTE的语法规则（递归引用不能出现在非递归项中）。同时原递归部分的关联逻辑也存在问题，没有正确遍历推荐链。

修正后的查询代码 #

@Query(value = "WITH RECURSIVE referral_chain AS (" +
            "    -- 非递归项：获取初始推荐关系（refer_by=指定ID的记录）" +
            "    SELECT refer_by, refer_to FROM referral WHERE refer_by = :referByUserId " +
            "    UNION ALL " +
            "    -- 递归项：从当前推荐链的被推荐人出发，获取下一级推荐关系" +
            "    SELECT r.refer_by, r.refer_to " +
            "    FROM referral r " +
            "    INNER JOIN referral_chain rc ON r.refer_by = rc.refer_to " +
            ") " +
            "SELECT refer_by, refer_to FROM referral_chain", nativeQuery = true)
List<Object[]> getReferralChain(final Long referByUserId);

修正说明 #

1. 重命名递归CTE：将递归CTE命名为referral_chain，避免与物理表referral重名，解决递归引用混淆问题。
2. 调整递归关联逻辑：递归部分通过referral_chain（已遍历的推荐链）的refer_to（被推荐人）关联物理表referral的refer_by，获取该被推荐人作为推荐人的新记录，实现推荐链的深度遍历。
3. 简化非递归项：直接从物理表查询初始条件的记录，无需冗余别名。

执行结果 #

当传入referByUserId=1时，将返回完整的推荐链数据：

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