Support for has_many-through with a join table that "has many targets"

[v-kolesnikov]

require 'rom'
require 'sqlite3'

rom = ROM.container(:sql, 'sqlite::memory') do |conf|
  conf.default.create_table(:bookings) do
    primary_key :booking_id
  end

  conf.default.create_table(:tickets) do
    primary_key :ticket_id
    column :booking_id, Integer
  end

  conf.default.create_table(:passes) do
    primary_key :pass_id
    column :ticket_id, Integer
  end

  conf.relation(:bookings) do
    schema(infer: true) do
      associations do
        has_many :tickets
        has_many :passes, through: :tickets
      end
    end

    def with_passes
      join(passes)
    end
  end

  conf.relation(:tickets) do
    schema(infer: true) do
      associations do
        belongs_to :booking
        has_many :passes
      end
    end
  end

  conf.relation(:passes) do
    schema(infer: true) do
      associations do
        belongs_to :tickets
      end
    end
  end
end

joined = rom.relation(:bookings).join(rom.relation(:passes))

Expected SQL:

SELECT `bookings`.`booking_id`
FROM `bookings`
INNER JOIN `tickets` ON (`bookings`.`booking_id` = `tickets`.`booking_id`)
INNER JOIN `passes` ON (`tickets`.`ticket_id` = `passes`.`pass_id`)
ORDER BY `bookings`.`booking_id`

Actual SQL:

SELECT `bookings`.`booking_id`
FROM `bookings`
INNER JOIN `tickets` ON (`bookings`.`booking_id` = `tickets`.`booking_id`)
INNER JOIN `passes` ON (`bookings`.`booking_id` = `tickets`.`booking_id`)
ORDER BY `bookings`.`booking_id`

[solnic]

OK I'm not sure if this is a bug after all. tickets is not a typical join table (it would be if it had a belongs_to :passes and has_many-through is designed to work with join tables exclusively. I'm gonna put this one on hold for now.

[solnic]

@flash-gordon wdyt about supporting has-many-through when join table is not a typical join table that has both FKs, and instead it 'has-many' targets?

[flash-gordon]

@solnic the actual problem is that it's not obvious how to join a relation through another relation

this doesn't work too:

joined = rom.relation(:bookings).join(rom.relation(:tickets)).join(rom.relation(:passes))
 :passes doesn't exist in ROM::AssociationSet registry (ROM::Registry::ElementNotFoundError)