- 목적
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
'SQL > RDBMS' 카테고리의 다른 글
쿼리 실행 속도 확인 (0) | 2024.05.30 |
---|---|
서브 쿼리 ORDER BY (0) | 2023.10.23 |
[MariaDB] 중복 파일명 처리(이름 뒤에 seq 붙이기) (0) | 2023.07.03 |
[MariaDB] 환경 구축 (생성, 삭제, 권한, 백업) (0) | 2023.04.25 |
ORA-01722: 수치가 부적합합니다 (0) | 2023.03.17 |