CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3full-text-search
sublimeike
sublimeike

Reputation: 182

Multiple keywords database search

I am currently using scopes in my model to perform searches within a database. I can stack these scopes and it will output results matching all parameters. 

scope :search_between, lambda{|begin_date, end_date|
  where "sub.date BETWEEN ? AND ?", begin_date, end_date
}

What I am having trouble with is integrating a keywords search that will search the entire database and output the results that contain the sum of the keywords. I would like to do something like this (displayed for simplicity):

scope :keywords, lambda{|search|
  search.chomp.split(/,\s*/) do |item|
    where "date like ? or city like ? or state like ?", "%#{item}%" and
    where "date like ? or city like ? or state like ?", "%#{item}%" and
    where "date like ? or city like ? or state like ?", "%#{item}%"
  end
}

I am currently using something like this to take care of searching multiple columns:

scope :keywords, lambda{|search|
  search.chomp.split(/,\s*/) do |item|
    where(Sub.column_names.map {|cn| "#{cn} like ?" }.join("or "), "%#{item}%"]  Sub.column_names.size)).join(' AND ')
  end
}

My problem is that I want to do multiple "where()'s" in the scope. Is it possible and if so, how?

Upvotes: 0

Views: 1814

Answers (2)

klochner
klochner

Reputation: 8125

Just turn it into a method that returns a scope:

def self.keywords(search)
  scope = self.scoped
  search.chomp.split(/,\s*/).each do |item|
    scope = scope.where(["date  like ? or 
                          city  like ? or 
                          state like ?", "%#{item}%","%#{item}%","%#{item}%"])
  end
  scope
end
The drawback is that you can't chain keywords off of other scopes, but you can chain other scopes off of keywords.

Upvotes: 1

tommy chheng
tommy chheng

Reputation: 9218

I think you will be hitting the limit with using the database for full text search.

Have you looked into using Solr or Sphinx for your full text searches? There's also IndexTank if you want to use a 3rd party service.

For solr, there are rubygems available to help you: sunspot, solrsan(i'm the author)

Sunspot is definitely more full-featured and solrsan is a barebones layer.

Upvotes: 0

Related Questions