2011年9月22日 星期四

postgresql 中階層式資料表查詢

comment_id | parent_id | author | comment
------------+-----------+--------+---------------------------------
1 | 1 | Fran | What's the cause of this bug?
2 | 1 | Ollie | I think it's a null pointer
3 | 2 | Fran | No. I checked for that.
4 | 1 | Kukla | We need to check invalid input.
5 | 4 | Ollie | Yes, that's a bug.
6 | 4 | Fran | Yes, please add a check.
7 | 6 | Kukla | That fixed it.
以上列資料表為例(SQL Antipatterns, Bill Karwin, p36.),每一列都有一欄指向父親,最上層的父親欄位設為1(用null可以解決問題,但是很奇怪。因為null在sql中的邏輯意義是"未知",而不是"沒有",總之必須要有一個辦法表示root。設成1之後避免recursive query跑不完的辦法是把下面code當中的union all 改成union)。

如果資料庫有支援遞迴式查詢,用上述的結構實現階層式資料的話,也能用簡單的查詢查出資料。例如要查出4號comment為根的subtree,在postgresql只要運用以下查詢查詢即可
with RECURSIVE subtree_4(comment_id, parent_id) as (
select comment_id, parent_id from comments where comment_id = 4
union all
select c.comment_id, c.parent_id
from subtree_4 sub, comments c
where c.parent_id = sub.comment_id
)
select * from subtree_4;
參考資料