Let's take into consideration the following tables:
CREATE TABLE actions ( id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, user_id BIGINT(20) unsigned NOT NULL ); CREATE TABLE recurring_actions ( original_action_id BIGINT(20) unsigned NOT NULL, recurring_action_id BIGINT(20) unsigned NOT NULL );
The data in each table just for an example is as follows:
actions id name user_id 1 fdfdk 3 2 43434 3 3 43334 5 4 sdkk 6 5 zz 7 6 ll 3 recurring_actions original_action_id recurring_action_id 1 2 4 6 2 3 3 5
How can someone query and fetch all the chain of recurring action ids that lead to the last child with id 5 ?
Expected result should be [1, 2, 3, 5] (including 5 is ok)
I can solve this so far only by recursive querying by application code. Get the original action then if found, query again and so on. Recursive consecutive queries initiated by PHP/C# or whatever code used.
I want to do this instead in one recursive (or other solution) MySQL query
The answer should focus only on a query solution (if possible) and not in organizing the database in another way. I am aware of other possible database designs which are more suitable for child parent relationships (such as closure tables, nested sets etc).