How to select recursively in a child parent design situation (in MySQL)?

Question

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

Answer 1

Without additional information such as transitive closure or nested sets, you need some kind of iteration (well, if you know that the length of the chain cant be longer than n you could do that number of left joins). Since 5.7 does not support recursive CTE:s you can express the recursion in a stored procedure instead:

DELIMITER $$
CREATE OR REPLACE PROCEDURE materialized_path
    (IN x BIGINT, IN acc text, OUT path TEXT)
BEGIN
    DECLARE parent_ID INT DEFAULT NULL;
    DECLARE tmppath text default '';

    select original_action_id
    from recurring_actions 
    where recurring_action_id = x into parent_ID;

    IF parent_ID IS NULL THEN
        SET path = rtrim(acc);
    ELSE     
        CALL materialized_path(parent_ID, rtrim(acc)||parent_ID||',', path);
    END IF;

END$$

DELIMITER ;

call materialized_path(5,'',@path); select @path;
Query OK, 0 rows affected, 1 warning (0.23 sec)

+--------+
| @path  |
+--------+
| 3,2,1, |
+--------+
1 row in set (0.00 sec)

Now you need another procedural part that splits @path into its components and there ordinal numbers.