Как присоединить дерево Postgres к таблице меток?

Каков рекомендуемый способ использования общего дерева Postgres для хранения категорий продуктов?

Например, мой столбец может содержать путь ltree, такой как "1.2.3", где 1, 2 и 3 — внешние ключи в таблицу меток категорий, которые могут отображаться пользователю:

categories

id | name
---+-----------
 1 | Hardware
---+-----------
 2 | Computers
---+-----------
 3 | Video Cards
---+-----------

Теперь для данного продукта я хотел бы выбрать его категорию и материализовать его как "Hardware > Computers > Video Cards".


postgresql ltree
person Jake    schedule 06.08.2015    source источник

Ответы (1)


arrow_upward
7
arrow_downward

В PG 9.4+:

SELECT p.id, string_agg(c.name, ' > ' ORDER BY t.ord) AS label
FROM product p
JOIN regexp_split_to_table(p.category::text, '[.]') WITH ORDINALITY t(category, ord) ON true
JOIN categories c ON c.id = t.category::int
GROUP BY p.id;

Эта строка:

regexp_split_to_table(p.category::text, '[.]') WITH ORDINALITY t(category, ord)

берет столбец ltree, а затем разбивает его на строки, по одной для каждого элемента в ltree. Предложение WITH ORDINALITY добавит к выходным данным номер строки, здесь с псевдонимом ord. Этот номер строки используется в функции string_agg() для сохранения меток категорий в правильном порядке.

Если вы используете более старую версию PG (9.0+), то (вам следует обновить или что-то другое) вам следует сделать следующее:

SELECT p.id, string_agg(c.name, ' > ' ORDER BY t.ord) AS label
FROM product p
JOIN generate_series(1, nlevel(p.category)) t(ord) ON true
JOIN categories c ON c.id = subltree(p.category, t.ord - 1, t.ord)::text::int
GROUP BY p.id;

Это менее эффективно, потому что ltree нужно анализировать для каждого отдельного элемента, содержащегося в нем (subltree(...)).

person Patrick    schedule 07.08.2015