Self-referential class: How to get jerarchical structure of employees in a company?

Question

I have a class defined in Python called Employees. Its attribute id_supervisor is the id of the employee who is in charge of him.

One employee can be in charge of (supervise) many other employees.

This is my class:

class Employee(DeclarativeBase):

    __tablename__ = 'employees'

    id_ = Column('id',Integer,primary_key=True)
    name = Column(Unicode(50))
    id_supervisor = Column(Integer, ForeignKey('employees.id'))
    #other attributes ...

What I need to do is to get the whole hierarchical structure of the company, from the boss of the company (he would have his id_supervisor attribute set to None or to himself) to the lowest worker of the company (he would be supervising no workers at all).

How could this be done? I have been thinking about using a recursive function, but I really don't know how to do it.

Any help would be appreciated.

You're concept/table design seems sufficient. What does your source for this information look like? That, I think, will determine your next steps. — , Dec 26 '13 at 10:20
That's the main problem with the adjacency list pattern - you cannot get a whole tree (or a whole branch of the tree) in a single query. You may want to consider using the nested sets (aka modified preorder tree traversal) pattern instead : http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — bruno desthuilliers, Dec 26 '13 at 10:23
How would you do this using just SQL, ignoring the SQLAlchemy part? If you don't know the answer to that, [then you should look into that first](http://stackoverflow.com/questions/38801/sql-how-to-store-and-navigate-hierarchies). If you do know the answer, then you should be able to do it with SQLAlchemy (since anything you can do in SQL you can use SQLAlchemy to do), so what part precisely of SQLAlchemy are you having trouble with? — Mark Hildreth, Dec 27 '13 at 18:11

Jayadevan · Accepted Answer · 2014-02-05T02:20:04.077

Please try the material https://class.coursera.org/db/wiki/coursematerials. You can see the section on recursion (Recursion in SQL). It is explained very well with PostgreSQL examples.

postgres=# select * from employees ;
 empid | supervisorid 
-------+--------------
     1 |             
     2 |             
     3 |            1
     4 |            2
     5 |            3
     6 |            4
(6 rows)

WITH RECURSIVE emp(empid,supervisorid) AS (
     SELECT empid, supervisorid  FROM employees where empid=1
     UNION ALL
     SELECT e.empid, e.supervisorid
     FROM emp s, employees e
     WHERE e.supervisorid = s.empid 
   )
SELECT * 
 FROM emp
 ;
 empid | supervisorid 
-------+--------------
     1 |             
     3 |            1
     5 |            3
(3 rows)

Self-referential class: How to get jerarchical structure of employees in a company?

1 Answers1