table - user
posts - (userId ,title, managerId)
(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/%'
- You have to perform a UPDATE after each Place to append PK to Lineage
I usally add another column where I place the path as text in (for example