SQL Algorithm Sorter

Question

This is my table data. If cat_parent_id is 0 that means its a parent, for example People and Culture is a parent with cat_id = 1, Employee Benefits and Certification is the child of People and Culture. However, Employee Benefits and Certification has also have child. Employee Benefits cat_id = 6, so his child is SSS Loan Inquiry, while Certification has a cat_id = 10, Certificate of Employment and SSS Certificate Of Contributions will be his child.

Expected output:

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

I have something like this at the moment no luck.

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

Main Goal is to sort the data alphabetically per parent (first priority), category (second priority), sub category (third priority). I'm playing with my alias sorter but I can't make it work.

Can you change your modelization or it cannot be changed in your database ? What server technology or what ORM are you using ? — Paul, Jul 04 '19 at 05:38
The problem is I can't change the database structure. It's built that way. I'm using Eloquent. We're developing using Laravel. — Benjoe, Jul 04 '19 at 05:56
This is a recursive problem. If you are using MySQL 8+, it is possible to do it with a recursive cte. If not, it will be a bit annoying to do if you want to include an ordering (see [here](https://stackoverflow.com/q/20215744) for unordered solutions, maybe you can adapt one of those to fit your problem), and you should probably just retrieve the table and do the sorting in your app (using laravel), or precalculate and store it every time you change a category (which usually happens only on occasion). In any case: the important information missing is if you use MyQSL 8+ or not. — Solarflare, Jul 04 '19 at 08:53
I removed the inconsistent database tags. Only tag with the database you are really using. — Gordon Linoff, Jul 04 '19 at 12:42
Thanks @Solarflare. I will try the workaround I've saw in the link you posted. Will update you guys what will happen! — Benjoe, Jul 05 '19 at 04:41
So are you on mysql8 or which version? It makes a huge difference for answering this question. The `sql` tag is not precise enough in this case. — trincot, Jul 05 '19 at 07:47
I think it must be like a company secret or something... Please tag your question with the DBMS and if it is `mysql`, then also the version tag, like `mysql-5.7` (or whichever version you have). — trincot, Jul 05 '19 at 10:08

trincot · Accepted Answer · 2019-07-05T10:44:38.013

In versions before MySQL 8, recursive queries are tricky. In your case you seem to only have 3 levels (0, 1 and 2), so then it is better to just self-outer-join your table that many times for getting the paths from the root to each node.

Finally sort on the concatenation of the names in the "path" from the root to the child:

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;

You can easily extend this query to support more levels; just add that many left join lines and table aliases and extend the concat expression accordingly.

In MySQL 8 you could use a recursive query which can deal with any number of levels:

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;

Output for both queries:

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

A double space is used as separator in the path (cat_order). This assumes you will not have double spaces in your names. If you would have, the order could turn out wrong if also you have names where one is the prefix of the other.

For the final indentation formatting you would use the cat_level column. But in my opinion such task does not belong to SQL, although it is easy to do with

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

A db fiddle for both queries.

I used the first query and it's working as expected. What I'm not clear is "the order could turn out wrong if also you have names where one is the prefix of the other." can you give example for this? Anyway thank you for the query I've been looking for answers almost a week :) — Benjoe, Jul 08 '19 at 00:07
Example: if you would have a name in your data that is "Information Technology abc" (with 2 spaces before abc), then the order would have that listed immediately after "Information Technology", while really the latter's children should be listed in between those two. But this is just a theoretical issue. Why would you have names like that :) — trincot, Jul 08 '19 at 07:14
Could you please do all of us a favour and *tag* your question with the database engine (`mysql`) and version (`mysql-5.7` ?)? — trincot, Jul 08 '19 at 07:15
OK, I have now added the version tag myself. The version is important, as with version 10.2 you would better use the recursive common table expression syntax (i.e. like for MySQL 8). — trincot, Jul 08 '19 at 07:44
I will take note of that so then we can update the code if we're ready to update the Laravel Version. Thanks @trincot. — Benjoe, Jul 08 '19 at 07:46

SQL Algorithm Sorter

1 Answers1