CODEWITHSUNDEEP
phpmysql
Swadesh
Swadesh

Reputation: 651

Recursive count of employees under a manager using mysql

I need the list of all employees, following the hierarchy of the manager using MYSQL from the following table. In oracle or mssql it is easy job, but could not find any solution in MySQL. Can anyone help me out how to sort it out.

id  name    manager

1   John    6
2   Gill    7
3   Ben     2
4   Roy     8
5   Lenin   6
6   Nancy   7
7   Sam     0
8   Dolly   3

Upvotes: 2

Views: 1093

Answers (1)

alephreish
alephreish

Reputation: 540

If you still can limit the maximal number of levels, here is a solution with a recursive procedure. Since recursive functions are not allowed in MySQL, we have here a function (manager_count), which wraps the results from the recursive procedure. Recursion depth is controlled by the max_sp_recursion_depth variable, which takes 255 as its maximum. Use as follows: SELECT *,manager_count(id) FROM my_table. It's not the optimal solution, since it doesn't take into account already counted branches of the hierarchy (a temporary table can actually serve as a cache).

DELIMITER //
DROP FUNCTION IF EXISTS manager_count//
CREATE FUNCTION manager_count(_manager INT) RETURNS INT
BEGIN
    DECLARE _count INT DEFAULT 0;
    SET max_sp_recursion_depth = 255;
    # manager_count_helper does the job
    CALL manager_count_helper(_manager, _count);
    # subtract 1, because manager_count_helper will count this manager as well
    RETURN _count - 1;
END//

DROP PROCEDURE IF EXISTS manager_count_helper//
CREATE PROCEDURE manager_count_helper(IN _manager INT, INOUT _count INT)
BEGIN
    IF EXISTS (SELECT 1 FROM my_table WHERE id = _manager) THEN
    BEGIN
        DECLARE _next_manager INT DEFAULT 0;
        DECLARE done BOOLEAN DEFAULT FALSE;
        # cursor to loop through the employees
        DECLARE _cursor CURSOR FOR SELECT id FROM my_table WHERE manager = _manager;
        # if done, the done variable gets TRUE and it's time too leave
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        # count 1, because this guy should be counted as well
        SET _count = _count + 1;
        OPEN  _cursor;
        read_loop: LOOP
            FETCH  _cursor INTO _next_manager;
            IF done THEN LEAVE read_loop;
            END IF;
            CALL manager_count_helper(_next_manager, _count);
        END LOOP;
        CLOSE  _cursor;
    END;
    END IF;
END

Upvotes: 3

Related Questions