search using join across databases?
timcreatewell opened this issue · 2 comments
Hi there,
I've unfortunately come across an issue that I'm not sure how to resolve with regards to searching on model attributes across two databases...
The situation is this: I have a table in one db - e.g "db1_dev.crew_application" and also another table in another db that is joined to this table by a "belongs_to" - e.g. "db2_dev.contact" .
"db1dev" is the main database for my project, so within the model for the "db2_dev.contact" table I have the necessary establish_connection "db2#{Rails.env}" . With this in place my rails app is chugging along just nicely with cross-database joins operating as expected.
Except I'm now trying to for the main table, "db1.crew_application", search on an attribute (first name) within "db2_dev.contact". I am doing this by declaring:
the_search = CrewApplication.search({:contact_first_name_contains => "Gandalf"})
This unfortunately renders the following error...
Mysql2::Error: Table 'db1_dev.contact' doesn't exist: SELECT COUNT(DISTINCT count_column) FROM (SELECT crew_application.id AS count_column FROM crew_application LEFT OUTER JOIN contact ON contact.id = crew_application.contact_id WHERE (contact.first_name LIKE '%Gandalf%') LIMIT 30 OFFSET 0) subquery_for_count
So, obviously the query isn't recognising that the table "contact" is actually in a totally different database to "crew_application"...
Is there something that I've neglected to do to make this work, or does anyone have any ideas how I could get this working?
Thanks in advance,
Tim.
Ok, fixed... Seems to be something lower down in rails..?
I fixed it by adding this to the model in the other database:
set_table_name "#{Contact.connection.current_database}.#{Contact.table_name}"
I also have exactly same problem while search using join across databases by meta_search plug-in.
My environment: Ruby 1.9.2p290, Rails 3.0.9, meta_search 1.0.6
My models look likes
class Visit < ActiveRecord::Base
self.establish_connection "lab"
belongs_to :patient
end
class Patient < ActiveRecord::Base
self.establish_connection "main"
has_many :visits
end
I added
=> set_table_name "#{Patient.connection.current_database}.#{Patient.table_name}"
in the other database's model as forum discussion, I got undefined method `eq' for nil:NilClass.
Patient.arel_table results is
=> # Arel::Table:0xabcb7f4 @name="main.patients", @engine=Patient(Table doesn't exist), @columns=nil, @Aliases=[], @table_alias=nil, @primary_key=nil
How to fix it ?
Thanks in advance,
Jmaniv