Форматирование иерархии

Мы привыкли видеть иерархичные данные в виде списков, в которых вложенность и определение родительского элемента определяется отступом слева. Посмотри, например, на списки в любимом текстовом редакторе. Или на вложенные блоки кода.

Давай отформатируем каталог товаров. Все данные для этого у нас есть. Уровень вложенности знаем, сортировать строки в нужном порядке умеем...

Нам осталось сделать мелочь - добавить пробелов слева от названия.

Добавим точек, для наглядности. Алгоритм следующий:

  • Если каталог родительский (level = 1), то ничего добавлять не нужно.
  • Для вложенных каталогов будем добавлять по четыре точки на каждый уровень вложенности.

Воспользуемся функцией [lpad] (https://learndb.ru/articles/article/70). Будем добивать пустую строку до нужной длины.

Длина строки равна (level - 1) * 4.

SELECT lpad('', 4 * (1-1), '.') AS level_1,
       lpad('', 4 * (2-1), '.') AS level_2,
       lpad('', 4 * (3-1), '.') AS level_3,
       lpad('', 4 * (4-1), '.') AS level_4
# level_1 level_2 level_3 level_4
1 .... ........ ............

К нужному количеству точек осталось добавить название каталога справа.

Собрав все вместе, получаем:

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) * 8) || c.name AS name,
       c.level
  FROM lv_hierarchy c
 ORDER BY path_sort
# category_id name level
1 1 Товары для дома 1
2 3 ....Бытовая техника 2
3 2 Цифровая техника 1
4 7 ....Аудиотехника 2
5 6 ....Игровые консоли 2
6 4 ....Ноутбуки и аксессуары 2
7 9 ........Ноутбуки 3
8 10 ........Рюкзаки 3
9 8 ....Сотовые телефоны 2
10 5 ....Фотоаппараты 2

10.9 Сортировка (надежная)

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