Setting up has_many_through association between different db

Rails
1

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

2

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