Queries running in a loop

I have stepped into a serious problem.

I am working in a db where one user is child to a previously registered user.

And that child will be having another two child users.

This goes to n levels

So i tried to find out the super most parent of the nth level user the problem arised

Now i am using query running in a loop to find out this开发者_开发问答, but i know its weird and my database server will hang once the levels starts to increase.

somebody please show me some light.

can stored procedures be used for this?

---

You can find an answer here : http://sqlpro.developpez.com/cours/arborescence/ (checknig for english document).

It means you will have to design your table again but some search queries will be very faster (it avoids recursivity).

---

you need to fetch from the db in one query only the first level of every user,

get all the results into big array,

in your server side languages you can run an algorithm, that find the most parent,

don't use sql queries to do that.

---

MySQL doesn't have hierarchical queries so this can't be done efficiently via SQL. You must calculate data for this query during write not during read.

Just add a column "most parent" to your table. Or you can add a sting value where you can store a path to current record. For example

id parent path
1  null   null
2  1      /1/
3  2      /1/2/
....

---

Here's a simple example that requires a single non recursive db call to generate an employee hierarchy. You should easily be able to adapt this to your model.

Full script can be found here : http://pastie.org/1266734

Hope it helps :)

Example MySQL calls

call employees_hier(1);

call employees_hier(3);

Example PHP script

<?php
$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);

$result = $conn->query(sprintf("call employees_hier(%d)", 1));

while($row = $result->fetch_assoc()){
 echo sprintf("#%s %s -> #%s %s<br/>", $row["emp_id"],$row["emp_name"], 
    $row["boss_emp_id"], $row["boss_name"]);
}
$result->close();
$conn->close();
?>

MySQL Script

drop table if exists employees;
create table employees
(
emp_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
boss_id smallint unsigned null,
key (boss_id)
)
engine = innodb;

insert into employees (name, boss_id) values
('f00',null), 
  ('ali later',1), 
  ('megan fox',1), 
      ('jessica alba',3), 
      ('eva longoria',3), 
         ('keira knightley',5), 
            ('liv tyler',6), 
            ('sophie marceau',6);


drop procedure if exists employees_hier;

delimiter #

create procedure employees_hier
(
in p_emp_id smallint unsigned
)
begin

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

create temporary table hier(
 boss_id smallint unsigned, 
 emp_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select boss_id, emp_id, v_dpth from employees where emp_id = p_emp_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table emps engine=memory select * from hier;

while not v_done do

    if exists( select 1 from employees e inner join hier on e.boss_id = hier.emp_id and hier.depth = v_dpth) then

        insert into hier select e.boss_id, e.emp_id, v_dpth + 1 
            from employees e inner join emps on e.boss_id = emps.emp_id and emps.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

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

    else
        set v_done = 1;
    end if;

end while;

select 
 e.emp_id,
 e.name as emp_name,
 p.emp_id as boss_emp_id,
 p.name as boss_name,
 hier.depth
from 
 hier
inner join employees e on hier.emp_id = e.emp_id
left outer join employees p on hier.boss_id = p.emp_id;

drop temporary table if exists hier;
drop temporary table if exists emps;

end #

delimiter ;


call employees_hier(1);