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, это не имеет значения.

Вы можете создать представление, использующее where parent_id is null, а затем запросить это представление с нужным идентификатором. НО это будет очень медленным, потому что Postgres неправильно оптимизирует CTE, и условие не будет помещено в CTE, но применено к полному результату.

a_horse_with_no_name 11.04.2018 11:59

ваша кодировка LEVEL довольно странная, IMHO: первое число - это фактически длина пути. (так: это избыточно) Другие числа - это разряды среди братьев и сестер. (который зависеть на братья и сестры)

wildplasser 11.04.2018 12:24
I need to get all id's for parent categories Для всех или только для конкретной записи?
wildplasser 11.04.2018 14:27
0
3
560
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 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 * 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).

Другие вопросы по теме