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?

I'm sure there's a question waiting to be asked in there somewhere, but I don't see it. Perhaps you can rewrite that to make what you are asking more clear? Please read "[ask]". — the Tin Man, Nov 06 '15 at 14:13
The title is a statement and follows SO's suggested statement form, however a question in the body needs to end with a `?`. While that may seem pedantic, it's important to clearly define the question. — the Tin Man, Nov 06 '15 at 17:17
OK, in the edit I mentioned above I added a sentence with a question mark at the end of it as the last sentence of the body. If you need more there, please let me know. — Yuri Gadow, Nov 06 '15 at 17:24

score 1 · Accepted Answer · answered Nov 07 '15 at 12:33

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.

answered Nov 07 '15 at 12:33

solnic

5,733
2
23
19

Thanks @solnic, I think this is working (with the second #qualified call, in #with_bars, omitted.) However the result—given a DB with one foo associated with three bars and a #select_append call added—is three bar hashes each containing the same foo hash rather than one foo hash containing three bars. I guess that's not wrong, just surprising. – Yuri Gadow Nov 07 '15 at 14:30
Unfortunately this approach seems to leave one with a result that can't be used to get at both foos and bars, e.g., if both foos and bars have a name attribute, the resulting foos seem to have bars' names. I think. A real puzzler this join handling is. – Yuri Gadow Nov 10 '15 at 22:15
You need to qualify all tables and rename conflicting columns so they are unique. In general using eager-loading through `combine` is much simpler exactly because of this reason. – solnic Nov 11 '15 at 16:02
Unfortunately I've not found a way to rename the columns in joined tables; though that's likely just because I haven't yet fully teased out #prefix's behavior, i.e., where in the sequence of calls it should appear. I did give combine and even repositories a try, unfortunately I couldn't see a way to use the former cleanly (leaky encapsulation) and the latter just blew up on a "hello world." Anyway, that's really a whole new issue/question: https://stackoverflow.com/questions/33642130/how-to-map-a-many-to-many-join-in-ruby-object-mapper – Yuri Gadow Nov 11 '15 at 16:12

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

1 Answers1