在树形结构中查找路径

huangapple go评论68阅读模式
英文:

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:

  1. Is there a better way to query the tree structure? I've read something about recursive CTEs, but have a hard time understanding it.
  2. 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:

  1. 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.
  2. 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
  1. 启动递归公共表表达式 (CTE)。递归CTE始终包含两个部分:起始查询和递归部分。
  2. 这两个部分由 UNION 子句分隔。
  3. 这是起始部分。在此部分中,将找到根记录。在您的情况下,根记录是没有父记录的记录。除了其列之外,我添加了一个数组来存储路径。路径的第一个元素是当前的 name
  4. 现在它的工作方式类似于您所做的。在第一次递归中,将搜索所有具有与先前找到的 id 值(在这种情况下,是根记录的值)相等的 parent_id 值的记录,通过执行 JOIN 操作。先前结果始终由 WITH 子句表示,因此连接表是 cte(在您的情况下,当前步骤的“表”是 taxons,先前结果的表是 cte)。接下来的递归将找到具有与上一次递归的 id 值相等的 parent_id 值的元素,依此类推。所有找到的记录都将附加到先前的记录中,通过 UNION 子句连接。另外,我添加了所有找到的 name 值到数组中,我在起始步骤中创建的用于保存路径的数组中。
  5. 之后获取整个递归集并从创建的路径数组中创建相应的字符串。
英文:

demo: db<>fiddle

WITH RECURSIVE cte AS (                       -- 1
    SELECT *, ARRAY[t.name -&gt;&gt; &#39;de&#39;] as path  -- 3
    FROM taxons t
    WHERE parent_id IS NULL

    UNION                                     -- 2

    SELECT                                    -- 4
        t.*,
        c.path || (t.name -&gt;&gt; &#39;de&#39;)
    FROM taxons t
    JOIN cte c ON c.id = t.parent_id
)
SELECT 
    array_to_string(c.path, &#39; -&gt; &#39;) as name   -- 5
FROM cte c
  1. Starting a recursive CTE. A recursive CTE always contains two parts. The starting query and the recursion part.
  2. Both parts are devided by the UNION clause.
  3. 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.
  4. Now it works similar to what you did. In the first recursion all records are searched which parent_id values equal the previously found id values (in that case the one of the root record) by doing a JOIN operation. The previous result is always represented by the WITH clause, so the joining table is cte (So, in your case the current step's table is taxons, the one for the previous results is cte). The next recursion will find the elements with the parent_id values equal to the id values of the previous recursion and so on. All found records will be appended to the previous records by the UNION clause. Additionally all found name values will be added to the array, I created in the root step to save the path.
  5. Get the entire recursion set afterwards and create the appropriate string from the created path array.

huangapple
  • 本文由 发表于 2023年7月6日 13:51:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/76625863.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定