MySQL: Fetch all hierarchy using parent_id from table

Question

I have mysql table something like this:

id | name| parent_id
---+-----+-----------
 1 | aaa | 0
 2 | bbb | 1
 3 | ccc | 2
 4 | ddd | 3
 5 | eee | 3
 6 | fff | 1
 7 | ggg | 0
 8 | hhh | 7
 9 | iii | 7

I need to create a query that will fetch all hierarchy for a given id.

For Example: for input=1, result should be something like this:

id | name| parent_id
---+-----+-----------
 1 | aaa | 0
 2 | bbb | 1
 3 | ccc | 2
 4 | ddd | 3
 5 | eee | 3
 6 | fff | 1

For Example: for input=7, result should be something like this:

id | name| parent_id
---+-----+-----------
 7 | ggg | 0
 8 | hhh | 7
 9 | iii | 7

I have tried something like this but don't know how to proceed. I don't know how many levels are there in hierarchy.

SELECT t1.id, t1.name, t1.parent_id, t2.name AS parent_name
FROM parent_test t1
LEFT JOIN parent_test t2 ON t1.parent_id=t2.id
WHERE t1.id=1 OR t1.parent_id=1;

Any Idea?

Arnold Daniels · Accepted Answer · 2015-12-07 20:42:08Z

2

With only a parent column, the query needs to be performed recursively. This is something MySQL can't do.

Luckily this is a common problem, with a common (though somewhat complicated) solution: the nested set model.

The nested set model is a particular technique for representing nested sets (also known as trees or hierarchies) in relational databases.

With a nested set you specify a left and right column. All records between the left and right are descendants.

id | name| lft | rght
---+-----+-------------
 1 | aaa |   0 |   11
 2 | bbb |   1 |    8
 3 | ccc |   2 |    7
 4 | ddd |   3 |    4
 5 | eee |   5 |    6
 6 | fff |   9 |   10
 7 | ggg |  12 |   17
 8 | hhh |  13 |   14
 9 | iii |  15 |   16

Now you can do a simple query to get all descendants for record 1:

SELECT t1.* FROM parent_test t1
INNER JOIN parent_test t2 ON t1.lft >= t2.lft AND t1.rght <= t2.rght 
WHERE t2.id = 1

Note that while read queries are simple, the complexity is added to write queries.

When inserting, you need find the correct left. The right is always left + 1. Next you need to increment the right column by 2 for all for records with a higher right column, because of the 2 inserted numbers.

Example, to insert a child of 2 you do.

START TRANSACTION;
SELECT `lft` + 1, `lft` + 2 FROM parent_test WHERE id = 2 INTO @lft, @rght
INSERT INTO parent_test (`name`, `lft`, `rght`) VALUES ('qqq', @lft, @rght);
UPDATE parent_test SET `rght` = `rght` + 2 WHERE `rght` > @rght;
COMMIT;

When deleting you do the opposite logic. For updating you do both.

edited Dec 7, 2015 at 20:42

answered Dec 7, 2015 at 13:13

Arnold Daniels

16.6k6 gold badges55 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Awan Over a year ago

Thanks for your response. if we don't want to use this lft-rght technique, can I get this with PHP-MySql combination sharply with less database calls and less php processing?

Kickstart Over a year ago

Not really. Using the nested set model reduces it to the min database calls and min php processing for reads. Otherwise you are using recursive processing to read the various child records which tends to be very slow.

Arnold Daniels Over a year ago

@Awan, no. It's this or recursive querying, getting one layer at a time.

Collectives™ on Stack Overflow

MySQL: Fetch all hierarchy using parent_id from table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related