I have a scenario where I regularly need to maintain records in a table that defines workflow order definitions to add and remove stages which puts the IDs in a non-deal order (cant change the IDs after the fact).
I need an order by (or other) method to select the records and show the order they will execute to easily manage them but cant find a solution though believe there would be an obvious one I am missing. NULL in parent_id is the start of the worflow.
Example Simple Records:
id strategy_id stage parent_id
74 15 REMINDER_01 NULL
99 15 WARNING_06 74
76 15 WARNING_05 75
91 15 WARNING_08 76
78 15 WARNING_07 91
75 15 DEMAND_01 99
Order I need them returned to easily work on them and see the order:
id strategy_id stage parent_id
74 15 REMINDER_01 NULL
99 15 WARNING_06 74
75 15 DEMAND_01 99
76 15 WARNING_05 75
91 15 WARNING_08 76
78 15 WARNING_07 91
I have tried searching for a solution based on the concept but believe I am using the wrong terminology so not getting anything that is a match.
Use the recursive method with database to generate your solution. I have used Postgres with version 16 to generate solution. Same solution will work for the MySql as well.
Working Query:
WITH RECURSIVE orderWorkflow AS (
SELECT id, strategy_id, stage, parent_id, 0 AS level
FROM mytable
WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.strategy_id, t.stage, t.parent_id, wo.level + 1 AS level
FROM mytable t
INNER JOIN orderWorkflow wo ON t.parent_id = wo.id
)
SELECT id, strategy_id, stage, parent_id
FROM orderWorkflow
ORDER BY level, id;
Working Demo: