Rails - Nov 2008 - Cross-database joins on has_many associations

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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---