Howto: mysql obtain the right sorting for nested records from the same table

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!