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.

why is the MenuParentID is -1? also for menue ids 1,2,5 you should get all submenus of Home, Accounts Users and packages rite? — AnandPhadke
– AnandPhadke, Commented Aug 4, 2012 at 7:20
MenuParentID represents that corresponding Menu has no parent menu. Yes, I'm able to get those sub menus. If they have further submenus, then I won't get it. — benjamin54
– benjamin54, Commented Aug 4, 2012 at 7:25
But when we compare MenuParentID = rootMenuID then it will not match since menuparent id is -1. — AnandPhadke
– AnandPhadke, Commented Aug 4, 2012 at 8:03

Najzero · Accepted Answer · 2012-08-04 07:37:23Z

3

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

answered Aug 4, 2012 at 7:37

Najzero

3,22220 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AnandPhadke · Accepted Answer · 2012-08-04 11:27:29Z

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;

Collectives™ on Stack Overflow

MySql recursive logic

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related