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 = '고객지원 가이드 게시판' ;
cs
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 = '노드 정보' ;
cs
생성된 테이블 :
NODE_ID(노드아이디)와 PNODE_ID(부모 노드 아이디)가 서로 참조하는 관계의 테이블 구조.
샘플 데이터 :
<3: 쿼리 작성>
필요한 쿼리 종류는 크게 두가지로 나눠볼 수 있다.
1. 셀렉트박스의 구성요소인 노드정보(node_info TABLE)를 조회하는 쿼리 : 멀티셀렉트박스를 그리기 위한 데이터를 뽑는 쿼리
2. 선택된 셀렉트박스 값들을 기준으로 가이드 목록(guide_info TABLE)을 조회한 결과 : 조회 결과를 출력하기 위한 데이터를 뽑는 쿼리
3-1. 셀렉트 박스의 구성요소인 노드정보를 조회하는 쿼리 :
N번째(Ndepth) 셀렉트박스가 특정값으로 선택된 경우 N+1번째(N+1depth) 셀렉트박스 조회
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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>
cs
* 8~15line : node_id 가 없는 경우, 아무것도 선택되지 않은 경우(화면에 최초 진입한 상태)이며 최고 조상이라 할 수 있는 node_id 1 을 기준으로 자식 노드를 구한다
3-2. 선택된 셀렉트박스 값들을 기준으로 가이드 목록을 조회한 결과
1) 마지막 셀렉트박스가 특정 값으로 선택된 경우 : 단순 join 으로 해결
2) 마지막 셀렉트박스가 '전체'로 선택된 경우 : 아래와 같이 해결
ORACLE 은 CONNECT BY 함수를 사용하여 트리구조의 데이터를 출력 할 수 있겠지만,
MYSQL 은 CONNECT BY 함수를 지원하지 않는다.
다음과 같이 특정 노드 밑에 있는 모든 자손, 특정 노드 상위에 있는 모든 조상 노드를 구할 수 있다.
1. 특정 노드의 자손 구하기
1
2
3
4
5
6
7
8
9
10
11
12
13
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))
cs
2. 특정 노드의 조상 구하기
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
cs
3. 특정 분류 밑의 모든 가이드 목록 조회 (특정 노드아이디 기준 모든 자손 노드를 구하는 1번 쿼리를 sub query로 사용)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
cs
※ 위와 같이 subquery 를 사용하여 sql 에서 처리하지 않고, 소스레벨에서 처리하고 싶은 경우 foreach 를 사용하여 다음과 같이 처리할 수 있다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 >
cs
* 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
