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

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

Описанный в предыдущем задании способ сортировки не является надежным. Сортировка сбивается, когда на одном уровне есть два родителя, название одного из которых включает название другого. В случае ФИО это могут быть полные тезки.

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

WITH RECURSIVE lv_category AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name
   FROM category c
  UNION ALL
 SELECT 11 AS category_id,
        NULL AS parent_category_id,
        'Товары для дома и бани' AS name
),
lv_hierarchy AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name,
        1 AS level,
        '/' || c.name AS path
   FROM lv_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
   FROM lv_hierarchy p,
        lv_category c
  WHERE c.parent_category_id = p.category_id
)
SELECT *
 FROM lv_hierarchy
ORDER BY path
#category_idparent_category_idnamelevelpath
11NULLТовары для дома1/Товары для дома
211NULLТовары для дома и бани1/Товары для дома и бани
331Бытовая техника2/Товары для дома/Бытовая техника
42NULLЦифровая техника1/Цифровая техника
572Аудиотехника2/Цифровая техника/Аудиотехника
662Игровые консоли2/Цифровая техника/Игровые консоли
742Ноутбуки и аксессуары2/Цифровая техника/Ноутбуки и аксессуары
894Ноутбуки3/Цифровая техника/Ноутбуки и аксессуары/Ноутбуки
9104Рюкзаки3/Цифровая техника/Ноутбуки и аксессуары/Рюкзаки
1082Сотовые телефоны2/Цифровая техника/Сотовые телефоны
1152Фотоаппараты2/Цифровая техника/Фотоаппараты

Обрати внимание на первые три строки результата

#category_idparent_category_idnamelevelpath
11NULLТовары для дома1/Товары для дома
211NULLТовары для дома и бани1/Товары для дома и бани
331Бытовая техника2/Товары для дома/Бытовая техника

Строки 2 и 3 поменялись местами.

Решение 1 (костыльное)

Чтобы избежать включения названия одной категории в другую, можно при формировании пути для сортировки в конец названия для каждой категории добавить какую-нибудь уникальную подстроку. Например, идентификатор записи.

Добавим в наше решение еще один путь path_sort с добавлением идентификатора через пробел и отсортируем результат по нему:

WITH RECURSIVE lv_category AS (
  SELECT c.category_id,
         c.parent_category_id,
         c.name
    FROM category c
   UNION ALL
  SELECT 11 AS category_id,
         NULL AS parent_category_id,
         'Товары для дома и бани' AS name
),
lv_hierarchy AS (
  SELECT c.category_id,
         c.parent_category_id,
         c.name,
         1 AS level,
         '/' || c.name AS path,
         '/' || c.name || ' ' || c.category_id AS path_sort
    FROM lv_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 || '/' || c.name || ' ' || c.category_id AS path_sort
    FROM lv_hierarchy p,
         lv_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/Бытовая техника 3
311NULLТовары для дома и бани1/Товары для дома и бани/Товары для дома и бани 11
42NULLЦифровая техника1/Цифровая техника/Цифровая техника 2
572Аудиотехника2/Цифровая техника/Аудиотехника/Цифровая техника 2/Аудиотехника 7
662Игровые консоли2/Цифровая техника/Игровые консоли/Цифровая техника 2/Игровые консоли 6
742Ноутбуки и аксессуары2/Цифровая техника/Ноутбуки и аксессуары/Цифровая техника 2/Ноутбуки и аксессуары 4
894Ноутбуки3/Цифровая техника/Ноутбуки и аксессуары/Ноутбуки/Цифровая техника 2/Ноутбуки и аксессуары 4/Ноутбуки 9
9104Рюкзаки3/Цифровая техника/Ноутбуки и аксессуары/Рюкзаки/Цифровая техника 2/Ноутбуки и аксессуары 4/Рюкзаки 10
1082Сотовые телефоны2/Цифровая техника/Сотовые телефоны/Цифровая техника 2/Сотовые телефоны 8
1152Фотоаппараты2/Цифровая техника/Фотоаппараты/Цифровая техника 2/Фотоаппараты 5

Строки встали на свои места, но по-прежнему можно подобрать данные, на которых оно сломается. Мы хотим иметь решение, которое будет работать в 100% случаев.

Надежное решение

Чтобы сделать надежное решение, нужно отказаться от сортировки по одному текстовому полю.

PostgreSQL позволяет в ORDER BY использовать массивы.

Например, если отсортировать массивы

  • [2, 1, 2]
  • [2, 1, 1]
  • [2, 1]
  • [2]
  • [1]
  • [1, 1]
  • [1, 2]

по возрастанию, то строки встанут в следующем порядке

  • [1]
  • [1, 1]
  • [1, 2]
  • [2]
  • [2, 1]
  • [2, 1, 1]
  • [2, 1, 2]

Воспользуемся этим для сортировки нашей иерархии каталогов товаров.

Осталось научиться 3 вещам:

  1. Определять номер строки для дочерних строк одного родителя на очередной итерации.
  2. Создавать массив из одного элемента.
  3. Добавлять элемент в массив на каждой итерации.

Определение номера строки для дочерних строк одного родителя

Для определение номера строки воспользуемся аналитической функцией row_number(). Аналитические функции работают как агрегатные функции, только не изменяют итоговое количество строк, а вычисляют результат для каждой строки.

WITH RECURSIVE lv_category AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name
   FROM category c
  UNION ALL
 SELECT 11 AS category_id,
        NULL AS parent_category_id,
        'Товары для дома и бани' AS name
)
SELECT c.parent_category_id,
       c.name,
       row_number () OVER (PARTITION BY c.parent_category_id ORDER BY c.name) AS sorting
  FROM lv_category c
 ORDER BY c.parent_category_id NULLS FIRST,
          c.name
#parent_category_idnamesorting
1NULLТовары для дома1
2NULLТовары для дома и бани2
3NULLЦифровая техника3
41Бытовая техника1
52Аудиотехника1
62Игровые консоли2
72Ноутбуки и аксессуары3
82Сотовые телефоны4
92Фотоаппараты5
104Ноутбуки1
114Рюкзаки2

Подробнее аналитические функции разберем в отдельной теме. Сейчас же кратко посмотрим на получение столбца sorting:

row_number () OVER (PARTITION BY c.parent_category_id ORDER BY c.name) AS sorting

Общий синтаксис вызова аналитических функций:

функция(...) OVER (условия для группировки строк и сортировки)

У нас:

row_number() - возвращает номер строки в группе, задаваемой PARTITION BY, с учетом сортировки из ORDER BY
PARTITION BY - по каким столбцам формировать группы строк. Работает так же, как и GROUP BY.
ORDER BY - по каким столбцам сортировать результат в рамках группы. Работает так же, как и ORDER BY в основном запросе.

Результат аналитической функции вычисляется для каждой строки.

Создание массива чисел

Для создание массива воспользуемся функцией array

SELECT array[1] AS one,
       array[1, 2] AS two,
       array[1, 2, 3] AS three
#onetwothree
1[1][1, 2][1, 2, 3]

Добавление элемента в массив

Для добавления элемента в конец массива достаточно новый элемент написать через ||

SELECT array[1] || 2 AS two,
       array[1, 2] || 3 AS three
#twothree
1[1, 2][1, 2, 3]

Итоговое решение

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

WITH RECURSIVE lv_category AS (
 SELECT c.category_id,
        c.parent_category_id,
        c.name
   FROM category c
  UNION ALL
 SELECT 11 AS category_id,
        NULL AS parent_category_id,
        'Товары для дома и бани' AS name
),
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 lv_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,
        lv_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]
311NULLТовары для дома и бани1/Товары для дома и бани[2]
42NULLЦифровая техника1/Цифровая техника[3]
572Аудиотехника2/Цифровая техника/Аудиотехника[3, 1]
662Игровые консоли2/Цифровая техника/Игровые консоли[3, 2]
742Ноутбуки и аксессуары2/Цифровая техника/Ноутбуки и аксессуары[3, 3]
894Ноутбуки3/Цифровая техника/Ноутбуки и аксессуары/Ноутбуки[3, 3, 1]
9104Рюкзаки3/Цифровая техника/Ноутбуки и аксессуары/Рюкзаки[3, 3, 2]
1082Сотовые телефоны2/Цифровая техника/Сотовые телефоны[3, 4]
1152Фотоаппараты2/Цифровая техника/Фотоаппараты[3, 5]

P.S. Если вы используется СУБД Oracle, то проблема с сортировкой в иерархических запросах решается очень легко. В конструкции CONNECT BY отсортировать дочерние строки одного родителя можно с помощью ORDER SIBLINGS BY. ORDER SIBLINGS BY работает точно так же, как и ORDER BY, только оперирует строками одного родителя.

ПредыдущаяСледующая