Connecting multiple databases - has_many fails

rubyonrails-talk
1

Has anyone faced this problem before?

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

2

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…

5

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.

6

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.

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.

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.