具有字符串路径的分层数据-查询节点,获取其节点的所有父级和第一级 [英] Hierarchic data with string paths - query for a node, get all parents and first level of their nodes
问题描述
说我得到了树数据:
- A A
- A0 A/A0
- A0.0 A/A0/A0.0
- A0.1 A/A0/A0.1
- A1 A/A1
- A1.0 A/A1/A1.0
- A1.1 A/A1/A1.1
- A2 A/A2
它存储在postgresql数据库 tree-data中,其中有一列 id是上面节点的路径,而一些帮助者列如 depth(整数,表示树中节点的深度),终端(布尔值,是叶节点,没有子节点)。
It is stored within a postgresql database "tree-data", with a column 'id' that is the path of the node like above and some helper columns like 'depth' (integer, representing the nodes depth in the tree), 'terminal' (boolean, is a leaf node and has no children).
我现在想要实现的是查询 A / A0 / A0.0,即可检索所有父母及其第一级孩子。
让所有父母都很容易:
SELECT name, id, depth, terminal
FROM "tree-data"
WHERE 'A/A0/A0.0' LIKE id||'%'
ORDER BY id;
这将返回以下节点:
A
A/A0
A/A0/A0.0
但这是我需要的:
A
A/A0
A/A0/A0.0
A/A0/A0.1
A/A1
A/A2
您能想到一种简单有效的方法吗?
Can you think of an easy and efficient way of achieving this? Optimizing/modifying the schema is possible, though not preferred.
推荐答案
您可以使用 regexp_replace获取父级。 ()
,然后使用与您相同的逻辑:
You can get the parent using regexp_replace()
and then use the same logic you are using:
SELECT name, id, depth, terminal
FROM "tree-data"
WHERE 'A/A0/A0.0' LIKE regexp_replace(id, '/[^/]+$', '') || '%'
ORDER BY id;
这篇关于具有字符串路径的分层数据-查询节点,获取其节点的所有父级和第一级的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!