Find Staff in Child Departments - PHP

Question

I am building a project with an organization chart using Codeigniter + MySQL + Active Record.

There are departments listed as organization tree, Staff for information of the persons, Staff Roles and Staff_Departments where i store the matching: Department - Staff - Role

You can see the structure below:

Departments (parent_id is used to build the tree)

Staff (raw Staff information)

Staff Roles (the lowest the weight, the highest in hierarchy)

Staff Departments (In which department - Who - What Role)

In a later phase, a Staff will probably belong in 2 or more departments with different roles. That's why i used a separate table Staff_departments for many-to-many. In this case, let's keep it simple and assume that 1 Staff belongs to 1 Department.

What i am trying to do:

1. A Manager(role weight=0 || role_id=1) in a Department, can view the Staff(Employees AND Supervisors) who work in his Department AND all the Staff(Employees AND Supervisors) from the Departments that are Children of his Department. The depth of the tree is unknown.
2. A Supervisor can view the Staff(only Employees) who work only in his Department.
3. An Employee can only view himself.

For Supervisors and Employees, the process is simple, so i think i am ok with that. For Managers probably i have to do something recursive, but i'm struggling every time i'm starting writing some lines of code.

My idea is to have a function find_related_staff($staff_id) {} in my controller, which i will pass the ID of the Staff who is logged in and it will return an array with the IDs of his related Staff. The only thing i got, is the ID of the Staff who is logged in.

If Manager return IDs of Managers,Supervisors and Employees related in his Department AND Managers,Supervisors and Employees from Child Departments of his Department.

If Supervisor return IDs of Supervisors and Employees related in his Department only.

If Employee return his ID

Any idea on how to achieve that?

Answer 1

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.

Answer 2

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!

Answer 3

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 t
JOIN stuff_departments sd ON sd.department_id = t.sub_id
JOIN staff s ON s.id = sd.staff_id
WHERE 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.