Arif Arif - 4 months ago 64
Ruby Question

Rails: Combine multiple search conditions

def self.search(search)
Post.where("caption LIKE ?", "%#{search}%")
Post.joins(:tags).where('tags.name LIKE ?', "%#{search}%" )
end


Here, I'm trying to combine both the conditions with an OR statement i.e. I want to be able to search through both post captions and tag names. How would I join them in a single line? Thanks!

PostsController

def index
if params[:search]
@posts = Post.search(params[:search]).order("created_at DESC").paginate(page: params[:page], per_page: 10)
else
@posts = Post.all.recent.paginate(page: params[:page], per_page: 10)
end
respond_to do |format|
format.html
format.js
end
end


post.rb

has_many :taggings
has_many :tags, through: :taggings


tag.rb

has_many :taggings
has_many :posts, through: :taggings


tagging.rb

belongs_to :post
belongs_to :tag

Answer

You have to join two tables and write the search conditions. Make sure to use LEFT JOIN, because JOIN will never give you a post that has no any tag. So, don't do it like this:

# This produces an SQL query with JOIN (not LEFT JOIN).
# Posts without tags will never be found.
Post.joins(:tags).where(...)

Instead, use Post.includes(:tags) whith references clause:

# you have to use 'references' clause here to produce a normal LEFT JOIN query instead of eager loading associations
Post.includes(:tags).where("posts.caption LIKE :search OR tags.name LIKE :search", search: "%#{search}%" ).references(:tags)

Or you can write the query condition manually using joins:

Post.joins('LEFT JOIN tags ON tags.post_id = posts.id').where("posts.caption LIKE :search OR tags.name LIKE :search", search: "%#{search}%")

If you are using Rails 5, there is a new method left_outer_joins that will do the same:

Post.left_outer_joins(:tags).where("posts.caption LIKE :search OR tags.name LIKE :search", search: "%#{search}%")
Comments