How to Join Through a Join Table in Ruby Object Mapper SQL

Question

Given a foo table, a bar table, and a foos_bars table, all three with id columns, the approach to getting bars with foos that the documentation would seem to imply is something like:

class Foo < ROM::Relation[:sql]
  def with_foos_bars
    qualified.inner_join(:foos_bars, foo_id: :id)
  end

  def with_bars
    with_category_fixtures.qualified.inner_join(:categories, id: :bar_id)
  end
end

However, #qualified only applies to the class, so this is actually just qualifying "Foo" twice, but we need to qualify at least two of the tables for a usable SQL query. The same seems to be the case for #prefix. Omitting #qualified and prefix simply leads to an ambiguous SQL query.

To clarify: the question is how does one join through a join table in Ruby Object Mapper?

Answer 1

You need to use symbol column names with Sequel naming conventions for now, so something like this:

class Foo < ROM::Relation[:sql]
  def with_foos_bars
    qualified.inner_join(
      foos_bars, foos_bars__foo_id: foos__id
    )
  end

  def with_bars
    with_category_fixtures.qualified.inner_join(
      :categories, categories__id: :foos_bars__bar_id
    )
  end
end

The plan is to provide new interfaces that would simplify that, although I gotta say this simple naming conventions has been working well for me. Having said that, there's definitely place for improvements here.

I hope this helps.