尝试在PostgreSQL中使用WITH RECURSIVE

Question

我试图遵循本文中的示例：https://learnsql.com/blog/how-to-query-hierarchical-data

我在PostgreSQL中构建了一个简单的表来保存分层数据：

media_set_id (uuid)
name (citext)
parent_id (uuid and NULL allowed)

这是我构建的查询：

WITH RECURSIVE hierarchy AS (
SELECT media_set_id AS id,
name,
parent_id,
CAST (name AS citext) AS path
FROM media_sets
WHERE parent_id IS NULL

UNION ALL

SELECT media_sets.media_set_id,
media_sets.name,
media_sets.parent_id,
hierarchy.path || '' || media_sets.name
FROM media_sets, hierarchy
WHERE media_sets.parent_id = hierarchy.id
)
SELECT * FROM hierarchy;

表中填充了一些示例数据。

当我运行查询时，我得到这样的错误：relationship"层次结构"；不存在

我不知道我的SQL代码出了什么问题。有什么想法吗？

Robert

Answer 1

稍微重写一下代码，就会得到预期的结果。

CREATE tABLE media_sets (
media_set_id uuid,
name text,
parent_id uuid )

WITH RECURSIVE hierarchy AS (
SELECT media_set_id AS id,
name,
parent_id,
CAST (name AS text) AS path
FROM media_sets
WHERE parent_id IS NULL

UNION ALL

SELECT media_sets.media_set_id,
media_sets.name,
media_sets.parent_id,
hierarchy.path || '' || media_sets.name
FROM media_sets JOIN hierarchy
ON media_sets.parent_id = hierarchy.id
)
SELECT * FROM hierarchy;

嗯。。。这种替代方法确实有效：

WITH RECURSIVE hierarchy AS (
SELECT media_set_id,
name,
parent_id
FROM media_sets
WHERE parent_id IS NULL

UNION ALL

SELECT ms.media_set_id,
ms.name,
ms.parent_id
FROM media_sets ms
INNER JOIN hierarchy h on ms.parent_id = h.media_set_id
)
SELECT * 
FROM hierarchy;

我真的不知道为什么这个有效，另一个无效，但我可以使用这个版本，因为它按照我在UI中构建层次树所需的顺序返回记录。

Answer 2

实际上，它没有做的一件事是按照我理想需要的方式对数据进行排序。我所希望的是层次结构像这样返回：

root
parent1
child1_of_parent1
child2_of_parent1
parent2
child1_of_parent2
child1_of_child1_of_parent2
child2_of_child1_of_parent2
child2_of_parent2

如果有人知道如何强制返回这样的记录顺序，那就是我想要解决的最后一块难题。

Answer 3

我很抱歉把这个问答搞砸了；A.但出于完整性考虑，我觉得提供一套我知道有效的SQL代码是合适的，因为它可能会帮助nbk！

WITH RECURSIVE hierarchy AS (
SELECT media_set_id,
name,
parent_id,
cast (name as text) as path
FROM media_sets
WHERE parent_id IS NULL
UNION ALL
SELECT ms.media_set_id,
ms.name,
ms.parent_id,
h.path || '' || ms.name
FROM media_sets ms
INNER JOIN hierarchy h on ms.parent_id = h.media_set_id
)
SELECT * 
FROM hierarchy
ORDER BY path;

最终的ORDER BY以一种允许我构建分层UI并从上到下填充树的方式返回记录。