This project is archived and is in readonly mode.

establish_connection does not support joins

#1902

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'
>>

Reported by Andrew Roth · February 7th, 2009 @ 04:05 AM

Activity

scott

+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

March 13th, 2009 @ 05:45 PM

scott

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

March 13th, 2009 @ 05:48 PM

scott

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

March 16th, 2009 @ 11:51 PM

jonbro (at gmail)
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.

May 28th, 2009 @ 08:35 PM
Jeremy Kemper
- Milestone changed from 2.x to 3.x
[bulk edit]

May 4th, 2010 @ 06:48 PM
Santiago Pastorino
- 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.

February 2nd, 2011 @ 04:41 PM
Santiago Pastorino
- State changed from open to stale
February 2nd, 2011 @ 04:41 PM
Mike Dalessio
Please note that I've opened a github issue for this: https://github.com/rails/rails/issues/539

May 13th, 2011 @ 02:19 PM