Connecting multiple databases - has_many fails

Rails
1

Has anyone faced this problem before?

I have 3 models, Project, ProjectRole and User. The tables are
distributed in two databases.

Databases:

Database X:
users

Database Y:
projects
project_roles

Models:

class Project < ActiveRecord::Base
has_many :project_roles
has_many :users, :through => :project_roles
end

class ProjectRole < ActiveRecord::Base
belongs_to :project
belongs_to :user
end

class User < ActiveRecord::Base
establish_connection :X
has_many :project_roles
has_many :projects, :through => :project_roles
end

Now when i try to access users from project:

p = Project.first
±—±----------±------+
| id | name | alias |
±—±----------±------+
| 1 | Jerde L | PRO |
±—±----------±------+

p.users
Hirb Error: Mysql::Error: Table ‘X.project_roles’ doesn’t exist:
SELECT users.* FROM users INNER JOIN project_roles ON
users.id = project_roles.user_id WHERE
((project_roles.project_id = 1))
C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/
active_record/connection_adapters/abstract_adapter.rb:219:in log' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/connection_adapters/mysql_adapter.rb:323:inexecute’
C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/
active_record/connection_adapters/mysql_adapter.rb:608:in select' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/connection_adapters/abstract/database_statements.rb:7:inselect_all_without_query_cache’
C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/
active_record/connection_adapters/abstract/query_cache.rb:62:in
select_all' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/base.rb:661:infind_by_sql’
C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/
active_record/base.rb:1548:in find_every' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/base.rb:615:infind’
C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/
active_record/associations/has_many_through_association.rb:83:in
find_target' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/associations/association_collection.rb:354:inload_target’
C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/
active_record/associations/association_proxy.rb:212:in
method_missing' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/associations/association_collection.rb:371:inmethod_missing_without_paginate’
c:/rubyonrails/rails_apps/test_project/vendor/gems/
will_paginate-2.3.12/lib/will_paginate/finder.rb:170:in
method_missing' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/ formatter.rb:78:indetermine_output_class’
C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/
formatter.rb:48:in format_output' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/ view.rb:213:inrender_output’
C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/
view.rb:126:in `view_output’

BUT: Getting projects from users works just fine.

u = User.first
±—±-------------+
| id | username |
±—±-------------+
| 1 | dummy |
±—±--------------+

u.projects
±—±--------------------±------+
| id | name | alias |
±—±--------------------±-------+
| 1 | Jerde L | PRO |
| 2 | Spinka Group | PRO |
| 3 | Mosciski LLC | PRO |
±–±----------------------±------+

Any idea what could be wrong?

2

On Apr 13, 2010, at 10:23 PM, tispratik wrote:

end
end

active_record/connection_adapters/mysql_adapter.rb:608:in select' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ view.rb:213:inrender_output’
±—±-------------+
±–±----------------------±------+

Any idea what could be wrong?

You’re trying to run a single SQL query that references tables in two
different databases. That isn’t going to work. Set things up so that
you can see the SQL being run and you’ll see why the first isn’t
working and the second is. The second isn’t trying to join any
tables. It’s running a query on the second database using a value
retrieved from the first.

-philip

3

Thanks for the quick reply Philip.
Unfortunately i am working on a windows machine and i am not aware of
any tool which shows the query being run.
An interesting workaround i found is by explicitly stating the
database.table_name in the intermediate model.

class ProjectRole < ActiveRecord::Base
set_table_name ‘Y.project_roles’
end

Seems like when i do user.projects, rails assumes that the
project_roles table is in the latter model’s database (which is true)
so it works.
But when i do, project.users, rails looks for project_roles table in
the database where users table is located (which is incorrect), so it
dosent.
But this dosent seem to be a good workaround as i have to hardcode the
database.table_name in the model class.

-Regards,
Pratik

4

hmmmm
u read about establish_connection on active record…

may be it will help u…

Thank you…

On Wed, Apr 14, 2010 at 12:43 PM, tispratik [email protected] wrote:

Seems like when i do user.projects, rails assumes that the

–

Senior Rails Developer
Anton Effendi - Wu You Duan

5

On Apr 13, 2010, at 10:43 PM, tispratik wrote:

Thanks for the quick reply Philip.
Unfortunately i am working on a windows machine and i am not aware of
any tool which shows the query being run.

In whatever windows uses as “.irbrc” put the following:

Log to STDOUT if in Rails

if ENV.include?(‘RAILS_ENV’) && !Object.const_defined?
(‘RAILS_DEFAULT_LOGGER’)
require ‘logger’
RAILS_DEFAULT_LOGGER = Logger.new(STDOUT)
end

Then run your queries in ./script/console and you should see the SQL.

But when i do, project.users, rails looks for project_roles table in
the database where users table is located (which is incorrect), so it
dosent.
But this dosent seem to be a good workaround as i have to hardcode the
database.table_name in the model class.

You’re getting lucky again. Move one of the databases to another
server and the above will break.

Setup the logging so you can see the queries. That will make it clear
why one works and one doesn’t.

6

In database.yml:

your_environment:
adapter: your_adapter
database: your_database
user: your_user
…

a_name_for_your_other_database_connection:

your connection information here: adapter, etc.

In your models for the other database:

class YourClassName < YourOtherDatabaseConnectionName # what you named
your other DB connection in database.yml

With the above you wouldn’t need to specify the DB and table names in
your models but the class definition would contain the connection the
model belongs to.