Find Staff in Child Departments - PHP

Yes, to get it done you must use recursive procedures. (I'm using MySQL 5.6.19)

I have created some test data before the stored procedures:

1. Sample data based on your question requirements:

   create table departments(    id int not null primary key auto_increment,    parent_id int,    department_name varchar(100));insert into departments (id,parent_id,department_name)values(1,0,'Test A'),(2,1,'Test B'),(3,2,'Test C');create table staff(    id int not null primary key auto_increment,    ip_address varchar(100),    username varchar(100));insert into staff values(1,'127.0.0.1','ats'),(2,'127.0.0.1','admin'),(3,'127.0.0.1','george'),(4,'127.0.0.1','jhon');create table staff_roles(    role_id int not null primary key auto_increment,    role_name varchar(100),    role_height int);insert into staff_roles values(1,'Manager',0),(2,'Supervisor',1),(3,'Employee',2);create table staff_departments(    staff_department_id int not null primary key auto_increment,    department_id int,    staff_id int,    role_id int);insert into staff_departments values(1,1,2,1),(2,2,1,2),(3,3,3,3),(4,3,4,3);

2. It's time to create the stored procedures:

   • find_related_staff is the procedure that receives the staff_id parameter, according to that value will find the role_id in staff_departments table.

     The variable @result will accumulate the final result as comma separated values.

   • find_recursive is the procedure that search in child departments and get the staff_id into @result variable;

   The procedure code:

   delimiter $$drop procedure if exists find_related_staff$$create procedure  find_related_staff(p_id int)begin    declare p_role_id int;    declare p_department_id int;    declare p_return varchar(255) default '';    declare p_role varchar(100);    select d.role_id, d.department_id, r.role_name        into p_role_id,p_department_id, p_role        from staff_departments d        inner join staff_roles r on d.role_id = r.role_id        where d.staff_id = p_id        limit 1;    case p_role_id    when 3 then -- employee (return the same id)            set @result = p_id;        when 2 then -- supervisor             select group_concat(s.staff_id)        into @result        from staff_departments s        where               s.role_id = 3              and s.department_id in                  ( select d.id                    from departments d                   where d.parent_id = p_department_id )              and s.role_id <> p_id;        when 1 then -- manager (complex recursive query)            select coalesce(group_concat(s.staff_id),'')              into @result            from staff_departments s            where               s.department_id =  p_department_id              and s.staff_id <>  p_id;           -- here we go!           call find_recursive(p_department_id);    end case;    select @result as result, p_role as role;end $$delimiter ;delimiter $$drop procedure if exists find_recursive$$create procedure  find_recursive(p_dept_id int)begin    declare done int default false;    declare p_department int default false;    declare tmp_result varchar(255) default '';    -- cursor for all depend departments    declare c_departments cursor for        select s.department_id        from staff_departments s        where               s.department_id in           ( select d.id                 from departments d                where d.parent_id = p_dept_id );    declare continue handler for not found set done = true;    -- getting current departmens    set tmp_result =         (select coalesce(group_concat(s.staff_id),'')            from staff_departments s            where               s.department_id in               ( select d.id                 from departments d                where d.parent_id = p_dept_id ));    if length(tmp_result) > 0 then        if length(@result) > 0 then            set @result = concat(@result,',',tmp_result);        else            set @result = tmp_result;        end if;        open c_departments;        read_loop: loop            fetch c_departments into  p_department;            if done then              leave read_loop;            end if;        call find_recursive(p_department);        end loop;        close c_departments;                end if;end $$delimiter ;

3. Testing:

   Important: The max deep in recursion is 0 as default, we must change that value:

   SET max_sp_recursion_depth=255; 

   Now we have the bellow configuration on your staff_departments table:

   +---------------------+---------------+----------+---------+| staff_department_id | department_id | staff_id | role_id |+---------------------+---------------+----------+---------+|                   1 |             1 |        2 |       1 ||                   2 |             2 |        1 |       2 ||                   3 |             3 |        3 |       3 ||                   4 |             3 |        4 |       3 |+---------------------+---------------+----------+---------+

   Running each case:

   call find_related_staff(2);+--------+---------+| result | role    |+--------+---------+| 1,3,4  | Manager |+--------+---------+call find_related_staff(1);+--------+------------+| result | role       |+--------+------------+| 3,4    | Supervisor |+--------+------------+call find_related_staff(3);+--------+----------+| result | role     |+--------+----------+|      3 | Employee |+--------+----------+call find_related_staff(4);+--------+----------+| result | role     |+--------+----------+|      4 | Employee |+--------+----------+

4. Enjoy!

Ok, I think that, in order to make things easier to understand, we need to break your problem into small pieces (and I'm only focus on the section that you say you really need help: the managers' recursion).

First, we get the current department associated with the user authenticated. As you've said, you only have the ID of the staff currently sign so we'll start with that. Let's say the user id is assigned to variable $user_id.

$user_department = $this->db->get_where('staff_departments', ['staff_id' => $user_id])->row();

Now that we have the department, we check to see what's the role of the user in that department. We'll add that info to the $user_department object:

$user_department->role = $this->db->get_where('staff_roles', ['role_id' => $user_department->role_id])->row();

Let's check the weight of the user's role, shall we? If it's 0, we know it's a manager on that department so we'll recursively find the nested departments and their staff information. As per your logic dictates, we can check, here, if the user is a supervisor, also, and escalate if necessary. Like this:

if ($user_department->role->role_weight <= 1) {    // the user is a supervisor OR a manager, but both those can see, at least, the current department's staff information    $user_department->staff = $this->db->get_where('staff_departments', ['department_id' => $user_department->department_id]);    // now is the user a manager? If so, let's find nested departments    if ($user_department->role->role_weight === 0) {        $user_department->childs = $this->getChildDepartmentsAndStaffOf($user_department->department_id);    }}

As you may note, there's a function that'll be called recursively. It must be something along this lines:

public function getChildDepartmentsAndStaffOf($department_id){    $child_departments = $this->db->get_where('departments', ['parent_id' => $department_id]);    if (! $child_departments) {        return null;    }    foreach ($child_departments as &$department) {        $department->staff = $this->db->get_where('staff_departments', ['department_id' => $department->department_id]);        $department->childs = $this->getChildDepartmentsAndStaffOf($department->department_id);    }    return $child_departments;}

Now, you have the structure you want. I know this might be refactored, but I think that is enough to get your answer and point you to the right path.

Hope I've helped a little.

I think the most powerfull schema for hierarchical data in a relational database is the transitive-closure-table.

Given your sample data for the departments table:

department_id | parent_id | department_name--------------|-----------|----------------            1 |         0 | TEST A            2 |         1 | TEST B            3 |         2 | TEST C

Your closure table (let's just call it departments_tree) would be like:

super_id | sub_id---------|-------       1 |      1       1 |      2       1 |      3       2 |      2       2 |      3       3 |      3

Read it as: super_id = superordinate department_id; sub_id = subordinate department_id.

Assuming the logged-in user is manager of department with department_id = 2, the query to get all "supervised" employees is:

SELECT DISTINCT s.*FROM departments_tree tJOIN stuff_departments sd ON sd.department_id = t.sub_idJOIN staff s ON s.id = sd.staff_idWHERE t.super_id = 2

You can use triggers to populate and update the closure table.

Insert trigger:

DELIMITER //CREATE TRIGGER `departments_after_insert` AFTER INSERT ON `departments` FOR EACH ROW BEGIN    INSERT INTO departments_tree (super_id, sub_id)        SELECT new.department_id, new.department_id        UNION ALL        SELECT super_id, new.department_id        FROM departments_tree        WHERE sub_id = new.parent_id;END//DELIMITER ;

Delete trigger:

DELIMITER //CREATE TRIGGER `departments_before_delete` BEFORE DELETE ON `departments` FOR EACH ROW BEGIN    DELETE FROM departments_tree    WHERE sub_id = old.department_id;END//DELIMITER ;

Update trigger:

DELIMITER //CREATE TRIGGER `departments_before_update` BEFORE UPDATE ON `departments` FOR EACH ROW BEGIN    DELETE t    FROM       departments_tree p     CROSS JOIN departments_tree c    INNER JOIN departments_tree t      ON  t.super_id = p.super_id      AND t.sub_id = c.sub_id    WHERE p.sub_id   = old.parent_id      AND c.super_id = new.department_id;    INSERT INTO departments_tree (super_id, sub_id)        SELECT p.super_id, c.sub_id        FROM       departments_tree p        CROSS JOIN departments_tree c        WHERE p.sub_id   = new.parent_id          AND c.super_id = new.department_id;END//

Note

You will not need a delete trigger, if you use foreighn keys with ON DELETE CASCADE:

CREATE TABLE `departments_tree` (    `super_id` INT(10) UNSIGNED NOT NULL,    `sub_id` INT(10) UNSIGNED NOT NULL,    PRIMARY KEY (`super_id`, `sub_id`),    INDEX `sub_id_super_id` (`sub_id`, `super_id`),    FOREIGN KEY (`super_id`) REFERENCES `departments` (`department_id`) ON DELETE CASCADE,    FOREIGN KEY (`sub_id`)   REFERENCES `departments` (`department_id`) ON DELETE CASCADE);

Note 2

In many implementations of a transitive closure table, you will find a depth or level column.But you don't need it for the given requirements. And I believe you will never really need it,as long as you don't try to format tree output in SQL.