select ancestors using query like a recursive function

I have a database table menu having id, name and parentid.

I have the following values in the database, I want to collect all fields including parent menus using query.

   id  name      parentid
   1   File         0
 开发者_StackOverflow  2   New          1
   3   Document     2
   4   Image        2
   5   Edit         0
   6   Copy         5
   7   Paste        5

Example: I have 2 as my current menu, I want to select all the fields having parent Id 2 and their parent and their parents' parent until I reach the top parent (i.e. with parentid=0).

Is it possible to collect it using a single query? If yes, how it can be achieved?

If you have control over your data structure then there is a better way of storing this data which then lets you do what you need and it is all much easier than trying to carry on as you are.

What you are doing is commonly known as the adjacency list model. You should check out the nested sets model which is a much more efficient way of storing and retrieving hierarchical data.

There is a good tutorial here and a quick search on the web for Joe Celko will give you lots of links in the right direction as he has been writing about this for many years.

Hope this helps

Fairly simple single call solution that uses an adjacency list implementation with a non recursive stored procedure. Would recommend avoiding nested sets like the plague - best left in the classroom those !

All you need to do is call one of these stored procs from your php !

call menus_hier_downward(1);
call menus_hier_upward(3);

Simples - hope it helps :)

Example results

call menus_hier_downward(1);
+---------+-----------+-----------+------------------+-------+
| menu_id | menu_name | parent_id | parent_menu_name | depth |
+---------+-----------+-----------+------------------+-------+
|       1 | File      |      NULL | NULL             |     0 |
|       2 | New       |         1 | File             |     1 |
|       3 | Document  |         2 | New              |     2 |
|       4 | Image     |         2 | New              |     2 |
+---------+-----------+-----------+------------------+-------+
4 rows in set (0.00 sec)

call menus_hier_upward(3);
+---------+-----------+-----------+------------------+-------+
| menu_id | menu_name | parent_id | parent_menu_name | depth |
+---------+-----------+-----------+------------------+-------+
|       3 | Document  |         2 | New              |     1 |
|       2 | New       |         1 | File             |     2 |
|       1 | File      |      NULL | NULL             |     3 |
+---------+-----------+-----------+------------------+-------+
3 rows in set (0.00 sec)

I've provided you two example stored procedures. One works downwards the other upwards. Full script as follows:

Example Tables

drop table if exists menus;
create table menus
(
menu_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
parent_id smallint unsigned null,
key (parent_id)
)
engine = innodb;

insert into menus (name, parent_id) values
('File',null), 
 ('New',1), 
   ('Document',2), 
   ('Image',2), 
('Edit',null), 
 ('Copy',5), 
 ('Paste',5);

Downward stored procedure

drop procedure if exists menus_hier_downward;

delimiter #

create procedure menus_hier_downward
(
in p_menu_id smallint unsigned
)
begin

declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);

create temporary table hier(
 parent_id smallint unsigned, 
 menu_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select parent_id, menu_id, v_dpth from menus where menu_id = p_menu_id;

/* 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 menus m inner join hier on m.parent_id = hier.menu_id and hier.depth = v_dpth) then

        insert into hier select m.parent_id, m.menu_id, v_dpth + 1 
            from menus m inner join tmp on m.parent_id = tmp.menu_id and tmp.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

        truncate table tmp;
        insert into tmp select * from hier where depth = v_dpth;

    else
        set v_done = 1;
    end if;

end while;

select 
 m.menu_id,
 m.name as menu_name,
 p.menu_id as parent_id,
 p.name as parent_menu_name,
 hier.depth
from 
 hier
inner join menus m on hier.menu_id = m.menu_id
left outer join menus p on hier.parent_id = p.menu_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

delimiter ;

Upward stored procedure

drop procedure if exists menus_hier_upward;

delimiter #

create procedure menus_hier_upward
(
in p_menu_id smallint unsigned
)
begin

declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);

create temporary table hier(
 parent_id smallint unsigned, 
 menu_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select menu_id, null, v_dpth from menus where menu_id = p_menu_id;

/* 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 menus m inner join hier on m.menu_id = hier.parent_id and hier.depth = v_dpth) then

        insert into hier select m.parent_id, m.menu_id, v_dpth + 1 
            from menus m inner join tmp on m.menu_id = tmp.parent_id and tmp.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

        truncate table tmp;
        insert into tmp select * from hier where depth = v_dpth;

    else
        set v_done = 1;
    end if;

end while;

select 
 m.menu_id,
 m.name as menu_name,
 p.menu_id as parent_id,
 p.name as parent_menu_name,
 hier.depth
from 
 hier
inner join menus m on hier.menu_id = m.menu_id
left outer join menus p on hier.parent_id = p.menu_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

delimiter ;