Hierarchical data in MySQL: parents and children in one query | EXPLAIN EXTENDED
IT노트/MYSQL 2015. 2. 16. 00:21Hierarchical data in MySQL: parents and children in one query
Answering questions asked on the site.
Michael asks:
I was wondering how to implement a hierarchical query in MySQL (using the ancestry chains version) for a single row, such that it picks up the parents (if any) and any children (if any).
The idea is, I want to be able to jump in at any point, provide an Id of some sort, and be able to draw out the entire hierarchy for that Id, both upwards and downwards.
We need to combine two queries here:
- Original hierarchical query that returns all descendants of a given
id(a descendancy chain) - A query that would return all ancestors of a given
id(an ancestry chain)
An id can have only one parent, that’s why we can employ a linked list technique to build an ancestry chain, like shown in this article:
Here’s the query to to this (no functions required):
01.SELECT @r AS _id,02.(03.SELECT @r := parent04.FROM t_hierarchy05.WHERE id = _id06.) AS parent,07.@l := @l + 1 AS lvl08.FROM (09.SELECT @r := 1218,10.@l := 0,11.@cl := 012.) vars,13.t_hierarchy h14.WHERE @r <> 0To combine two queries, we can employ a simple UNION ALL.
The only problem that is left to preserve the correct level, since the ancestry chain query conts level backwards, and the hierarchical query will count it starting from zero.
Let’s create a sample table and see what we get:
Table creation details
Now, let’s try to UNION ALL the queries as is:
01.SELECT CONCAT(REPEAT(' ', lvl - 1), _id) AS treeitem, parent, lvl AS level02.FROM (03.SELECT @r AS _id,04.(05.SELECT @r := parent06.FROM t_hierarchy07.WHERE id = _id08.) AS parent,09.@l := @l + 1 AS lvl10.FROM (11.SELECT @r := 1218,12.@l := 0,13.@cl := 014.) vars,15.t_hierarchy h16.WHERE @r <> 017.ORDER BY18.lvl DESC19.) qi20.UNION ALL21.SELECT CONCAT(REPEAT(' ', level - 1), CAST(hi.id AS CHAR)), parent, level22.FROM (23.SELECT hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level24.FROM (25.SELECT @start_with := 1218,26.@id := @start_with,27.@level := 028.) vars, t_hierarchy29.WHERE @id IS NOT NULL30.) ho31.JOIN t_hierarchy hi32.ON hi.id = ho.idWe see that the hierarchical order is mangled: the first resultset is upside down, the second one is starting from level = 1.
To fix it, we need to change the code that calculates level a little.
First, we need to reverse the ancestry part.
This can be easily done by sorting it on lvl DESC:
01.SELECT CONCAT(REPEAT(' ', level - 1), _id) AS treeitem, parent, level02.FROM (03.SELECT @r AS _id,04.(05.SELECT @r := parent06.FROM t_hierarchy07.WHERE id = _id08.) AS parent,09.@l := @l + 1 AS level10.FROM (11.SELECT @r := 1218,12.@l := 0,13.@cl := 014.) vars,15.t_hierarchy h16.WHERE @r <> 017.ORDER BY18.level DESC19.) qiWe now have it in correct order but with wrong level values.
Since a level is essentially a rownum here, we can just calculate it as a rownum instead:
01.SELECT CONCAT(REPEAT(' ', level - 1), id) AS treeitem, parent, level02.FROM (03.SELECT _id AS id, parent,04.@cl := @cl + 1 AS level05.FROM (06.SELECT @r AS _id,07.(08.SELECT @r := parent09.FROM t_hierarchy10.WHERE id = _id11.) AS parent,12.@l := @l + 1 AS level13.FROM (14.SELECT @r := 1218,15.@l := 0,16.@cl := 017.) vars,18.t_hierarchy h19.WHERE @r <> 020.ORDER BY21.level DESC22.) qi23.) qoWe disregard the previously selected level at all, leaving it only inside the inline view qi for ordering purposes.
Now, we need to merge the descendancy tree query, but with levels starting from 6, not from 1.
Since this query will come after the ancestry one, we can use accumulated value of @cl (which we used to calculate level in the previous query) as a seed.
To do that, we can take the level returned by the descendancy tree query, and just add @cl to it.
The only problem it to determine when we should increment @cl and when we should add it to level.
We will just use a boolean field which will help us to tell the sets apart.
Here’s the query to do it:
01.SELECT CONCAT(REPEAT(' ', level - 1), CAST(id AS CHAR)),02.parent,03.level04.FROM (05.SELECT id, parent, IF(ancestry, @cl := @cl + 1, level + @cl) AS level06.FROM (07.SELECT TRUE AS ancestry, _id AS id, parent, level08.FROM (09.SELECT @r AS _id,10.(11.SELECT @r := parent12.FROM t_hierarchy13.WHERE id = _id14.) AS parent,15.@l := @l + 1 AS level16.FROM (17.SELECT @r := 1218,18.@l := 0,19.@cl := 020.) vars,21.t_hierarchy h22.WHERE @r <> 023.ORDER BY24.level DESC25.) qi26.UNION ALL27.SELECT FALSE, hi.id, parent, level28.FROM (29.SELECT hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level30.FROM (31.SELECT @start_with := 1218,32.@id := @start_with,33.@level := 034.) vars, t_hierarchy35.WHERE @id IS NOT NULL36.) ho37.JOIN t_hierarchy hi38.ON hi.id = ho.id39.) q40.) q2'IT노트 > MYSQL' 카테고리의 다른 글
| [mysql] function 정보 (0) | 2015.02.20 |
|---|---|
| [MySQL] DB 캐릭터셋 확인하기 (0) | 2015.02.19 |
| MySQL 프로시저(Procedure) (0) | 2015.02.16 |
| Incorrect string value 에러 : alter TABLE INC_ACT_INFO modify column ACT_CONTENT text character set utf8 collate utf8_general_ci; (0) | 2015.02.16 |
| 참고 DB 생성 (0) | 2015.02.14 |
