has_many_many.rb

require 'rom'
require 'rom-repository'
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 :ticket
      end
    end
  end
end

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

query.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`