Сортировщик алгоритмов SQL

Question

Это мои данные таблицы. Если cat_parent_id равен 0, это означает, что его родитель, например "Люди и культура", является родителем с cat_id = 1, "Преимущества и сертификация сотрудников" являются потомками людей и культуры. Тем не менее, пособия и сертификации работника также есть ребенок. Вознаграждения работникам cat_id = 6, поэтому его ребенок - SSS Loan Inquiry, в то время как Сертификация имеет cat_id = 10, Сертификат занятости и Сертификат о взносах SSS будут его ребенком.

Ожидаемый результат:

Admin and Facilities
  • Safety and Security Related Concerns
     • CCTV Footage

Information Technology
  • User Account
     • Enable / Disable Access

People and Culture
     • Certification
        • Certificate of Employment
        • SSS Certificate of Employment
     • Employee Benefits Request
        • SSS Loan Inquiry

У меня как то так на данный момент не повезло.

SELECT category.cat_id AS catId, category.cat_parent_id AS catParentId, 
subcategory.cat_id AS subcatId,subcategory.cat_parent_id AS subcatParentId,
category.cat_name,
CONCAT( IFNULL(subcategory.cat_parent_id, category.cat_parent_id), 
category.cat_parent_id, category.cat_id, category.cat_name) AS sorter
FROM ticket_categories AS category
LEFT JOIN ticket_categories AS subcategory ON subcategory.cat_parent_id = 
category.cat_id
GROUP BY category.cat_id
ORDER BY sorter

Основная цель состоит в сортировке данных в алфавитном порядке по родителям (первый приоритет), категории (второй приоритет), подкатегории (третий приоритет). Я играю со своим сортировщиком псевдонимов, но я не могу заставить его работать.

Answer 1

В версиях до MySQL 8 рекурсивные запросы сложны. В вашем случае кажется, что у вас есть только 3 уровня (0, 1 и 2), поэтому лучше просто самостоятельно подключаться к вашей таблице много раз, чтобы получить пути от корня к каждому узлу.

Наконец, сортируем конкатенацию имен в "пути" от корня до потомка:

select a.cat_id, a.cat_level, a.cat_name, 
    concat(
        ifnull(concat(c.cat_name, '  '), ''),
        ifnull(concat(b.cat_name, '  '), ''),
        a.cat_name) as cat_order
from ticket_categories a
left join ticket_categories b on b.cat_id = a.cat_parent_id 
left join ticket_categories c on c.cat_id = b.cat_parent_id 
order by cat_order;

Вы можете легко расширить этот запрос для поддержки большего количества уровней; просто добавь так много left join линии и псевдонимы таблиц и расширить concat выражение соответственно.

В MySQL 8 вы можете использовать рекурсивный запрос, который может работать с любым количеством уровней:

with recursive cte(cat_id, cat_level, cat_name, cat_order) as (
  select cat_id, cat_level, cat_name, cat_name
  from ticket_categories
  where cat_parent_id = 0
  union
  select t.cat_id, t.cat_level, t.cat_name, concat(cte.cat_order, '  ', t.cat_name)  
  from cte
  inner join ticket_categories t on t.cat_parent_id = cte.cat_id
)
select * from cte
order by cat_order;

Вывод для обоих запросов:

cat_id | cat_level | cat_name                             | cat_order
-------+-----------+--------------------------------------+-------------------------------------------------------------------------------
   3   |     0     | Admin and Facilities                 | Admin and Facilities
   4   |     1     | Safety and Security Related Concerns | Admin and Facilities  Safety and Security Related Concerns
   9   |     2     | CCTV Footage Request                 | Admin and Facilities  Safety and Security Related Concerns  CCTV Footage Request
   2   |     0     | Information Technology               | Information Technology
   5   |     1     | User Account                         | Information Technology  User Account
   8   |     2     | Enable / Disable Access              | Information Technology  User Account  Enable / Disable Access
   1   |     0     | People and Culture                   | People and Culture
  10   |     1     | Certification                        | People and Culture  Certification
  11   |     2     | Certificate of Employment            | People and Culture  Certification  Certificate of Employment
  12   |     2     | SSS Certificate of Contributions     | People and Culture  Certification  SSS Certificate of Contributions

Двойной пробел используется в качестве разделителя в пути (cat_order). Это предполагает, что у вас не будет двойных пробелов в ваших именах. Если бы вы имели, порядок мог оказаться неправильным, если у вас также есть имена, где один является префиксом другого.

Для окончательного форматирования отступа вы должны использовать cat_level колонка. Но, на мой взгляд, такая задача не относится к SQL, хотя это легко сделать с

concat(repeat('  ', cat_level), cat_name)

Скрипка БД для обоих запросов.