# Cross-database joins on has\_many associations

**URL:** <https://www.ruby-forum.com/t/cross-database-joins-on-has-many-associations/153491>\
**Category:** Rails\
**Created:** [November 17, 2008, 11:59pm UTC](https://www.ruby-forum.com/t/cross-database-joins-on-has-many-associations/153491 "2008-11-17T23:59:29Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [November 17, 2008, 11:59pm UTC](https://www.ruby-forum.com/t/cross-database-joins-on-has-many-associations/153491/1 "2008-11-17T23:59:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [November 18, 2008, 1:21am UTC](https://www.ruby-forum.com/t/cross-database-joins-on-has-many-associations/153491/2 "2008-11-18T01:21:21Z")

</div>

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