[MariaDB] RECURSIVE 계층 메뉴 구성

- 목적

MENU 테이블에 저장되어 있는 메뉴를 MENULEVEL과 PARENTMENUID를 이용하여 순서대로 들고온다.

 

- 문제

현재 4단계 계층만 표현 -> 단계제약을 없애고 싶음.

MENUID와 PARENTMENUID가 같을 때 무한루프

CONCAT이 잘림

WITH RECURSIVE CTE AS (
        SELECT
          PROJECTID, MENUID, MENUTYPE, PARENTMENUID, MENULEVEL, POSITION, POSITION AS X
        FROM MENU 
        WHERE PARENTMENUID = 'root' AND PROJECTID   = #{projectId} 
        UNION ALL
        SELECT
          B.PROJECTID, B.MENUID, B.PARENTMENUID, B.MENULEVEL, B.POSITION,
          CONCAT(C.X, '-', B.POSITION) X 
          FROM MENU B
          INNER JOIN CTE C ON B.PARENTMENUID = C.MENUID AND B.PROJECTID = C.PROJECTID 
      ) 
      SELECT * FROM (
            SELECT  PROJECTID, MENUID, PARENTMENUID, MENULEVEL , POSITION, X,
                CASE WHEN MENULEVEL = 1 THEN  
                          LPAD(substring_index(X, '-',1),3,'0') 
                     WHEN MENULEVEL = 2 THEN   
                          CONCAT(LPAD(substring_index(X, '-',1),3,'0'),'-',
                                 LPAD(substring_index(substring_index(X, '-',2),'-',-1),3,'0'))                
                     WHEN MENULEVEL = 3 THEN 
                          CONCAT(LPAD(substring_index(X, '-',1),3,'0'),'-',
                                LPAD(substring_index(substring_index(X, '-',2),'-',-1),3,'0'),'-',
                                LPAD(substring_index(substring_index(X, '-',3),'-',-1),3,'0')) 
                     WHEN MENULEVEL = 4 THEN   
                          CONCAT(LPAD(substring_index(X, '-',1),3,'0'),'-',
                                LPAD(substring_index(substring_index(X, '-',2),'-',-1),3,'0'),'-',
                                LPAD(substring_index(substring_index(X, '-',3),'-',-1),3,'0'),'-',
                                LPAD(substring_index(X, '-',-1),3,'0')) 
                    END AS Z 
            FROM CTE WHERE PROJECTID = #{projectId}
   ) D    
       ORDER BY Z

 

- 보정

1. 현재 4단계 계층만 표현 -> 단계제약을 없애고 싶음.

기존 불필요하게 X를 구한 후 재구성하여 Z를 만들 던 부분 삭제

2. MENUID와 PARENTMENUID가 같을 때 무한루프

데이터를 넣을 때 무한루프가 발생할 수 있는 데이터를 넣지 않는 것이 가장 좋지만

MENULEVEL != 1이라는 조건을 추가하여 무한루프 발생 임시로 방지함.

3. CONCAT이 잘림

char 또는 varchar로 구성된 컬럼을 concat() 함수로 연결해야 문제발생 x

CAST를 사용하여 CHAR로 변환함

WITH RECURSIVE CTE AS (
SELECT
  A.PROJECTID,
  A.MENUID,
  A.PARENTMENUID,
  A.MENULEVEL ,
  A.POSITION,
  CAST(LPAD(A.POSITION, 3, 0) AS CHAR(500))  AS Z
FROM MENU A
WHERE PARENTMENUID = 'root' 
  AND PROJECTID   = #{projectId} 
UNION ALL
SELECT
  B.PROJECTID,
  B.MENUID,
  B.PARENTMENUID,
  B.MENULEVEL,
  B.POSITION,
  CAST(CONCAT(C.Z, '-', LPAD(B.POSITION, 3, 0)) AS CHAR(500)) AS Z 
  FROM MENU B
  INNER JOIN CTE C 
   ON B.PARENTMENUID = C.MENUID
     AND B.PROJECTID = C.PROJECTID 
     AND B.MENULEVEL != 1
) 
SELECT  *  FROM CTE 
ORDER BY Z

 

+ USER별로 개인화된 메뉴를 들고오게 하는 WHERE절 추가

WITH RECURSIVE CTE AS (
SELECT
  A.PROJECTID,
  A.MENUID,
  A.PARENTMENUID,
  A.MENULEVEL ,
  A.POSITION,
  CAST(LPAD(A.POSITION, 3, 0) AS CHAR(500))  AS Z
FROM MENU A
WHERE PARENTMENUID = 'root' 
  AND PROJECTID   = #{projectId} 
UNION ALL
SELECT
  B.PROJECTID,
  B.MENUID,
  B.PARENTMENUID,
  B.MENULEVEL,
  B.POSITION,
  CAST(CONCAT(C.Z, '-', LPAD(B.POSITION, 3, 0)) AS CHAR(500)) AS Z 
  FROM MENU B
  INNER JOIN CTE C 
   ON B.PARENTMENUID = C.MENUID 
     AND B.PROJECTID = C.PROJECTID 
     AND B.MENULEVEL != 1
) 
SELECT * FROM CTE WHERE PROJECTID = #{projectId} 
    AND MENUID in (
        SELECT MENUID 
        FROM USERGROUPMENU 
        WHERE USERGROUPID = (SELECT USERGROUPID FROM USERPROJECTMAP  
            WHERE PROJECTID = #{projectId} 
            AND USERID = #{userId} )
        AND PROJECTID = #{projectId} 
        UNION ALL 
        SELECT MENUID 
        FROM USERPROFILEMENU  
        WHERE PROJECTID = #{projectId} 
        AND  USERID = #{userId}
        )
ORDER BY Z