英文:
Find path in tree structure
问题
I have a PostgreSQL 15 table taxons
with this structure:
CREATE TABLE taxons (
id integer NOT NULL,
parent_id integer,
name jsonb
);
INSERT INTO taxons VALUES (1, null, '{ "en": "Clothes", "de": "Bekleidung" }');
INSERT INTO taxons VALUES (2, 1, '{ "en": "Men", "de": "Männer" }');
INSERT INTO taxons VALUES (3, 2, '{ "en": "Pants", "de": "Hosen" }');
I want to find a specific path by name in this structure, e.g. Clothes > Men > Pants
. The names could be in any locale of the name
field.
This is the query I have so far:
SELECT
t0.*
FROM
taxons t0
JOIN taxons t1 ON (
LOWER(t1.name->>'en') = LOWER('clothes') OR LOWER(t1.name->>'de') = LOWER('clothes')
)
AND t1.parent_id IS NULL
JOIN taxons t2 ON (
LOWER(t2.name->>'en') = LOWER('MEN') OR LOWER(t2.name->>'de') = LOWER('MEN')
)
AND t2.parent_id = t1.id
WHERE
(
LOWER(t0.name->>'en') = LOWER('Hosen') OR LOWER(t0.name->>'de') = LOWER('Hosen')
)
AND t0.parent_id = t2.id
My questions would be:
- Is there a better way to query the tree structure? I've read something about recursive CTEs, but have a hard time understanding it.
- Is it possible to use some kind of jsonb function to search within
name
for a match so that I don't have to know all the locales in my query? Some records can have different locales, and sometimes there are more than 5 translations for a name, so the query gets pretty long and unreadable.
Here is a DB Fiddle link.
英文:
I have a PostgreSQL 15 table taxons
with this structure:
CREATE TABLE taxons (
id integer NOT NULL,
parent_id integer,
name jsonb
);
INSERT INTO taxons VALUES (1, null, '{ "en": "Clothes", "de": "Bekleidung" }');
INSERT INTO taxons VALUES (2, 1, '{ "en": "Men", "de": "Männer" }');
INSERT INTO taxons VALUES (3, 2, '{ "en": "Pants", "de": "Hosen" }');
I want to find a specific path by name in this structure, e.g. Clothes > Men > Pants
. The names could be in any locale of the name
field.
This is the query I have so far:
SELECT
t0.*
FROM
taxons t0
JOIN taxons t1 ON (
LOWER(t1.name ->> 'en') = LOWER('clothes') OR LOWER(t1.name ->> 'de') = LOWER('clothes')
)
AND t1.parent_id IS NULL
JOIN taxons t2 ON (
LOWER(t2.name ->> 'en') = LOWER('MEN') OR LOWER(t2.name ->> 'de') = LOWER('MEN')
)
AND t2.parent_id = t1.id
WHERE
(
LOWER(t0.name ->> 'en') = LOWER('Hosen') OR LOWER(t0.name ->> 'de') = LOWER('Hosen')
)
AND t0.parent_id = t2.id
My questions would be:
- Is there a better way to query the tree structure? I've read something about recursive CTE's, but have a hard time understanding it.
- Is it possible to use some kind of jsonb function to search within
name
for a match so that I don't have to know all the locales in my query? Some records can have different locales, and sometimes there are more than 5 translations for a name, so the query get's pretty long and unreadable.
Here is a DB Fiddle https://www.db-fiddle.com/f/b28vZrTpRcX5RbhKm4ZvKo/3
答案1
得分: 1
以下是翻译好的部分:
WITH RECURSIVE cte AS ( -- 1
SELECT *, ARRAY[t.name ->> 'de'] as path -- 3
FROM taxons t
WHERE parent_id IS NULL
UNION -- 2
SELECT -- 4
t.*,
c.path || (t.name ->> 'de')
FROM taxons t
JOIN cte c ON c.id = t.parent_id
)
SELECT
array_to_string(c.path, ' -> ') as name -- 5
FROM cte c
- 启动递归公共表表达式 (CTE)。递归CTE始终包含两个部分:起始查询和递归部分。
- 这两个部分由
UNION
子句分隔。 - 这是起始部分。在此部分中,将找到根记录。在您的情况下,根记录是没有父记录的记录。除了其列之外,我添加了一个数组来存储路径。路径的第一个元素是当前的
name
。 - 现在它的工作方式类似于您所做的。在第一次递归中,将搜索所有具有与先前找到的
id
值(在这种情况下,是根记录的值)相等的parent_id
值的记录,通过执行JOIN
操作。先前结果始终由WITH
子句表示,因此连接表是cte
(在您的情况下,当前步骤的“表”是taxons
,先前结果的表是cte
)。接下来的递归将找到具有与上一次递归的id
值相等的parent_id
值的元素,依此类推。所有找到的记录都将附加到先前的记录中,通过UNION
子句连接。另外,我添加了所有找到的name
值到数组中,我在起始步骤中创建的用于保存路径的数组中。 - 之后获取整个递归集并从创建的路径数组中创建相应的字符串。
英文:
WITH RECURSIVE cte AS ( -- 1
SELECT *, ARRAY[t.name ->> 'de'] as path -- 3
FROM taxons t
WHERE parent_id IS NULL
UNION -- 2
SELECT -- 4
t.*,
c.path || (t.name ->> 'de')
FROM taxons t
JOIN cte c ON c.id = t.parent_id
)
SELECT
array_to_string(c.path, ' -> ') as name -- 5
FROM cte c
- Starting a recursive CTE. A recursive CTE always contains two parts. The starting query and the recursion part.
- Both parts are devided by the
UNION
clause. - This is the start. Here the root record(s) will be found. In your case the root is the record with no parents. Additionally to its columns, I added an array for storing the path. The first element in the path is the current
name
. - Now it works similar to what you did. In the first recursion all records are searched which
parent_id
values equal the previously foundid
values (in that case the one of the root record) by doing aJOIN
operation. The previous result is always represented by theWITH
clause, so the joining table iscte
(So, in your case the current step'stable
is taxons, the one for the previous results iscte
). The next recursion will find the elements with theparent_id
values equal to theid
values of the previous recursion and so on. All found records will be appended to the previous records by theUNION
clause. Additionally all foundname
values will be added to the array, I created in the root step to save the path. - Get the entire recursion set afterwards and create the appropriate string from the created path array.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论