README.markdown

To get this running locally:

bundle
createdb hierarchy_association
bundle exec bundle exec ruby hierarchy_association.rb

Gemfile

source "https://rubygems.org"

gem "rom"
gem "rom-repository"
gem "rom-sql"
gem "pg"
gem "dry-monitor"

hierarchy_association.rb

require "bundler/setup"
require "rom"
require "rom/sql"
require "dry/monitor/notifications"
require "dry/monitor/sql/logger"
require "logger"

# Set up logging

$stdout.sync = true
logger = Logger.new($stdout)

notifications = Dry::Monitor::Notifications.new(:hierarchy)
notifications.register_event :sql

Dry::Monitor::SQL::Logger.new(logger).subscribe(notifications)

# Configure database URL

config = ROM::Configuration.new(:sql, "postgres://localhost/hierarchy_association")
config.plugin(:sql, relations: :instrumentation) do |p|
  p.notifications = notifications
end
rom = ROM.container(config)

# Migrate database

rom.gateways[:default].tap do |gateway|
  migration = gateway.migration do
    change do
      run "CREATE EXTENSION IF NOT EXISTS ltree"

      drop_table? :categories

      create_table :categories do
        primary_key :id
        column :name, :text, null: false
        column :parent_id, :integer
        column :path, :ltree
      end
    end
  end

  migration.apply gateway.connection, :up

  # Populate data

  gateway.connection.tap do |connection|
    connection.execute("INSERT INTO categories (id, name, parent_id, path) VALUES (1, 'Books', NULL, '1')")
    connection.execute("INSERT INTO categories (id, name, parent_id, path) VALUES (2, 'Fiction', 1, '1.2')")
    connection.execute("INSERT INTO categories (id, name, parent_id, path) VALUES (3, 'Non-Fiction', 1, '1.3')")
    connection.execute("INSERT INTO categories (id, name, parent_id, path) VALUES (4, 'Fantasy', 2, '1.2.4')")
    connection.execute("INSERT INTO categories (id, name, parent_id, path) VALUES (5, 'Science-Fiction', 2, '1.2.5')")
  end
end

# Define relations

module Relations
  class Categories < ROM::Relation[:sql]
    schema :categories, infer: true do
      associations do
        has_many :categories,
          as: :ancestors,
          view: :self_and_ancestors,
          override: true
      end
    end

    def self_and_ancestors(association, categories)
      full_hierarchy(id: categories.pluck(:id))
    end

    def full_hierarchy(conditions)
      conditions = conditions.each_with_object({}) { |(key, value), hash|
        hash[Sequel.lit("nodes.#{key}")] = value
      }

      join(
        :categories,
        Sequel.lit("categories.path @> nodes.path"),
        table_alias: :nodes
      ).where(conditions).order(self[:path])
    end
  end
end

# Register relations and finalize rom container

config.register_relation Relations::Categories

rom = ROM.container(config)

# Define entities/repositories

module Entities
  class Category < ROM::Struct
  end
end

class CategoryRepo < ROM::Repository[:categories]
  struct_namespace Entities

  def with_ancestors
    categories.combine(:ancestors)
  end

  def with_ancestors_by_pk(id)
    with_ancestors.by_pk(id).one
  end
end

# Instantiate repository and inspect output

repo = CategoryRepo.new(rom)
result = repo.with_ancestors_by_pk(4)

puts ""
puts "Querying a single category:"
puts result.inspect
puts "Ancestors:"
puts result.ancestors.inspect
puts ""

results = repo.with_ancestors.to_a

puts ""
puts "Querying all categories:"
puts results.inspect
puts "Ancestors:"
puts results.map(&:ancestors).inspect