cleanUrl: /posts/save-tree-in-mysql-closure-pattern-hierarchy-structure
hierarchy 구조를 저장하는 방법중 그래도 꽤 괜찮은 방법이다.
핵심은 데이터는 데이터대로 저장하는데 데이터 간의 관계(vertex)를 별도의 테이블에 저장하는 방식이다.
그런데 단순히 저장만 하는 것이 아니라 모든 관계를 다 저장한다 parent → child 심지어 자기 자신까지도 저장한다.
대신 두개의 테이블이 필요하다. 다음의 나오는 코드는 SQL antipattern 의 순전한 트리 챕터를 참고했다.
CREATE TABLE Comments (
comment_id SERIAL PRIMARY KEY,
author varchar(50) NOT NULL,
comment TEXT NOT NULL
);
insert into Comments(comment_id, path, author, comment)values(1, '1/', 'Fran', '이 버그의 원인이 뭘까?' ), (2, '1/2/', 'Ollie', '널 포인터 때문인 것 같아'),(3, '1/2/3/', 'Fran', '아니, 그건 확인해봤어.'), (4, '1/4/', 'Kukla', '입력값이 효한지 확인할 필요가 있어'),(5, '1/4/5/', 'Ollie', '그래, 그게 버그야'), (6, '1/4/6/', 'Fran', '그래, 확인하는 코드를 추가해'),(7, '1/4/6/7/', 'Kukla', '수정됐어.');
쇠파이프 에서 user 에 해당하는 테이블이고
CREATE TABLE TreePaths (
ancestor BIGINT UNSIGNED NOT NULL,
descendant BIGINT UNSIGNED NOT NULL,
PRIMARY KEY(ancestor, descendant),
FOREIGN KEY (ancestor) REFERENCES Comments(comment_id),
FOREIGN KEY (descendant) REFERENCES Comments(comment_id)
);
insert into TreePaths values (1, 1), (1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,2),(2,3),(3,3),(4,4),(4,5),(4,6),(4,7),(5,5),(6,6),(6,7),(7,7);
위의 댓글 중 4번에 해당하는 댓글들을 가져오려면 다음과 같이 질의 하면 된다.
code block 에 테스트해볼 수 있는 데이터를 넣어두었으니, 실행해보자
SELECT c.*
FROM Comments AS c
JOIN TreePaths AS t ON c.comment_id = t.descendant
WHERE t.ancestor = 4;
6번에 해당하는 상위 댓글들을 가져오려면 다음과 같이 질의한다.
SELECT c.*
FROM Comments AS c
JOIN TreePaths AS t ON c.comment_id = t.ancestor
WHERE t.descendant = 6;
