search using join across databases?

[timcreatewell]

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.

[timcreatewell]

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}"

[jmaniv]

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