ltree介绍

ltree是PostgreSQL的一个扩展插件，即extension，使用它可以实现树型结构，而且还支持索引和丰富的查询。

ltree官方文档给出了详细的解释。

它的概念很简单，打个比方，比如，我们要存一个树型菜单，不限定级数，有祖先(根)，根子节有子节点，子节点又有子节点，以此类推，形成一颗树。假如我们存公司的数据，它的表名叫companies，有个字段叫name，存的是分公司的名称。有一个很简单的方法，来实现这种树型结构，只要在companies表中增加一个字段parent_id即可，它存的是父节点的id，以此来找到父节点，根节点的parent_id为null，其他节点都为父节点的id。这种方式是可以的，它也能查询到所有的节点，由于存有parent_id，它找子节点，父节点，根节点都很简单，若要找其他节点，只能通过遍历了，效率较低。而且，每次添加节点，都要查找父节点的id，即parent_id，这样也不够直观和灵活。

而ltree是在数据库级别支持的树型结构。它支持丰富的查询。

ltree使用

我们来演示一下搭建这样的树型结构。

  1.                         Top
  2.                      /   |  \
  3.              Science Hobbies Collections
  4.                  /       |              \
  5.         Astronomy   Amateurs_Astronomy Pictures
  6.            /  \                            |
  7. Astrophysics  Cosmology                Astronomy
  8.                                         /  |    \
  9.                                  Galaxies Stars Astronauts

也会演示它强大的查询方法。

首先开启ltree扩展。

  1. sudo -u postgres psql
  2. CREATE EXTENSION IF NOT EXISTS ltree;

创建数据库表。表名为test，字段名为path，类型指定为ltree。

  1. CREATE TABLE test (path ltree);

插入数据。

  1. INSERT INTO test VALUES ('Top');
  2. INSERT INTO test VALUES ('Top.Science');
  3. INSERT INTO test VALUES ('Top.Science.Astronomy');
  4. INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
  5. INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
  6. INSERT INTO test VALUES ('Top.Hobbies');
  7. INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
  8. INSERT INTO test VALUES ('Top.Collections');
  9. INSERT INTO test VALUES ('Top.Collections.Pictures');
  10. INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
  11. INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
  12. INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
  13. INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');

因为要查询，给数据表加上索引，索引有两种，分别是btree和gist，GiST支持的操作符更为丰富些。具体可看官方文档。

  1. CREATE INDEX path_gist_idx ON test USING gist(path);
  2. CREATE INDEX path_idx ON test USING btree(path);

现在整张表的结果是这样。

  1. rails365_pro=# select * from test;
  2.                      path                      
  3. -----------------------------------------------
  4.  Top
  5.  Top.Science
  6.  Top.Science.Astronomy
  7.  Top.Science.Astronomy.Astrophysics
  8.  Top.Science.Astronomy.Cosmology
  9.  Top.Hobbies
  10.  Top.Hobbies.Amateurs_Astronomy
  11.  Top.Collections
  12.  Top.Collections.Pictures
  13.  Top.Collections.Pictures.Astronomy
  14.  Top.Collections.Pictures.Astronomy.Stars
  15.  Top.Collections.Pictures.Astronomy.Galaxies
  16.  Top.Collections.Pictures.Astronomy.Astronauts
  17. (13 rows)

接下来演示查询方法。

先来演示第一个，再来介绍语法。

  1. rails365_pro=# SELECT path FROM test WHERE path ~ '*.Astronomy.*';
  2.                      path                      
  3. -----------------------------------------------
  4.  Top.Science.Astronomy
  5.  Top.Science.Astronomy.Astrophysics
  6.  Top.Science.Astronomy.Cosmology
  7.  Top.Collections.Pictures.Astronomy
  8.  Top.Collections.Pictures.Astronomy.Stars
  9.  Top.Collections.Pictures.Astronomy.Galaxies
  10.  Top.Collections.Pictures.Astronomy.Astronauts
  11. (7 rows)

这样会查找所有包含Astronomy的项。

根据官方的解释。语法大约是这样的。

  1. SELECT path FROM test WHERE ltree 操作符 lquery;

ltree就是要查找的字段名。~就是操作符，官方列出了所有支持的操作符，也给了解释。lquery是表示被匹配的正则表达式的字符串。

  1. rails365_pro=# SELECT path FROM test WHERE path <@ 'Top.Science';
  2.                 path                
  3. ------------------------------------
  4.  Top.Science
  5.  Top.Science.Astronomy
  6.  Top.Science.Astronomy.Astrophysics
  7.  Top.Science.Astronomy.Cosmology
  8. (4 rows)

<@的意思是”is left argument a descendant of right (or equal)?”，就是返回指定元素的后代啦，返回的结果正是我们期待的。

只要懂得了语法，ltree支持的所有操作符只要看官方文档的解释就可以使用了。

ltree还支持函数。用于选择和组合返回我们想要的结果。比如:

  1. rails365_pro=# select * from test WHERE path <@ 'Top.Science.Astronomy';
  2.                 path                
  3. ------------------------------------
  4.  Top.Science.Astronomy
  5.  Top.Science.Astronomy.Astrophysics
  6.  Top.Science.Astronomy.Cosmology
  7. (3 rows)
  9. rails365_pro=# SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ 'Top.Science.Astronomy';
  10.                  ?column?                 
  11. ------------------------------------------
  12.  Top.Science.Space.Astronomy
  13.  Top.Science.Space.Astronomy.Astrophysics
  14.  Top.Science.Space.Astronomy.Cosmology
  15. (3 rows)

subpath的语法是这样的。subltree(ltree, int start, int end)中start是起始位置(从0开始算)，end是结束位置，但不包含结束位置。subpath(path,0,2)返回的就是第一个元素和第二个,即Top.Science.。

还有其他函数，看官方文档的解释就好了。

ltree介绍完了。

ltree_hierarchy的使用

ltree_hierarchy是一个ruby的gem，它实现了PostgreSQL的ltree的功能。提供了简单的方法来实现树型结构的功能。

先安装ltree_hierarchy这个gem。

  1. gem 'ltree_hierarchy'

然后执行bundle。

我们用已存在的articles这张表来演示。

  1. # 20151010060005_add_ltree_to_articles.rb
  2. class AddLtreeToArticles < ActiveRecord::Migration
  3.   def change
  4.     enable_extension "ltree"
  5.     add_column :articles, :parent_id, :integer, index: true
  6.     add_column :articles, :path, :ltree
  7.   end
  8. end

执行rake db:migrate。

在app/models/article.rb文件中添加下面那行。

  1. class Article < ActiveRecord::Base
  2.   has_ltree_hierarchy
  3. end
  1.   root     = Article.create!(name: 'UK')
  2.   child    = Article.create!(name: 'London', parent: root)
  3.   subchild = Article.create!(name: 'Hackney', parent: child)
  5.   root.parent   # => nil
  6.   child.parent # => root
  7.   root.children # => [child]
  8.   root.children.first.children.first # => subchild
  9.   subchild.root # => root

parent_id存的是交节点的id，像上述所说的，path存的是以”.”分隔的id。

除了root，children，parent，ltree_hierarchy还实现了其他查询方法。比如查叶子节点，查后代所有节点之类的。具体的查看官方的readme文件就好了。

完结。