Как выбрать все нижестоящие категории?

Question

[McMike]

Есть каталог с категориями, с неограниченной вложенностью. Хранится все в postgres, обычная таблица с полями id и parent_id из самой себя.
Как выбрать для указанного id все подкателгории?

Answer 1

[Сергей Горностаев]

Используйте рекурсивный CTE:

with recursive r as (
    select
      id, parent_id, name
    from catalog_category
    where id = 8
  union
    select
      cc.id, cc.parent_id, cc.name
    from catalog_category as cc
    join r
      on cc.parent_id = r.id
)
select id, name from r where id <> 8;

Этот запрос выбирает все категории дочерние по отношению к категории с ID = 8.

Comments

[longclaps]

Последнее where можно убрать, изменив первое условие на where parent_id = 8:

with recursive r as (
    select
      id, parent_id, name
    from catalog_category
    where parent_id = 8
  union
    select
      cc.id, cc.parent_id, cc.name
    from catalog_category as cc
    join r
      on cc.parent_id = r.id
)
select id, name from r;

Answer 2

[lega]

Если нужно быстро и одним запросом, то каждый элемент должен содержать массив (+индекс) всех его родителей.

Comments

[McMike]

Я думал в сторону всяких postgres-примочек, типа ltree или рекурсивного запроса, но не знаю как реализовать и возможно ли

[Пасечник Кузьмич]

McMike: ltree примерно так и делает, в таблицу нужно будет добавить поле, которое хранить путь к корню. Правда, не в виде массива идентификаторов родителей, а в более эффективной форме.

Answer 3

[freeExec]

Как вывести из базы данных дерево категорий (Java / jsp )?

Answer 4

[McMike]

Вот еще нашел статью )) https://habrahabr.ru/post/269497/