write recursive function query with all subordinates

Question

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' => []],
                        ]
                    ]
                ],
            ]
        ],
    ],
   ...  
];

Ali Rahimi · Accepted Answer · 2022-04-10 21:11:33Z

1

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 = findUserById();
$subordinates = findAllSubordinates();

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']);

edited Apr 10, 2022 at 21:11

answered Apr 9, 2022 at 14:58

Ali Rahimi

4026 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

calin24 Over a year ago

I have tested yor sql but.... according to my query .. the direct subordinates for superior_id 1 is 16. With Your query I get the direct subordinates only 5

Ali Rahimi Over a year ago

Since I don't have your data I can't verify that my solution is incorrect. It worked on a dataset I created to test.aybe somehow give me specific data to test?

calin24 Over a year ago

Your solution is ok. I was doing something wrong :-) I have tried to make some changes (edit) but it does not allow (.... Sugested queue edit is full...)... anyway, you need to add the extra condtion in the main where WHERE departments.company_id = 1 AND company_user.company_id = 1. The second error: DB::select($query); without get(). Third: when calling $subordinate['superior_id'] (cannot use object of type stdClass as array) need to be like this: $subordinate->superior_id. Thx again for the info ;)

Ali Rahimi Over a year ago

Yes my code was not tailored to eloquent which returns models and objects.

Collectives™ on Stack Overflow

write recursive function query with all subordinates

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related