can you use activerecord to find substring of a fi

Question

Suppose a database contains a field 'keywords' and sample records include: "pipe wrench" "monkey wrench" "crescent wrench" "crescent roll" "monkey bars"

is there a way in activerecord to find the records where the keyword field contains the substring "crescent"?

(It's just a quick and dirty lookup for a quick concept prototype)

Answer 1

The Postgres database syntax would be:

YourModelName.where("yourFieldName like ?", "%" + yourSearchTerm + "%")

Answer 2

Yeah, just use a LIKE statement in MySQL.

In Rails 2.x:

Table.find(:all, :conditions => ['keywords LIKE ?', '%crescent%'])

In Rails 3.x:

Table.where('keywords LIKE ?', '%crescent%').all

Answer 3

It all depends on your DB. Is it Postgres? MySQL? MongoDB? Anything else?

With Postgres, you could use something like :

Rails 2.x => Model.find(:all, :conditions=>["models.keywords ~= ?", 'crescent'])
Rails 3.x => Model.where("models.keywords ~= ?", 'crescent')

You just have to find the right syntax for your DB / Rails / ActiveRecord version.

Answer 4

I had a similar issue. I needed to see if there are keywords passed from conrolled input in the frontend component in the body of any questions in my questions table. Here is how I did it in my controller:

   def search
     input = params[:q]
       @questions = Question.all
       search_words = input.split(' ')
       @found_question = [];

       search_words.each do |word|
         @found_question << Question.where("questions.body LIKE ?", "%#{word}%")
     end
   end