problem joining two models that establish_connection to the same db #697

Closed
lighthouse-import opened this Issue May 16, 2011 · 7 comments

Comments

Projects
None yet
1 participant

Imported from Lighthouse. Original ticket at: http://rails.lighthouseapp.com/projects/8994/tickets/5054
Created by Mark Donnelly - 2010-11-25 12:13:49 UTC

I'm trying to retrieve information from a bugzilla database on a separate DB server. I'm trying to join across two models that both exist in the bugzilla schema, Bug and Flag:

class Bug < ActiveRecord::Base
establish_connection :bugzilla
set_primary_key :bug_id
has_many :flags
end
class Flag
establish_connection :bugzilla
belongs_to :bug
end

Most calls I get produce an error:
ree-1.8.7-2010.01 > Bug.joins(:flags).count
NoMethodError: undefined method quote_table_name' for #<Arel::Memory::Engine:0x91275c4> from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/arel-0.4.0/lib/arel/engines/sql/formatters.rb:7:insend'
from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/arel-0.4.0/lib/arel/engines/sql/formatters.rb:7:in quote_table_name' from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/arel-0.4.0/lib/arel/engines/sql/formatters.rb:103:intable'
from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/arel-0.4.0/lib/arel/engines/sql/relations/utilities/recursion.rb:9:in table_sql' from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/arel-0.4.0/lib/arel/engines/sql/relations/operations/join.rb:11:injoins'
from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/arel-0.4.0/lib/arel/algebra/relations/utilities/compound.rb:6:in __send__' from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/arel-0.4.0/lib/arel/algebra/relations/utilities/compound.rb:6:injoins'
from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/arel-0.4.0/lib/arel/algebra/relations/utilities/compound.rb:6:in __send__' from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/arel-0.4.0/lib/arel/algebra/relations/utilities/compound.rb:6:injoins'
from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/activerecord-3.0.0.beta4/lib/active_record/relation/calculations.rb:143:in perform_calculation' from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/activerecord-3.0.0.beta4/lib/active_record/relation/calculations.rb:128:incalculate'
from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/activerecord-3.0.0.beta4/lib/active_record/relation/calculations.rb:45:in `count'
from (irb):10

However, this call:
Bug.where(:assigned_to => 2468).includes(:flags).all.size
=> 547
works.

Finally, if I change the default database connection to point to the bugzilla database, remove the establish_connection calls, and try this again, it works as expected.

As you can see above, this uses rails3-beta4. I'm using mysql, if that matters.

I understand that joining from one database connection to another is probably unrealistic, but I expect that joins should work for two models that both lie within the same database.

Imported from Lighthouse.
Comment by Miles Egan - 2010-07-10 16:30:27 UTC

It looks to me like the issue aries in Arel at this point:

lib/arel/algebra/relations/operations/join.rb, line 44:

def engine
relation1.engine != relation2.engine ? Memory::Engine.new : relation1.engine
end

Even though the two engines are pointing to the same underlying data source, they don't compare as equal because they're different objects.

Imported from Lighthouse.
Comment by Miles Egan - 2010-07-10 16:52:52 UTC

Digging a little deeper, it looks like rails maintains a distinct connection pool for each class that establishes a connection. In most cases there's only one since it's established in ActiveRecord::Base but since you're establishing connections in two separate classes two separate connection pools are created and Arel doesn't recognize them as speaking to the same DB.

If you read the comments in /activerecord/lib/active_record/connection_adapters/abstract/connection_specification.rb around the ConnectionHandler class it explains the relationship between connection pools and classes in more detail.

It looks to me like fixing this would require a fairly significant change in the connection pooling code.

Imported from Lighthouse.
Comment by Albert Llop Yacobi - 2010-07-30 09:44:15 UTC

This bug hit me today aswell. Managed it changing all of the queries to find_by_sql, but it's a shame losing all the ActiveRecord goodness. Any progress on the matter?

Imported from Lighthouse.
Comment by Kane - 2010-08-01 13:26:34 UTC

If you want a tempory fix till this issue is addressed in the rails core you could make a superclass for this models in which you connect to the database.

class Bugzilla < ActiveRecord::Base
  self.abstract_class = true  
   establish_connection :bugzilla
end

class Bug < Bugzilla 
 set_primary_key :bug_id has_many :flags 
end

class Flag < Bugzilla 
  belongs_to :bug
end

Imported from Lighthouse.
Comment by ronin-68998 (at lighthouseapp) - 2010-09-16 16:48:00 UTC

By Kane:
If you want a tempory fix till this issue is addressed in the rails core you could make a superclass for this models in which you connect to the database.

This man is genius, flawless hack. Thanks.

I'm using ActiveScaffold w/ Rails 3, and a remote database, ran into this bug when I started to join models together.

Hit: undefined method 'connection' in Arel::Memory, bleh bleh bleh.

Thanks again.

Imported from Lighthouse.
Comment by rails - 2011-02-26 00:00:09 UTC

This issue has been automatically marked as stale because it has not been commented on for at least three months.

The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

Imported from Lighthouse.
Comment by ravionrails - 2011-03-04 14:59:14 UTC

Address model
establish_connection :connection_name (mention in database.yml)

If I do

User.joins(:addresses)

it doesn't connect to the addresses table of other db mention in :connection_name
but try to find the addresses table in the application's database

it ignores the establish_connection

but joins work on tables which are in the application's (default) database

I think there is problem in joins for establish_connection models/tables

[state:open]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment