CODEWITHSUNDEEP
mysqlschemaquery-optimization
rahul
rahul

Reputation: 1473

Getting manager of manager of an employee

I have an employee table which contains a manager ID as a column. If I need to get the manager of manager of a certain employee, how would I do it efficiently? How about if we are required to a 5 layers deep?

If this a requirement can we change the database schema so that this could be done more efficienty?

Upvotes: 2

Views: 922

Answers (3)

Jess
Jess

Reputation: 8700

The only way I can think of is to just recurse up as far as you need. A simple example would be:

///hight: how far up the manager chain to go
///startid: The id from which to start
function getManager ($startid, $hight)
{
    return getManagerRecurse ($hight, 0, $startid);
}
function getManagerRecurse ($hight, $curHight, $curid)
{
    if ($hight == $curHight)
        return $curid;
    $sql = "SELECT `managerid` FROM `table` WHERE `id`='$curid' LIMIT 1";
    $result = mysql_query($sql);
    if (mysql_num_rows($result) == 0)
        return -1; //Can't go up any further, so just return -1
    list($manid) = mysql_fetch_array($result);
    return getManagerRecurse ($hight, $curHight + 1, $manid);
}


getManager (15,2); //Start at id 15, go up 2 managers

Note: not tested

Upvotes: 0

Syntax Error
Syntax Error

Reputation: 4527

When I'm doing something like this I would probably just have people in table employee

Then I'd have a table management with columns manager_id and employee_id

Both columns are referencing employee.id (because a manager is an employee too, get it?)

That way you can just create a management entry for each employee that points to his manager, and do the same for each manager, etc... and it will go as many levels deep as you need.

Edit: Really I would assume you would have another table managerdata with additional data about each manager, but I wouldn't duplicate data that's in employee - just add the extra details about the manager and include a column pointing to employee.id

Upvotes: 0

Wes
Wes

Reputation: 6585

Read this article to see how you can define your own version of Oracle's connect by customization.

Upvotes: 1

Related Questions