数据库►PostgreSQL

使用PostgreSQL无限递归 SELECT 评论系统

假设有个评论系统，要求支持无限层级的回复，就像一棵树那样

1
2
3
4
5
       文章
     /      \
    /        \
评论1     评论2
....

注意可以有任意个子树以及做任意个叶子

大意的表结构

1
2
3
4
5
6
7
8
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)
);
1
2
3
4
5
6
7
8
9
10
11
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是树的深度，显示的时候，按已经排序好的层次及相应的父结点显示出来就可以了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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反模式》