MySQL Recursive get all child from parent
if you want to get all level child of a particular parent then you should try this
select id,
name,
parent
from (select * from tablename
order by parent, id) tablename,
(select @pv := '1') initialisation
where find_in_set(parent, @pv) > 0
and @pv := concat(@pv, ',', id)
Try this one , much simple and easy to understand.
(but supports only one hierarchy level)
SET @pv = 1;
select * from tablename
where FIND_IN_SET(parentrecordID,@pv) and !isnull(@pv:= concat(@pv, ',', id));
I tried this one
select id from (select * from roles order by parent_role, id) roles,(select @pv := '1') initialisation
where find_in_set(parent_role, @pv) > 0
and @pv := concat(@pv, ',', id)
but it works for depth 2 only, i need to make it work with more depth since i have 8 levels
In my opinion the key to finding all children in a hierarchical table structure is to first find the path to the parent, and then use FIND_IN_SET
to see if the requested node is in the path. It's easier and more efficient to search up than down, because the link to the parent is already there in the table.
So let's start with a hierarchy like this:
1 Pets
├─ 2 Dogs
│ ├─ 3 Katie
├─ 4 Cats
│ ├─ 5 George
│ ├─ 6 Pete
│ ├─ 7 Alice
├─ 8 Other
│ ├─ 9 Rabbits
│ │ ├─ 10 Noah
│ │ ├─ 11 Teddy
│ │ ├─ 12 Bella
│ ├─ 13 Rats
│ │ ├─ 14 Henry
And now you want to find all children under the category Other
(with the category included) then the expected result would be:
8,9,10,11,12,13,14
Now let's have a look at the hierarchical path of Henry. The parent for Henry (14) is Rats (13), which has parent Other (8) and finally Pets (1). If we use the ID's to make a path for Henry it would look like this:
1,8,13,14
And this is where the MySQL function FIND_IN_SET
kicks in. With FIND_IN_SET
you can filter results where a variable can be found in a comma separated list. In this example we are looking for all children in the category Other (8) and we can simply use FIND_IN_SET(8, path)
.
To get the path for a hierarchical table I would like to refer to my answer in the post here MySql: ORDER BY parent and child. We will just change the dash to a comma, so we can use the FIND_IN_SET
function.
The example above would look like this in a hierarchical table:
+----+--------+---------+
| id | parent | name |
+----+--------+---------+
| 1 | NULL | Pets |
| 2 | 1 | Dogs |
| 3 | 2 | Katie |
| 4 | 1 | Cats |
| 5 | 4 | George |
| 6 | 4 | Pete |
| 7 | 4 | Alice |
| 8 | 1 | Other |
| 9 | 8 | Rabbits |
| 10 | 9 | Noah |
| 11 | 9 | Teddy |
| 12 | 9 | Bella |
| 13 | 8 | Rats |
| 14 | 13 | Henry |
+----+--------+---------+
In my approach I will use a procedure that will recursively call itself and keep prepending the path with the parent of the requested id
until it reaches the NULL
parent.
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PATH`(IN `input` INT, OUT `output` VARCHAR(128))
BEGIN
DECLARE _id INT;
DECLARE _parent INT;
DECLARE _path VARCHAR(128);
SET `max_sp_recursion_depth` = 50;
SELECT `id`, `parent`
INTO _id, _parent
FROM `database`.`table`
WHERE `table`.`id` = `input`;
IF _parent IS NULL THEN
SET _path = _id;
ELSE
CALL `PATH`(_parent, _path);
SELECT CONCAT(_path, ',', _id) INTO _path;
END IF;
SELECT _path INTO `output`;
END $$
DELIMITER ;
We need these results in a SELECT
query so we need a FUNCTION
too that wraps the results of the PROCEDURE
.
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GETPATH`(`input` INT) RETURNS VARCHAR(128)
BEGIN
CALL `PATH`(`input`, @path);
RETURN @path;
END $$
DELIMITER ;
Now we can use the path in a query. On a table with 10000 rows it takes just over a second on my workstation.
SELECT `id`, `parent`, `name`, GETPATH(`id`) `path` FROM `database`.`table`;
Example output:
+----+--------+---------+-----------+
| id | parent | name | path |
+----+--------+---------+-----------+
| 1 | NULL | Pets | 1 |
| 2 | 1 | Dogs | 1,2 |
| 3 | 2 | Katie | 1,2,3 |
| 4 | 1 | Cats | 1,4 |
| 5 | 4 | George | 1,4,5 |
| 6 | 4 | Pete | 1,4,6 |
| 7 | 4 | Alice | 1,4,7 |
| 8 | 1 | Other | 1,8 |
| 9 | 8 | Rabbits | 1,8,9 |
| 10 | 9 | Noah | 1,8,9,10 |
| 11 | 9 | Teddy | 1,8,9,11 |
| 12 | 9 | Bella | 1,8,9,12 |
| 13 | 8 | Rats | 1,8,13 |
| 14 | 13 | Henry | 1,8,13,14 |
+----+--------+---------+-----------+
And to find all children of Other (8) with Other itself also included we can use the same query and filter with FIND_IN_SET
:
SELECT `id`, `parent`, `name`, GETPATH(`id`) `path` FROM `database`.`table` WHERE FIND_IN_SET(8, GETPATH(`id`));
And finally here is the result. We put a recursion limit of 50 levels in the procedure, but besides that we have no limit in the depth.
+----+--------+---------+-----------+
| id | parent | name | path |
+----+--------+---------+-----------+
| 8 | 1 | Other | 1,8 |
| 9 | 8 | Rabbits | 1,8,9 |
| 10 | 9 | Noah | 1,8,9,10 |
| 11 | 9 | Teddy | 1,8,9,11 |
| 12 | 9 | Bella | 1,8,9,12 |
| 13 | 8 | Rats | 1,8,13 |
| 14 | 13 | Henry | 1,8,13,14 |
+----+--------+---------+-----------+
7 rows in set (0,01 sec)
If you want to have a single value instead of rows then you might want to use GROUP_CONCAT
like this:
SELECT GROUP_CONCAT(`id`) `children` FROM `database`.`table` WHERE FIND_IN_SET(8, GETPATH(`id`));
Giving you the following result:
+--------------------+
| children |
+--------------------+
| 8,9,10,11,12,13,14 |
+--------------------+
1 row in set (0,00 sec)