Recursive Loop - Parent/Child Tree

Question

I'm trying to recursively loop through and return all child_id's that have the root element of 9.

The structure:

 +-- 9
 |   +-- 8
 |       +-- 17
 |       +-- 33
 |       +-- 18
 |   +-- 22
 |       +-- 11
 |       +-- 4

Parent Child Link Table: (table name: elements_children)

+----+-----------+----------+
| id | parent_id | child_id |
+----+-----------+----------+
|  1 |         9 |        8 |
|  2 |         8 |       17 |
|  3 |         8 |       33 |
|  4 |         8 |       18 |
|  5 |         9 |       22 |
|  6 |        22 |       11 |
|  7 |        22 |        4 |
|  8 |         3 |        5 |
+----+-----------+----------+

The Desired Output - [8,17,33,18,22,11,4]

Procedure 1 (getChildren):

BEGIN

-- CREATE TEMP TABLE
DROP TABLE IF EXISTS total_children;
CREATE TEMPORARY TABLE total_children(
    id INT(11) NOT NULL auto_increment PRIMARY KEY, 
    child_id VARCHAR(255)
);

-- CALL TO PROCEDURE 2
CALL getNodes(rootNode);

-- SELECT child_id's FROM the temp table, then drop the table
SELECT child_id FROM total_children;
DROP TABLE total_children;

END

Procedure 2 (getNodes):

BEGIN
-- VARIABLES
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE childNode VARCHAR(255);

-- CURSOR1
DECLARE cur1 CURSOR FOR SELECT child_id FROM elements_children WHERE parent_id = parentNode;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;

-- START LOOP
myloop:LOOP
    FETCH cur1 INTO childNode;
    -- CHECK IF DONE IS TRUE
    IF(done) THEN 
        LEAVE myloop; 
    END IF;
    -- APPEND TO TOTAL
    INSERT INTO total_children(child_id) SELECT childNode;
    -- RECURSIVE
    CALL getNodes(childNode);
END LOOP myloop;
-- END LOOP

-- END CURSOR1
CLOSE cur1;

END

I'm getting error: recursion limit exceeded 200

I set the recursion limit to 200 and I know the procedure shouldn't recurse 200 times so there has to be an error in my code that isn't stopping the recursion, I believe something to do with the done variable in the myloop: LOOP.

Question: Why is my procedure generating this recursion error?

procedures calling procedures - oh my! There are times when that makes sense, but not sure this is one of them. It might be simpler to Have a loop that builds a list of child ID's, appending new ones each time through the loop until none are returned inside the loop. I'll see if I can write one up.... — Sloan Thrasher, Jun 05 '17 at 22:50
@SloanThrasher yeah I've been looking at the `cursor` to iterate through a select results, but still trying to come up with a solution. — , Jun 05 '17 at 22:53
things are simpler if you have a column to store the "path", eg `9/22/11` — Bohemian, Jun 06 '17 at 00:30
Just have two (main) columns; `ID` and `path`, where path is like a file path or directories. So a path of "a/b" means "I am a child of b, which is a child of a". Finding all sub node of x is just `where path like '%x_%'` etc. it may not help you depending on your exact need, but it avoids the recursive problem — Bohemian, Jun 06 '17 at 07:07

score 0 · Answer 1 · answered Jun 06 '17 at 00:13

I think the following stored procedure will yield the results you are asking for. I setup a table, and populated it with the data from your question:

DROP TABLE IF EXISTS `parent_child`;

CREATE TABLE `parent_child` (
    `id`            INT(10) UNSIGNED        NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    `parent_id`     INT(10) UNSIGNED        NOT NULL,
    `child_id`      INT(10) UNSIGNED        NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_parent_child_parent_id` (`parent_id`)
)
    ENGINE=MyISAM 
    AUTO_INCREMENT=1 
    DEFAULT CHARSET=utf8 
    COLLATE=utf8_unicode_ci
    COMMENT '';

INSERT INTO `parent_child`
(`id`,`parent_id`,`child_id`)
VALUES
('1','9','8'),
('2','8','17'),
('3','8','33'),
('4','8','18'),
('5','9','22'),
('6','22','11'),
('7','22','4'),
('8','3','5');

Then I created a procedure to progressively get children until there aren't any left to get.

DROP PROCEDURE GetChildren;

DELIMITER //

CREATE PROCEDURE GetChildren(root_id INT)
    BEGIN
        SET @list = root_id;
        SET @new_list = root_id;
        SET @maxDepth = 4;
        SET @depth = 0;

        WHILE (@new_list <> "" AND @depth < @maxDepth) DO
            SELECT @new_list as `new_list_before`,@list as `whole_list_before`;
            SET @depth = @depth + 1;
            SET @querystr = CONCAT("SELECT GROUP_CONCAT(`child_id`) as `children` INTO @c FROM `parent_child` WHERE `parent_id` in (?) AND (NOT (`child_id` IN (?)));");
            PREPARE stmt1 FROM @querystr;
            EXECUTE stmt1 USING @new_list,@list;
            IF @c <> "" THEN
                SET @list = CONCAT(@list,",",@c);
            END IF;
            SET @new_list = @c;
            SELECT @new_list as `new_list`,@list as `whole_list`;
            DEALLOCATE PREPARE stmt1;
        END WHILE;

        SELECT @list;
    END //
DELIMITER ;

Finally, here's how it would be called for a root id of 9:

CALL GetChildren(9);

This produces:

@list:
9,8,22,17,33,18

For reference, here's one of the selects executed:

SELECT GROUP_CONCAT(`child_id`) as `children` 
FROM `parent_child` 
WHERE `parent_id` in (9,8,22) AND (NOT `child_id` IN (9,8,22)) 
GROUP BY `parent_id`;

It could also be done with a temp table, using a select to insert rows for all children, with a unique key on the child IDs to prevent duplicates. Repeating a join between the real table and temp table until it doesn't increase in size. — Sloan Thrasher, Jun 06 '17 at 00:16
If you have a max number of depth levels that is reasonable _(< 10-ish)_, it could also be done with a single query. — Sloan Thrasher, Jun 06 '17 at 00:18

Recursive Loop - Parent/Child Tree

1 Answers1