Рекурсивные подзапросы

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

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

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

select *
  from category
#category_idparent_category_idname
11NULLТовары для дома
22NULLЦифровая техника
331Бытовая техника
442Ноутбуки и аксессуары
552Фотоаппараты
662Игровые консоли
772Аудиотехника
882Сотовые телефоны
994Ноутбуки
10104Рюкзаки

Каждая строка хранит ссылку на родительский каталог 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_idparent_category_idnamelevel
11NULLТовары для дома1
22NULLЦифровая техника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_idparent_category_idnamelevel
11NULLТовары для дома1
22NULLЦифровая техника1
331Бытовая техника2
442Ноутбуки и аксессуары2
552Фотоаппараты2
662Игровые консоли2
772Аудиотехника2
882Сотовые телефоны2
994Ноутбуки3
10104Рюкзаки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 (временная таблица, см. предыдущее задание) лежат строки, полученные в результате:

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

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

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

Итоги

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

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