Is it possible to do CTE hierarchical recursive query in MySQL?

Fatias

I have a table with thousands of rows in which there is a relationship of child/parent between the rows. For each record I would like to have all the hierarchy related with that record.

As an example, I have the following table and for the id=19 I would like to return all its related children, which are [20, 21, 22]

I have done this with a while loop in MySQL and it is working. However it is is taking some time and I believe with recursive CTE would be faster. I tried the following code for a single record only:

WITH cte (id, name, parent_id) AS (

 SELECT   `id`,

       `name`,

       `parent_id`

 FROM    `products`

 WHERE   parent_id = 19

 UNION ALL

 SELECT   p.`id`,

       p.`name`,

       p.`parent_id`

 FROM    `products` p

 INNER JOIN cte

     ON p.`parent_id` = cte.id

)

SELECT * FROM cte;

However this code is returning an error of syntax "You have an error in your SQL syntax" and I am wondering if the MySQL version of Domo does not support this.

Thanks

MarkSnodgrass

Have you tried writing it like this:

SELECT   p.`id`,
       p.`name`,
       p.parent_id,
c.`name` as childname,
 FROM    `products` p
 INNER JOIN `products` c on p.id = c.parentid

You could change the inner join to a left join in case there are products with no children and you want to still include those.

Fatias

I believe that would work if everything belonged in the same hierarchy. However, if you have different hierarchies how do you distinguish them? For example, with the table below, your query would bring all the relations including the 30 and 31 which are not related with the id=19

jaeW_at_Onyx

MySQL 5.6 does not support CTEs.

https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query

this approach works.

Fatias

@jaeW_at_Onyx Thanks for letting me know that MySQL version in Domo is MySQL 5.6

I had followed that query example you sent for the 5.6 version and it definitely works when the parent_id < id. However in my use case, I have records in which the parent_id > id so I guess I'll have to stick with the while loop for now.

I will have that example into account if my dataset has an hierarchy where all records fulfill the condition parent_id < id but it would be good to know if Domo has in its pipeline any upgrade of the MySQL version in the near future.

jaeW_at_Onyx

@Fatias MySQL 5.65 is several years old. I'm pretty sure it's safe to say it's not a priority to upgrade the version of MySQL to something more modern.

you don't have to implemen using parent_id < id. you can implement where you accumulate the path

concat(path '+', id) as path

and just filter

WHERE path not like '%id%'