Order by on recursive table

Question

I have a categories table, with some recursion. Some categories are sub categories and so have a value in a parent_category_id field. Top level categories have null in the parent_category_id field.

The requirement is to list categories so that they appear as:

Parent category 1
  Sub category 1
  Sub category 2

Parent category 2
  Subcategory 3
  Subcategory 4

Is this possible with a single query order statement, or do I need to make separate queries?

Some sample data as requested:

Please add some example data and the expected output. Also, tag the question with the RDBMS you're using - hierarchical queries like these are not always portable between different RDBMSs. — Frank Schmitt, May 04 '16 at 09:28
Recursive common table expression (ORACLE, MS SQL, Postgresql ) exactly matches the task. — Serg, May 04 '16 at 09:36
Is there always just one sub-category? Or might there be deeper levels? Please state the structure of your table and the RDBMS (vendor and version) — Shnugo, May 04 '16 at 09:45
"*Is this possible with a single query order statement*" - yes it is. Use a recursive common table expression: http://www.postgresql.org/docs/current/static/queries-with.html — , May 04 '16 at 09:57
Sorry I should have added that this is mysql - is the procedural code below the only option? — Positonic, May 04 '16 at 11:16
@iKode, Maybe I put the question wrong, at least it is not yet clear to me: Are there deeper nestings too? Could a sub-category have a sub-sub-category? How deep could this be? — Shnugo, May 04 '16 at 11:21
@Shnugo - at this time there are no further nestings. This may be the case in the future, how ever I can revisit it, if no further nestings provides an easy solution now — Positonic, May 04 '16 at 11:52

Josh Greifer · Accepted Answer · 2016-05-09T15:58:44.943

2

In MYSQL, which doesn't support recursive cte's:

select id, path(id) from mytable order by 2, where path(id) is defined by

DELIMITER $$
CREATE FUNCTION path(v_id INT(10)) RETURNS varchar(255)
begin
declare v_path varchar(255);
declare v_parent_id INT(10); 
declare MAX_ITERS int;
declare iters int;
set v_path = '';
set MAX_ITERS = 20;
set iters = 0;

if not exists (select * from node where id = v_id) then
    return 'no such node'; 
end if;

if not exists (select * from node where parent_id < 0) then
    return 'no root node in table';
end if;

select parent_id into v_parent_id from node where id = v_id;
while (v_parent_id >= 0) do
    set iters = iters + 1;
    if iters >= MAX_ITERS then
        return 'path too long'; 
    end if;
    select parent_id, concat(id, '.', v_path) into v_parent_id, v_path from node where id = v_id;
    set v_id = v_parent_id;
end while;
return trim(both '.' from v_path);

end$$


DELIMITER ;
;

Note, in my example, instead of a null parent node for the root, I'm using a node id of -1.

For performance, maintain a second table (using triggers) which stores the 'path' of each node, where path(node) is defined by the above UDF.

edited May 09 '16 at 15:58

answered May 04 '16 at 09:52

Josh Greifer

3,151
24
25

This is *procedural code*... SQL should be *set based code*... You are using the very badly performing multi-statement UDF and the very badly performing loop. This is better be done with a recursive CTE... – Shnugo May 04 '16 at 10:02
Above is for MYSQL http://stackoverflow.com/questions/8833535/how-to-transform-a-mssql-cte-query-to-mysql – Josh Greifer May 04 '16 at 10:13
Hi Josh, yes this is true, if there is no rec CTE you must somehow travers through the tree. You edited the MySQL hint... As we (still) do not know the actual RDBMS this is worth +1 :-) – Shnugo May 04 '16 at 10:21
@JoshGreifer thanks for this, how do I add this in mysql? I try to copy and paste it, and run it as sql and I get an error saying "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 " – Positonic May 09 '16 at 09:08
@iKode Ah yes, you need to put the thing in delimiters: Add the line DELIMITER $$ before the beginning and change the END to END$$ and then add the line DELIMITER ; at the end. That last line will restore the normal semicolon delimiter that normally terminates MySQL statements. I've updated the code accordingly – Josh Greifer May 09 '16 at 15:54

Shnugo · Answer 2 · 2016-05-04T11:05:16.267

This is SQL Server syntax:

DECLARE @category TABLE(category_id INT,parent_category_id INT,caption varchar(100));
INSERT INTO @category VALUES
 (1,NULL,'Top1')
,(2,1,'Sub11')
,(3,1,'Sub12')
,(4,2,'Sub111')
,(5,2,'Sub112')
,(6,NULL,'Top2')
,(7,6,'Sub21')
,(8,6,'Sub22')
,(9,8,'Sub221');

WITH rCTE AS
(
    SELECT c.category_id
          ,c.parent_category_id
          ,c.caption
          ,CAST(ROW_NUMBER() OVER(ORDER BY c.category_id) AS VARCHAR(MAX)) +  '.' AS [Level] 
          ,REPLACE(STR(ROW_NUMBER() OVER(ORDER BY c.category_id),4),' ','0') +  '.' AS [Sortable] 
    FROM @category AS c WHERE parent_category_id IS NULL

    UNION ALL

    SELECT c.category_id
          ,c.parent_category_id
          ,c.caption
          ,rCTE.[Level] + CAST(ROW_NUMBER() OVER(ORDER BY c.category_id) AS VARCHAR(MAX)) +  '.'
          ,rCTE.Sortable + REPLACE(STR(ROW_NUMBER() OVER(ORDER BY c.category_id),4),' ','0') +  '.'
    FROM rCTE 
    INNER JOIN @category AS c ON c.parent_category_id=rCTE.category_id 
)

SELECT * 
FROM rCTE
ORDER BY Sortable

The result

+-------------+--------------------+---------+--------+-----------------+
| category_id | parent_category_id | caption | Level  | Sortable        |
+-------------+--------------------+---------+--------+-----------------+
| 1           | NULL               | Top1    | 1.     | 0001.           |
+-------------+--------------------+---------+--------+-----------------+
| 2           | 1                  | Sub11   | 1.1.   | 0001.0001.      |
+-------------+--------------------+---------+--------+-----------------+
| 4           | 2                  | Sub111  | 1.1.1. | 0001.0001.0001. |
+-------------+--------------------+---------+--------+-----------------+
| 5           | 2                  | Sub112  | 1.1.2. | 0001.0001.0002. |
+-------------+--------------------+---------+--------+-----------------+
| 3           | 1                  | Sub12   | 1.2.   | 0001.0002.      |
+-------------+--------------------+---------+--------+-----------------+
| 6           | NULL               | Top2    | 2.     | 0002.           |
+-------------+--------------------+---------+--------+-----------------+
| 7           | 6                  | Sub21   | 2.1.   | 0002.0001.      |
+-------------+--------------------+---------+--------+-----------------+
| 8           | 6                  | Sub22   | 2.2.   | 0002.0002.      |
+-------------+--------------------+---------+--------+-----------------+
| 9           | 8                  | Sub221  | 2.2.1. | 0002.0002.0001. |
+-------------+--------------------+---------+--------+-----------------+

Sorry, there was an error with the hardcoded "1" in the parent part of the rCTE — Shnugo, May 04 '16 at 10:05
@LiyaTansky, yes, if OP needs this ordered.. But you would have to return the level in a sortable form with padded numbers (like `001.002.001`), otherwise a `1.12.3` was sorted *before* as `1.2.3`... — Shnugo, May 04 '16 at 10:19

Order by on recursive table

2 Answers2