[colug-432] Ruby ActiveRecord joined tables

[Rob Funk]

Rick Hornsby wrote:
> I'm trying to write a Ruby script (not RoR) using ActiveRecord.  The
> documentation is confusing to say the least, probably because while I
> know what it is, I've never worked with ActiveRecord before.

Yikes. Most ActiveRecord documentation is oriented toward use inside
Rails (or maybe vice-versa), so you're not going the easy way here.

> I was given the table SONGLIST, and I created the table
> amazon_metadata. The PK on SONGLIST is 'id' and the PK on
> amazon_metadata is 'songid'.  The tables have a 1-to-1 relationship.
> (If it helps explain, I could have just appended the amazon_metadata
> columns to SONGLIST, but I don't want to modify SONGLIST.)

Now you're just going the hard way. ActiveRecord has strong ideas
about how tables and fields should be named, so it's not very good for
pre-existing databases. Its default assumptions can be overridden, but
having to do that throws out most of the simplicity advantages of
ActiveRecord.

Specifically, ActiveRecord assumes that tables are named as plurals
(e.g. "songs"), and foreign keys are singular tablename underscore id
(e.g. "song_id"). It probably doesn't know about
singularizing/pluralizing the word "metadata" (though it can be
taught).

> I set up my ActiveRecord thusly:
> 	https://gist.github.com/rjhornsby/26addcbaef5459182947
> 
> The relationship has_one and belongs_to cannot be enough, because it
> doesn't describe what fields the tables should be joined on.

With ActiveRecord's default assumptions, has_one :supplemental in a
Track class would get interpreted as something like "INNER JOIN
supplementals ON tracks.supplemental_id=supplementals.id".
With your table_name settings, it would be something like "INNER JOIN
amazon_metadata ON SONGLIST.supplemental_id=amazon_metadata.id".

> Supposing I figure out *where* to write a join clause (that seems to
> be missing from all the docs I've found so far), I don't understand
> how to check for the presence of/retrieve for the amazon_metadata.asin
> field in the loop.

I actually think you're basically right on that part, though you do
have to be careful about data access inside loops causing way more
queries than you intended. (It's the infamous ActiveRecord "N+1
queries problem".) It looks like your code would probably hit that
issue. But that's a performance optimization issue rather than a "make
it work" issue.

> I’m having a hard time finding somewhere that shows both the has_one
> syntax and how to actually use that to retrieve records - because
> other places show using a Class.join syntax - but not how or if it
> relates to the has_one syntax in the class?
> 
> Sorry - very confused. I know I'm missing something, but I'm not
> sure what?

I think what you're missing is that ActiveRecord is a bad fit for your
task.

I wonder if Sequel would be a better fit, since it's designed for use
outside Rails, though it may have some of the same naming issues as
ActiveRecord.  http://twin.github.io/ode-to-sequel/