[MariaDB] 재귀쿼리를 활용한 트리구조 메뉴 만들기 RECURSIVE

댓글이나 메뉴를 구현하기위해 트리구조로 데이터를 뽑아와야하는 경우가 있다.

이럴때 테이블에 필요한 필수 컬럼과 재귀쿼리를 이용해 데이터를 가지고 오는 방법을 설명하겠다.

 

category 테이블 구성 컬럼 정보

• cateId : 카테고리의 고유 아이디
• cateNmae : 카테고리 이름
• firstDate : 생성 날짜
• cateDeletFlag : 삭제 유무
• parentId : 부모 카테고리 아이디

 

트리구조로 메뉴 데이터를 가지고 오는 재귀쿼리

• WITH RECURSIVR tree AS() : 메모리상에 tree라는 가상의 테이블을 생성한다.
• UNION ALL을 기준으로 위에나오는 SELECT문은 비 반복문 으로써 재귀쿼리에 기준이 된다. 여기서는 cateId = 'al'인 데이터가 기준이 된다.
• UNION ALL을 기준으로 아래에 나오는 SELECT문에서 가상의 테이블인 tree를 사용해 재귀쿼리를 호출한다.
• 조건을 걸지 않으면 무한으로 돌기 때문에 꼭 종료 조건을 걸어줘야 한다. 여기서는 자신의 cateId를 parentId로 가지는 row가 없을 시 종료가 된다.
• orderString : 정렬을 위해 생성해 주는 컬럼이다.
• depth : 출력 시 UI를 형성에 편리함을 위해 생성해주는 컬럼이다.

WITH RECURSIVE  tree AS(
	SELETC 
		cateId,
		cateName,
		parentId,
		cateId AS orderString,
		0 AS depth
		FROM category
		WHERE cateId = 'al'
	
	UNION ALL
	
	SELECT 
		cate1.cateId,
		cate1.cateName,
		cate1.parentId,
		CONCAT(cate2.orderString,', ' ,  cate1.cateId) AS orderString,
		cate2.depth + 1 AS depth
		FROM category AS cate1
		INNER JOIN tree AS cate2
		ON cate1.parentId = cate2.cateId
	
)
SELECT *
FROM tree
order by orderString

 

단계별 실행 결과 

1. 테이블 category에서 cateId가 'al'인 행을 가상의 테이블 tree에 행으로 넣는다. 

테이블 tree

2. 테이블 category에서 parentId가 'al'인 행을(1의 결과) 가상의 테이블 tree에 행으로 넣는다. 

 

3. 테이블 category에서 parentId가 'da', 'en', 'ga', 'hu', 'it', 'sh' 인 행을(2의 결과) 가상의 테이블 tree에 행으로 넣는다.

3. 테이블 category에서 parentId가 'c', 'fo', 'ja', 'tr'인 행을(3의 결과) 가상의 테이블 tree에 행으로 넣는다.

4. 테이블 category에서 parentId가 'am', 'ko', 'op'인 행을(3의 결과) 가상의 테이블 tree에 행으로 넣는다.

결과

테이블 category에서 parentId가 'la'인 행이(4의 결고) 없으므로 쿼리는 종료된다. 이후 orderString으로 정렬하면 트리형 구조로 데이터를 가지고 올 수있다.