Cross-database joins on has_many associations

Rails
1

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

  1. 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

2

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