CODEWITHSUNDEEP
mysqlnestedhierarchy
Initialis
Initialis

Reputation: 35

MySQL nested hierarchy select

I have a next nested hierarchy in MySQL: 

ROOT
|
+--Group1
|
+--Group2
|   |
|   +--SubGroup1
|   |
|   +--ABC
|
+--Group3
|  |
|  +--SubGroup2
|
+--Group4
|  |
|  +--SubGroup1
|     |
|     +--ABC

The table contents is:

mysql> select * from nest;
+----+------+------------+------+
| id | lft  | group_name | rgt  |
+----+------+------------+------+
|  1 |    1 | ROOT       |   20 |
|  2 |    2 | Group1     |    3 |
|  3 |    4 | Group2     |    9 |
|  4 |    5 | SubGroup1  |    8 |
|  5 |    6 | ABC        |    7 |
|  6 |   10 | Group3     |   13 |
|  7 |   11 | SubGroup2  |   12 |
|  8 |   14 | Group4     |   19 |
|  9 |   15 | SubGroup1  |   18 |
| 10 |   16 | ABC        |   17 |
+----+------+------------+------+

I am trying to select all tree from nested hierarchy at MySQL. 

SELECT
    CONCAT( REPEAT(' ', COUNT(parent.group_name) - 1), node.group_name) AS group_name
FROM 
    nest AS node,
    nest AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY
    node.group_name
ORDER BY
    node.lft;

And get results, with repeated childs only for first match:

+----------------+
| group_name     |
+----------------+
| ROOT           |
|  Group1        |
|  Group2        |
|      SubGroup1 |
|        ABC     |
|  Group3        |
|   SubGroup2    |
|  Group4        |
+----------------+

How I can get repeated results for Group4, similarly Group2? Like this:

+----------------+
| group_name     |
+----------------+
| ROOT           |
|  Group1        |
|  Group2        |
|      SubGroup1 |
|        ABC     |
|  Group3        |
|   SubGroup2    |
|  Group4        |
|      SubGroup1 |
|        ABC     |
+----------------+

Thank you.

Upvotes: 3

Views: 348

Answers (1)

Pantamtuy
Pantamtuy

Reputation: 243

Change

GROUP BY
node.group_name

To

GROUP BY
node.id

Like this

SELECT
    CONCAT( REPEAT(' ', COUNT(parent.group_name) - 1), node.group_name) AS group_name
FROM 
    nest AS node,
    nest AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY
    node.id
ORDER BY
    node.lft

Upvotes: 1

Related Questions