MYSQL 8, Hierarchy Adjacency list, How each rows to know the top node of the alias root.?

All we need is an easy explanation of the problem, so here it is.

MYSQL 8.
Here are some rows I have in a table containing hierarchical data.

+-----+-----------+------+--------------------------------------------+
| id  | parent_id | sort | name                                       |
+-----+-----------+------+--------------------------------------------+
|  30 |      NULL |    1 | AKTIVA                                     |
|  40 |        30 |    2 | Aktiva Lancar                              |
|  41 |        40 |    3 | Kas & Bank                                 |
|  42 |        41 |    4 | Kas                                        |
|  43 |        41 |    5 | Kas Tunai USD                              |
|  44 |        41 |    6 | Kas Tunai Di Brankas                       |
| 213 |      NULL |   56 | HUTANG & MODAL                             |
|  88 |       213 |   57 | Hutang                                     |
|  89 |        88 |   58 | Hutang Jangka Pendek                       |
| 106 |        89 |   59 | Hutang Dagang Lainnya                      |
|  93 |        89 |   60 | Hutang SDL                                 |
|  94 |        89 |   61 | Hutang WHL                                 |
+-----+-----------+------+--------------------------------------------+

I use CTE to construct the data without much use of SELF JOIN
So far, here are the query I’ve used:

WITH RECURSIVE account_path (root, id, parent_id, name, lvl, `sort`, account_roll_up_id) AS
                   (
                       SELECT id AS root, id, parent_id, `name`, 0 lvl, `sort`, account_roll_up_id
                       FROM account_type
                       WHERE id IN (30, 213) 

                       UNION ALL

                       SELECT 30 AS root, c.id, c.parent_id, c.name, (cp.lvl + 1), c.sort, c.account_roll_up_id
                       FROM account_path AS cp
                                JOIN account_type AS c ON cp.id = c.parent_id
                   )

SELECT root, id, parent_id, CONCAT(REPEAT("  ", lvl), name) AS name, lvl, `sort`, account_roll_up_id FROM account_path
ORDER BY `sort`;

The results are as follows:

+------+------+-----------+------------------------------------------------+------+------+--------------------+
| root | id   | parent_id | name                                           | lvl  | sort | account_roll_up_id |
+------+------+-----------+------------------------------------------------+------+------+--------------------+
|   30 |   30 |      NULL | AKTIVA                                         |    0 |    1 |               NULL |
|   30 |   40 |        30 |   Aktiva Lancar                                |    1 |    2 |                  2 |
|   30 |   41 |        40 |     Kas & Bank                                 |    2 |    3 |                  2 |
|   30 |   42 |        41 |       Kas                                      |    3 |    4 |                  2 |
|   30 |   43 |        41 |       Kas Tunai USD                            |    3 |    5 |                  2 |
|   30 |   44 |        41 |       Kas Tunai Di Brankas                     |    3 |    6 |                  2 |
|  213 |  213 |      NULL | HUTANG & MODAL                                 |    0 |   56 |               NULL |
|   30 |   88 |       213 |   Hutang                                       |    1 |   57 |               NULL |
|   30 |   89 |        88 |     Hutang Jangka Pendek                       |    2 |   58 |                  9 |
|   30 |  106 |        89 |       Hutang Dagang Lainnya                    |    3 |   59 |                  9 |
|   30 |   93 |        89 |       Hutang SDL                               |    3 |   60 |                  9 |
|   30 |   94 |        89 |       Hutang WHL                               |    3 |   61 |                  9 |
+------+------+-----------+------------------------------------------------+------+------+--------------------+

My problem is, I need each line to know the top node of the alias root.
I need the following data forms.

+------+------+-----------+------------------------------------------------+------+------+--------------------+
| root | id   | parent_id | name                                           | lvl  | sort | account_roll_up_id |
+------+------+-----------+------------------------------------------------+------+------+--------------------+
|   30 |   30 |      NULL | AKTIVA                                         |    0 |    1 |               NULL |
|   30 |   40 |        30 |   Aktiva Lancar                                |    1 |    2 |                  2 |
|   30 |   41 |        40 |     Kas & Bank                                 |    2 |    3 |                  2 |
|   30 |   42 |        41 |       Kas                                      |    3 |    4 |                  2 |
|   30 |   43 |        41 |       Kas Tunai USD                            |    3 |    5 |                  2 |
|   30 |   44 |        41 |       Kas Tunai Di Brankas                     |    3 |    6 |                  2 |
|  213 |  213 |      NULL | HUTANG & MODAL                                 |    0 |   56 |               NULL |
|  213 |   88 |       213 |   Hutang                                       |    1 |   57 |               NULL |
|  213 |   89 |        88 |     Hutang Jangka Pendek                       |    2 |   58 |                  9 |
|  213 |  106 |        89 |       Hutang Dagang Lainnya                    |    3 |   59 |                  9 |
|  213 |   93 |        89 |       Hutang SDL                               |    3 |   60 |                  9 |
|  213 |   94 |        89 |       Hutang WHL                               |    3 |   61 |                  9 |
+------+------+-----------+------------------------------------------------+------+------+--------------------+

Thank you for the help.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Replace:

SELECT 30 AS root

with this:

SELECT cp.root

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply