# Connecting multiple databases - has\_many fails

**URL:** <https://www.ruby-forum.com/t/connecting-multiple-databases-has-many-fails/186910>\
**Category:** Rails\
**Created:** [April 14, 2010, 7:25am UTC](https://www.ruby-forum.com/t/connecting-multiple-databases-has-many-fails/186910 "2010-04-14T07:25:05Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![pakothawade](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ba9def/32.png) [@pakothawade](https://www.ruby-forum.com/u/pakothawade)\
**Post date:** [April 14, 2010, 7:25am UTC](https://www.ruby-forum.com/t/connecting-multiple-databases-has-many-fails/186910/1 "2010-04-14T07:25:05Z")

</div>

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:in`execute’  
> > 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:in`select\_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:in`find\_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:in`find’  
> > 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:in`load\_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:in`method\_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:in`determine\_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:in`render\_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?

---

<div class="post-metadata">

**Author:** ![pakothawade](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ba9def/32.png) [@pakothawade](https://www.ruby-forum.com/u/pakothawade)\
**Post date:** [April 14, 2010, 7:39am UTC](https://www.ruby-forum.com/t/connecting-multiple-databases-has-many-fails/186910/2 "2010-04-14T07:39:08Z")

</div>

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:in`render\_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

---

<div class="post-metadata">

**Author:** ![pakothawade](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ba9def/32.png) [@pakothawade](https://www.ruby-forum.com/u/pakothawade)\
**Post date:** [April 14, 2010, 7:45am UTC](https://www.ruby-forum.com/t/connecting-multiple-databases-has-many-fails/186910/3 "2010-04-14T07:45:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pakothawade](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ba9def/32.png) [@pakothawade](https://www.ruby-forum.com/u/pakothawade)\
**Post date:** [April 14, 2010, 9:01am UTC](https://www.ruby-forum.com/t/connecting-multiple-databases-has-many-fails/186910/4 "2010-04-14T09:01:41Z")

</div>

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 [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

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

–

Senior Rails Developer  
Anton Effendi - Wu You Duan

---

<div class="post-metadata">

**Author:** ![pakothawade](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ba9def/32.png) [@pakothawade](https://www.ruby-forum.com/u/pakothawade)\
**Post date:** [April 14, 2010, 5:58pm UTC](https://www.ruby-forum.com/t/connecting-multiple-databases-has-many-fails/186910/5 "2010-04-14T17:58:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pakothawade](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ba9def/32.png) [@pakothawade](https://www.ruby-forum.com/u/pakothawade)\
**Post date:** [April 14, 2010, 3:46pm UTC](https://www.ruby-forum.com/t/connecting-multiple-databases-has-many-fails/186910/6 "2010-04-14T15:46:47Z")

</div>

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.