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).