MySql recursive logic

Question

I'm trying to get munus & submenus on the basis of roles (specified in other table). On the basis of role, ex. if I chose MenuIDs: 1,2,5 I should get all submenus of M1, M2 & M3. MenuParentID specifies MenuId of the parent.

 MenuID MenuParentID   MenuName           MenuNavigateUrl       HasSubMenus 
 1      -1             M1                 1.aspx                0 
 2      -1             M2                 #                     1 
 3       2             M2.1               2.aspx                0 
 4       2             M2.2               3.aspx                0 
 5      -1             M3                 #                     1 
 6       5             M3.1               #                     1
 7       5             M3.2               #                     1 
 8       6             M3.1.1             4.aspx                0 
 9       6             M3.1.2             5.aspx                0 
10       7             M3.2.1             6.aspx                0 
11       7             M3.2.2             7.aspx                0 
12      -1             M4                 #                     1 
13      12             M4.1               8.aspx                0 
14      12             M4.2               9.aspx                0 

Here's what I did:

DELIMITER $$

DROP FUNCTION IF EXISTS `myDB`.`GetPermissions`$$

CREATE DEFINER=`root`@`%` FUNCTION `GetPermissions`(
rootMenuID int(11)
) RETURNS varchar(200) CHARSET latin1
BEGIN
DECLARE menuIdList VARCHAR(100);
DECLARE menu_id INT(11);
DECLARE record_not_found INT DEFAULT 0;
DECLARE getMenuCursor CURSOR FOR SELECT DISTINCT(MenuId) FROM MenuIdListTable;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1; 
CREATE TEMPORARY TABLE MenuIdListTable(MenuId INT(11) NULL);
SET menuIdList = ',';
IF((SELECT COUNT(*) FROM menus WHERE MenuParentID = rootMenuID) > 0) THEN
    INSERT INTO MenuIdListTable(MenuID) SELECT MenuID FROM menus WHERE    
MenuParentID = rootMenuID; 
    OPEN getMenuCursor;
    read_loop: LOOP
        FETCH getMenuCursor INTO menu_id;
        IF record_not_found THEN 
           LEAVE read_loop; 
        END IF;
        SET menuIdList = CONCAT(menuIdList,menu_id,',');
    END LOOP read_loop;
END IF;
DROP TEMPORARY TABLE MenuIdListTable;
SET menuIdList = SUBSTR(menuIdList,1,LENGTH(menuIdList)-1);
RETURN menuIdList;
END$$

DELIMITER ;

But I'm not able to apply recursive logic to get all sub menus. Ex. For 'M3' (MenuID = 5), I'm getting submenus 'M3.1' & 'M3.2'; but not their submenus. i.e For 'M3.1': 'M3.1.1', 'M3.1.2' and for 'M3.2': 'M3.2.1', 'M3.2.2'. Also problem will persist if one of them have submenus! Please help.

Answer 1

Aww the joy of "connect by prior" from plsql or CTE from other dbs missing mysql.

Since my brain currently does not have the capacity to fully parse your given mysql function, it just throws a reference to: http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ It points you to some good explainations (sometimes evil... like the tree strucutre in only one select) regarding hierarchicals in mysql

Answer 2

Try this code:

DROP TABLE IF EXISTS temp;
CREATE TABLE temp(id int,MenuID int,MenuName varchar(50),MenuParentID int,HasSubMenus int) SELECT (@id:=@id+1) as id,MenuID,MenuName,MenuParentID,HasSubMenus from menus,
(SELECT @id:=0) id where MenuParentID = 5;
select * from temp;
DROP TABLE IF EXISTS output;
CREATE TABLE output(MenuID int,MenuName varchar(50),MenuParentID int,HasSubMenus int) select MenuID,MenuName,MenuParentID,HasSubMenus from  temp;

SET @idmin = (SELECT min(id) from temp);
SET @idmax = (SELECT max(id) from temp);

WHILE @idmin <= @idmax DO

INSERT INTO output(MenuID,MenuName,MenuParentID,HasSubMenus)
select MenuID,MenuName,MenuParentID,HasSubMenus from  menus where MenuParentID=(select MenuID from temp where id = @idmin);

SET @idmin=@idmin+1;

END WHILE;

select (@id:=@id) as id1,GROUP_CONCAT(MenuName,',') from output, (SELECT @id:=1) id1 group by id1;