Alex Rivera | Logout

Hierarchical table - how to get paths of the items [linked lists in MySQL]

Asked 2012-08-20T08:47:27.950
8

I have a hierarchical table in MySQL: parent field of each item points to the id field of its parent item. For each item I can get the list of all its parents [regardless the depth] using the query described here. With GROUP_CONCAT I get the full path as a single string:

SELECT GROUP_CONCAT(_id SEPARATOR ' > ') FROM (
SELECT  @r AS _id,
         (
         SELECT  @r := parent
         FROM    t_hierarchy
         WHERE   id = _id
         ) AS parent,
         @l := @l + 1 AS lvl
 FROM    (
         SELECT  @r := 200,
                 @l := 0
         ) vars,
         t_hierarchy h
WHERE    @r <> 0
ORDER BY lvl DESC
) x

I can make this work only if the id of the item is fixed [it's 200 in this case].

I want to do the same for all rows: retrieve the whole table with one additional field (path) which will display the full path. The only solution that comes to my mind is to wrap this query in another select, set a temporary variable @id and use it inside the subquery. But it doesn't work. I get NULLs in the path field.

SELECT @id := id, parent, (
    SELECT GROUP_CONCAT(_id SEPARATOR ' > ') FROM (
    SELECT  @r AS _id,
             (
             SELECT  @r := parent
             FROM    t_hierarchy
             WHERE   id = _id
             ) AS parent,
             @l := @l + 1 AS lvl
     FROM    (
             SELECT  @r := @id,
                     @l := 0
             ) vars,
             t_hierarchy h
    WHERE    @r <> 0
    ORDER BY lvl DESC
    ) x
) as path
 FROM t_hierarchy

P.S. I know I can store the paths in a separate field and update them when inserting/updating, but I need a solution ba

mysql sql linked-list hierarchical-data
Edit
Report

1 Answer

2

Consider the difference between the following two queries:

SELECT @id := id as id, parent, (
    SELECT concat(id, ': ', @id)
) as path
FROM t_hierarchy;

SELECT @id := id as id, parent, (
    SELECT concat(id, ': ', _id)
    FROM (SELECT @id as _id) as x
) as path
FROM t_hierarchy;

They look nearly identical, but give dramatically different results. On my version of MySQL, _id in the second query is the same for each row in its result set, and equal to the id of the last row. However, that last bit is only true because I executed the two queries in the order given; after SET @id := 1, for example, I can see that _id is always equal to the value in the SET statement.

So what's going on here? An EXPLAIN yields a clue:

mysql>     explain SELECT @id := id as id, parent, (
    ->         SELECT concat(id, ': ', _id)
    ->         FROM (SELECT @id as _id) as x
    ->     ) as path
    ->     FROM t_hierarchy;
+----+--------------------+-------------+--------+---------------+------------------+---------+------+------+----------------+
| id | select_type        | table       | type   | possible_keys | key              | key_len | ref  | rows | Extra          |
+----+--------------------+-------------+--------+---------------+------------------+---------+------+------+----------------+
|  1 | PRIMARY            | t_hierarchy | index  | NULL          | hierarchy_parent | 9       | NULL | 1398 | Using index    |
|  2 | DEPENDENT SUBQUERY | <derived3>  | system | NULL          | NULL             | NULL    | NULL |    1 |                |
|  3 | DERIVED            | NULL        | NULL   | NULL          | NULL             | NULL    | NULL | NULL | No tables used |
+----+--------------------+-------------+--------+---------------+------------------+---------+------+------+----------------+
3 rows in set (0.00 sec)

                        
                        

                            answered 2012-08-29T18:36:12.220

Your Answer