Rails: setting up has_many_through association between tables from different databases

Question

I am trying to setup a has_many :through relationship between two models User and CustomerAccount through another join model AccountOwnership (users and account_ownerships tables are in one db, say db1 and the customer_accounts table is in remote db, say db2).

Here is the relevant code, that sets up the associations

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

config/database.yml (configuration is correct, although not shown here)

development:
  reconnect: false
  database: db1
  pool: 5

db2_development:
  reconnect: false
  database: db2
  host: different.host
  pool: 5

In script/console

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 the "account_ownerships" and "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 database 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 db 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)

It looks like I can workaround this issue by moving my account_ownerships table to db2, but this less than ideal for me atleast.

Are there any alternate mechanisms/schemas to setup this association in Rails.

Thanks in advance. M

Answer 1

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.