Строим иерархию объектов

Иерархическая структура данных

Посмотрим внимательно на таблицу категорий товаров

select *
  from category

#	category_id	parent_category_id	name
1	1	NULL	Товары для дома
2	2	NULL	Цифровая техника
3	3	1	Бытовая техника
4	4	2	Ноутбуки и аксессуары
5	5	2	Фотоаппараты
6	6	2	Игровые консоли
7	7	2	Аудиотехника
8	8	2	Сотовые телефоны
9	9	4	Ноутбуки
10	10	4	Рюкзаки

Каждая строка хранит ссылку на родительский каталог parent_category_id. Если родительского каталога нет (каталог является корневым), то parent_category_id принимает значение NULL. Это типовая структура для хранения иерархических данных в реляционных базах данных.

Таким образом каталоги хранят в себе подкаталоги:

Товары для дома
      Бытовая техника
Цифровая техника
      Ноутбуки и аксессуары
            Ноутбуки
            Рюкзаки
      Фотоаппараты
      Игровые консоли
      Аудиотехника
      Сотовые телефоны

Давай научимся выводить иерархические данные с помощью SQL запросов.

Для каждого каталога выведем его уровень вложенности.

Для этого получим в нерекурсивной части SQL запроса каталоги, у которых нет родителя. Будем их считать каталогами 1-го уровня:

WITH RECURSIVE lv_hierarchy AS (
  SELECT c.category_id,
         c.parent_category_id,
         c.name,
         1 AS level
    FROM category c
   WHERE c.parent_category_id IS NULL
)
SELECT *
  FROM lv_hierarchy

#	category_id	parent_category_id	name	level
1	1	NULL	Товары для дома	1
2	2	NULL	Цифровая техника	1

Теперь с помощью рекурсивной части подзапроса будем добавлять вложенные каталоги.

WITH RECURSIVE lv_hierarchy AS (
  SELECT c.category_id,
         c.parent_category_id,
         c.name,
         1 AS level
    FROM category c
   WHERE c.parent_category_id IS NULL

   UNION ALL

  SELECT c.category_id,
         c.parent_category_id,
         c.name,
         p.level + 1 AS level
    FROM lv_hierarchy p,
         category c
   WHERE c.parent_category_id = p.category_id
)
SELECT *
  FROM lv_hierarchy

#	category_id	parent_category_id	name	level
1	1	NULL	Товары для дома	1
2	2	NULL	Цифровая техника	1
3	3	1	Бытовая техника	2
4	4	2	Ноутбуки и аксессуары	2
5	5	2	Фотоаппараты	2
6	6	2	Игровые консоли	2
7	7	2	Аудиотехника	2
8	8	2	Сотовые телефоны	2
9	9	4	Ноутбуки	3
10	10	4	Рюкзаки	3

Разберем рекурсивную часть подзапроса

  SELECT c.category_id,
         c.parent_category_id,
         c.name,
         p.level + 1 AS level
    FROM lv_hierarchy p,
         category c
   WHERE c.parent_category_id = p.category_id

В lv_hierarchy p (временная таблица, см. предыдущее задание) лежат строки, полученные в результате:

Нерекурсивной части подзапроса для первой итерации;
Предыдущей итерации рекурсивной части.

Это каталоги, для которых ищем дочерние на текущей итерации. Псевдоним p дан таблице по первой букве от слова parent.

К родительским каталогам присоединяем таблицу category c по условию c.parent_category_id = p.category_id: для каждого родителя находим дочерние каталоги, в которых идентификатор родительского каталога parent_category_id равен идентификатору записи текущего родителя.

Итоги

Мы получили уровень вложенности каждого каталога. Но в данном решении есть несколько проблем:

Строки отсортированы в порядке их получения. Хотелось бы видеть дочерние строки одного родителя рядом.
Уровень вложенности не виден визуально.
Не показан путь (хлебные крошки до каталога).