Multiple keywords database search

Question

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?

klochner · Accepted Answer · 2011-07-29 18:15:52Z

1

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.

answered Jul 29, 2011 at 18:15

klochner

8,1451 gold badge35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tommy chheng · Accepted Answer · 2011-07-29 16:53:03Z

0

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.

answered Jul 29, 2011 at 16:53

tommy chheng

9,25810 gold badges57 silver badges75 bronze badges

1 Comment

sublimeike Over a year ago

Honestly, I was trying to do something that did not require me to install anything on the server

Collectives™ on Stack Overflow

Multiple keywords database search

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related