# Setting up has\_many\_through association between different db

**URL:** <https://www.ruby-forum.com/t/setting-up-has-many-through-association-between-different-db/195772>\
**Category:** Rails\
**Created:** [September 29, 2010, 10:38pm UTC](https://www.ruby-forum.com/t/setting-up-has-many-through-association-between-different-db/195772 "2010-09-29T22:38:06Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![maruthymukund](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/cdc98d/32.png) [@maruthymukund](https://www.ruby-forum.com/u/maruthymukund)\
**Post date:** [September 29, 2010, 10:38pm UTC](https://www.ruby-forum.com/t/setting-up-has-many-through-association-between-different-db/195772/1 "2010-09-29T22:38:06Z")

</div>

I am trying to setup a has\_many :through relationship between two models  
User and CustomerAccount through another join model AccountOwnership  
(the underlying tables which BELONG to two DIFFERENT DATABASES).

# Here is the relevant code

class User \< ActiveRecord::Base  
has\_many :account\_ownerships, :dependent =\> :destroy  
has\_many :companies, :through =\> :account\_ownerships  
end

class AccountOwnership \< ActiveRecord::Base  
belongs\_to :user  
belongs\_to :company, :class\_name =\> “Reporting::CustomerAccount”  
end

# class CustomerAccount \< Reporting::Base set\_table\_name “customers” establish\_connection(“db2\_#{RAILS\_ENV}”) end

a = AccountOwnership.new(:user\_id =\> 2, :company\_id =\> 10)  
a.user ## Returns the correct user  
a.company ## returns the correct CustomerAccount instance

also

a.user.account\_ownership ## returns a as anticipated  
but  
a.user.companies ## produces the following error:

#ActiveRecord::StatementInvalid: Mysql::Error: Table  
#‘db2.account\_ownerships’ doesn’t exist: SELECT `customers`.\* FROM  
#`customers` INNER JOIN `account_ownerships` ON `customers`.id =  
#`account_ownerships`.company\_id WHERE ((`account_ownerships`.user\_id  
= 4))

The issue here is that account\_ownerships, users tables are contained in  
one default database (say db1), and the customers table is contained in  
a different database (say db2). The connections to the databases are  
configured properly, but during the lookup since there is only one  
connection object available, Rails tries to find the account\_ownerships  
database in db2 and hence fails.

It looks like my design/logic might be flawed because I cannot see a way  
to connect to two different databases using the same connection, but I  
would be thrilled to see if there is a workaround, without changing the  
design. (I am reluctant to change the design because db2 is not under my  
control)

Please suggest any alternate mechanisms to setup this association in  
Rails.

Thanks in advance.  
M

---

<div class="post-metadata">

**Author:** ![maruthymukund](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/cdc98d/32.png) [@maruthymukund](https://www.ruby-forum.com/u/maruthymukund)\
**Post date:** [September 30, 2010, 1:38am UTC](https://www.ruby-forum.com/t/setting-up-has-many-through-association-between-different-db/195772/2 "2010-09-30T01:38:43Z")

</div>

Solution:

Seems that this cannot be achieved through any Rails association magic,  
since this is a core limitation of any database access mechanism  
including raw SQL.

Here is what I did to workaround the issue:

class User \< ActiveRecord::Base  
has\_many :account\_ownerships, :dependent =\> :destroy

def companies  
(account\_ownerships.collect { |r|  
Reporting::CustomerAccount.find(r.company\_id) }).flatten  
end  
end

This provides a correct approximation as shown:

a = AcccountOwnership.create!(:user\_id =\> 10, :company\_id =\> 10)  
u = User.find(10)  
u.account\_ownerships ### will return the correct account\_ownership  
instance

ALSO

u.companies ### will return a list of all companies enlisted for each  
account

And we need to add two instance methods to the account\_ownership model,  
to approximate the association behavior

class CustomerAccount \< ActiveRecord::Base  
set\_table\_name “customers”

########################################################

## This cannot be used because, customers and

## account\_ownerships tables are contained in

## different databases, because of this it is

## impossible to query these two tables from a

## single db connection, which is what we are

## attempting to achieve here.

## has\_many :account\_ownerships, :dependent =\> :destroy

########################################################

def account\_ownerships  
AccountOwnership.find(:all, :conditions =\> [“company\_id = ?”,  
self.id])  
end

def users  
(account\_ownerships.collect { |r| User.find(r.user\_id) }).flatten  
end  
end

Now we can do

c = CustomerAccount.find(10)  
c.account\_ownerships ## will return the right ownership accounts

AND

c.users ## will iterate over all the accounts accumulating any users

CAUTION: 1. Since there is no delete cascading done on the  
CustomerAccount model, if any accounts are deleted, this will not be  
reflected in the account\_ownership table, hence this can give rise to  
ugly ActiveRecord::RecordNotFound errors in the users method.

Engine Y. wrote:

> I am trying to setup a has\_many :through relationship between two models  
> User and CustomerAccount through another join model AccountOwnership  
> (the underlying tables which BELONG to two DIFFERENT DATABASES).
> 
> # Here is the relevant code
> 
> class User \< ActiveRecord::Base  
> has\_many :account\_ownerships, :dependent =\> :destroy  
> has\_many :companies, :through =\> :account\_ownerships  
> end
> 
> class AccountOwnership \< ActiveRecord::Base  
> belongs\_to :user  
> belongs\_to :company, :class\_name =\> “Reporting::CustomerAccount”  
> end
> 
> # class CustomerAccount \< Reporting::Base set\_table\_name “customers” establish\_connection(“db2\_#{RAILS\_ENV}”) end
> 
> a = AccountOwnership.new(:user\_id =\> 2, :company\_id =\> 10)  
> a.user ## Returns the correct user  
> a.company ## returns the correct CustomerAccount instance
> 
> also
> 
> a.user.account\_ownership ## returns a as anticipated  
> but  
> a.user.companies ## produces the following error:
> 
> #ActiveRecord::StatementInvalid: Mysql::Error: Table  
> #‘db2.account\_ownerships’ doesn’t exist: SELECT `customers`.\* FROM  
> #`customers` INNER JOIN `account_ownerships` ON `customers`.id =  
> #`account_ownerships`.company\_id WHERE ((`account_ownerships`.user\_id  
> = 4))
> 
> The issue here is that account\_ownerships, users tables are contained in  
> one default database (say db1), and the customers table is contained in  
> a different database (say db2). The connections to the databases are  
> configured properly, but during the lookup since there is only one  
> connection object available, Rails tries to find the account\_ownerships  
> database in db2 and hence fails.
> 
> It looks like my design/logic might be flawed because I cannot see a way  
> to connect to two different databases using the same connection, but I  
> would be thrilled to see if there is a workaround, without changing the  
> design. (I am reluctant to change the design because db2 is not under my  
> control)
> 
> Please suggest any alternate mechanisms to setup this association in  
> Rails.
> 
> Thanks in advance.  
> M