Rails: search by SQL with different models

Go To StackoverFlow.com

0

I have model like this:

scope :search_posts, lambda { |query|
  mega_posts.where( "title LIKE ? OR category_id LIKE ? OR author_id LIKE ?", 
                    "%#{query}%",
                    "%#{query}%",
                    "%#{query}%"
  )
}

Every post has one category and one author. So, I want to have possibility to find my posts by category name or author name in my query (not by id). How can I do it? Thanks.

2012-04-03 20:28
by Oleg Pasko
Unless your table has just a few hundred records you should be using Sphinx. Queries like this just don't really scale well - Michael Chaney 2014-09-30 15:41


2

The most basic answer would be to do a join. If you've set up your associations properly, this should Just Work:

mega_posts.joins(:category).where("posts.title LIKE :query OR categories.name LIKE :query", :query => query)

Edit: In case of multiple associated models. You can do multiple joins:

mega_posts.joins(:category, :author).where('posts.title LIKE :query OR categories.name LIKE :query OR authors.name LIKE :query', :query => query)

It's even possible to nest joins:

Category.joins(:posts => [:author])

Or to write out the joins yourself, in case their more complex than Rails readily supports:

Post.joins('left join categories as c on c.id = posts.category_id').where('c.name = ?', 'foo')

Alternatively, if you're getting into really complex searches, you may want to look into a searching engine, like Sphinx or SOLR.

2012-04-03 20:56
by MrTheWalrus
Thanks! But what about situation, when I have one more related model (I've updated my question) - Oleg Pasko 2012-04-03 21:01
Annnd updated answer. : - MrTheWalrus 2012-04-03 21:10
Works! 100500.times do; p "Thanks"; end : - Oleg Pasko 2012-04-03 21:21