Search for multiple words, across multiple models

I'm trying to create search functionality in a site, and I want the user to 开发者_开发问答be able to search for multiple words, performing substring matching against criteria which exist in various models.

For the sake of this example, let's say I have the following models:

• Employee
• Company
• Municipality
• County

A county has multiple municipalities, which has multiple companies, which have multiple employees.

I want the search to be able to search against a combination of Employee.firstname, Employee.lastname, Company.name, Municipality.name and County.name, and I want the end result to be Employee instances.

For example a search for the string "joe tulsa" should return all Employees where both words can be found somewhere in the properties I named in the previous sentence. I'll get some false positives, but at least I should get every employee named "Joe" in Tulsa county.

I've tried a couple of approaches, but I'm not sure I'm going down the right path. I'm looking for a nice RoR-ish way of doing this, and I'm hoping someone with more RoR wisdom can help outline a proper solution.

What I have tried:

I'm not very experienced with this kind of search, but outside RoR, I'd manually create an SQL statement to join all the tables together, create where clauses for each separate search word, covering the different tables. Perhaps use a builder. Then just execute the query and loop through the results, instantiate Employee objects manually and adding them to an array.

To solve this in RoR, I've been:

1) Dabbling with named scopes in what in my project corresponds to the Employee model, but I got stuck when I needed to join in tables two or more "steps" away (Municipality and County).

2) Created a view (called "search_view") joining all the tables together, to simplify the query. Then thought I'd use Employee.find_by_sql() on this table, which would yield me these nice Employee objects. I thought I'd use a builder to create the SQL, and it seemed that Arel was the thing to use, so I tried doing something like:

view = Arel::Table.new(:search_view)

But the resulting Ariel::Table does not contain any columns, so it's not usable to build my query. At this point I'm a bit stuck as I don't know how to get a working query builder.

I strongly recommend using a proper search engine for something like this, it will make life a lot easier for you. I had a similar problem and I thought "Boy, I bet setting up something like Sphinx means I have to read thousands of manuals and tutorials first". Well, that's not the case.

Thinking Sphinx is a Rails gem I recommend which makes it very easy to integrate Sphinx. You don't need to have much experience at all to get started:

http://freelancing-god.github.com/ts/en/

I haven't tried other search engines, but I'm very satisfied with Sphinx. I managed to set up a relatively complex real-time search in less than a day.