Need help about counting relevant data with hierarchy(proposed solution - recurring mysql)

Need help about counting relevant data with hierarchy(proposed solution - recurring mysql)

<table>
    <thead>
        <tr>
            <th>Name</th>
            <th>Email</th>
            <th>Role</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>{{ $user->name }}</td>
            <td>{{ $user->email }}</td>
            <td>{{ $user->role }}</td>
        </tr>
    </tbody>
</table>

There is an array of managers hierarchy

$managerLevels = [ 'level' => 1, 'designation' => 'chairman', 'level' => 2, 'designation' => 'vice chairman', 'level' => 3, 'designation' => 'secretary', 'level' => 4, 'designation' => 'assistant secretary', 'level' => 5, 'designation' => 'member', ];

There are following tables

Managers (Table Structure)

column key remarks id primary parent_id foreign level from array $managerLevels

Hence managers are connected with parent id from same table

Managers (Table Sample Data)

id parent_id level 1 NULL 1 2 1 2 3 1 2 4 2 3 5 3 3 6 4 4 7 5 4 8 3 3 9 1 2 10 4 4 11 1 2 12 NULL 1 13 12 2 14 NULL 1 15 14 2 16 12 2 17 15 3 18 17 4 19 18 5 20 10 5 21 6 5

Managers (Table Sample Data -- rearranged)

id parent_id level 1 NULL 1 2 1 2 3 1 2 9 1 2 11 1 2 4 2 3 5 3 3 8 3 3 6 4 4 10 4 4 7 5 4 21 6 5

| 12 | NULL | 1 | 13 | 12 | 2 | 14 | NULL | 1 | 15 | 14 | 2 | 16 | 12 | 2 | 17 | 15 | 3 | 18 | 17 | 4 | 19 | 18 | 5 | 20 | 10 | 5

Universities

column key remarks id primary manager_id foreign key table: managers (level 5)

Universities (Table Sample Data)

id manager_id 1 21 2 19 3 20 4 19 5 19 6 20 7 21 8 21

Only lowest level manager is connected with universities table Hence manager_id is the lowest level (hence lowest level = 5) manager from managers table

Now need to get the count of total universities under each manager