Hierarchical datasets via a join table

[mrship]

I'm trying to construct an employee hierarchy where the manager/subordinates relationship is specified using a join table (called positions).

However, when I have the setup below, I get an error where ROM can't find the association.

/Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-3.2.1/lib/rom/registry.rb:35:in `block in fetch': ROM::SQL::Association::Name(employees as subordinates)

I'm also hoping to be able to also establish a manager association so that all subordinates would have their manager setup in a the result set, but I'm beginning to wonder if this almost circular reference is something that ROM can support with regular associations - should I, instead, be using custom views? If so, a pointer would be appreciated.

Anyway, here's my example that blows up:

#!/usr/bin/env ruby
require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'rom'
  gem 'rom-sql'
  gem 'rom-repository'
  gem 'dry-types'
  gem 'sqlite3'
end

require 'rom-sql'
require 'rom-repository'
require 'dry-types'

module Types
  include Dry::Types.module
end

rom = ROM.container(:sql, 'sqlite::memory') do |c|
  c.gateways[:default].create_table :employees do
    primary_key :id, Integer
    column :name, String
  end

  c.gateways[:default].create_table :positions do
    primary_key :id, Integer
    column :manager_id, Integer
    column :participant_id, Integer
  end

  c.gateways[:default].use_logger(Logger.new($stdout))

  c.relation(:employees) do
    schema(:employees, infer: false) do
      attribute :id, Types::Int.meta(primary_key: true)
      attribute :name, Types::String

      associations do
        has_many :employees, as: :subordinates, through: :positions, foreign_key: :participant_id
      end
    end
  end

  c.relation(:positions) do
    schema(:positions, infer: false) do
      attribute :id, Types::Int.meta(primary_key: true)
      attribute :manager_id, Types::Int.meta(foreign_key: true)
      attribute :participant_id, Types::Int.meta(foreign_key: true)
    end
  end
end

jane = rom.relations[:employees].insert(name: "Jane")
fred = rom.relations[:employees].insert(name: "Fred")
rom.relations[:positions].insert(manager_id: jane, participant_id: fred)

class EmployeeRepo < ROM::Repository[:employees]
  relations :employees

  def query
    aggregate(:subordinates).to_a
  end
end

repo = EmployeeRepo.new(rom)

puts repo.query.to_a.inspect

[solnic]

You need to add :subordinates to repo relations, so relations :subordinates (instead of :employees because that's already set)

[mrship]

That gives me a different error because :subordinates doesn't exist as a relation.

/Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-3.2.1/lib/rom/registry.rb:35:in `block in fetch': :subordinates doesn't exist in ROM::RelationRegistry registry (ROM::Registry::ElementNotFoundError)

[solnic]

Try rom-sql 1.3.0 I fixed a bug recently that seems related

[mrship]

I am using the latest gems - here's the full backtrace.

/Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-3.2.1/lib/rom/registry.rb:35:in `block in fetch': :subordinates doesn't exist in ROM::RelationRegistry registry (ROM::Registry::ElementNotFoundError)
	from /Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-3.2.1/lib/rom/registry.rb:32:in `fetch'
	from /Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-3.2.1/lib/rom/registry.rb:32:in `fetch'
	from /Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-3.2.1/lib/rom/container.rb:157:in `relation'
	from /Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-repository-1.3.1/lib/rom/repository.rb:118:in `block (2 levels) in initialize'
	from /Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-repository-1.3.1/lib/rom/repository.rb:117:in `each'
	from /Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-repository-1.3.1/lib/rom/repository.rb:117:in `block in initialize'
	from /Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-3.2.1/lib/rom/relation_registry.rb:6:in `initialize'
	from /Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-repository-1.3.1/lib/rom/repository.rb:116:in `new'
	from /Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-repository-1.3.1/lib/rom/repository.rb:116:in `initialize'
	from /Users/shipmana/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/rom-repository-1.3.1/lib/rom/repository/root.rb:61:in `initialize'
	from ./hierarchy.rb:67:in `new'
	from ./hierarchy.rb:67:in `<main>'

[tomoyoirl]

I believe I'm encountering a similar problem. I have relations Contacts, Bookings, and BookingContacts.

I'm trying to configure a BookingContactsRepo (which seems a fine place to define the logical operation for "here is the first contact, set up a contact and a potential booking so that they have a reference number to work with for the future"). However, attempting to do so tells me that

     ROM::Registry::ElementNotFoundError:
       :booking_contacts doesn't exist in ROM::RelationRegistry registry
     # ./vendor/ruby/2.4.0/gems/rom-3.3.1/lib/rom/registry.rb:35:in `block in fetch'
     # ./vendor/ruby/2.4.0/gems/rom-3.3.1/lib/rom/registry.rb:32:in `fetch'
     # ./vendor/ruby/2.4.0/gems/rom-3.3.1/lib/rom/registry.rb:32:in `fetch'
     # ./vendor/ruby/2.4.0/gems/rom-repository-1.4.0/lib/rom/repository.rb:128:in `block (2 levels) in initialize'
     # ./vendor/ruby/2.4.0/gems/rom-repository-1.4.0/lib/rom/repository.rb:127:in `each'
     # ./vendor/ruby/2.4.0/gems/rom-repository-1.4.0/lib/rom/repository.rb:127:in `block in initialize'
     # ./vendor/ruby/2.4.0/gems/rom-3.3.1/lib/rom/relation_registry.rb:6:in `initialize'
     # ./vendor/ruby/2.4.0/gems/rom-repository-1.4.0/lib/rom/repository.rb:126:in `new'
     # ./vendor/ruby/2.4.0/gems/rom-repository-1.4.0/lib/rom/repository.rb:126:in `initialize'
     # ./vendor/ruby/2.4.0/gems/rom-repository-1.4.0/lib/rom/repository/root.rb:61:in `initialize'
     # ./vendor/ruby/2.4.0/gems/dry-auto_inject-0.4.3/lib/dry/auto_inject/strategies/args.rb:52:in `initialize'
     # ./vendor/ruby/2.4.0/gems/dry-auto_inject-0.4.3/lib/dry/auto_inject/strategies/args.rb:52:in `initialize'
     # ./vendor/ruby/2.4.0/gems/dry-auto_inject-0.4.3/lib/dry/auto_inject/strategies/args.rb:52:in `initialize'
     # ./vendor/ruby/2.4.0/gems/dry-auto_inject-0.4.3/lib/dry/auto_inject/strategies/args.rb:17:in `new'
     # ./vendor/ruby/2.4.0/gems/dry-auto_inject-0.4.3/lib/dry/auto_inject/strategies/args.rb:17:in `block (2 levels) in define_new'
     # ./vendor/ruby/2.4.0/gems/dry-auto_inject-0.4.3/lib/dry/auto_inject/strategies/args.rb:17:in `block (2 levels) in define_new'
     # ./vendor/ruby/2.4.0/gems/dry-auto_inject-0.4.3/lib/dry/auto_inject/strategies/args.rb:17:in `block (2 levels) in define_new'
     # ./vendor/ruby/2.4.0/gems/dry-system-0.7.1/lib/dry/system/loader.rb:48:in `call'
     # ./vendor/ruby/2.4.0/gems/dry-system-0.7.1/lib/dry/system/component.rb:104:in `instance'
     # ./vendor/ruby/2.4.0/gems/dry-system-0.7.1/lib/dry/system/auto_registrar/configuration.rb:13:in `block in <class:Configuration>'
     # ./vendor/ruby/2.4.0/gems/dry-system-0.7.1/lib/dry/system/auto_registrar.rb:38:in `block (3 levels) in call'
     # ./vendor/ruby/2.4.0/gems/dry-container-0.6.0/lib/dry/container/item.rb:29:in `call'
     # ./vendor/ruby/2.4.0/gems/dry-container-0.6.0/lib/dry/container/resolver.rb:25:in `call'
     # ./vendor/ruby/2.4.0/gems/dry-container-0.6.0/lib/dry/container/mixin.rb:112:in `resolve'
     # ./vendor/ruby/2.4.0/gems/dry-system-0.7.1/lib/dry/system/container.rb:433:in `resolve'
     # ./vendor/ruby/2.4.0/gems/dry-container-0.6.0/lib/dry/container/mixin.rb:125:in `[]'
     # ./vendor/ruby/2.4.0/gems/dry-auto_inject-0.4.3/lib/dry/auto_inject/strategies/args.rb:14:in `block (3 levels) in define_new'
     # ./vendor/ruby/2.4.0/gems/dry-auto_inject-0.4.3/lib/dry/auto_inject/strategies/args.rb:13:in `map'
     # ./vendor/ruby/2.4.0/gems/dry-auto_inject-0.4.3/lib/dry/auto_inject/strategies/args.rb:13:in `with_index'
     # ./vendor/ruby/2.4.0/gems/dry-auto_inject-0.4.3/lib/dry/auto_inject/strategies/args.rb:13:in `block (2 levels) in define_new'
     # ./vendor/ruby/2.4.0/gems/dry-system-0.7.1/lib/dry/system/loader.rb:48:in `call'
     # ./vendor/ruby/2.4.0/gems/dry-system-0.7.1/lib/dry/system/component.rb:104:in `instance'
     # ./vendor/ruby/2.4.0/gems/dry-system-0.7.1/lib/dry/system/auto_registrar/configuration.rb:13:in `block in <class:Configuration>'
     # ./vendor/ruby/2.4.0/gems/dry-system-0.7.1/lib/dry/system/auto_registrar.rb:38:in `block (3 levels) in call'
     # ./vendor/ruby/2.4.0/gems/dry-container-0.6.0/lib/dry/container/item.rb:29:in `call'
     # ./vendor/ruby/2.4.0/gems/dry-container-0.6.0/lib/dry/container/resolver.rb:25:in `call'
     # ./vendor/ruby/2.4.0/gems/dry-container-0.6.0/lib/dry/container/mixin.rb:112:in `resolve'
     # ./vendor/ruby/2.4.0/gems/dry-system-0.7.1/lib/dry/system/container.rb:433:in `resolve'
     # ./vendor/ruby/2.4.0/gems/dry-container-0.6.0/lib/dry/container/mixin.rb:125:in `[]'
     # ./vendor/ruby/2.4.0/gems/dry-web-roda-0.7.3/lib/dry/web/roda/application.rb:25:in `resolve'
     # ./vendor/ruby/2.4.0/gems/dry-web-roda-0.7.3/lib/dry/web/roda/application.rb:29:in `[]'
     # ./apps/main/web/routes/contact.rb:9:in `block (2 levels) in <class:Application>'

... which is the line in my app where I attempt to

include MyApp::Import.args['repo.booking_contacts_repo']

which is defined as

    class BookingContactsRepo < MyApp::Repository[:booking_contacts]

I'm pretty confident it's registered, but even so, added this line to my setup after the dry-web-roda autogenerated auto-registration line:

config.register_relation(Persistence::Relations::BookingContacts)

whereupon I get aROM::RelationAlreadyDefinedError.

This is all on gems freshly installed within the past week, including:

    rom-mapper (0.5.1)
    rom-repository (1.4.0)
    rom-sql (1.3.3)

[solnic]

I'll try to reproduce this with rom 4.0.0.beta. There's a big chance it'll work there as I've fixed a lot of issues related to handling aliased relations/associations. It will not be fixed in rom-sql 1.3.x unfortunately, because it'd be too hard to backport the fixes.