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

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

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

or

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?

Thanks,
Wes
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.

Wes
This topic is locked and can not be replied to.