Is there a better way to perform a variable-length recursive query using MySQL ver. 5.6?

Options
Casey_Tsujita
Casey_Tsujita Member
edited March 2023 in SQL DataFlows

I'm looking to perform a recursive query on a data hierarchy with an unknown amount of levels. I've found that making use of stored procedures (or simple self-joins if the there are very few levels) works well enough most of the time, but it can be messy and time-consuming. From what I understand, Common Table Expressions aren't possible on this version.

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @Casey_Tsujita

    Domo doesn't exactly support recursive queries as you've mentioned because they're on MySQL 5.6.

    For my use cases I ended up utilizing a Magic ETL dataflow and then self-join the dataset to itself to go down the recursive list and did it 20 times to handle up to 20 levels deep. It's not the most elegant solution but ended up being faster than MySQL. Ideally Domo would support recursion but right now it's not quite possible.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @Casey_Tsujita

    Domo doesn't exactly support recursive queries as you've mentioned because they're on MySQL 5.6.

    For my use cases I ended up utilizing a Magic ETL dataflow and then self-join the dataset to itself to go down the recursive list and did it 20 times to handle up to 20 levels deep. It's not the most elegant solution but ended up being faster than MySQL. Ideally Domo would support recursion but right now it's not quite possible.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories