title: Find Staff in Child Departments - PHP
body: <p>I am building a project with an organization chart using Codeigniter + MySQL + Active Record. </p>
  
  <p>There are departments listed as organization tree, Staff for information of the persons, Staff Roles and Staff_Departments where i store the matching: 
  <strong><em>Department - Staff - Role</em></strong></p>
  
  <p>You can see the structure below:</p>
  
  <p><strong>Departments</strong> <em>(parent_id is used to build the tree)</em></p>
  
  <p><a href="https://i.stack.imgur.com/m5WeQ.png" rel="noreferrer"><img src="https://i.stack.imgur.com/m5WeQ.png" alt="enter image description here"></a></p>
  
  <p><strong>Staff</strong> <em>(raw Staff information)</em></p>
  
  <p><a href="https://i.stack.imgur.com/Xyn0f.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Xyn0f.png" alt="enter image description here"></a></p>
  
  <p><strong>Staff Roles</strong> <em>(the lowest the weight, the highest in hierarchy)</em> </p>
  
  <p><a href="https://i.stack.imgur.com/GWZwW.png" rel="noreferrer"><img src="https://i.stack.imgur.com/GWZwW.png" alt="enter image description here"></a></p>
  
  <p><strong>Staff Departments</strong> <em>(In which department - Who - What Role)</em></p>
  
  <p><a href="https://i.stack.imgur.com/Cy4qL.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Cy4qL.png" alt="enter image description here"></a></p>
  
  <p><em>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.</em></p>
  
  <p><strong>What i am trying to do:</strong></p>
  
  <ol>
  <li>A Manager(<em>role weight=0 || role_id=1</em>) in a Department, can view the Staff(Employees AND Supervisors) who work in his Department <strong>AND</strong> all the Staff(Employees AND Supervisors) from the Departments that are Children of his Department. The depth of the tree is unknown.</li>
  <li>A Supervisor can view the Staff(only Employees) who work only in his Department.</li>
  <li>An Employee can only view himself.</li>
  </ol>
  
  <p>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.</p>
  
  <p>My idea is to have a function <strong>find_related_staff($staff_id) {}</strong> 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.</p>
  
  <blockquote>
    <p><strong>If Manager</strong> return IDs of Managers,Supervisors and Employees related in his Department AND Managers,Supervisors and Employees from
    Child Departments of his Department.</p>
    
    <p><strong>If Supervisor</strong> return IDs of Supervisors and Employees related in his Department only.</p>
    
    <p><strong>If Employee</strong> return his ID</p>
  </blockquote>
  
  <p>Any idea on how to achieve that? </p>