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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
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 On 9/19/06, Bill Devaul <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > 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 > > -- > Posted via http://www.ruby-forum.com/. > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Carl Fyffe 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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
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 On 9/19/06, Bill Devaul <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > Carl Fyffe 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 > > -- > Posted via http://www.ruby-forum.com/. > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---