SQL nested order?

Question

I am sure that this was asked earlier, but I do not know what to call it to find the answer.

I have a table of categories and subcategories. Each of them has an identifier and a parent identifier. If this is a top-level category, the parent identifier is 0. Subcategories have a parent identifier set in the category id of this parent.

category_id # The ID for this record category_name # The name of the category parent_id # The parent ID for this category display_order # Order of categories within their grouping 1 A 0 0 # First primary category 2 a1 1 0 # Subcategory, parent is A, display_order is 0 3 a2 1 1 4 a3 1 2 5 B 0 1 # Second primary category 6 b1 5 0 # Subcategory, parent is B, display_order is 0 7 b2 5 1 8 b3 5 2 

I am trying to write an SQL query that will give me all categories in the following order:

A, a1, a2, a3, B, b1, b2, b3

 SELECT * FROM categories ORDER BY display_order 

Is this possible in SQL, or will I need to use multiple queries

Thanks Brad

Answer 1

Perhaps something like this might work:

 SELECT * FROM categories ORDER BY IF(parent_id, parent_id, category_id), parent_id, display_order 

but since it cannot use the index, it will be slow. (Not tested, maybe wrong)

The first ORDER BY sorts parents and children together; then the second ensures that the parent element precedes its children; the third sorts children among themselves.

In addition, it will obviously only work if you explicitly described where you have a two-level hierarchy.

Answer 2

the answer has already been accepted, but I thought I would share my thoughts on this. I tried to sort the main categories after the display_order column. here is my table

 mysql> select * from categories; +-------------+---------------+-----------+---------------+ | category_id | category_name | parent_id | display_order | +-------------+---------------+-----------+---------------+ | 1 | B | 0 | 2 | | 2 | C | 0 | 3 | | 3 | b2 | 1 | 2 | | 4 | b1 | 1 | 1 | | 5 | c3 | 2 | 3 | | 6 | A | 0 | 1 | | 7 | c2 | 2 | 2 | | 8 | b3 | 1 | 3 | | 9 | a2 | 6 | 2 | | 10 | a1 | 6 | 1 | | 11 | c1 | 2 | 1 | | 12 | a3 | 6 | 3 | +-------------+---------------+-----------+---------------+ 12 rows in set (0.00 sec) 

As you can see, I made sure to add categories to one linear order :)

my request:

 SELECT sub_id AS category_id, sub_name AS category_name, sub_parent_id AS parent_id, main_order + sub_order AS display_order FROM ( SELECT c1.display_order + c1.display_order * ( SELECT inner_c.display_order FROM categories AS inner_c WHERE inner_c.parent_id <> 0 ORDER BY inner_c.display_order DESC LIMIT 1) AS main_order, c2.display_order AS sub_order, c2.category_name AS sub_name, c2.category_id AS sub_id, c2.parent_id AS sub_parent_id FROM categories AS c1 JOIN categories AS c2 ON c1.category_id = c2.parent_id WHERE c1.parent_id = 0 ) AS renumbered UNION ALL SELECT category_id, category_name, parent_id, display_order + display_order * ( SELECT inner_c.display_order FROM categories AS inner_c WHERE inner_c.parent_id <> 0 ORDER BY inner_c.display_order DESC LIMIT 1) AS display_order FROM categories WHERE parent_id = 0 ORDER BY display_order; 

Answer 3

It sounds almost identical to the other I answered with a similar hierarchy of parent / child elements, keeping the children at the same group level as the corresponding parent ... Mark this topic

Answer 4

Whenever possible, I create SQL in stages, not least because it gives me the opportunity to test when I go.

The first thing we need to do is define the top-level categories:

  SELECT category_id AS tl_cat_id, category_name AS tl_cat_name, display_order AS tl_disp_order FROM Categories WHERE parent_id = 0; 

Now we need to join this with categories and subcategories to get the result:

 SELECT t.tl_cat_id, t.cat_name, t.tl_disp_order, c.category_id, c.category_name, CASE WHEN c.parent_id = 0 THEN 0 ELSE c.display_order END AS disp_order FROM Categories AS c JOIN (SELECT category_id AS tl_cat_id, category_name AS tl_cat_name, display_order AS tl_disp_order FROM Categories WHERE parent_id = 0) AS t ON c.tl_cat_id = t.parent_id OR (c.parent_id = 0 AND t.tl_cat_id = c.category_id) ORDER BY tl_disp_order, disp_order; 

The join condition is unusual, but should work; it collects rows where the parent identifier matches the current category identifier or rows where the parent identifier is 0, but the category identifier is the same. Then the ordering is almost trivial - except that when you are dealing with a subcategory, you want the parent to be at the top of the list. The CASE expression processes this mapping.