This project is archived and is in readonly mode.

#5054 open
Mark Donnelly

problem joining two models that establish_connection to the same db

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

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.

Comments and changes to this ticket

  • Miles Egan

    Miles Egan July 10th, 2010 @ 05:30 PM

    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.

  • Miles Egan

    Miles Egan July 10th, 2010 @ 05:52 PM

    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.

  • Albert Llop Yacobi

    Albert Llop Yacobi July 30th, 2010 @ 10:44 AM

    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?

  • Kane

    Kane August 1st, 2010 @ 02:26 PM

    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
    
  • ronin-68998 (at lighthouseapp)

    ronin-68998 (at lighthouseapp) September 16th, 2010 @ 05:48 PM

    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.

  • rails

    rails February 26th, 2011 @ 12:00 AM

    • 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.

  • rails

    rails February 26th, 2011 @ 12:00 AM

    • State changed from “open” to “stale”
  • ravionrails

    ravionrails March 4th, 2011 @ 02:59 PM

    • 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]

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>