PostgreSQL получает родительские категории из таблицы

У меня есть таблица, как показано ниже.

CREATE TABLE my.categories (id bigint, parent_id bigint, name varchar(128));

INSERT INTO my.categories (id, parent_id, name) VALUES (1, null, 'LEVEL 1');
INSERT INTO my.categories (id, parent_id, name) VALUES (2, 1, 'LEVEL 2.1');
INSERT INTO my.categories (id, parent_id, name) VALUES (3, 1, 'LEVEL 2.2');
INSERT INTO my.categories (id, parent_id, name) VALUES (4, 2, 'LEVEL 3.1.1');
INSERT INTO my.categories (id, parent_id, name) VALUES (5, 2, 'LEVEL 3.1.2');
INSERT INTO my.categories (id, parent_id, name) VALUES (6, 3, 'LEVEL 3.2.1');

+----+-----------+---------------+
| id | parent_id | name          |
+----+-----------+---------------+
|  1 |      null |     'LEVEL 1' |
|  2 |         1 |   'LEVEL 2.1' |
|  3 |         1 |   'LEVEL 2.2' |
|  4 |         2 | 'LEVEL 3.1.1' |
|  5 |         2 | 'LEVEL 3.1.2' |
|  6 |         3 | 'LEVEL 3.2.1' |
+----+-----------+---------------+

Мне нужно получить все идентификаторы для родительских категорий.

WITH RECURSIVE tree(theId) AS (
  SELECT id
  FROM my.categories
  WHERE id = theId -- wrong here, because its not a procedure
  UNION ALL
  SELECT table1.id
  FROM my.categories AS table1
    JOIN tree AS parent ON theId = table1.parent_id
)
SELECT DISTINCT theId FROM tree WHERE theId = 6;

Пример результата с данными, но на самом деле мне нужны только идентификаторы.

+----+-----------+---------------+
| id | parent_id | name          |
+----+-----------+---------------+
|  1 |      null |     'LEVEL 1' |
|  3 |         1 |   'LEVEL 2.2' |
|  6 |         3 | 'LEVEL 3.2.1' |
+----+-----------+---------------+

Или вот так:

+----+-----------+---------------+
| id | parent_id | name          |
+----+-----------+---------------+
|  3 |         1 |   'LEVEL 2.2' |
|  6 |         3 | 'LEVEL 3.2.1' |
+----+-----------+---------------+

Проблема в том, что мне не разрешено использовать процедуры. Этот запрос следует использовать в качестве подзапроса для многих других запросов. И, пожалуйста, не смотрите на столбец name, он не имеет значения.


postgresql recursive-query
person retro    schedule 11.04.2018    source источник
comment
Вы можете создать представление, использующее where parent_id is null, а затем запросить это представление с нужным идентификатором. НО это будет очень медленным, потому что Postgres не оптимизирует должным образом CTE, и условие не будет помещено в CTE, а будет применено к полному результату.   -  person a_horse_with_no_name    schedule 11.04.2018
comment
ваша кодировка УРОВНЯ довольно странная, ИМХО: первое число на самом деле является длиной пути. (поэтому: это избыточно) Остальные числа - это ранги среди братьев и сестер. (которые зависят от братьев)   -  person wildplasser    schedule 11.04.2018
comment
I need to get all id's for parent categories Для всех или только для определенной записи?   -  person wildplasser    schedule 11.04.2018

Ответы (1)


arrow_upward
1
arrow_downward

Если я тебя поймаю, это то, что тебе нужно.

Во-первых, с помощью следующего запроса вы можете получить все родительские идентификаторы:

WITH RECURSIVE t(id, parentlist) AS (
  SELECT id, ARRAY[]::bigint[] FROM my.categories WHERE parent_id IS NULL
  UNION
  SELECT my.categories.id, my.categories.parent_id || t.parentlist
    FROM my.categories 
    JOIN t ON categories.parent_id = t.id
) SELECT * FROM t
-- outputs:
-- id  | parentlist
-- ----+------------
-- 1   | {}
-- 2   | {1}
-- 3   | {1}
-- 4   | {2,1}
-- 5   | {2,1}
-- 6   | {3,1}

Если вы хотите получить запись о родителях одного идентификатора, вам просто нужно изменить запрос, например:

WITH RECURSIVE t(id, parentlist) AS (
  SELECT id, ARRAY[]::bigint[] FROM my.categories WHERE parent_id IS NULL
  UNION
  SELECT my.categories.id, my.categories.parent_id || t.parentlist
    FROM my.categories 
    JOIN t ON categories.parent_id = t.id
) SELECT unnest(parentlist) as parents_ids FROM t WHERE id=6;
-- outputs:
-- parents_ids
-- -----------
-- 3
-- 1

Обратите внимание, что последний запрос не выводит «текущий» идентификатор (6).

person Dan    schedule 11.04.2018