Display direct/indirect users under a manager in totara

Display direct/indirect users under a manager in Totara

Direct Users: The users under a manager.
In the following example, Manager1 is direct manager of Manager2.
Indirect Users: The users who are not directly under the manager instead they are under some other manager but that manager is under the main manager.
In the following example, Manager3 is the manager of TestUser1, Manager2 is the manager of Manager3 and Manager1 is the manager of Manager2 therefore, Manager1 is indirectly the manager of TestUser1.

Hierarchy:
Manager1
——-Manager2 ( User of Manager1)
———————-Manager3 ( User of Manage2)
————————————TestUser1 ( User of Manager1)
————————————TestUser2 ( User of Manager2)
————————————TestUser3 ( User of Manager3)
————————————TestUser4 ( User of Manager2)
To view the list of all the direct/indirect users under a Manager, we need to run the following query:

SELECT base.id AS userid 
FROM   {user} base 
WHERE  (( EXISTS (SELECT 1 
                  FROM   {user} u1 
                         INNER JOIN {job_assignment} u1ja 
                                 ON u1ja.userid = u1.id 
                         INNER JOIN {job_assignment} d1ja 
                                 ON d1ja.managerjaid = u1ja.id 
                  WHERE  u1.id = :viewer1 
                         AND d1ja.userid = base.id 
                         AND d1ja.userid != u1.id) 
           OR EXISTS (SELECT 1 
                      FROM   {user} u2 
                             INNER JOIN {job_assignment} u2ja 
                                     ON u2ja.userid = u2.id 
                             INNER JOIN {job_assignment} i2ja 
                                     ON i2ja.managerjapath LIKE 
                                        Concat(u2ja.managerjapath, 
                                        '/%') 
                      WHERE  u2.id = :viewer2 
                             AND i2ja.userid = base.id 
                             AND i2ja.userid != u2.id 
                             AND i2ja.managerjaid != u2ja.id) )) 
       AND base.deleted = 0 
    	 
   	 $params['viewer1'] = <userid of manager1>;
   	 $params['viewer2'] = < userid of manager1>;	   	    
    
   	 $users = $DB->get_records_sql($sql, $params);

By executing this query, all the direct/indirect users under manager1 will be shown:

Result:

Manager2  
Manager3  
             TestUser1
             TestUser2
                          TestUser3
                          TestUser4