使用PostgreSQL无限递归 SELECT 评论系统
Contents
假设有个评论系统,要求支持无限层级的回复,就像一棵树那样
文章
/ \
/ \
评论1 评论2
....
注意可以有任意个子树以及做任意个叶子
大意的表结构
create table comments (
comment_id serial primary key,
parent_id bigint,
bug_id bigint not null,
author varchar(20) not null,
comment text not null,
foreign key (parent_id) references comments(comment_id)
);
test=# select * from comments;
comment_id | parent_id | bug_id | author | comment
------------+-----------+--------+--------+---------------------
1 | | 1 | Fran | 这个bug的成因是什么
2 | 1 | 1 | Ollie | 我觉得是一个空指针
3 | 2 | 1 | Fran | 不,我查过了
4 | 1 | 1 | Kukla | 我们需要查无效输入
5 | 4 | 1 | Ollie | 是的,那是个问题
6 | 4 | 1 | Fran | 好,查一下吧
7 | 6 | 1 | Kukla | 解决了
(7 rows)
SQL语句
利用递归查询,可以查某篇文章评论组成的树结构。其中 depth是树的深度,显示的时候,按已经排序好的层次及相应的父结点显示出来就可以了。
test=# with RECURSIVE commenttree (comment_id, bug_id, parent_id, author, comment, depth) as (select comment_id, bug_id, parent_id, author, comment , 0 as depth from comments where parent_id is null union all select c.comment_id, c.bug_id, c.parent_id, c.author, c.comment, ct.depth+1 as depth from commenttree as ct join comments as c on (ct.comment_id = c.parent_id)) select * from commenttree where bug_id = 1;;
comment_id | bug_id | parent_id | author | comment | depth
------------+--------+-----------+--------+---------------------+-------
1 | 1 | | Fran | 这个bug的成因是什么 | 0
2 | 1 | 1 | Ollie | 我觉得是一个空指针 | 1
4 | 1 | 1 | Kukla | 我们需要查无效输入 | 1
3 | 1 | 2 | Fran | 不,我查过了 | 2
5 | 1 | 4 | Ollie | 是的,那是个问题 | 2
6 | 1 | 4 | Fran | 好,查一下吧 | 2
7 | 1 | 6 | Kukla | 解决了 | 3
(7 rows)
test=#
注意PostgreSQL里,必须加 RECURSIVE 才能支持递归。
内容来源资料: [1]《SQL反模式》