Concatenate multiple rows of hierarchical data

Question

Ticket:

id_ticket |id_ticket_category |
----------|-------------------|
1         |8                  |
2         |8                  |
3         |13                 |

Category:

id_category |id_parent |label                             |id_category_type |
------------|----------|----------------------------------|-----------------|
1           |-1        |Demande d'information             |1                |
2           |-1        |Réclamation                       |1                |
3           |1         |Autre                             |2                |
4           |1         |Mairie                            |2                |
5           |1         |Stationnement                     |2                |
6           |2         |Autre                             |2                |
8           |3         |Autre                             |3                |
9           |4         |Réservation de salle              |3                |
10          |4         |Autre                             |3                |
11          |5         |Horaires de stationnement         |3                |
12          |5         |Autre                             |3                |
13          |6         |Autre                             |3                |
16          |7         |Autre                             |3                |

Notice that the id_ticket_category column in the Ticket table refers to the id_category in the Category table.

I want to return the count and concat row who i have a id_ticket_category (id_category) with id_parentof element.

Example:

For the id_ticket_category = 8 i look on the Category table

id_category |id_parent |label                             |id_category_type |
------------|----------|----------------------------------|-----------------|
8           |3         |Autre                             |3                |

I look is id_parent is not equal at -1

id_category |id_parent |label                             |id_category_type |
------------|----------|----------------------------------|-----------------|
3           |1         |Autre                             |2                |

I look is id_parent is not equal at -1

id_category |id_parent |label                             |id_category_type |
------------|----------|----------------------------------|-----------------|
1           |-1        |Demande d'information             |1                |

id_parent is equal -1 i print my result

Category                 |count|
-------------------------|-----|
Autre-Demande Information| 2   |

Actually i have this request:

SELECT CONCAT(c.label, '-', parents.label), c.id_parent  
FROM Category c INNER JOIN
(SELECT id_category AS id, label FROM category c WHERE id_parent = -1) AS parents
ON parents.id = c.id_parent 
WHERE  c.id_category_type < 3 
ORDER BY c.id_category_type;

so does anyone know how to improve this?

Are you set on using MySQL? I'm asking because other RDBMS have recursive properties for exactly what you need. — KindaTechy, Dec 22 '17 at 19:37
Is there a limit on depth? If max depth is known the solution is simple. — Salman A, Dec 26 '17 at 07:37
@Mercer your structure allows unlimited depth... I am asking if it is OK to hardcode, for example 3, as the maximum depth? Also, are you OK with alternate tree structure? — Salman A, Dec 26 '17 at 09:09
Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — M Khalid Junaid, Dec 26 '17 at 11:09

score 2 · Answer 1 · answered Dec 26 '17 at 07:31

You can create the following prcedure to perform the task,

Following is the Procedure :

DROP PROCEDURE IF EXISTS getConcatRows;

DELIMITER //

CREATE PROCEDURE getConcatRows(IN id_tc int)
 BEGIN

 DECLARE id_parent_tc int;
 SET id_parent_tc = id_tc;

 DROP temporary table IF EXISTS temp_category;

 # Create temporarty table to store hirarchy
 create TEMPORARY table temp_category 
 AS 
 select 0 as id_category,category.label,-1 as id_tc from category limit 1;


 WHILE id_parent_tc <> -1 
 DO
   insert into temp_category select id_category,label,id_tc from category WHERE id_category=id_parent_tc;

   SELECT id_parent into id_parent_tc FROM category WHERE id_category=id_parent_tc;
 END WHILE;

 # Subtract 1 since it will contain the total herarchy
 select GROUP_CONCAT(distinct label),count(1)-1 from temp_category t where t.id_tc = id_tc group by id_tc;
 END //

 DELIMITER ;

 CALL getConcatRows(8);
 # Will return the required output

Salman A · Accepted Answer · 2017-12-26T11:05:49.457

Seems like you need to group tickets by category and display the count and the path. This works if category tree is maximum 3 level deep:

SELECT
    id_ticket_category,
    CONCAT_WS(' > ', c3.label, c2.label, c1.label) AS `category`,
    COUNT(*) AS `count`
FROM ticket
LEFT JOIN category c1 ON ticket.id_ticket_category = c1.id_category
LEFT JOIN category c2 ON c1.id_parent = c2.id_category
LEFT JOIN category c3 ON c2.id_parent = c3.id_category
GROUP BY id_ticket_category, category

Output:

+--------------------+---------------------------------------+-------+
| id_ticket_category | category                              | count |
+--------------------+---------------------------------------+-------+
|                  8 | Demande d'information > Autre > Autre |     2 |
|                 13 | Réclamation > Autre > Autre           |     1 |
+--------------------+---------------------------------------+-------+

Converting to Nested Set Model

The nested set model is more efficient at retrieving hierarchical data. However, maintaining it could be difficult. If you are interested then you first need to transform your data into this:

+-------------+-----------+---------------------------+------------------+------+------+
| id_category | id_parent | label                     | id_category_type | l    | r    |
+-------------+-----------+---------------------------+------------------+------+------+
|           1 |        -1 | Demande d'information     |                1 |    2 |   19 |
|           2 |        -1 | Réclamation               |                1 |   20 |   25 |
|           3 |         1 | Autre                     |                2 |    3 |    6 |
|           4 |         1 | Mairie                    |                2 |    7 |   12 |
|           5 |         1 | Stationnement             |                2 |   13 |   18 |
|           6 |         2 | Autre                     |                2 |   21 |   24 |
|           8 |         3 | Autre                     |                3 |    4 |    5 |
|           9 |         4 | Réservation de salle      |                3 |    8 |    9 |
|          10 |         4 | Autre                     |                3 |   10 |   11 |
|          11 |         5 | Horaires de stationnement |                3 |   14 |   15 |
|          12 |         5 | Autre                     |                3 |   16 |   17 |
|          13 |         6 | Autre                     |                3 |   22 |   23 |
|          16 |         7 | Autre                     |             NULL | NULL | NULL |
+-------------+-----------+---------------------------+------------------+------+------+

Then you can use this query (which has no limit on depth of category tree):

SELECT
    id_ticket_category,
    GROUP_CONCAT(DISTINCT CONCAT(parent.label, ' (', parent.id_category, ')') ORDER BY parent.l SEPARATOR ' > ') AS category,
    COUNT(DISTINCT id_ticket) AS `count`
FROM ticket
LEFT JOIN category ON ticket.id_ticket_category = category.id_category
LEFT JOIN category parent ON category.l BETWEEN parent.l AND parent.r
GROUP BY id_ticket_category

Output:

+--------------------+---------------------------------------------------+-------+
| id_ticket_category | category                                          | count |
+--------------------+---------------------------------------------------+-------+
|                  8 | Demande d'information (1) > Autre (3) > Autre (8) |     2 |
|                 13 | Réclamation (2) > Autre (6) > Autre (13)          |     1 |
+--------------------+---------------------------------------------------+-------+

Unfortunately it is sort of hard-coded. If your data is maximum 4 level deep then you need another `LEFT JOIN category c4 ON c3.id_parent = c4.id_category` and so on. — Salman A, Dec 26 '17 at 10:27
There is another solution but it requires you change your data structure. That should not be a problem but the new fields need to be updated every time a category is added/removed, and that could be a pain-in-the-bottom if using SQL. — Salman A, Dec 26 '17 at 10:29
I can not make disappear the `id_ticket_category `column disappear from the output result i have an error — Mercer, Dec 26 '17 at 10:36
@Mercer I made small changes to the `GROUP BY` clause in the query. Now you can remove it. — Salman A, Dec 26 '17 at 10:49

Concatenate multiple rows of hierarchical data

2 Answers2