Ruby on Rails

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@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.