Example of PostgreSQL's ltree

example_of_postgresql_ltree.rb

require 'pg'
db = PG.connect dbname: 'josh_testing'
db.exec 'begin'

def db.exec(*)
  super.to_a
rescue
  $!.set_backtrace caller.drop(1)
  raise
end

db.exec <<~SQL
create extension ltree;
create table units (id integer, paths ltree[]);
create index paths_gist_index  on units using gist  (paths); -- for <, <=, =, >=, >, @>, <@, @, ~, ?
create index paths_btree_index on units using btree (paths); -- for <, <=, =, >=, >

insert into units
values (1, '{1}'),
         (2, '{1.21}'),
           (3, '{1.21.3a}'),
           (4, '{1.21.3b}'),
              (5, '{1.21.3a.4,1.21.3b.4}'),
         (6, '{1.22}'),
           (7, '{1.22.3a}'),
           (8, '{1.22.3b}'),
             (9, '{1.22.3a.4,1.22.3b.4}'),
      (10, '{unrelated}');
SQL

# descendants of 1.21
  db.exec "select * from units where paths <@ '1.21'"
  # => [{"id"=>"2", "paths"=>"{1.21}"},
  #     {"id"=>"3", "paths"=>"{1.21.3a}"},
  #     {"id"=>"4", "paths"=>"{1.21.3b}"},
  #     {"id"=>"5", "paths"=>"{1.21.3a.4,1.21.3b.4}"}]

# ancestors of unit5
  paths = db.exec('select paths from units where id = 5').first["paths"]
  db.exec_params(<<~SQL, [paths]).to_a
    select distinct units.*
    from units, unnest($1::ltree[]) path
    where paths @> path
  SQL
  # => [{"id"=>"1", "paths"=>"{1}"},
  #     {"id"=>"2", "paths"=>"{1.21}"},
  #     {"id"=>"3", "paths"=>"{1.21.3a}"},
  #     {"id"=>"4", "paths"=>"{1.21.3b}"},
  #     {"id"=>"5", "paths"=>"{1.21.3a.4,1.21.3b.4}"}]

# all 3bs
  db.exec "select * from units where paths ~ '*.3b'::lquery"
  # => [{"id"=>"4", "paths"=>"{1.21.3b}"}, {"id"=>"8", "paths"=>"{1.22.3b}"}]

# all 3bs sorted by their proximity to unit #3
  paths = db.exec("select paths from units where id = 3").first['paths']
  db.exec_params(<<~SQL, [paths]).to_a
  with
    ancestry as (
      select
        units.id    as id,
        units.paths as paths,
        target      as target,
        lca(
          potential.ancestor || 'irrelevant',
          target             || 'irrelevant'
        ) as ancestor
      from
        units,
        unnest($1::ltree[]) as target,
        lateral (select lca(paths) ancestor) as potential
    )
  select id, ancestor, paths
  from ancestry
  where nlevel(ancestor) > 0
    and paths ~ '*.3b'::lquery
  order by nlevel(ancestor) desc, id
  SQL
  # => [{"id"=>"4", "ancestor"=>"1.21", "paths"=>"{1.21.3b}"},
  #     {"id"=>"8", "ancestor"=>"1", "paths"=>"{1.22.3b}"}]