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

Нумерация вложенных списков

Любое оглавление выглядит примерно так:

1. ...
   1.1. ...
   1.2. ...
   1.2. ...
2. ...
   2.1. ...
   2.2. ...
      2.2.1. ...
      2.2.2. ...
      2.2.3. ...
   2.3. ...

Научимся строить такое оглавление с помощью SQL запроса.

Взглянем внимательно на массив, по которому мы сортируем иерархию каталогов.

WITH RECURSIVE lv_hierarchy AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        1 AS level,
        '/' || c.name AS path,
        array[row_number () over (order by c.name)] AS path_sort
   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,
        p.path || '/' || c.name AS path,
        p.path_sort || row_number () over (partition by c.parent_category_id order by c.name) AS path_sort
   FROM lv_hierarchy p,
        category c
  WHERE c.parent_category_id = p.category_id
)
SELECT *
 FROM lv_hierarchy
ORDER BY path_sort
#category_idparent_category_idnamelevelpathpath_sort
11NULLТовары для дома1/Товары для дома["1"]
231Бытовая техника2/Товары для дома/Бытовая техника["1","1"]
32NULLЦифровая техника1/Цифровая техника["2"]
472Аудиотехника2/Цифровая техника/Аудиотехника["2","1"]
562Игровые консоли2/Цифровая техника/Игровые консоли["2","2"]
642Ноутбуки и аксессуары2/Цифровая техника/Ноутбуки и аксессуары["2","3"]
794Ноутбуки3/Цифровая техника/Ноутбуки и аксессуары/Ноутбуки["2","3","1"]
8104Рюкзаки3/Цифровая техника/Ноутбуки и аксессуары/Рюкзаки["2","3","2"]
982Сотовые телефоны2/Цифровая техника/Сотовые телефоны["2","4"]
1052Фотоаппараты2/Цифровая техника/Фотоаппараты["2","5"]

Числа в массивах именно те, которые нам нужны для нумерации.

Осталось собрать все в строку. Можно конкатенировать номер строки в рамках общего родителя на каждой итерации иерархического запроса. Но мы воспользуемся встроенной функцией array_to_string для path_sort:

array_to_string(массив, разделитель)
WITH RECURSIVE lv_hierarchy AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        1 AS level,
        '/' || c.name AS path,
        array[row_number () over (order by c.name)] AS path_sort
   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,
        p.path || '/' || c.name AS path,
        p.path_sort || row_number () over (partition by c.parent_category_id order by c.name) AS path_sort
   FROM lv_hierarchy p,
        category c
  WHERE c.parent_category_id = p.category_id
)
SELECT c.category_id,
       lpad('', (c.level - 1) * 4, '.') || array_to_string(c.path_sort, '.') || '. ' || c.name AS full_name
  FROM lv_hierarchy c
 ORDER BY path_sort
#category_idfull_name
111. Товары для дома
23....1.1. Бытовая техника
322. Цифровая техника
47....2.1. Аудиотехника
56....2.2. Игровые консоли
64....2.3. Ноутбуки и аксессуары
79........2.3.1. Ноутбуки
810........2.3.2. Рюкзаки
98....2.4. Сотовые телефоны
105....2.5. Фотоаппараты
ПредыдущаяСледующая