rom_fk_join.rb

require 'rom'
require 'rom-repository'
require 'sqlite3'

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

  conf.default.create_table(:tickets) do
    primary_key :ticket_no
    column :booke_ref, String, null: false
  end

  conf.default.create_table(:boarding_passes) do
    column :ticket_no, String, null: false
  end

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

  conf.relation(:tickets) do
    schema(infer: true) do
      associations do
        belongs_to :booking, foreign_key: :book_ref
        has_many :boarding_passes, foreign_key: :ticket_no
      end
    end
  end

  conf.relation(:boarding_passes) do
    schema(infer: true) do
      associations do
        belongs_to :tickets, foreign_key: :ticket_no
      end
    end
  end
end

class BookingsRepo < ROM::Repository[:bookings]
  relations :bookings, :boarding_passes, :tickets

  def test
    bookings
      .join(tickets)
      .join(boarding_passes)
  end
end

repo = BookingsRepo.new(rom)
repo.test

> #<ROM::Relation[Bookings] name=bookings dataset=#<Sequel::SQLite::Dataset: "SELECT `bookings`.`book_ref` FROM
`bookings` INNER JOIN `tickets` ON (`bookings`.`book_ref` = `tickets`.`book_ref`) INNER JOIN `tickets` ON
(`bookings`.`book_ref` = `tickets`.`book_ref`) INNER JOIN `boarding_passes` ON (`bookings`.`book_ref` =
`tickets`.`booking_id`) ORDER BY `bookings`.`book_ref`">>