# 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文件就好了。 完结。