CODEWITHSUNDEEP
ruby-on-railssearch
Arif
Arif

Reputation: 742

Rails: Combine multiple tables 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

Upvotes: 2

Views: 2852

Answers (4)

chumakoff
chumakoff

Reputation: 7024

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 OUTER JOIN taggings ON taggings.post_id = posts.id LEFT OUTER JOIN tags ON taggings.tag_id = tags.id')
.where("posts.caption LIKE :search OR tags.name LIKE :search", search: "%#{search}%").uniq

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}%")

Upvotes: 3

Ioana Surdu Bob
Ioana Surdu Bob

Reputation: 139

For advanced filtering, I'd use scopes. You could create two scopes with the queries and call them both in the controller.

Model:

scope :search1, -> (search) {where("caption LIKE :search", "%#{search}%")}
scope :search2, -> (search) {joins(:tags).where('tags.name LIKE :search', "%#{search}%" )}

Controller:

@posts = Post.where(nil)
@posts = @posts.search1(params[:search]) if params[:search].present?
@posts = @posts.search2(params[:search]) if params[:search].present?
@posts = @posts.recent.paginate(page: params[:page], per_page: 10)

More about scopes here: http://www.justinweiss.com/articles/search-and-filter-rails-models-without-bloating-your-controller/

His post helped me a lot, I recommend you to read it :)

Upvotes: 0

孙悟空
孙悟空

Reputation: 1285

Try this: 

Post.joins(:tags).where("caption LIKE ? OR tags.name LIKE ?", "%#{search}%", "%#{search}%")

Upvotes: 0

Deepak Mahakale
Deepak Mahakale

Reputation: 23661

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

Upvotes: 0

Related Questions