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

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

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

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

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

Строки 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

Строки встали на свои места, но по-прежнему можно подобрать данные, на которых оно сломается. Мы хотим иметь решение, которое будет работать в 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

Подробнее аналитические функции разберем в отдельной теме. Сейчас же кратко посмотрим на получение столбца 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

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

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

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

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

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

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

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