Created
March 5, 2014 22:18
-
-
Save jdmullin/9377818 to your computer and use it in GitHub Desktop.
Stored procedure to return hierarchical data
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
drop procedure if exists category_hier; | |
delimiter # | |
create procedure category_hier | |
( | |
in p_cat_id smallint unsigned, | |
in p_name_separator varchar(3) -- string to separate full name path segments | |
) | |
begin | |
declare v_done tinyint unsigned default 0; | |
declare v_depth smallint unsigned default 0; | |
create temporary table hier( | |
parent_cat_id smallint unsigned, | |
cat_id smallint unsigned, | |
depth smallint unsigned default 0, | |
full_name varchar(2048) | |
)engine = memory; | |
if (p_cat_id is null) then | |
insert into hier select parent_cat_id, cat_id, v_depth, name from categories where parent_cat_id is NULL; | |
else | |
insert into hier select parent_cat_id, cat_id, v_depth, name from categories where cat_id = p_cat_id; | |
end if; | |
/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */ | |
create temporary table tmp engine=memory select * from hier; | |
while not v_done do | |
if exists( select 1 from categories p inner join hier on p.parent_cat_id = hier.cat_id and hier.depth = v_depth) then | |
insert into hier | |
select p.parent_cat_id, p.cat_id, v_depth + 1, concat(tmp.full_name,p_name_separator,p.name) from categories p | |
inner join tmp on p.parent_cat_id = tmp.cat_id and tmp.depth = v_depth; | |
set v_depth = v_depth + 1; | |
truncate table tmp; | |
insert into tmp select * from hier where depth = v_depth; | |
else | |
set v_done = 1; | |
end if; | |
end while; | |
select | |
p.cat_id, | |
p.name as category_name, | |
b.cat_id as parent_cat_id, | |
b.name as parent_category_name, | |
hier.depth, | |
hier.full_name | |
from | |
hier | |
inner join categories p on hier.cat_id = p.cat_id | |
left outer join categories b on hier.parent_cat_id = b.cat_id | |
order by | |
hier.depth, hier.cat_id; | |
drop temporary table if exists hier; | |
drop temporary table if exists tmp; | |
end # |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Tweak on solution provided in this SO post: http://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159