# Joining across databases in a find statement with include (l

**URL:** <https://www.ruby-forum.com/t/joining-across-databases-in-a-find-statement-with-include-l/91114>\
**Category:** Rails\
**Created:** [April 4, 2007, 9:02pm UTC](https://www.ruby-forum.com/t/joining-across-databases-in-a-find-statement-with-include-l/91114 "2007-04-04T21:02:57Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![arjackson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/57b2e6/32.png) [@arjackson](https://www.ruby-forum.com/u/arjackson)\
**Post date:** [April 4, 2007, 9:02pm UTC](https://www.ruby-forum.com/t/joining-across-databases-in-a-find-statement-with-include-l/91114/1 "2007-04-04T21:02:57Z")

</div>

Hi,

I have a legacy database ‘old’ and a new database ‘new’ which rails  
uses. I set up models using old with establish\_connection(old), and  
this works well, except for include:

class OldModel \< ActiveRecord::Base  
establish\_connection(old)  
end

class NewModel \< ActiveRecord::Base  
has\_one :old\_model  
end

a = OldModel.find :first  
NewModel.find(:all, :include =\> :old\_model)

will give an error like

Mysql::Error: Table ‘new\_db.old\_models’ doesn’t exist: SELECT … FROM  
new\_models LEFT OUTER JOIN old\_models ON … WHERE …

Obviously a performance hit without the join. MySQL does support  
joins you specify the database first (ex old\_db.old\_models). I can  
hack it by doing the join manually but that doesn’t sound like the  
rails way. Any ideas?

-Andrew R.

---

<div class="post-metadata">

**Author:** ![arjackson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/57b2e6/32.png) [@arjackson](https://www.ruby-forum.com/u/arjackson)\
**Post date:** [April 4, 2007, 9:38pm UTC](https://www.ruby-forum.com/t/joining-across-databases-in-a-find-statement-with-include-l/91114/2 "2007-04-04T21:38:31Z")

</div>

If you must do it like this (and my must I mean you can’t run the “new”  
database on the “old” database by using new tables), I would create  
faux-ActiveRecord methods in the model classes.

class OldModel \< ActiveRecord::Base  
establish\_connection(old)  
end

class NewModel \< ActiveRecord::Base

def old\_model  
OldModel.find(:first, :conditions =\> [“new\_model\_id = ?”, self.id])  
end

def old\_model=  
if self.old.nil?  
OldModel.create({:new\_model\_id =\> self.id}.join(params))  
else  
OldModel.update(self.id, params)  
end  
end  
end

---

<div class="post-metadata">

**Author:** ![arjackson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/57b2e6/32.png) [@arjackson](https://www.ruby-forum.com/u/arjackson)\
**Post date:** [April 4, 2007, 9:48pm UTC](https://www.ruby-forum.com/t/joining-across-databases-in-a-find-statement-with-include-l/91114/3 "2007-04-04T21:48:22Z")

</div>

Andrew R. wrote:

> Hi,
> 
> I have a legacy database ‘old’ and a new database ‘new’ which rails  
> uses. I set up models using old with establish\_connection(old), and  
> this works well, except for include:
> 
> class OldModel \< ActiveRecord::Base  
> establish\_connection(old)  
> end
> 
> class NewModel \< ActiveRecord::Base  
> has\_one :old\_model  
> end
> 
> a = OldModel.find :first  
> NewModel.find(:all, :include =\> :old\_model)
> 
> will give an error like
> 
> Mysql::Error: Table ‘new\_db.old\_models’ doesn’t exist: SELECT … FROM  
> new\_models LEFT OUTER JOIN old\_models ON … WHERE …
> 
> Obviously a performance hit without the join. MySQL does support  
> joins you specify the database first (ex old\_db.old\_models). I can  
> hack it by doing the join manually but that doesn’t sound like the  
> rails way. Any ideas?
> 
> -Andrew R.

This may not be as easy as it looks. Rails has no way to know that a  
cross data source connection can be handled in a single query. (Imagine  
if in ‘establish\_connection(old)’, old referred to a database on another  
server). I think there might be a way to use a :through and :source to  
cajole the correct query. I’m sure you can use :finder\_sql in your  
has\_many statement, but MySQL, which doesn’t always play well with  
compound queries, may instead execute this as 1+n queries.

John M.

---

<div class="post-metadata">

**Author:** ![arjackson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/57b2e6/32.png) [@arjackson](https://www.ruby-forum.com/u/arjackson)\
**Post date:** [April 6, 2007, 3:37am UTC](https://www.ruby-forum.com/t/joining-across-databases-in-a-find-statement-with-include-l/91114/4 "2007-04-06T03:37:12Z")

</div>

Just wanted to follow up on this. I actually got it working by simply  
pretending the table name was legacy\_db.table! Easily done with  
set\_table\_name, or if you have a bunch of models for the legacy db,  
something like

class LegacyBase \< ActiveRecord::Base  
def self.pluralize\_table\_names  
false # our legacy db uses singular table names  
end

def self.db\_name  
“legacy\_db”  
end

def self.table\_name\_prefix  
self.db\_name + “.”  
end

establish\_connection(self.db\_name)  
end

class OldModel \< LegacyBase  
…  
end

-Andrew R.

---

<div class="post-metadata">

**Author:** ![arjackson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/57b2e6/32.png) [@arjackson](https://www.ruby-forum.com/u/arjackson)\
**Post date:** [April 5, 2007, 5:17am UTC](https://www.ruby-forum.com/t/joining-across-databases-in-a-find-statement-with-include-l/91114/5 "2007-04-05T05:17:46Z")

</div>

Good point. But at least it could try putting “database.” when it  
detects different connections on the chance they will join. A  
parameter to has\_many, :join\_across\_dbs =\> true could work too, but  
I’m not sure I’m ready to start hacking Active Record!

Do people really put lots of tables in one database? Don’t tables  
start to get prefixed, like we have accountadmin\_viewer,  
accountadmin\_access, etc. plus form\_elements, form\_questions, … and  
a bunch more. Isn’t it DRY - or at least good practice - to start  
making different databases after a few hundred of these tables?

-Andrew

On Apr 4, 3:48 pm, John M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)