ltree Hierarchy System

PostgreSQL's ltree extension provides efficient hierarchical queries with human-readable slug-based paths (e.g., 'goods.towel_warmers') for better code readability and stability.

Quick Reference

Resource	Location
Lineage Concern	`app/concerns/models/lineage.rb`
LtreeLineage Concern	`app/concerns/models/ltree_lineage.rb`
Arel Predicates + Ransack	`config/initializers/arel_ltree_predicates.rb`
WhereChain Helpers	`lib/active_record_extended/ltree_query_methods.rb`
Path Constants	`app/constants/ltree_paths.rb`
Tests	`test/lib/ltree_query_methods_test.rb`

Architecture

Dual Path Strategy

Each hierarchical table maintains TWO ltree columns:

Column	Format	Purpose
`ltree_path_ids`	`'1.2.3'`	Backward compatibility, numeric lookups
`ltree_path_slugs`	`'floor_heating.tempzone'`	Human-readable, code constants

Data Flow

USER INPUT                    DERIVED (auto-computed by triggers)
──────────                    ───────────────────────────────────
parent_id  ──────────────────► ltree_path_ids (traversing parent chain)
name       ──────────────────► ltree_path_slugs (from slugified names)

Tables Using ltree

Table	ltree Columns	Purpose
`product_lines`	`ltree_path_ids`, `ltree_path_slugs`	Product hierarchy
`product_categories`	`ltree_path_ids`, `ltree_path_slugs`	Category hierarchy
`sources`	`ltree_path_ids`, `ltree_path_slugs`	Lead source hierarchy
`items`	`primary_pl_path_`, `pc_path_`, `all_pl_paths_*`	Denormalized for queries

ltree Operators

Operator	Name	Description	Example
`<@`	Descendant of	Left is child of right	`'goods.controls' <@ 'goods'` → true
`@>`	Ancestor of	Left contains right	`'goods' @> 'goods.controls'` → true
`~`	Matches lquery	Pattern match with wildcards	`path ~ '.tempzone.'`
`?`	Matches any	Matches any lquery in array	`path ? ARRAY['.a', '.b']`

ltree Functions

Function	Description	Example
`nlevel(ltree)`	Number of labels	`nlevel('goods.controls')` → 2
`subpath(ltree, offset, len)`	Extract subpath	`subpath('goods.controls.thermostats', 0, 2)` → 'goods.controls'
`subpath(ltree, offset)`	Extract to end	`subpath('goods.controls', 1)` → 'controls'
`lca(ltree, ltree, ...)`	Longest common ancestor	`lca('goods.controls', 'goods.sensors')` → 'goods'
`index(ltree, ltree)`	Position of match	`index('goods.controls.thermostats', 'controls')` → 1

Models::Lineage Concern

File: app/concerns/models/lineage.rb

Base concern for hierarchical models using parent_id.

Configuration

class ProductLine < ApplicationRecord
  include Models::Lineage
  acts_as_lineage order: :name
end

Options

Option	Default	Description
`:order`	`:name`	Column for ordering children
`:counter_cache`	`true`	Maintain `children_count`
`:foreign_key`	`:parent_id`	Foreign key column
`:touch_parent`	`false`	Touch parent on changes

Class Methods

Model.parents_only           # Records with no parent
Model.children_only          # Records with a parent
Model.roots                  # Top-level records
Model.self_and_descendants_ids(id)  # ltree-powered (via LtreeLineage)

Instance Methods

record.root?                 # Is this a root record?
record.leaf?                 # Has no children?
record.root                  # Get root ancestor
record.depth                 # Tree depth (0 for root)
record.ancestors             # Parent chain
record.descendants           # All children recursively

Models::LtreeLineage Concern

File: app/concerns/models/ltree_lineage.rb

Extends Models::Lineage with ltree-powered query methods.

Configuration

class ProductLine < ApplicationRecord
  include Models::Lineage
  include Models::LtreeLineage

  acts_as_lineage order: :name
  acts_as_ltree_lineage
end

Class Methods

ProductLine.descendants_ids(5)              # [6, 7, 8, ...]
ProductLine.self_and_descendants_ids(5)     # [5, 6, 7, 8, ...]
ProductLine.ancestors_ids(8)                # [5, 1] (child-to-root)
ProductLine.self_and_ancestors_ids(8)       # [8, 5, 1]
ProductLine.root_ids(8, 9)                  # [1, 1] (roots for each)

Instance Methods

record.ltree_descendants              # Descendant records
record.ltree_self_and_descendants     # Self + descendants
record.ltree_ancestors                # Ancestor records
record.ltree_self_and_ancestors       # Self + ancestors
record.ltree_root                     # Root record
record.ltree_siblings                 # Siblings (same parent)
record.ltree_depth                    # Tree depth
record.ltree_root?                    # Is root?
record.ltree_leaf?                    # Is leaf?
record.ltree_descendant_of?(other)    # Relationship check
record.ltree_ancestor_of?(other)      # Relationship check

Scopes

ProductLine.ltree_descendants_of(record)
ProductLine.ltree_ancestors_of(record)
ProductLine.ltree_self_and_descendants_of(id)
ProductLine.ltree_matching_slug('floor_heating.*')

Query API — Arel Predicates (Canonical)

File: config/initializers/arel_ltree_predicates.rb

All ltree WHERE clause operators (<@, @>, ~) use Arel predicates via Model[:column]:

# Descendant of — single path
Item.where(Item[:pc_path_slugs].ltree_descendant(LtreePaths::PC_CONTROLS))
# => WHERE pc_path_slugs <@ 'goods.controls'::ltree

# Descendant of — array of paths
Item.where(Item[:pc_path_slugs].ltree_descendant(['goods.controls', 'goods.heating_elements']))
# => WHERE pc_path_slugs <@ ANY(ARRAY[...]::ltree[])

# Negation
Item.where.not(Item[:pc_path_slugs].ltree_descendant(LtreePaths::PC_PUBLICATIONS))
# => WHERE NOT (pc_path_slugs <@ 'goods.publications'::ltree)

# Ancestor of — single path
ProductLine.where(ProductLine[:ltree_path_ids].ltree_ancestor('1.2.3'))
# => WHERE ltree_path_ids @> '1.2.3'::ltree

# Ancestor of — array of paths
ProductLine.where(ProductLine[:ltree_path_ids].ltree_ancestor(['1.2', '3.4']))
# => WHERE ltree_path_ids @> ANY(ARRAY[...]::ltree[])

# Pattern matching — single
ProductLine.where(ProductLine[:ltree_path_slugs].ltree_matches('*.tempzone.*'))
# => WHERE ltree_path_slugs ~ '*.tempzone.*'::lquery

# Pattern matching — array
ProductLine.where(ProductLine[:ltree_path_slugs].ltree_matches(['*.tempzone.*', '*.environ.*']))
# => WHERE ltree_path_slugs ? ARRAY[...]::lquery[]

# Cross-table via join
ProductSpecification.joins(:product_line)
  .where(ProductLine[:ltree_path_ids].ltree_descendant(paths))

# In concerns (model-agnostic — use arel_table)
where(arel_table[:pc_path_slugs].ltree_descendant(LtreePaths::PC_CONTROLS))

Ransack Integration

Item.ransack(pc_path_slugs_ltree_descendant: 'goods.controls').result

<%= search_form_for @q do |f| %>
  <%= f.select :pc_path_slugs_ltree_descendant,
               [['Floor Heating', 'goods.heating_elements'],
                ['Controls', 'goods.controls']],
               include_blank: 'All Categories' %>
<% end %>

WhereChain Helpers (Kept)

File: lib/active_record_extended/ltree_query_methods.rb

These methods use ltree functions with no simple Arel equivalent:

# Contains check — uses index() function
# Checks if any of the given IDs appear as labels in the ltree path(s)
ViewProductCatalog.where.ltree_contains(:all_pl_paths_ids, [1, 2])
ProductLine.where.ltree_contains(:ltree_path_ids, '102', array: false)

# Depth queries — uses nlevel() function
ProductCategory.where.ltree_depth(:ltree_path_ids, 1)         # Exact
ProductCategory.where.ltree_depth(:ltree_path_ids, 2, :gt)    # Greater than
# Operators: :eq, :gt, :gte, :lt, :lte

SELECT Helpers

ProductCategory.select_ltree_depth(:ltree_path_ids)
# => each record has .ltree_path_ids_depth

ProductCategory.select_ltree_root(:ltree_path_slugs)
# => each record has .ltree_path_slugs_root

ProductCategory.select_ltree_parent(:ltree_path_slugs)
# => each record has .ltree_path_slugs_parent

ProductCategory.select_ltree_depth(:ltree_path_ids, 'tree_depth')
# => custom alias

ORDER Helpers

ProductCategory.order_by_ltree_depth(:ltree_path_slugs)         # ASC
ProductCategory.order_by_ltree_depth(:ltree_path_slugs, :desc)  # DESC

Arel Helpers

For composing complex SQL:

ActiveRecordExtended::Ltree.nlevel(:path)           # nlevel(path)
ActiveRecordExtended::Ltree.subpath(:path, 0, 1)    # subpath(path, 0, 1)
ActiveRecordExtended::Ltree.subpath(:path, 1)        # subpath(path, 1) - to end
ActiveRecordExtended::Ltree.lca(:path1, :path2)      # lca(path1, path2)
ActiveRecordExtended::Ltree.index(:path, 'goods')    # index(path, 'goods')
ActiveRecordExtended::Ltree.text2ltree('goods.controls')
ActiveRecordExtended::Ltree.ltree2text(:path)

LtreePaths Constants

File: app/constants/ltree_paths.rb

Hardcoded slug paths for frequently-used hierarchies. Eliminates database lookups.

Product Categories

LtreePaths::PC_GOODS                    # 'goods'
LtreePaths::PC_SERVICES                 # 'services'
LtreePaths::PC_HEATING_ELEMENTS         # 'goods.heating_elements'
LtreePaths::PC_CONTROLS                 # 'goods.controls'
LtreePaths::PC_CONTROLS_THERMOSTATS     # 'goods.controls.thermostats'
LtreePaths::PC_ACCESSORIES              # 'goods.accessories'
LtreePaths::PC_TOWEL_WARMERS            # 'goods.towel_warmers'
LtreePaths::PC_MIRRORS                  # 'goods.mirrors'
LtreePaths::PC_RADIANT_PANELS          # 'goods.radiant_panels'
LtreePaths::PC_POWER                    # 'goods.power'
LtreePaths::PC_SENSORS                  # 'goods.sensors'
LtreePaths::PC_PUBLICATIONS             # 'goods.publications'

Product Lines

LtreePaths::PL_FLOOR_HEATING                    # 'floor_heating'
LtreePaths::PL_FLOOR_HEATING_TEMPZONE           # 'floor_heating.tempzone'
LtreePaths::PL_FLOOR_HEATING_CONTROL            # 'floor_heating.control'
LtreePaths::PL_FLOOR_HEATING_CONTROL_SMARTSTAT  # 'floor_heating.control.smartstat'
LtreePaths::PL_SNOW_MELTING                     # 'snow_melting'
LtreePaths::PL_SNOW_MELTING_CONTROL             # 'snow_melting.control'
LtreePaths::PL_TOWEL_WARMER                     # 'towel_warmer'
LtreePaths::PL_RADIANT_PANEL                    # 'radiant_panel'
LtreePaths::PL_LED_MIRROR                       # 'led_mirror'

Verification

LtreePaths.verify_all
# => ✅ All 39 ltree slug paths verified!

Item Model Scopes (Example)

class Item < ApplicationRecord
  scope :heating_elements, -> { where(Item[:pc_path_slugs].ltree_descendant(LtreePaths::PC_HEATING_ELEMENTS)) }
  scope :controls,         -> { where(Item[:pc_path_slugs].ltree_descendant(LtreePaths::PC_CONTROLS)) }
  scope :towel_warmers,    -> { where(Item[:pc_path_slugs].ltree_descendant(LtreePaths::PC_TOWEL_WARMERS)) }
  scope :goods,            -> { where(Item[:pc_path_slugs].ltree_descendant(LtreePaths::PC_GOODS)) }
  scope :non_publications, -> { where.not(Item[:pc_path_slugs].ltree_descendant(LtreePaths::PC_PUBLICATIONS)) }

  scope :floor_heating_controls, -> {
    controls.where(Item[:primary_pl_path_slugs].ltree_descendant(LtreePaths::PL_FLOOR_HEATING_CONTROL))
  }
  scope :tempzones, -> {
    where(Item[:primary_pl_path_slugs].ltree_descendant(LtreePaths::PL_FLOOR_HEATING_TEMPZONE)).merge(heating_elements)
  }
end

Database Triggers

PostgreSQL triggers automatically maintain ltree paths on items:

Trigger	Table	Fires On	Action
`sync_item_ltree_paths`	`items`	INSERT/UPDATE of FK columns	Syncs `primary_pl_path_` and `pc_path_`
`sync_item_all_pl_paths`	`item_product_lines`	INSERT/UPDATE/DELETE	Syncs `all_pl_paths_*`
`cascade_pl_path_to_items`	`product_lines`	UPDATE of `ltree_path_*`	Cascades changes to items
`cascade_pc_path_to_items`	`product_categories`	UPDATE of `ltree_path_*`	Cascades changes to items

Trigger Functions

Located in db/functions/:

Function	Purpose
`sync_item_ltree_paths_v02.sql`	Syncs both ID and slug paths from PL/PC
`sync_item_all_pl_paths_v02.sql`	Syncs array columns from item_product_lines
`cascade_pl_path_to_items_v02.sql`	Cascades ProductLine path changes
`cascade_pc_path_to_items_v02.sql`	Cascades ProductCategory path changes

Performance

Before ltree (Array Overlap)

-- Slow: Cannot use index efficiently
WHERE inherited_item_product_line_ids && ARRAY[5]::integer[]
-- Execution: 7-12 seconds

After ltree (GiST Index)

-- Fast: Uses GiST index
WHERE pc_path_slugs <@ 'goods.towel_warmers'
-- Execution: <100ms

Performance Comparison

Query Type	Before	After	Improvement
Item category scope	~50ms	<5ms	10x faster
URL-based filtering	2 queries	1 query	50% fewer queries
Items by PL descendants	N+1 risk	Single query	Eliminated N+1
Path sync on save	Rails callback	DB trigger	More reliable

Indexes

CREATE INDEX index_items_on_primary_pl_path_slugs ON items USING gist(primary_pl_path_slugs);
CREATE INDEX index_items_on_pc_path_slugs ON items USING gist(pc_path_slugs);
CREATE INDEX index_items_on_all_pl_paths_slugs ON items USING gist(all_pl_paths_slugs);

CREATE INDEX idx_product_lines_ltree_path_ids ON product_lines USING gist(ltree_path_ids);
CREATE INDEX idx_product_lines_ltree_path_slugs ON product_lines USING gist(ltree_path_slugs);
CREATE INDEX idx_product_categories_ltree_path_ids ON product_categories USING gist(ltree_path_ids);
CREATE INDEX idx_product_categories_ltree_path_slugs ON product_categories USING gist(ltree_path_slugs);

Testing

mise exec -- bin/rails test test/lib/ltree_query_methods_test.rb

Key Files

File	Purpose
`app/concerns/models/lineage.rb`	Base hierarchical model concern
`app/concerns/models/ltree_lineage.rb`	ltree query methods for hierarchies
`config/initializers/arel_ltree_predicates.rb`	Canonical Arel predicates + Ransack integration
`lib/active_record_extended/ltree_query_methods.rb`	ltree_contains, ltree_depth, SELECT/ORDER helpers
`config/initializers/ltree_query_methods.rb`	Loads the AR extension
`app/constants/ltree_paths.rb`	Hardcoded path constants
`db/functions/sync_item_ltree_paths_v02.sql`	Trigger function for items
`db/functions/sync_item_all_pl_paths_v02.sql`	Trigger function for item_product_lines
`db/functions/cascade_pl_path_to_items_v02.sql`	Cascade function for product_lines
`db/functions/cascade_pc_path_to_items_v02.sql`	Cascade function for product_categories

ltree Hierarchy System

Quick Reference

Architecture

Dual Path Strategy

Data Flow

Tables Using ltree

ltree Operators

ltree Functions

Models::Lineage Concern

Configuration

Options

Class Methods

Instance Methods

Models::LtreeLineage Concern

Configuration

Class Methods

Instance Methods

Scopes

Query API — Arel Predicates (Canonical)

Ransack Integration

WhereChain Helpers (Kept)

SELECT Helpers

ORDER Helpers

Arel Helpers

LtreePaths Constants

Product Categories

Product Lines

Verification

Item Model Scopes (Example)

Database Triggers

Trigger Functions

Performance

Before ltree (Array Overlap)

After ltree (GiST Index)

Performance Comparison

Indexes

Testing

Key Files

References