many_to_many.rb

rom = ROM.container(:sql, 'sqlite::memory') do |conf|
  conf.gateways[:default].use_logger(Logger.new($stdout))

  conf.default.create_table(:users) do
    primary_key :id
    column :login, String, null: false
  end

  conf.default.create_table(:employees) do
    primary_key :id
    column :name, String, null: false
  end

  conf.default.create_table(:employees_users) do
    foreign_key :verifable_id, :employees
    foreign_key :user_id, :users
  end

  conf.relation(:users) do
    schema(:users, infer: true) do
      associations do
        has_many :employees_users, foreign_key: :user_id
        has_many :employees, through: :employees_users, foreign_key: :verifable_id
      end
    end
  end

  conf.relation(:employees) do
    schema(:employees, infer: true) do
      associations do

      end
    end
  end

  conf.relation(:employees_users) do
    schema(:employees_users, infer: true) do
      associations do
        belongs_to :user, foreign_key: :user_id
        belongs_to :employee, foreign_key: :verifable_id
      end
    end
  end
end

class UserRepo < ROM::Repository[:users]
  def by_id(id)
    users
      .join(:employees)
      .by_pk(id)
      .to_a
  end
end

employee = rom.relations[:employees].changeset(:create, name: 'Jane').commit

user = rom.relations[:users]
  .changeset(:create, login: '[email protected]')
  .commit

rom.relations[:employees_users]
  .changeset(:create, user_id: user[:id], verifable_id: employee[:id]).commit

repo = UserRepo.new(rom)

user = repo.by_id(1)
# Actual ROM query:
# SELECT `users`.`id`, `users`.`login` FROM `users` 
# INNER JOIN `employees_users` ON (`users`.`id` = `employees_users`.`user_id`) 
# INNER JOIN `employees` ON (`users`.`id` = `employees_users`.`verifable_id`) 
# WHERE (`users`.`id` = 1) ORDER BY `users`.`id`

# Expected query:
# SELECT `users`.`id`, `users`.`login` FROM `users` 
# INNER JOIN `employees_users` ON (`users`.`id` = `employees_users`.`user_id`) 
# INNER JOIN `employees` ON (`employees`.`id` = `employees_users`.`verifable_id`) 
# WHERE (`users`.`id` = 1) ORDER BY `users`.`id`

# Workaround:
    users
      .join(:employees_users)
      .join(:employees, { id: :verifable_id })
      .by_pk(id)
      .one
# produces expected result:
# SELECT `users`.`id`, `users`.`login` FROM `users` 
# INNER JOIN `employees_users` ON (`users`.`id` = `employees_users`.`user_id`) 
# INNER JOIN `employees` ON (`employees`.`id` = `employees_users`.`verifable_id`) 
# WHERE (`users`.`id` = 1) ORDER BY `users`.`id`