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

MySQLqueryrecursiveselect

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

Best Answer

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.

Related Question