Database multitable and/or multicolumn search

I’d like to search a database through n terms for something like:

WHERE
(category.name, subcategory.name) LIKE TERM1 AND
(category.name, subcategory.name) LIKE TERM2 AND
(category.name, subcategory.name) LIKE TERM3 …

A category has many subcategories and a subcategory has one category.

I suppose I could iterate through the results and check for the terms in
each field but that seems…well…ugly.

This seems more complicated than a typical case, but does ActiveRecord
support something like this out of the box?

OT: If not, is there an SQL search that will give the result I’m
looking for?
I’m hoping to stick with MySQL but would consider other *nix DBMS to get
this result.

Thanks,
Bill

Bill,

There are two directions you could go with this, you could go strictly
SQL and use the EZWhere plugin to help you build the SQL or you could
go with an index search engine using the Ferret plugin. Personally I
think I would go with Ferret, but I am already familiar with Lucene
(which Ferret is based on).

Just two ideas, I am sure others will have more. Good luck!

Carl

Carl F. wrote:

There are two directions you could go with this, you could go strictly
SQL and use the EZWhere plugin to help you build the SQL or you could
go with an index search engine using the Ferret plugin. Personally I
think I would go with Ferret, but I am already familiar with Lucene
(which Ferret is based on).

Thanks Carl. I will investigate both.

I guess I’m also a little stumped on creating the SQL for this. I’ve
spent an hour or so searching and the best I seem to find is MSSQL’s
fulltext search.

For example, imagine a table with many email columns. One is for work,
one for home, etc. (I know this may not be a best practice.) How do I
push those fields together for one search?

Bill

Bill,

When you say fulltext search, that really points to using Ferret,
especially if you want to avoid using database specific features. I
believe most of the modern databases all have some type of fulltext
search built in. I know Oracle does, you told me MSSQL does. Google
would have the answer for the others.

However, your many emails problem can be solved by normalizing your
database and putting those fields into their own table that has a
foreign key back to the table they belong to.

Carl

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs