laravel 5.4 hierarchy / tree using query builder

Question

I am trying to create a company organisation tree but I just can't get things working.

I am hoping some of you clever people will shine some light on my mistakes.

My database only has a few row and looks like this for testing...

id, parent_id, client_id, department_id
1, 0, 1, 4
2, 1, 1, 4
3, 2, 1, 4
4, 0, 1, 4

All i can ever seam to get is this

array:1
["children" => array:2
    [0 => array:2
        [
            "id" => 0
            "name" => "Marketing Director"
        ]
     1 => array:2
        [
            "id" => 0
            "name" => "Accounts Director"
        ]
    ]
]

But what I actually want is the parent child structure

Parent > Child > Child etc etc...

Here's my code so far...

public static function generateOrgTree($clientid, $parent)
{
    $all_cats = DB::table('client_roles_rel')
           ->join('client_job_roles', 'client_job_roles.role_id', '=', 'client_roles_rel.role_id')
           ->join('client_departments', 'client_departments.department_id', '=', 'client_roles_rel.department_id')
           ->where('client_roles_rel.client_id', '=', $clientid)
           ->orderby('client_roles_rel.rel_id', 'asc')
           ->get();

    $tree = [];

    foreach ($all_cats as $cats) 
    {
        $pid = $cats->parent_id;
        $id = $cats->id;
        $department = $cats->department_name;

        if($pid == '0')
        {
            $tree['children'][] = ['id' => $pid, 'name' => $rolename];
        }
        else
        {
            if($pid == $parent) 
            {
                $children = General::generateOrgTree($clientid, $id);

                $pid = $cats->parent_id;
                $id = $cats->id;
                $department = $cats->department_name;

                $tree['children'][] = ['id' => $pid, 'name' => $rolename];
            }
        }
    }

    //dd(json_encode($tree));
    return $tree;
}

Using models => https://stackoverflow.com/questions/24672629/laravel-orm-from-self-referencing-table-get-n-level-hierarchy-json — Nouphal.M, Aug 10 '20 at 05:53

score 0 · Answer 1 · answered May 30 '17 at 14:05

OK, so no responses but I got this working, here is my code for anyone to use, if you have a better way of doing this then please share.

public static function generateOrgTree($elements, $parent, $num)
{
        $tree = [];

        //Log::info('Num: ' . $num);

        if($num <= 10)
        {

            foreach ($elements as $element) 
            {
                $pid = $element->parent_id;
                $id   = $element->rel_id;
                $relid = $element->rel_id;
                $rolename = $element->job_roles;
                $department = $element->department_name;

                $element->title = $rolename;
                $element->name = $department;

                if($pid == $parent)
                {
                    $children = General::generateOrgTree($elements, $id, $num +1);

                    if ($children) {

                        //$element['children'] = $children;
                        //$children->department_name = $children->department_name;

                        $element->children = $children;
                    }

                    $tree[] = $element;


                }

            }

        }

        return $tree;
}

How to use: call function with(array, parent id, limit) and pass collection to my view:

$getOrg = General::generateOrgTree($all_cats, 0, 0);

$tree = collect($getOrg);

->with('orgtree', $tree->toJson())

In my view I am using OrgChart https://github.com/dabeng/OrgChart

<script type="text/javascript">

$(document).ready(function(){

 var dataset = jQuery.parseJSON('<?php echo $orgtree; ?>');

  dataset.forEach(function(e) {

   // rename or remove properties from the data set...
   //e.title = e.department_name;
   delete e.department_name; 
   //e.name = e.job_roles;

   delete e.job_roles; 
   delete e.parent_id; 
   delete e.rel_id; 


});

  // console.log(dataset);

  var datasource = {'name': 'Company', 'title': '{{$clientdetails->client_company_name}}', 'children': dataset};

  $('#chart-container').orgchart({
    'data' : datasource,
    'nodeContent': 'title',
    'exportButton': false,
    'exportFilename': 'MyOrgChart',
    'draggable': false,
  });


});

</script>

score 0 · Answer 2 · answered May 30 '17 at 15:46

Roughly something like this:

$staff = Personnel::all()->keyBy('id');
$bosses = [];
foreach($staff as $member)
{
  if($member->parent_id)
  {
      $parent = $staff[$member->parent_id];
      if (!is_array($parent->children)) $parent->children = [];
      $parent->children[] = $member;
  }
  else
  {
      $bosses[] = $member;
  }
}

Afterwards you have an array of hierarchy top honchos in the $bosses array and array of each node's children in the children property. There is no need for recursion if all you need is to construct the tree.

laravel 5.4 hierarchy / tree using query builder

2 Answers2