CODEWITHSUNDEEP
phpmysql
Umar Adil
Umar Adil

Reputation: 5277

MySQL Select Query

I have a huge employee data in mysql with an attribute as parent id which stores the supervisor of each employee and is defined in a hierarchy. Each user is working under other employee and is handling team of 4-5 members. I frequently need the supervisor or subordinates tree for which i am using a recursive function to fetch an employ with its team. Please suggest me a method so that I don't have to call recursive function each time I need employee data. Is using "Views or stored procedure" a good idea?

Thanks.

Upvotes: 0

Views: 348

Answers (3)

Kim
Kim

Reputation: 2787

Have a look at these links:

Upvotes: 0

Jon Black
Jon Black

Reputation: 16569

Here's a non recursive stored procedure implementation which obviously only requires one call from your application code not n calls (one for each level of the tree). Would recommend staying well away from nested sets and stick with you adjacency list implementation - think connect by Oracle and CTE in sql server - say no more.

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 this sproc from your php

call employees_hier(1);

Upvotes: 2

Oswald
Oswald

Reputation: 31685

When you use stored procedures, you will still need recursion. You only move the recursion from the PHP source code to the database.

You can use nested sets to store hierarchical data. This gets rid of recursions at the price of higher costs for insertion, deletion and relocation. Basically, you create two additional fields left and right where left < right and e1 is subordinate of e2 iff e1.left > e2.left && e1.right < e2.right.

This makes SELECT queries hard to read, but efficient. Do this when all else fails.

Upvotes: 0

Related Questions