In those latest days I was wondering how to sort a table in mySQL that has an id a parent_id and a order column like the one that follows:
id | parent_id | ordr | title ---+-----------+------+---------------------------------- 1 | 0 | 1 | first section 2 | 0 | 2 | second section 3 | 1 | 1 | title of first subitem (of first section) 4 | 2 | 2 | title of second subitem (of second section) 5 | 3 | 1 | title of third level item 6 | 2 | 1 | title of first subitem (of second section) 7 | 2 | 1 | title of first subitem (duplicated order index)
Where:
- id is an autoincrement key,
- parent_id is the id of the parent node for the current record, we are on the root it will be 0 (Zero).
- ordr is the sorting relative to the current nesting level
- title is just a simple description of the record.
The above informations would be represented in this way:
+-+- First section | | | +-+- title of first subitem (of first section) | | | +--- title of third level item | +-+- second section | +--- title of first subitem (of second section) | +--- title of first subitem (duplicate order index) | +--- title of second subitem (of second section)
In a table representation it would be:
id | parent_id | sort | title ---+-----------+------+---------------------------------- 1 | 0 | 1 | first section 3 | 1 | 1 | title of first subitem (of first section) 5 | 3 | 1 | title of third level item 2 | 0 | 2 | second section 6 | 2 | 1 | title of first subitem (of second section) 7 | 2 | 1 | title of first subitem (duplicate order index) 4 | 2 | 2 | title of second subitem (of second section)
But with a simple (or complex) query it would not be feasible. However, coding a simple sql function will solve this hard problem.
Online I discovered a complex stored procedure, that requires a complex usage mode (here is the solution that I dislike due its complexity) however it’s inspired by the Oracle database and maybe the Oracle’s DBA would like that against the mine solution.
IMHO, in mySQL, the way to do this is really simple using recursive functions, however by default, MySQL does not allow recursive execution in stored procedures and functions so, to allow the function to work propery in all environments I’ve ignored the recursive way and followed the old-school method! :-)
CREATE FUNCTION `getTreeOrder`(my_id int(11))
RETURNS longtext CHARSET utf8
BEGIN
-- Declaration section
declare sorting INT(11) DEFAULT 0;
declare pid INT(11) DEFAULT 0;
declare current_index INT(11) DEFAULT 0;
declare index_structure LONGTEXT DEFAULT '';
-- Recursion alternative
while my_id<>0 do
-- Getting informations about the given id (my_id)
select parent_id, ordr into pid, sorting
from my_tree_table
where id = my_id;
-- Using the above informations to detect the real position
-- in the node context (not the one defined by the "weak"
-- ordr column)
select count(*) into current_index
from my_tree_table
where parent_id = pid
and ( ordine < sorting or
ordine = sorting and id<=my_id)
and ordine <= sorting
order by
ordr, id;
-- building a string that will describe the nesting of
-- current node
if index_structure <> "" then
set index_structure = concat(">", index_structure);
end if;
-- the script is limited up to 9999 childs.
-- To increase this limit upgrade the '0000' with the right
-- number of digits and increase the '4' with the same
-- digits limit.
set index_structure = concat(
right(concat('0000', current_index), 4), index_structure
);
-- go up of one level
-- (remember that pid is the same as parent_id)
set my_id = pid;
end while;
-- returning the hirerarchical structure
return index_structure;
END
Well now that we have a function, let’s see how to use it.
select title, -- and just for debug ordr, id, parent_id, -- and for the curious ones getTreeOrder(id) the_nesting_structure from my_tree_table order by getTreeOrder(id);
Of course it’s easy as 1,2,3. I dare you to say otherwise!