# Database multitable and/or multicolumn search

**URL:** <https://www.ruby-forum.com/t/database-multitable-and-or-multicolumn-search/69235>\
**Category:** Rails\
**Created:** [September 19, 2006, 3:53pm UTC](https://www.ruby-forum.com/t/database-multitable-and-or-multicolumn-search/69235 "2006-09-19T15:53:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![devaulw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/8e7dd6/32.png) [@devaulw](https://www.ruby-forum.com/u/devaulw)\
**Post date:** [September 19, 2006, 3:53pm UTC](https://www.ruby-forum.com/t/database-multitable-and-or-multicolumn-search/69235/1 "2006-09-19T15:53:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![devaulw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/8e7dd6/32.png) [@devaulw](https://www.ruby-forum.com/u/devaulw)\
**Post date:** [September 19, 2006, 4:17pm UTC](https://www.ruby-forum.com/t/database-multitable-and-or-multicolumn-search/69235/2 "2006-09-19T16:17:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![devaulw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/8e7dd6/32.png) [@devaulw](https://www.ruby-forum.com/u/devaulw)\
**Post date:** [September 19, 2006, 5:15pm UTC](https://www.ruby-forum.com/t/database-multitable-and-or-multicolumn-search/69235/3 "2006-09-19T17:15:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![devaulw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/8e7dd6/32.png) [@devaulw](https://www.ruby-forum.com/u/devaulw)\
**Post date:** [September 19, 2006, 9:45pm UTC](https://www.ruby-forum.com/t/database-multitable-and-or-multicolumn-search/69235/4 "2006-09-19T21:45:02Z")

</div>

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