Find level or depth of child in child parent (Adjacency List Model) relation using PHP

Question

There is a relation of employee and manager in mysql table which is stored as Adjacency List Model Employee has only one manager and manager have many employees if value of manager is empty that means employee without manager

relation

employee (1 to 1) manager
employee (many to 1) manager

employee      manager   
10            11
15            10
9             15
6              0

I want to depth of manager like for

11 depth is 3
for 10 depth is 2 
for 15 depth is 1
for 6 depth is 0 
......
......
.......

How can i achieve this using php below is my incomplete logic.

<?php
get_level(11) // 3
get_level(10) // 2
get_level(15) // 1
get_level(6) // 0

function get_level($level){
   $this->db->get_where('manager_user', array('manager_no' => $level))
   ->result_array()
    // logic 
    return no; //3 for 11
}
?>

Can some one help me in this. If any one provide me the solution with mysql function this will be also helpful for me data stored in mysql multilevel hierarchy.

Edit : I edit my question as suggested by @sintakonte.

Step taken by me to solve the issue - first i changed my table structure from The Adjacency List Model

to The Nested Set Model

after that this class helped me to achieve the desired result

Using switch? http://php.net/manual/en/control-structures.switch.php — Roy Bogado, Aug 09 '18 at 08:56
why should 11 be depth 3 ? - shouldn't be 11 on the employee column too and associated to a manager ? (according to your example...) — Atural, Aug 09 '18 at 13:21
yea i didn't get that either. figured there was no correlation and that he just posted that for giggles — Alex, Aug 09 '18 at 19:34
@sintakonte - because, there is 3 employee under the manager 11 (10, 15, 9). and 11 parent will be zero. — ihcc aman, Aug 11 '18 at 06:04
@sintakpnte - Please check this ) https://stackoverflow.com/questions/10688608/mysql-output-in-tree-format-or-adding-level-parent-child — ihcc aman, Aug 11 '18 at 06:27
and what is 6 ? 6 is an employee without manager or what ? if so why is this even in your table ? — Atural, Aug 14 '18 at 05:09
and the next question: can any employee have more than one manager and vice versa? if so what happens if 15 is manager of 10 or 9 is manager of 11 etc... i mean dude you've to come up with a bit more data here because this is a very lousy description for a question ;) — Atural, Aug 14 '18 at 05:17
@sintakonte, Thanks for your solution. I believe logic is good enough but have large data in the table. It is showing error "Fatal error: Maximum function nesting level of '256' reached, aborting! " — ihcc aman, Aug 18 '18 at 05:58

score 0 · Answer 1 · answered Aug 09 '18 at 09:25

0

Simple. Just use a switch statement:

function get_level($level){
  $query = $this->db->get_where('manager_user', array('manager_no' => $level));
  if ($query->num_rows !== 1) {
    return false;  
  }
  $manager_no = intval($query->row()->manager_no);
  switch ($manager_no) {
      default:
          return false;
      case 11:
          return 3;
      case 10:
          return 2;
      case 15
          return 1;
      case 6:
          return 0;
  }
}

Returns false if case doesn't exist or if query returns no rows. I'm also assuming that manager_no is unique.

Also think for 6 depth is 0 you said should be reversed but you can figure that part out.

answered Aug 09 '18 at 09:25

Alex

9,215
8
39
82

I want to operate dynamic data with the function - @Alex – ihcc aman Aug 09 '18 at 11:24
don't know what that means – Alex Aug 09 '18 at 19:33
Data is stored [Adjacency Model](https://dba.stackexchange.com/questions/89051/stored-procedure-to-update-an-adjacency-model-to-nested-sets-model) in my example why you use switch case – ihcc aman Aug 12 '18 at 09:10

score 0 · Answer 2 · answered Aug 09 '18 at 10:26

Here is the solution for with dynamic data

function get_level($level){
   $query = $this->db->query('SELECT count(*) as depth FROM manager_user WHERE manager_no > (SELECT manager_no FROM roles WHERE manager_no ='.$level.')');
   $depth = $query->row_array()['depth'];
}

score 0 · Answer 3 · answered Aug 14 '18 at 07:35

this is a pretty tough task i think - because one problem is the lack of information and the other is to build the tree

Anyway i tried something which should work (please study the code carefully) - i'm not sure if there are better concepts out there but i think this is a good one

Create a model called Manageruser_model.php like the following

class Manageruser_model extends CI_Model
{

    private $arrFieldsWithKeys = [];

    private function createTree()
    {
        $arrResult = $this->db->get_where('manager_user')->result();
        //in case a manager is no employee - we've to add this one as an employee with manager id 0
        $arrResult = $this->addManagersAsEmployee($arrResult);

        $arrFieldsWithKeys = [];

        foreach($arrResult AS $obj)
        {
            $arrFieldsWithKeys[$obj->employee] = $obj;
            if (!isset($arrFieldsWithKeys[$obj->manager]) && $obj->manager != 0) $arrFieldsWithKeys[$obj->manager] = $obj;
        }

        $arrFoundChilds = [];
        foreach($arrResult AS $obj)
        {
            if (isset($arrFieldsWithKeys[$obj->manager]))
            {
                if (!isset($arrFieldsWithKeys[$obj->manager]->childs)) $arrFieldsWithKeys[$obj->manager]->childs = [];
                $arrFieldsWithKeys[$obj->manager]->childs[] = $obj;
                $arrFoundChilds[] = $obj->employee;
            }
        }

        $this->arrFieldsWithKeys = $arrFieldsWithKeys;

        $arrRemovedChildsFromMasterNode =array_diff_key($arrFieldsWithKeys,array_flip($arrFoundChilds));
        $this->setTreeDepth($arrRemovedChildsFromMasterNode);

    }

    private function addManagersAsEmployee($arrResult)
    {
        $employees = array_column($arrResult, 'employee');
        $manager = array_column($arrResult, 'manager');
        $arrMissingManagersAsEmployee = array_diff($manager, $employees);
        foreach($arrMissingManagersAsEmployee AS $strId)
        {
            if ($strId > 0)
            {
                $obj = new stdClass();
                $obj->employee = $strId;
                $obj->manager = 0;
                $arrResult[] = $obj;
            }
        }
        return $arrResult;
    }

    private function setTreeDepth($arr)
    {
        $level = 0;
        foreach($arr AS $obj)
        {
            if (isset($obj->childs))
            {
                $level = $this->setTreeDepth($obj->childs);
                $obj->level = $level;
            }
            else
            {
                $obj->level = 0;
            }
        }
        return $level + 1;
    }

    public function getDepth(int $id)
    {
        if (empty($this->arrFieldsWithKeys))    $this->createTree();
        if (!isset($this->arrFieldsWithKeys[$id]))  throw new Exception($id.' doesn\'t exist.');

        return $this->arrFieldsWithKeys[$id]->level;
    }
}

and now in your controller you can simply load the model and get a depth in return like

$this->load->model('Manageruser_model');
echo $this->Manageruser_model->getDepth(11);

Find level or depth of child in child parent (Adjacency List Model) relation using PHP

3 Answers3