GetFamilyTree

DELIMITER $$

DROP FUNCTION IF EXISTS `GetFamilyTree`$$

CREATE FUNCTION `GetFamilyTree`(GivenID INT,LevelLimit INT) RETURNS VARCHAR(1024) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE rv,q,queue,queue_children,front_rc,level_part VARCHAR(1024);
DECLARE queue_length,front_id,front_ht,pos,curr_level INT;
SET rv = ”;
SET queue = CONCAT(GivenID,’:0′);
SET queue_length = 1;
WHILE queue_length > 0 DO
SET front_id = FORMAT(queue,0);
IF queue_length = 1 THEN
SET front_rc = queue;
SET queue = ”;
SET pos = LOCATE(‘:’,front_rc);
ELSE
SET pos = LOCATE(‘,’,queue);
SET front_rc = LEFT(queue,pos – 1);
SET q = SUBSTR(queue,pos + 1);
SET queue = q;
SET pos = LOCATE(‘:’,front_rc);
END IF;
SET front_id = LEFT(front_rc,pos – 1);
SET front_ht = SUBSTR(front_rc,pos + 1);
SET queue_length = queue_length – 1;
SELECT IFNULL(qc,”) INTO queue_children
FROM (SELECT GROUP_CONCAT(CONCAT(id,’:’,front_ht+1)) qc
FROM users 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
IF front_ht < LevelLimit THEN
SET rv = queue_children;
END IF;
ELSE
IF front_ht 0 THEN
SET curr_level = 1;
WHILE curr_level <= LevelLimit DO
SET level_part = CONCAT(':',curr_level);
SET rv = REPLACE(rv,level_part,'');
SET curr_level = curr_level + 1;
END WHILE;
END IF;
RETURN rv;
END$$

DELIMITER ;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s