계층 쿼리의 CRUD

# Table

MySQL Workbench로 테이블은 이렇게 구성하였다.

계층 구조를 구성하기 위한 컬럼은 MENU_ID, MENU_LEVEL, MENU_REF_ID, MENU_ORDER 네 가지이다.

• MENU_ID : 메뉴 아이디
• MENU_REF_ID : 상위 메뉴의 아이디
• MENU_LEVEL : 메뉴의 레벨
• MENU_ORDER : 메뉴의 순서

# CRUD

# CREATE

1. MENU_ID의 최대값을 조회
   AUTO INCREMENT를 사용해도 된다.

SELECT MAX(MENU_ID)
  FROM IRON_MENU;

2. 메뉴가 위치할 곳 찾기(상위 메뉴의 가장 마지막 순서로 추가됨)
   2-1. 같은 REF_ID와 LEVEL인 메뉴 중 최대 MENU_ORDER를 조회하여 그것 다음 순서로 추가됨.

SELECT MAX(MENU_ORDER)
  FROM IRON_MENU
 WHERE MENU_REF_ID = 8	-- 8은 추가될 메뉴의 상위메뉴 ID
   AND MENU_LEVEL = 2;	-- 2는 추가될 메뉴의 상위메뉴의 LEVEL + 1

2-1에서 조회했을 경우 값이 없으면 0, 있으면 그 값의 +1을 해야 하는 것 적용.

SELECT 
       CASE WHEN MAX(MENU_ORDER) IS NULL THEN 0
            ELSE MENU_ORDER+1
        END AS MAX_ORDER
  FROM IRON_MENU
 WHERE MENU_REF_ID = 8	-- 8은 추가될 메뉴의 상위메뉴 ID
   AND MENU_LEVEL = 2;	-- 2는 추가될 메뉴의 상위메뉴의 LEVEL + 1

2-2. 완성

INSERT INTO IRON_MENU (
                      MENU_ID
                    , MENU_NM
                    , MENU_URL
                    , MENU_REF_ID
                    , MENU_LEVEL
                    , MENU_ORDER
                    , MENU_REG_DATE
                    )
               VALUES (
                      (SELECT MAX(M_MAX.MENU_ID) +1
                         FROM IRON_MENU AS M_MAX
                      )
                    , '새로 추가할 메뉴의 이름'
                    , '새로 추가할 메뉴 클릭시 이동할 URL'
                    , 8
                    , 2
                    , (
                       SELECT 
                         CASE WHEN MAX(M_ORDER.MENU_ORDER) IS NULL THEN 0
                              ELSE M_ORDER.MENU_ORDER+1
                              END AS MAX_ORDER
                         FROM IRON_MENU AS M_ORDER
                        WHERE M_ORDER.MENU_REF_ID = 8
                          AND M_ORDER.MENU_LEVEL = 2
                    ) + 1
                    , NOW()
                    );

# READ

아직 재귀쿼리를 사용하질 못해서 최대 LEVEL이 3인 경우로 짜 보았습니다.

각 MENU_ID별 MENU_ORDER 순서대로 새로운 문자열을 만들어서 새 문자열을 기준으로 나열하는 방법을 사용.

SELECT M1.MENU_ID
     , M1.MENU_NM
     , M1.MENU_REF_ID
     , M1.MENU_LEVEL
     , M1.MENU_ORDER
     , CASE WHEN M2.MENU_ID IS NULL THEN LPAD(CONVERT(M1.MENU_ORDER, CHAR(4)), 4, '0')
            WHEN M3.MENU_ID IS NULL THEN CONCAT(LPAD(CONVERT(M2.MENU_ORDER, CHAR(4)), 4, '0')
                                              , LPAD(CONVERT(M1.MENU_ORDER, CHAR(4)), 4, '0'))
            ELSE CONCAT(LPAD(CONVERT(M3.MENU_ORDER, CHAR(4)), 4, '0')
                      , LPAD(CONVERT(M2.MENU_ORDER, CHAR(4)), 4, '0')
                      , LPAD(CONVERT(M1.MENU_ORDER, CHAR(4)), 4, '0'))
        END SEQ_CHAR
  FROM IRON_MENU M1
  LEFT OUTER JOIN IRON_MENU M2 ON M1.MENU_REF_ID = M2.MENU_ID
  LEFT OUTER JOIN IRON_MENU M3 ON M2.MENU_REF_ID = M3.MENU_ID
 ORDER BY SEQ_CHAR;

# UPDATE

수정의 경우. 세번의 단계로 이루어지게 짜 보았습니다.

메뉴가 다른 상위 메뉴로 이동할 경우를 고려하여 짜 보았습니다.

1. 메뉴 정보 수정

UPDATE IRON_MENU
   SET MENU_NM       = '수정할 메뉴이름'
     , MENU_URL      = '수정할 메뉴의 URL'
     , MENU_MDF_DATE = NOW()
 WHERE MENU_ID  = 13;		-- 수정할 메뉴의 ID

2. 메뉴의 위치 수정

2-1. 메뉴의 기존 위치의 ORDER 정리

메뉴순서가 0, 1, 2, 3, 4, 5 가 있었다고 했을 때 만약 2번이 다른 곳으로 이동한다면?

0, 1, 3, 4, 5 가 남게 되고. 3, 4, 5는 -1씩 해줘서 정렬해야 한다.

UPDATE IRON_MENU
   SET MENU_ORDER = MENU_ORDER - 1
 WHERE MENU_REF_ID = 8		-- 기존 위치의 상위 메뉴 ID
   AND MENU_LEVEL = 2		-- 기존 위치의 LEVEL 
   AND MENU_ORDER > 1;		-- 기존 위치 순서

2-2. 메뉴가 이동할 곳의 ORDER 정리

정리를 먼저 하고 새 메뉴의 REF_ID와 LEVEL 수정을 해야 한다.

UPDATE IRON_MENU
   SET MENU_ORDER = MENU_ORDER + 1
 WHERE MENU_REF_ID = 6
   AND MENU_LEVEL = 2
   AND MENU_ORDER <= 0;

2-3. 메뉴의 위치 변경

UPDATE IRON_MENU
   SET MENU_REF_ID   = 6
     , MENU_LEVEL    = 2
     , MENU_ORDER    = 0
     , MENU_MDF_DATE = NOW()
 WHERE MENU_ID = 13;

2-4. 메뉴 위치 수정하는 쿼리 하나로 합치기

START TRANSACTION;
-- 이동 전 메뉴의 ORDER 정리
UPDATE IRON_MENU
   SET MENU_ORDER = MENU_ORDER - 1
 WHERE MENU_REF_ID = 8  -- BEFORE REF ID
   AND MENU_LEVEL = 2   -- BEFORE LEVEL
   AND MENU_ORDER > 1;   -- BEFORE ORDER
-- 이동 후 메뉴의 ORDER 정리
UPDATE IRON_MENU
   SET MENU_ORDER = MENU_ORDER + 1
 WHERE MENU_REF_ID = 6  -- AFTER REF ID
   AND MENU_LEVEL = 2   -- AFTER LEVEL
   AND MENU_ORDER >= 0;  -- AFTER ORDER
-- 해당 메뉴 위치 변경
UPDATE IRON_MENU
   SET MENU_REF_ID   = 6    -- AFTER REF ID
     , MENU_LEVEL    = 2    -- AFTER LEVEL
     , MENU_ORDER    = 0    -- AFTER ORDER
     , MENU_MDF_DATE = NOW()
 WHERE MENU_ID = 13;        -- 이동할 MENU_ID
    
commit;

# DELETE

삭제의 경우 UPDATE의 일부분을 가져오면 된다.

1. 삭제할 메뉴와 동등한 위치의 ORDER 정리

UPDATE IRON_MENU
   SET MENU_ORDER    = MENU_ORDER - 1
     , MENU_MDF_DATE = NOW()
 WHERE MENU_REF_ID = 8  -- BEFORE REF ID
   AND MENU_LEVEL = 2   -- BEFORE LEVEL
   AND MENU_ORDER > 1;   -- BEFORE ORDER

2. 해당 메뉴 삭제

DELETE FROM IRON_MENU
 WHERE MENU_ID = 13;	-- 삭제할 메뉴의 ID