PostgreSQL如何递归查询指定package的所有层级依赖关系

可行查询语句 #

以下是基于递归CTE实现的全量依赖查询语句，直接替换其中的包名和版本号即可使用：

WITH RECURSIVE dependency_tree AS (
    -- 锚点：定位需要查询的根包，层级设为1
    SELECT 
        p.name,
        v.version,
        v.id AS version_id,
        1 AS level
    FROM package p
    INNER JOIN version v ON p.id = v.package_id
    WHERE 
        p.name = 'react-router-dom'
        AND v.version = '5.2.0'
    
    UNION ALL
    
    -- 递归遍历下层依赖，层级逐次+1
    SELECT 
        dep_pkg.name,
        dep_ver.version,
        dep_ver.id AS version_id,
        dt.level + 1 AS level
    FROM dependency_tree dt
    INNER JOIN dependency dep ON dt.version_id = dep.version_id
    INNER JOIN version dep_ver ON dep.dependency_id = dep_ver.id
    INNER JOIN package dep_pkg ON dep_ver.package_id = dep_pkg.id
)
SELECT name, version, level
FROM dependency_tree
ORDER BY level, name;

防循环依赖优化 #

如果你的存储数据存在循环依赖场景，可以添加已遍历版本ID的检测逻辑，避免出现无限递归：

WITH RECURSIVE dependency_tree AS (
    SELECT 
        p.name,
        v.version,
        v.id AS version_id,
        1 AS level,
        ARRAY[v.id] AS visited_ids
    FROM package p
    INNER JOIN version v ON p.id = v.package_id
    WHERE 
        p.name = 'react-router-dom'
        AND v.version = '5.2.0'
    
    UNION ALL
    
    SELECT 
        dep_pkg.name,
        dep_ver.version,
        dep_ver.id AS version_id,
        dt.level + 1 AS level,
        dt.visited_ids || dep_ver.id
    FROM dependency_tree dt
    INNER JOIN dependency dep ON dt.version_id = dep.version_id
    INNER JOIN version dep_ver ON dep.dependency_id = dep_ver.id
    INNER JOIN package dep_pkg ON dep_ver.package_id = dep_pkg.id
    WHERE dep_ver.id <> ALL(dt.visited_ids)
)
SELECT name, version, level
FROM dependency_tree
ORDER BY level, name;

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