Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

problem joining two models that establish_connection to the same db

#5054

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><br/>

from ~/.rvm/gems/ree-1.8.7-2010.01@app/gems/arel-0.4.0/lib/arel/engines/sql/formatters.rb:7:in `__send__'
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:in `table'
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:in `joins'
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:in `joins'
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:in `joins'
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:in `calculate'
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.

Reported by Mark Donnelly · July 6th, 2010 @ 04:12 PM

State: open
Milestone: none
Assigned to: nobody
Importance: none

Activity

  1. Miles Egan
    Miles Egan

    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.

    July 10th, 2010 @ 05:30 PM

  2. Miles Egan
    Miles Egan

    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.

    July 10th, 2010 @ 05:52 PM

  3. Albert Llop Yacobi
    Albert Llop Yacobi

    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?

    July 30th, 2010 @ 10:44 AM

  4. Kane
    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.

    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

    August 1st, 2010 @ 02:26 PM

  5. ronin-68998 (at lighthouseapp)
    ronin-68998 (at lighthouseapp)

    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.

    September 16th, 2010 @ 05:48 PM

  6. rails
    rails
    • State changed from new to open

    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.

    February 26th, 2011 @ 12:00 AM

  7. rails
    rails
    • State changed from open to stale

    February 26th, 2011 @ 12:00 AM

  8. ravionrails
    ravionrails
    • State changed from stale to open

    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]

    March 4th, 2011 @ 02:59 PM