# 7. ltree 插件
#### ltree介紹
ltree是PostgreSQL的一個擴展插件,即extension,使用它可以實現樹型結構,而且還支持索引和豐富的查詢。
[ltree](http://www.postgresql.org/docs/current/static/ltree.html)官方文檔給出了詳細的解釋。
它的概念很簡單,打個比方,比如,我們要存一個樹型菜單,不限定級數,有祖先(根),根子節有子節點,子節點又有子節點,以此類推,形成一顆樹。假如我們存公司的數據,它的表名叫companies,有個字段叫name,存的是分公司的名稱。有一個很簡單的方法,來實現這種樹型結構,只要在companies表中增加一個字段parent\_id即可,它存的是父節點的id,以此來找到父節點,根節點的parent\_id為null,其他節點都為父節點的id。這種方式是可以的,它也能查詢到所有的節點,由于存有parent\_id,它找子節點,父節點,根節點都很簡單,若要找其他節點,只能通過遍歷了,效率較低。而且,每次添加節點,都要查找父節點的id,即parent\_id,這樣也不夠直觀和靈活。
而ltree是在數據庫級別支持的樹型結構。它支持豐富的查詢。
#### ltree使用
我們來演示一下搭建這樣的樹型結構。
```
Top
/ | \
Science Hobbies Collections
/ | \
Astronomy Amateurs_Astronomy Pictures
/ \ |
Astrophysics Cosmology Astronomy
/ | \
Galaxies Stars Astronauts
```
也會演示它強大的查詢方法。
首先開啟ltree擴展。
```
sudo -u postgres psql
CREATE EXTENSION IF NOT EXISTS ltree;
```
創建數據庫表。表名為test,字段名為path,類型指定為ltree。
```
CREATE TABLE test (path ltree);
```
插入數據。
```
INSERT INTO test VALUES ('Top');
INSERT INTO test VALUES ('Top.Science');
INSERT INTO test VALUES ('Top.Science.Astronomy');
INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO test VALUES ('Top.Hobbies');
INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO test VALUES ('Top.Collections');
INSERT INTO test VALUES ('Top.Collections.Pictures');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');
```
因為要查詢,給數據表加上索引,索引有兩種,分別是btree和gist,GiST支持的操作符更為豐富些。具體可看官方文檔。
```
CREATE INDEX path_gist_idx ON test USING gist(path);
CREATE INDEX path_idx ON test USING btree(path);
```
現在整張表的結果是這樣。
```
rails365_pro=# select * from test;
path
-----------------------------------------------
Top
Top.Science
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
Top.Hobbies
Top.Hobbies.Amateurs_Astronomy
Top.Collections
Top.Collections.Pictures
Top.Collections.Pictures.Astronomy
Top.Collections.Pictures.Astronomy.Stars
Top.Collections.Pictures.Astronomy.Galaxies
Top.Collections.Pictures.Astronomy.Astronauts
(13 rows)
```
接下來演示查詢方法。
先來演示第一個,再來介紹語法。
```
rails365_pro=# SELECT path FROM test WHERE path ~ '*.Astronomy.*';
path
-----------------------------------------------
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
Top.Collections.Pictures.Astronomy
Top.Collections.Pictures.Astronomy.Stars
Top.Collections.Pictures.Astronomy.Galaxies
Top.Collections.Pictures.Astronomy.Astronauts
(7 rows)
```
這樣會查找所有包含Astronomy的項。
根據官方的解釋。語法大約是這樣的。
```
SELECT path FROM test WHERE ltree 操作符 lquery;
```
`ltree`就是要查找的字段名。`~`就是操作符,官方列出了所有支持的操作符,也給了解釋。`lquery`是表示被匹配的正則表達式的字符串。
```
rails365_pro=# SELECT path FROM test WHERE path <@ 'Top.Science';
path
------------------------------------
Top.Science
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
(4 rows)
```
`<@`的意思是"is left argument a descendant of right (or equal)?",就是返回指定元素的后代啦,返回的結果正是我們期待的。
只要懂得了語法,ltree支持的所有操作符只要看官方文檔的解釋就可以使用了。
ltree還支持函數。用于選擇和組合返回我們想要的結果。比如:
```
rails365_pro=# select * from test WHERE path <@ 'Top.Science.Astronomy';
path
------------------------------------
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
(3 rows)
rails365_pro=# SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ 'Top.Science.Astronomy';
?column?
------------------------------------------
Top.Science.Space.Astronomy
Top.Science.Space.Astronomy.Astrophysics
Top.Science.Space.Astronomy.Cosmology
(3 rows)
```
subpath的語法是這樣的。`subltree(ltree, int start, int end)`中`start`是起始位置(從0開始算),`end`是結束位置,但不包含結束位置。`subpath(path,0,2)`返回的就是第一個元素和第二個,即`Top.Science.`。
還有其他函數,看官方文檔的解釋就好了。
ltree介紹完了。
#### ltree\_hierarchy的使用
[ltree\_hierarchy](https://github.com/Leadformance/ltree_hierarchy)是一個ruby的gem,它實現了PostgreSQL的ltree的功能。提供了簡單的方法來實現樹型結構的功能。
先安裝**ltree\_hierarchy**這個gem。
```
gem 'ltree_hierarchy'
```
然后執行`bundle`。
我們用已存在的articles這張表來演示。
```
# 20151010060005_add_ltree_to_articles.rb
class AddLtreeToArticles < ActiveRecord::Migration
def change
enable_extension "ltree"
add_column :articles, :parent_id, :integer, index: true
add_column :articles, :path, :ltree
end
end
```
執行`rake db:migrate`。
在app/models/article.rb文件中添加下面那行。
```
class Article < ActiveRecord::Base
has_ltree_hierarchy
end
```
```
root = Article.create!(name: 'UK')
child = Article.create!(name: 'London', parent: root)
subchild = Article.create!(name: 'Hackney', parent: child)
root.parent # => nil
child.parent # => root
root.children # => [child]
root.children.first.children.first # => subchild
subchild.root # => root
```
`parent_id`存的是交節點的id,像上述所說的,`path`存的是以"."分隔的id。
除了`root`,`children`,`parent`,ltree\_hierarchy還實現了其他查詢方法。比如查葉子節點,查后代所有節點之類的。具體的查看官方的readme文件就好了。
完結。