CODEWITHSUNDEEP
mysqlsqlhierarchical-datarecursive-query
Positonic
Positonic

Reputation: 9411

Order by on recursive table

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: enter image description here

Upvotes: 1

Views: 224

Answers (2)

Josh Greifer
Josh Greifer

Reputation: 3221

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.

Upvotes: 2

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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. |
+-------------+--------------------+---------+--------+-----------------+

Upvotes: 2

Related Questions