Based on this question, have any way to implement a parent level count?
Find highest level of a hierarchical field: with vs without CTEs
Example table:
+----+-----------+
| id | parent_id |
+----+-----------+
| 1 | 0 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 3 |
| 7 | 3 |
| 8 | 5 |
| 9 | 5 |
| 10 | 7 |
+----+-----------+
Function:
DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$
CREATE FUNCTION `junk`.`GetFamilyTree` (GivenID INT, Count INT) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE rv,q,queue,queue_children VARCHAR(1024);
DECLARE queue_length,front_id,pos INT;
SET rv = '';
SET queue = GivenID;
SET queue_length = 1;
SET count_limit = Count;
WHILE queue_length > 0 && count_limit > 0 DO
SET front_id = FORMAT(queue,0);
IF queue_length = 1 THEN
SET queue = '';
ELSE
SET pos = LOCATE(',',queue) + 1;
SET q = SUBSTR(queue,pos);
SET queue = q;
END IF;
SET queue_length = queue_length - 1;
SELECT IFNULL(qc,'') INTO queue_children
FROM (SELECT GROUP_CONCAT(id) qc
FROM pctable WHERE parent_id = front_id) A;
IF LENGTH(queue_children) = 0 THEN
IF LENGTH(queue) = 0 THEN
SET queue_length = 0;
END IF;
ELSE
IF LENGTH(rv) = 0 THEN
SET rv = queue_children;
ELSE
SET rv = CONCAT(rv,',',queue_children);
END IF;
IF LENGTH(queue) = 0 THEN
SET queue = queue_children;
ELSE
SET queue = CONCAT(queue,',',queue_children);
END IF;
SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
END IF;
END WHILE;
RETURN rv;
END $$
I need implement a count on that function to limit iterate based on parameter passed on query.
SELECT id,parent_id, GetFamilyTree(1, 3) FROM pctable;
Detail: first param is id of desired family tree secund param is a level depth of descendants
Example result of example table:
+----+-----------+
| id | parent_id |
+----+-----------+
| 1| 0 |
| 2| 1 |
| 3| 1 |
| 4| 2 |
| 5| 2 |
| 6| 3 |
| 7| 3 |
+----+-----------+
It is possible?
In this case, the count not working as well...its counting every tree item, not every tree level.
GetFamilyTree(id,3)
to behave. Do you wantGetFamilyTree(id,3)
to bring back a tree whose height is 3 or do you want the return list liimited by the first three ids in the output ?