How to select all subcategories?

Question

PostgreSQL

There is a catalog with categories, with unlimited nesting. Everything is stored in postgres, a regular table with id and parent_id fields from itself.
How to select all subcategories for the specified id?

Answer 1

Use a recursive 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;

This query selects all child categories of the category with ID = 8.

Answer 2

If you need to quickly and in one request, then each element should contain an array (+ index) of all its parents.

Answer 3

How to display category tree from database (Java / jsp)?

Answer 4

I also found an article)) https://habrahabr.ru/post/269497/