世界上最伟大的投资就是投资自己的教育

全场限时 5 折

首页PostgreSQL
随风 · 练气

PostgreSQL 的 ltree 插件 (七)

随风发布于3586 次阅读

ltree 介绍

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

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

它的概念很简单,打个比方,比如,我们要存一个树型菜单,不限定级数,有祖先 (根),根子节有子节点,子节点又有子节点,以此类推,形成一颗树。假如我们存公司的数据,它的表名叫 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是一个 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。

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

完结。

本站文章均为原创内容,如需转载请注明出处,谢谢。

0 条回复
暂无回复~~
相关小书
postgresql教程

postgresql教程

postgresql 最全面,最细致的特性介绍与应用教程

发表于

喜欢
统计信息
    学员: 29063
    视频数量: 1973
    文章数量: 489

© 汕尾市求知科技有限公司 | Rails365 Gitlab | Qiuzhi99 Gitlab | 知乎 | b 站 | 搜索

粤公网安备 44152102000088号粤公网安备 44152102000088号 | 粤ICP备19038915号

Top