계층형 쿼리

개발 한 스푼/MySQL

계층형 쿼리

idleday 2023. 8. 17. 16:57

공지사항 게시판에 댓글 기능을 도입한다고 한다.

댓글-대댓글로 2 depth인 줄 알았으나, 대대댓글 3 depth가 될 수도 있고 아닐수도 있고 변경가능성 농후..하므로

일단 3 depth 기준으로 조회 쿼리를 짜기로 했다.

댓글-대댓글 구조의 테이블 설계는 SQLD 공부할 때 접했던 계층형 쿼리(start with, prior 등) 기능으로

간단하지 않을까했다.

주로 많이 사용하는 기능

WITH RECURSIVE 구문
CONNECT BY (Oracle)
재귀형 함수 짜기

Oracle이 아니니까 CONNECT BY PRIOR, START WITH 못써도 괜찮아

아 니 근 데

WITH RECURSIVE 은 mysql 5.7 이하 미지원인데

SELECT VERSION();
SHOW VARIABLES LIKE '%VERSION%';

innodb_version 이 5.6.36-82.2 이었다..

MySQL 버전이 낮아서 재귀 구문을 못쓴다 허허

허허

그래서 무식하게 생짜로 짜기로 했다.

댓글 레벨이 3레벨..보다 깊어지는 경우는 생각하지않고 일단 짠다

이어지는 한나절 간의 두뇌 풀가동!

결국 해냈다는 거지..

SELECT A.*
FROM comment_table A LEFT OUTER JOIN comment_table B ON A.parentId = B.id -- 부모
WHERE A.boardId = 36 
ORDER BY CASE A.level 
            WHEN 1 THEN A.id 
            WHEN 2 THEN A.pardntId
            WHEN 3 THEN B.parentId
        END
	 , IF(A.level = 3, A.pardntId, A.id)
	 , A.createdDate
;

order by 정렬조건 때문에 몇시간 머리 싸맸지만

일단 이걸로 간다..

재귀 펑션 활용기는.. 다음에 이어서 해보기로.

이제.. 조회쿼리 짰으니 등록/수정/삭제 만들어야지..

+ 2023-08-23 추가

위의 쿼리는.. 댓글이 3depth일때만 사용가능한 매우 제한적인 조건의 쿼리이다.

현업에서 무한댓글을 구현하고자한다..

그래서 결국 펑션에 손대버렸다.

그렇게 만들게 된 재귀함수 fn_hierarchi()

#### Function 원리 ####

1. 자신을 부모로 가지는 노드를 찾는다.

2. 검색된 노드가 없거나 start_with 과 같은 부모노드를 가진다면 해당 노드를 반환한다.

3. 2번과정이 아니라면 상위 노드로 이동하여 다시 검색한다. 이때 앞에 검색된 id 값보다 증가된 값을 찾는다.

DELIMITER $$

USE `myDB`$$

DROP FUNCTION IF EXISTS `fn_hierarchi`$$

CREATE DEFINER=`myDB`@`%` FUNCTION `fn_hierarchi`() RETURNS INT(11)
    READS SQL DATA
BEGIN
	  DECLARE v_id INT;
	  DECLARE v_parent INT;    
	  DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
	  
	  SET v_parent = @id;
	  SET v_id = -1;
	  
	  IF @id IS NULL THEN
		RETURN NULL;
	  END IF;
	  
	  LOOP
	  
	  SELECT MIN(seqNo)
	    INTO @id
	    FROM t_comment_list
	  WHERE IFNULL(upprSeqNo,0) = v_parent
	    AND seqNo > v_id;
	  
	  IF (@id IS NOT NULL) OR (v_parent = @start_with) THEN 
		SET @level = @level + 1;
		RETURN @id;
	  END IF;
	  
	  SET @level := @level - 1;
	  
	  SELECT seqNo, IFNULL(upprSeqNo,0)
	    INTO v_id, v_parent
	    FROM t_comment_list
	   WHERE seqNo = v_parent;
	   
	   END LOOP;
	
    END$$

DELIMITER ;

-- 계층형쿼리 실행
SELECT A.seqNo, CASE WHEN fn.lev -1 > 0 THEN CONCAT(CONCAT(REPEAT('  ', fn.lev - 1),'┗'), IF(A.delYn = 'Y','삭제',A.cmnt)) ELSE A.cmnt END AS hierarchi
	 , fn.sort
	, A.*
FROM (	SELECT fn_hierarchi() AS seqNo, @level AS lev, @row:=@row+1 AS sort
	FROM ( SELECT @start_with:=0, @id:=@start_with, @level:=0) vars 
	INNER JOIN t_comment_list A 
	INNER JOIN (SELECT @row:=0 ) B
	WHERE @id IS NOT NULL	
     ) fn INNER JOIN t_comment_list A ON fn.seqNo = A.seqNo AND A.ntcSeqNo = #게시물번호#
WHERE 1=1
ORDER BY fn.sort
;

결과물:

+ 2023-11-02 추가

와!

등록/수정/삭제는 무한 depth 기준으로 구현해야하는데

조회는 2단계로 보여진다한다 마치 유튜브처럼..

위 Function으로 인해 대댓글 정렬은 어찌저찌 성공했으나

삭제여부와 자식댓글이 있는지 유무를 따져

1. 삭제된 댓글인데, 자식이 없다면 → ❌

2. 삭제된 댓글인데, 자식이 있다면 ✅

2.1 만일 자식이 살아있다면 ✅

2.2 만일 자식이 삭제됐다면 ✅

2.21 자식의 자식이 살아있다면 ✅

2.22 자식의 자식이 삭제됐다면(해당 댓글 밑에 달린 모든 후손_손자,증손 등등_이 삭제됐다면) → ❌

1번과 2.22번 저 두 경우를 아예 프론트에 보낼때 제외하고 댓글목록을 정렬해서 보내야했다.

DB레벨에서는 당연히 한 쿼리로 구현 불가.

며칠 삽질해봤으나 이건.. 안됐다. 특히 살아있는 후손이 있는지 체크하는게 대댓글 정렬 함수를 돌리면서 동시에 쿼리로 처리 안됨

그래서 어떻게 했냐?

1번까지는 쿼리에서 조인을 통해 제외할 수 있었으나

2.22번 후손체크같은 경우는

[JAVA] 반복문에서 배열요소 삭제 에서 to be continued..

[JAVA] 반복문에서 배열요소 삭제

무한댓글 구현 + 삭제여부 확인하며 새로 알게된 개념 메모 참고) https://idleday.tistory.com/109 계층형 쿼리 공지사항 게시판에 댓글 기능을 도입한다고 한다. 댓글-대댓글로 2 depth인 줄 알았으나, 대

idleday.tistory.com

Ref1. 머리싸매며 알게된 재밋는 자료들..

SQL-계층-구조-쿼리는-성능을-위해-RECURSIVE하게-짜지-말자 <- 무식한 방법이 때로는 좋을 수 있다
테이블 하나로 무한 카테고리 쿼리문 짜기 <- 서커스같다.. WOW
카테고리, 댓글/답글과 같은 계층(트리) 데이터베이스 구조 설계 및 개선하기 <- 트리구조는 좌우가 필요하구나!
[MySQL] ORDER BY 정렬시 NULL값 뒤로 보내기 <- NULL 골칫거리 뒤로 빼는법

Ref2. 계층형쿼리 함수

저작자표시 비영리

'개발 한 스푼 > MySQL' 카테고리의 다른 글

you do not have the SUPER privilege (Error Code: 1419) (0)	2023.09.20
NOW()와 SYSDATE()의 차이 (0)	2023.09.05
[MySQL] YEAR_MONTH() 현재 연도, 월 알아내기 (0)	2023.07.17
LIKE% 위치에 따른 인덱스 사용 여부 (0)	2023.07.11
[MySQL] id컬럼 데이터타입 - int? Bigint? (0)	2022.12.24

현재글계층형 쿼리

완주를 향하여