table - user

posts - (userId ,title, managerId)

rows -

(1,nilesh,0)
(2,nikhil,1)    
(3,nitin ,2)  
(4,Ruchi,2)

basically give id of user it will list all confirming individuals to him . basically give userId = 2 it will return 3,4.

Is query correct

SELECT ad3.userId
FROM user au , user  au2 , user  au3
WHERE 
    ad.managerId = ad2.managerId AND 
    ad3.managerId = ad2.userId AND
    ad.userId=2

Can there be any efficent method to manage tree structure in DB ? What about left and right leaf way ?

SELECT user.id FROM user WHERE user.managerid = 2

Is what you would like?

Something similar to this (ANSI SQL):

WITH RECURSIVE emptree (userid, name, managerid) AS (
    SELECT userid, 
           name, 
           managerid
    FROM the_table 
    WHERE userid = 2

    UNION ALL

    SELECT c.userid, 
           c.name,
           c.managerid
    FROM the_table c
       JOIN emptree p ON p.userid = c.managerid
)
SELECT *
FROM emptree

For me, the issue using the adjacency list model is it will get difficult to cope with in SQL particularly when you do not know how deeply nested your tree structure will probably be.

The 'left and right leaf way' you mention is most likely the nested set model and enables you to definitely store such things as this

LFT   RGT   Name
1     8      nilesh
2     7      nikhil
3     4      nitin
5     6      Ruchi

You'll be able to find all anyones subordinates simply by

SELECT Name FROM Hierarchy WHERE LFT BETWEEN @LFT AND @RGT

It is a lot simpler to cope with for querying but is harder to complete for tree modifications. In case your data does not change much i quickly think this can be a far better solution. (Not everybody will accept me though)

There's a Very good Tutorial here

I personally use a text area to cope with trees in SQL. It's simpler than using left/right values.

Allows go ahead and take example in the MySQL article:

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

It might create a table such as this:

Id      ParentId        Lineage     Name

1       null            /1/         ELECTRONICS
2       1               /1/2/       TELEVISIONS
3       2               /1/2/3/     TUBE
4       2               /1/2/4/     LCD
5       2               /1/2/5/     PLASMA
6       6               /1/6/       GAME CONSOLES
7       1               /1/7/       PORTABLE ELECTRONICS
8       7               /1/7/8/     MP3 PLAYERS
9       8               /1/7/8/9/   FLASH
10      7               /1/7/10/    CD PLAYERS
11      1               /1/11/      2 WAY RADIOS
12      11              /1/11/12/   FRS

Do find all portables you just make use of the Lineage from portables:

SELECT * FROM theTable WHERE Lineage LIKE '/1/7/%'

Cons:

  • You have to perform a UPDATE after each Place to append PK to Lineage

Suggestion:

I usally add another column where I place the path as text in (for example 'electronics/televisions/tube')