How to get parentlist from child id – Mysql Function

A

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS TEXT CHARSET utf8
READS SQL DATA
BEGIN

DECLARE v_rec INT DEFAULT 0;

DECLARE done INT DEFAULT FALSE;
DECLARE v_res TEXT DEFAULT ”;
DECLARE v_papa INT;
DECLARE v_papa_papa INT DEFAULT -1;
DECLARE csr CURSOR FOR
SELECT _id,parent_id — @n:=@n+1 as rownum,T1.*
FROM
(SELECT @r AS _id,
(SELECT @r := parent_id FROM users WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := the_id, @l := 0,@n:=0) vars,
users m
WHERE @r 0
) T1
WHERE T1.parent_id IS NOT NULL
ORDER BY T1.lvl DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN csr;
read_loop: LOOP
FETCH csr INTO v_papa,v_papa_papa;
SET v_rec = v_rec+1;
IF done THEN
LEAVE read_loop;
END IF;
— add first
IF v_rec = 1 THEN
SET v_res = v_papa_papa;
END IF;
SET v_res = CONCAT(v_res,’,’,v_papa);
END LOOP;
CLOSE csr;
RETURN v_res;
END

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