ernie/meta_search — Issue #69: search using join across databases?
Author: timcreatewell
Created: 2011-10-15 03:26:32+00:00
State: open

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.