Cross-database joins on has_many associations


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


  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?


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 forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs