Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)

Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)

 

고객들이 열람할 가이드 정보가 있고,

가이드 정보의 카테고리를 N depth 로 분류(멀티 셀렉트박스 사용)하여 조회할 수 있게 하여 다음과 같은 화면을 만들어 보자.

<1: 테이블 설계>

1. '전체' 옵션의 유무

case 1 멀티 셀렉트박스에 '전체'라는 옵션이 없는 경우 :

마지막 셀렉트박스의 value(node_id)를 기준으로 가이드를 조회

case 2 멀티 셀렉트박스에 '전체'라는 옵션이 존재하는 경우 :

'전체'가 선택된 마지막 셀렉트박스 직전의 value(node_id)를 기준으로 모든 자손 node_id를 찾아 조회해야한다.

 

예를 들어,

case 1 ex) 고객지원 > 고객지원sub > 고객지원sub의sub 와 같이 마지막 select box에 특정 값이 선택된 경우 :

고객지원sub의sub 의 value(node_id) 를 기준으로 가이드 조회

case 2 ex) 고객지원 > 고객지원sub > 전체 와 같이 마지막 select box가 '전체'로 선택된 경우 :

고객지원sub을 부모 노드로 삼는 모든 자손 노드를 기준으로 가이드 조회

 

2. 동적 depth 여부

case 1 : depth 가 특정 값으로 고정인 경우, 셀렉트박스용 분류 코드테이블을 따로 두고 join 을 몇번 걸면 해결이 된다

case 2 : depth 가 동적(N)인 경우, join 이 N 번 되어야 하므로 단순한 join으로 해결 할 수 없다..

 

전체 옵션이 존재하고 depth 가 동적(N)인 경우를 전재하고 설계 및 개발해보자.

 

 

<2: 테이블 생성>

1. 메인테이블 생성 : 가이드 정보 테이블 생성

가이드 정보 테이블은 제목, 가이드의 노드 아이디, 상세 내용만 지니고 있다.

CREATE TABLE `guide_info` (
  `SEQ` int(11) NOT NULL AUTO_INCREMENT COMMENT '일련번호',
  `TITLE` varchar(100) NOT NULL COMMENT '제목',
  `GUIDE_ID` int(11) NOT NULL COMMENT '가이드 노드 아이디',
  `TEXT_ITEM_1` text COMMENT '가이드 내용',
  PRIMARY KEY (`SEQ`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='고객지원 가이드 게시판';
 

 

2. 서브테이블 생성 : 가이드 분류 정보를 따로 지니고 있는 노드 정보 테이블 생성

CREATE TABLE `node_info` ( 
  `NODE_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '노드아이디', 
  `PNODE_ID` int(11) NOT NULL COMMENT '부모노드아이디', 
  `NODE_PATH` varchar(200) DEFAULT NULL COMMENT '노드경로', 
  `NODE_NM` varchar(20) DEFAULT NULL COMMENT '노드명', 
  `DEPTH` int(11) DEFAULT '1' COMMENT '노드DEPTH', 
  `LEAF_YN` varchar(1) DEFAULT 'N' COMMENT '자식노드존재유무', 
  PRIMARY KEY (`NODE_ID`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='노드 정보'; 

 

생성된 테이블 :

NODE_ID(노드아이디)와 PNODE_ID(부모 노드 아이디)가 서로 참조하는 관계의 테이블 구조.

 

샘플 데이터 :

 

<3: 쿼리 작성>

필요한 쿼리 종류는 크게 두가지로 나눠볼 수 있다.

1. 셀렉트박스의 구성요소인 노드정보(node_info TABLE)를 조회하는 쿼리 : 멀티셀렉트박스를 그리기 위한 데이터를 뽑는 쿼리

2. 선택된 셀렉트박스 값들을 기준으로 가이드 목록(guide_info TABLE)을 조회한 결과 : 조회 결과를 출력하기 위한 데이터를 뽑는 쿼리

 

3-1. 셀렉트 박스의 구성요소인 노드정보를 조회하는 쿼리 :

N번째(Ndepth) 셀렉트박스가 특정값으로 선택된 경우 N+1번째(N+1depth) 셀렉트박스 조회

SELECT  node_id
      , pnode_id
      , node_nm
      , depth
      , leaf_yn
  FROM  node_info 
 WHERE  1=1
<choose>
    <when test='node_id neq null and node_id neq ""'> 
       AND  pnode_id = #{node_id}
    </when>
    <otherwise>  
       AND  pnode_id = 1
    </otherwise>
</choose>
 
 

* 8~15line : node_id 가 없는 경우, 아무것도 선택되지 않은 경우(화면에 최초 진입한 상태)이며 최고 조상이라 할 수 있는 node_id 1 을 기준으로 자식 노드를 구한다

 

3-2. 선택된 셀렉트박스 값들을 기준으로 가이드 목록을 조회한 결과

1) 마지막 셀렉트박스가 특정 값으로 선택된 경우 : 단순 join 으로 해결

2) 마지막 셀렉트박스가 '전체'로 선택된 경우 : 아래와 같이 해결

 

ORACLE 은 CONNECT BY 함수를 사용하여 트리구조의 데이터를 출력 할 수 있겠지만,

MYSQL 은 CONNECT BY 함수를 지원하지 않는다.

 

다음과 같이 특정 노드 밑에 있는 모든 자손, 특정 노드 상위에 있는 모든 조상 노드를 구할 수 있다.

 

1. 특정 노드의 자손 구하기

SELECT node_id
     , pnode_id
     , depth
     , leaf_yn
     , node_path
 FROM  (
        SELECT * 
          FROM node_info
      ORDER BY pnode_id, node_id
        ) node_sorted,
       (SELECT @pv := #{node_id}) initial
WHERE   FIND_IN_SET(pnode_id, @pv)
  AND   LENGTH(@pv := concat(@pv, ',', node_id))

 

2. 특정 노드의 조상 구하기

SELECT   nid AS node_id
       , pnode_id
       , leaf_yn
       , depth
       , node_path
  FROM (
        SELECT  @nid AS nid,
              (
               SELECT  @nid := pnode_id
                 FROM  node_info
                WHERE  node_id = nid
               ) AS pnode_id
               , leaf_yn
               , depth
               , node_path
          FROM  (
                 SELECT  @nid := #{node_id}
                ) vars
                , node_info
         WHERE  @nid <> 0
        ) as tmp

참조 : https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

 

3. 특정 분류 밑의 모든 가이드 목록 조회 (특정 노드아이디 기준 모든 자손 노드를 구하는 1번 쿼리를 sub query로 사용)

SELECT  seq, 
        title, 
        (SELECT node_path FROM node_info where node_id = gi.guide_id) as guide_path
  FROM  guide_info gi
 WHERE  1=1
   AND  gi.guide_id IN  (SELECT * FROM (
                                             SELECT   node_id
                                               FROM  (SELECT node_id, pnode_id
                                                        FROM node_info
                                                    ORDER BY pnode_id, node_id
                                                     ) node_sorted,
                                                     (select @pv := #{node_id}) initial
                                              WHERE   FIND_IN_SET(pnode_id, @pv)
                                                AND   LENGTH(@pv := concat(@pv, ',', node_id))
                            ) as temp
                            )
ORDER BY seq

 

※ 위와 같이 subquery 를 사용하여 sql 에서 처리하지 않고, 소스레벨에서 처리하고 싶은 경우 foreach 를 사용하여 다음과 같이 처리할 수 있다.

SELECT   gi.title, gi.guide_id, gi.text_item_1, ni.node_path AS rs
  FROM   guide_info gi
       , node_info ni
 WHERE   1=1
   AND   ni.node_id = gi.guide_id
<if test = 'sch_word neq null and sch_word neq ""'>
   AND   title LIKE CONCAT('%',#{sch_word},'%')
</if>
<if test = 'totalSearchFlag neq null and totalSearchFlag neq ""'>
  <choose>
     <when test = 'totalSearchFlag eq "Y"'>
      AND   ni.node_id IN 
       <foreach item="node_id_grp" index="i" collection="node_id_grp" separator="," open="(" close=")">
        #{node_id_grp}
       </foreach>
      </when>
      <otherwise>
        AND   ni.node_id = #{node_id}
      </otherwise>
  </choose>
</if>
 

* totalSearchFlag 는 selectBox 에서 '전체'가 선택되었음을 판별하기 위해 사용되는 값

* 1번 쿼리(특정노드의 모든 자손 구하기)로 뽑아온 모든 자손 노드를 필드명이 node_id_grp 인 List 자료형 에 담아 sql mapper 로 넘겨주어 사용

* open 은 for 문이 맨처음 돌기 전에, close 는 for문이 끝까지 돌고나서, separator 는 for 문이 한 번 씩 돌 때마다 적용된다.

* foreach(for문)로 작성된 쿼리 결과물은 다음과 같다

  (생략) ~ AND ni.node_id IN (2, 6)

 

 

 

위 쿼리를 사용해 가져온 데이터를 화면에 그려보자.

https://developyo.tistory.com/55?category=689339