Is there a better way to perform a variable-length recursive query using MySQL ver. 5.6?
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.
Best Answer
-
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!**1
Answers
-
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!**1
Categories
- All Categories
- 1.2K Product Ideas
- 1.2K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 525 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 437 Beast Mode
- 23 Variables
- 513 Automate
- 115 Apps
- 390 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive