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

Fatias
Fatias Member
edited March 2023 in SQL DataFlows

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]

image.png




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

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Fatias
    Fatias Member

    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

    image.png


  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    MySQL 5.6 does not support CTEs.

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

    this approach works.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Fatias
    Fatias Member

    @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
    jaeW_at_Onyx Coach

    @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%'


    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories