This project is archived and is in readonly mode.

#1902 ✓stale
Andrew Roth

establish_connection does not support joins

Reported by Andrew Roth | February 7th, 2009 @ 04:05 AM | in 3.x

Hello,

I have a situation where I need to use a legacy database. I'm finding establish_connection is not adequate to use all of the active record features, for example has_many :through across different databases fails. Would a solution be to detect the different connections, and split the query into two separate queries?

Is establish_connection even meant to work in this situation?

On mysql databases on the same server, I have found setting the table name to "database.table" seamlessly fixes the problem since mysql supports joins over databases. Would a patch on active record that sets a prefix "database." when establish_connection is used be useful / possible?


# SQLite version 3.x
#   gem install sqlite3-ruby (not necessary on OS X Leopard)
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

legacy:
  adapter: sqlite3
  database: db/legacy.sqlite3
  pool: 5
  timeout: 5000


class A < ActiveRecord::Base
  has_many :a_bs
  has_many :bs, :through => :a_bs
end
class AB < ActiveRecord::Base
  belongs_to :a
  belongs_to :b
end
class B < ActiveRecord::Base
  establish_connection 'legacy'
end


Loading development environment (Rails 2.2.2)
>> a = A.create!
=> #<A id: 1, created_at: "2009-01-20 21:21:12", updated_at: "2009-01-20 21:21:12">
>> a.a_bs
=> []
>> b = B.create!
=> #<B id: 1, created_at: "2009-01-20 21:22:19", updated_at: "2009-01-20 21:22:19">
>> a.a_bs.create! :b_id => b.id
=> #<AB id: 1, a_id: 1, b_id: 1, created_at: "2009-01-20 21:22:36", updated_at: "2009-01-20 21:22:36">
>> a.a_bs
=> [#<AB id: 1, a_id: 1, b_id: 1, created_at: "2009-01-20 21:22:36", updated_at: "2009-01-20 21:22:36">]
>> a.bs
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such table: abs: SELECT "bs".* FROM "bs"  INNER JOIN abs ON bs.id = abs.b_id    WHERE (("abs".a_id = 1)) 
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract_adapter.rb:188:in `log'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/sqlite_adapter.rb:132:in `execute'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/sqlite_adapter.rb:372:in `catch_schema_changes'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/sqlite_adapter.rb:132:in `execute'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/sqlite_adapter.rb:275:in `select'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:635:in `find_by_sql'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:1490:in `find_every'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:589:in `find'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/associations/has_many_through_association.rb:73:in `find_target'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/associations/association_collection.rb:344:in `load_target'
	from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/associations/association_proxy.rb:139:in `inspect'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:298:in `output_value'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:151:in `eval_input'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:259:in `signal_status'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:147:in `eval_input'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:146:in `eval_input'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:70:in `start'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:69:in `catch'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:69:in `start'
>>

Comments and changes to this ticket

  • scott

    scott March 13th, 2009 @ 05:45 PM

    +1 currently on a project where we have to monkey patch column_alias to get this to work correctly. Why it's aliasing off the current connection and not the reflected one seems to be a bug. Patch below

    
module ActiveRecord::Associations::ClassMethods
  def column_aliases(join_dependency)
    join_dependency.joins.collect{|join| join.column_names_with_alias.collect{|column_name, aliased_name|
      if join.respond_to?(:reflection)
        "#{join.reflection.klass.connection.quote_table_name join.aliased_table_name}.#{connection.quote_column_name column_name} AS #{aliased_name}"
      else
        "#{connection.quote_table_name join.aliased_table_name}.#{connection.quote_column_name column_name} AS #{aliased_name}"
      end }}.flatten.join(", ")
  end
end
  • scott

    scott March 13th, 2009 @ 05:48 PM

    Sorry forgot to mention had to fix quote_table_name (for Mysql) to include the db name automatically, we originally hoped that would take care of it all.

    
class ActiveRecord::ConnectionAdapters::MysqlAdapter
  def database
    @connection_options[3]
  end
  
  def quote_table_name(name)
        if name.index(".").nil?
          name = database + "." + name 
        end
        @quoted_table_names[name] ||= quote_column_name(name).gsub('.', '`.`')
  end
end
  • scott

    scott March 16th, 2009 @ 11:51 PM

    Yea it's been a fun week, you are probably gonna need this to fix the table names as well

    
class ActiveRecord::Associations::ClassMethods::JoinDependency::JoinAssociation  
  def table_alias_for(table_name, table_alias)
        "#{reflection.klass.connection.quote_table_name(table_name)} #{table_alias if table_name != table_alias}".strip
  end
end
  • jonbro (at gmail)

    jonbro (at gmail) May 28th, 2009 @ 08:35 PM

    I am also having problems with this, and cannot find my way around active record enough to figure it out. The issue that I am having is if it is being used for an order, the join still fails.

  • Jeremy Kemper

    Jeremy Kemper May 4th, 2010 @ 06:48 PM

    • Milestone changed from “2.x” to “3.x”
  • Santiago Pastorino

    Santiago Pastorino February 2nd, 2011 @ 04:41 PM

    • State changed from “new” to “open”
    • Importance changed from “” to “”

    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.

  • Santiago Pastorino

    Santiago Pastorino February 2nd, 2011 @ 04:41 PM

    • State changed from “open” to “stale”
  • Mike Dalessio

    Mike Dalessio May 13th, 2011 @ 02:19 PM

    Please note that I've opened a github issue for this: https://github.com/rails/rails/issues/539

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>

People watching this ticket

Tags

Pages