Connecting multiple databases - has_many fails

Has anyone faced this problem before?

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

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

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

hmmmm
u read about establish_connection on active record…

may be it will help u…

Thank you…

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.

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.