MySQL Forums
Forum List  »  Newbie

Re: Traversing a Category tree, is there an easy way to do this? Need an experts opinion!!!!!
Posted by: Roland Bouman
Date: July 13, 2005 02:05AM

Hi all!

you don't need recursion, you can do it iteratively.

create a table like this:

| Field | Type | Null | Key | Default | Extra |
| sid | int(11) | YES | | NULL | |
| lvl | int(11) | YES | | NULL | |
| category_id | int(10) unsigned | YES | | NULL | |

(I tried with a temporary table, but this did not work out for some reason unrelated to this problem)

and then do this:

drop procedure traverse_category_down

create procedure traverse_category_down(
start_with int unsigned
declare v_level int default 0;
declare v_sid int default connection_id();

from current_categories
where sid = v_sid

into current_categories(sid,lvl,category_id)
values (v_sid,v_level,start_with)
declare v_no_more_levels boolean;
declare v_level_done boolean default false;
declare v_id int unsigned;
declare v_name varchar(30);
declare categories_cursor cursor for
select cat.category_id
from category cat
inner join current_categories curr
on cat.parent_category_id = curr.category_id
where curr.sid = v_sid
and curr.lvl = v_level
declare continue handler for sqlstate '02000'
set v_level_done := true
set v_no_more_levels := true;
open categories_cursor;
set v_level := v_level + 1;
nodeloop: loop
fetch categories_cursor
into v_id
, v_name
if v_level_done then
leave nodeloop;
set v_no_more_levels := false;
end if;
-- put your traversal action here
select v_level
, v_name
-- end of your traversal action
into current_categories(
, lvl
, category_id
) values(
, v_level
, v_id
end loop;
close categories_cursor;
until v_no_more_levels end repeat;

Hope it helps!

By the way, jay, erin, thanks for the links.
I think I would prefer the nested set solution.
I've seen some naive solutions that use just a single large numeric code, like this:

root: 1000
child1: 1100 (child 1 of root)
child2: 1200 (child 2 of root)
child9: 1900 (child 9 of root)

child11: 1110 (child 1 of child1)
child12: 1120 (child 2 of child1)

and so on. This has some properties akin to the nested set model, such as needing a single query to select all nodes down from a particular node.
The update problem is here a lot bigger of course than in the nested set model, and you really need to know on beforehand how large every thing is going to be.
Guess what, it isn't even a hobby project, but a professional personell app.


Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.