Forum: Ruby on Rails Cross-database joins on has_many associations

Announcement (2017-05-07): is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see and for other Rails- und Ruby-related community platforms.
Bb4bdf2b184027bc38d4fb529770cde5?d=identicon&s=25 Wes Gamble (weyus)
on 2008-11-17 23:59

I'm looking at possibly having to define a has_many relationship across
two databases (possibly even two databases on different servers).  To be
clear, I mean model A lives in database X and it's related to model B
that lives in database Y.  A has_many B's.

It appears that if I want to do this, I have two options:

1) I could provide custom finder_sql A's has_many (and B's belongs_to)
declaration that would handle the cross-DB join for me.  This implies
DB-specific SQL syntax depending on how the DB in question handles
cross-DB queries


2) I could define a DB-specific synonym from DB X to DB Y to allow the
association backing table for model B to appear to be local in the same
DB as model A.  This approach basically puts the complexity in the DB
instead of the finder_sql.

I wanted to look into pursuing option #1 for now.

I'm under the impression that I should be able to dynamically construct
the cross-DB finder SQL that I need based on calls that I can make to
the Connection object in ActiveRecord::Base.  I figure I can pull out
the server, db name, and possibly even port from the connection
specification to construct appropriate SQL.

Has anyone done this or tried to do it?

Bb4bdf2b184027bc38d4fb529770cde5?d=identicon&s=25 Wes Gamble (weyus)
on 2008-11-18 01:21
Some answers...

In my particular case, I may not even need to worry about the has_many
relationship from A to B.  If all I need to take advantage of is the
fact that B belongs_to A, _and_ I'm sure that my keys are all
consistent, then simply doing

  belongs_to :a, :foreign_key => 'appropriate_foreign_key' will be fine

since "belongs_to" only generates a query on the parent table and isn't
trying to talk to both model tables at once.

And even if I do need the has_many relationship, it is easiest to simply
define the appropriate "bs" (that's as in the plural of "b") method on A
which can just go directly to the B table and filter it based on the
foreign key.

None of this cross-DB stuff is really necessary.

This topic is locked and can not be replied to.