PostgreSQL递归查询报错：FROM子句引用无效，求获取目录及文档方案

问题描述 #

尝试编写递归查询获取指定目录下的所有子目录及文档时，执行以下SQL报错：

WITH RECURSIVE FilesInDir AS (
    SELECT
        dir.id as dirid,
        dir.directoryid,
        doc.id as docid
    FROM
        directory dir,
        document doc
    WHERE
        dir.id = 17
    UNION
        SELECT
            dir.id as dirid,
            dir.directoryId,
            doc.id as docid
        FROM
            directory dir,
            document doc
        INNER JOIN FilesInDir fid ON fid.dirid = dir.directoryid
) SELECT
    *
From
    FilesInDir;

错误信息 #

[102] ERROR:  invalid reference to FROM-clause entry for table "dir" at character 353
[102] HINT:  There is an entry for table "dir", but it cannot be referenced from this part of the query.

已实现仅获取指定目录下所有子目录的递归查询：

WITH RECURSIVE FilesInDir AS (
    SELECT
        id,
        directoryid
    FROM
        directory
    WHERE
        id = 17
    UNION
        SELECT
            dir.id,
            dir.directoryId
        FROM
            directory dir
        INNER JOIN FilesInDir fid ON fid.id = dir.directoryid
) SELECT
    *
From
    FilesInDir;

数据库表结构：

CREATE TABLE Repository(
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  repositoryName VARCHAR(128) UNIQUE NOT NULL);

CREATE TABLE Directory(
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  directoryName VARCHAR(128) NOT NULL,
  directoryId INTEGER REFERENCES Directory(id),
  repositoryId INTEGER REFERENCES Repository(id) NOT NULL,
  markdeleted BOOLEAN DEFAULT FALSE,
  UNIQUE NULLS NOT DISTINCT (directoryname, directoryid, repositoryId));

CREATE TABLE Document(
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  documentName VARCHAR(128) NOT NULL,
  directoryId INTEGER REFERENCES Directory(id),
  repositoryId INTEGER REFERENCES Repository(id) NOT NULL,
  markdeleted BOOLEAN DEFAULT FALSE,
  UNIQUE NULLS NOT DISTINCT (documentname, directoryid, repositoryid));

错误原因 #

1. JOIN语法优先级问题：UNION部分的FROM directory dir, document doc INNER JOIN FilesInDir fid ON ...中，INNER JOIN优先级高于逗号分隔的表连接，数据库会先将document doc与FilesInDir fid关联，此时dir表不在该关联的作用域内，导致无法引用dir.directoryid。
2. 无关联条件的笛卡尔积：初始查询中directory dir和document doc未添加关联条件，会生成所有文档与目标目录的笛卡尔积，不符合需求。

正确实现方案 #

方案一：分离递归目录与文档关联（推荐） #

先递归获取所有目标目录，再关联对应文档，结果清晰且高效：

WITH RECURSIVE AllDirs AS (
    -- 初始目录：指定ID的未删除目录
    SELECT id, directoryid, directoryName
    FROM directory
    WHERE id = 17 AND markdeleted = FALSE
    UNION ALL
    -- 递归获取所有子目录
    SELECT dir.id, dir.directoryid, dir.directoryName
    FROM directory dir
    INNER JOIN AllDirs ad ON ad.id = dir.directoryid
    WHERE dir.markdeleted = FALSE
)
-- 合并目录与文档数据
SELECT
    'directory' AS item_type,
    ad.id AS item_id,
    ad.directoryName AS name,
    ad.directoryid AS parent_id,
    NULL AS document_id
FROM AllDirs ad
UNION ALL
SELECT
    'document' AS item_type,
    doc.id AS item_id,
    doc.documentName AS name,
    ad.id AS parent_id,
    doc.id AS document_id
FROM AllDirs ad
INNER JOIN document doc ON ad.id = doc.directoryid
WHERE doc.markdeleted = FALSE
-- 按类型、父目录、名称排序，便于查看
ORDER BY item_type, parent_id, name;

方案二：左连接关联目录与文档（保留空文档目录） #

如果需要显示所有目录（包括无文档的目录），可使用左连接：

WITH RECURSIVE AllDirs AS (
    SELECT id, directoryid, directoryName
    FROM directory
    WHERE id = 17 AND markdeleted = FALSE
    UNION ALL
    SELECT dir.id, dir.directoryid, dir.directoryName
    FROM directory dir
    INNER JOIN AllDirs ad ON ad.id = dir.directoryid
    WHERE dir.markdeleted = FALSE
)
SELECT
    ad.id AS dir_id,
    ad.directoryName AS dir_name,
    ad.directoryid AS parent_dir_id,
    doc.id AS doc_id,
    doc.documentName AS doc_name
FROM AllDirs ad
LEFT JOIN document doc 
    ON ad.id = doc.directoryid 
    AND doc.markdeleted = FALSE
ORDER BY parent_dir_id, dir_id, doc_id;

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