Rails - Sep 2010 - setting up has_many_through association between different db

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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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 Yard 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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.