I'd like to build a search function on my website that searches multiple tables in my database. I have the pages table and pages_categories table in which I'd like to search.
So the pages table looks like - id - parent_id - page_category_id - nl_title - fr_title - nl_content - fr_content
and the pages_categories looks like - id - nl_title - fr_title
At the moment, I can search in the pages table in it's title or content.
A page can also be a parent (for example fruit), if it's a parent the parent_id is empty. There are also children (like apple, orange,..), the child has the parent_id, this is represented by the id of a parent.
SELECT * FROM
pages WHERE (((nl_title LIKE '%fruit%' OR nl_content LIKE '%fruit%' OR nl_intro LIKE '%fruit%')))
So I'd like to search in the page_categories, in the pages and in the children of a page.
Some examples, - if I search for cocktail and it's a page_category, it has to show the pages that belong to cocktail. - if I search for the parent fruit, I'd like to get all the fruits, apples, oranges, ... - and 1 last one, if I search for a simple page, I'd like to find that also.
How can I do this on a rails way?
I think this is the query you needed..
Page.all(:conditions=>['nl_title like ? || nl_content like ? || nl_intro like ?', 'fruit', 'fruit', 'fruit'])
If u want to search from 2 tables related each other, see my blog..
If your site is going to have a lot of content and search is going to be prominent, you may want to consider adding a search engine such as SOLR or Sphinx.
I've used SOLR with Lucene in a previous project. Setting up and configuring the server is pretty straight forward. Search results are very fast, especially if you use the results from the search engine directly and not look up every result in your database.
I too would recommend using a plugin if you want to easily search across multiple models. Thinking Sphinx is great if you have a large database, but needs some extra work to re-index the data periodically. For smaller projects, searchlogic is a good option since it's really easy to get started with.
If you want query based searching then go for searchlogic and if you want full text search then go for acts_as_ferret. It is better than Sphnix. In older version of rails sphnix break the server.
sphinx and solr are both very robust indexed search options. i perfer acts_as_solr over acts_as_ferret for solr. thinking sphinx is an excellent gem for sphinx. i would give solr the nod simply due to the fact heroku doesn't support sphinx and heroku rocks. solr does give you actual document indexing which may or may not come in handy. other than that i would say there are rather equal.