I have this function in laravel 9

private function userDirectSubordinates($user_id, $company_id) 
{
    return User::select(['users.id', 'users.name as label', 'avatar', 'departments.name as department'])
        ->join('department_user', 'users.id', '=', 'department_user.user_id')
        ->join('departments', 'departments.id', '=', 'department_user.department_id')
        ->where('departments.company_id', $company_id)
        ->whereIn('users.id', function($query) use($user_id) {
            $query->select('user_id')
                ->from('company_user')
                ->where('superior_id', $user_id);
        })
        ->get();
}

This function return the direct subordinates for the user with id $user_id from the company selected $company_id. Example:

[
    ["id" => 880, 'label' => 'User 880', 'avatar' => null, 'department' => 'IT'],
    ["id" => 41, 'label' => 'User 41', 'avatar' => null, 'department' => 'Finance'],
    ...
]

How can I make a recursive function to get from each user from directSubordinates ... all subordinates. The final array should look like this:

[
    ["id" => 880, 'label' => 'User 880', 'avatar' => null, 'department' => 'IT',
        'children' => [
            ["id" => 32, 'label' => 'User 32', 'avatar' => null, 'department' => 'IT', 'children' => []],
            ["id" => 56, 'label' => 'User 56', 'avatar' => null, 'department' => 'IT',
                'children' => [
                    ["id" => 21, 'label' => 'User 21', 'avatar' => null, 'department' => 'Maintenance', 'children' => []],
                    ["id" => 687, 'label' => 'User 687', 'avatar' => null, 'department' => 'Development',
                        'children' => [
                            ["id" => 334, 'label' => 'User 334', 'avatar' => null, 'department' => 'Development', 'children' => []],
                            ["id" => 335, 'label' => 'User 335', 'avatar' => null, 'department' => 'Development', 'children' => []],
                        ]
                    ]
                ],
            ]
        ],
    ],
   ...  
];

CodePudding user response：

Presumably you are using MySQL 8 with Laravel 9 which means you can do this(recursive with syntax):

SQL:

select users.id, users.username as username, departments.name as dept, company_user.superior_id as superior_id
FROM users
INNER JOIN department_user on users.id = department_user.user_id
INNER JOIN departments on departments.id = department_user.department_id
INNER JOIN company_user on company_user.user_id = users.id
WHERE
    departments.company_id = 1
    AND
    users.id IN (
      with recursive cte (user_id) as (
        select     user_id
        from       company_user
        where      superior_id = 1
        union all
        select     parent.user_id
        from       company_user parent
        inner join cte
        on parent.superior_id = cte.user_id
      )
      select * from cte
    )

Personally, I wouldn't bother using Laravel query builder for this(Just use raw).

The result you get is something like:

note that the parent(target user) isn't present and you should fetch it first.

now you can loop over the data and append children to their parents in PHP and create a tree:

$user = User::find($user_id);
$subordinates = DB::select($query)->get();

function buildTree(array &$subordinates, $parentId) {

    $branch = array();

    foreach ($subordinates as &$subordinate) {

        if ($subordinate['superior_id'] == $parentId) {
            $children = buildTree($subordinates, $subordinate['id']);
            if ($children) {
                $subordinate['children'] = $children;
            }
            $branch[] = $subordinate;
            unset($subordinate);
        }
    }
    return $branch;
}

$user['children'] = buildTree($subordinates, $user['id']);