I have a table in the db which has columns of the structure of the organization:

And as seen in the example data above, not all the levels have data so some are null.

I want to get all the possible combinations of hierarchies so I fetch the data using GROUP BY:

$data = Table::select('group', 'branch', 'unit')->groupBy('group', 'branch', 'unit')->get();

This returns the result as separated rows.

But how can I make it into an hierarchy array, for example:

[ 
    "g1" => ["b1" => ["other", "u2"]]
    "g2" => ["b6" => ["u4"], "other" => ["u3"]]
    "g3" => ["b7" => ["other", "u5"]]
]

I replaced null with other in this case for better reference

CodePudding user response：

I'm not a Laravel developer, so I can't say if there's a built-in way to do this, but in general PHP it isn't that complicated.

I personally like to back my things with object whenever they reach a certain level of complexity, so the way that I normally solve this is to represent the Group, Branch and Unit as classes, the first two with children, and then a main array to hold all of the groups. I also store groups and children with the object's "name" or "identifier" as an index in the array to make it easier to look things up. Then it is just a matter of looping.

You can optionally move the isset logic into the classes themselves with an addBranch method on the group, or something like that, but it mostly works out the same.

Class Unit{
    public function __construct(public readonly ?string $name){}
}

Class Branch{
    public array $units = [];
    public function __construct(public readonly ?string $name){}
}

Class Group{
    public array $branches = [];
    public function __construct(public readonly string $name){}
}

$sampleData = [
    ['g1', 'b1',   null],
    ['g1', 'b1',   'u2'],
    ['g2', null,   'u3'],
    ['g2', 'b6',   'u4'],
    ['g3', 'b7',   'u5'],
];

$groups = [];
foreach($sampleData as $row){
    $groupId  = $row[0];
    $branchId = $row[1];
    $unitId   = $row[2];
    
    if(!isset($groups[$groupId])){
        $groups[$groupId] = new Group($groupId);
    }
    
    if(!isset($groups[$groupId]->branches[$branchId])){
        $groups[$groupId]->branches[$branchId] = new Branch($branchId);
    }
    
    if(!isset($groups[$groupId]->branches[$branchId]->units[$unitId])){
        $groups[$groupId]->branches[$branchId]->units[$unitId] = new Unit($unitId);
    }
}

Demo: https://3v4l.org/WE17l#v8.1.13

CodePudding user response：

You can use the collection groupBy() method "twice" to reach that result (1 number of groups since it's in a loop).

$data = Table::select('group', 'branch', 'unit')->groupBy('group', 'branch', 'unit')->get();

$data = $data->groupBy('group');
$data = $data->map(function($group){
    return $group->groupBy('branch');
});

Here is an example

You can also do a simple loop to generate the result in an array. Autovivification will do its magic.

$data = Table::select('group', 'branch', 'unit')->groupBy('group', 'branch', 'unit')->get();

$groups = [];
foreach($data as $row){
    $groups[$row->group][$row->branch][] = $row->unit;
}

Demo