Rails 3 Searching through a string using an array

Question

I am attempting to do a search in Rails 3 like so. I have a database object like so with an attribute like so:

@food.fruit = "Apples, Pears, Plums"

and I have a search that uses params from a checkbox, like so:

params[:search] = ["Apples", "Oranges", "Bananas", "Grapefruit"]

Since my @food object above has "Apples" and the search array has "Apples" I'd like for this search to be successful. However I am having trouble having this work properly.

My initial thought was to do something like

@results = Food.where("fruit LIKE ?", "%params[:search]%")

or

@results = Food.where("fruit IN ?", params[:search])

But the first only works if the params[:search] contains ONLY the @food.fruit elements and no others. The second doesn't work at all.

My last ditch resort is to do something like

@results = Array.new
params[:search].each do |search|
  @results << Food.where("fruit LIKE ?", search)
end

but I'd rather not do that if I don't have to. Anyone have any advice?

mu is too short · Accepted Answer · 2011-08-14 20:15:40Z

2

What you're looking for is some SQL like this:

WHERE LOWER(fruit) LIKE '%apples%'
   OR LOWER(fruit) LIKE '%oranges%'
   OR LOWER(fruit) LIKE '%bananas%'
   OR LOWER(fruit) LIKE '%grapefruit%'

Note that LIKE is not necessarily case insensitive so pushing everything to lower case (or upper case) is generally a good idea.

That's simple enough but saying x.where(...).where(...)... connects the conditions with AND when you want OR. One way is to build the first argument to where as a string by pasting together the right number of "LOWER(fruit) LIKE ?" strings to match the number of elements in params[:search]:

@results = Food.where(
    (["LOWER(fruit) LIKE ?"] * params[:search].length).join(' OR '),
    *(params[:search].map { |s| '%' + s.downcase + '%' })
)

edited Aug 14, 2011 at 20:15

answered Aug 12, 2011 at 3:37

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

4 Comments

goddamnyouryan Over a year ago

Thanks for your answer. This makes complete sense to me, and totally should work...and it does, when the params[:search] has one item in it. But when I make that array 2 or more, it throws me the "wrong number of bind variables (1 for 3)" error, with the 3 being how many items are in the array. This really doesn't make sense as to why this is happening.

mu is too short Over a year ago

@Ryan: You could try adding a splat to un-array the bind variable array (as in my fixed answer).

goddamnyouryan Over a year ago

ahh perfect...thank you! I wish I could be as brilliant with my SQL as you! Any tips to learn more?

mu is too short Over a year ago

@Ryan: Practice, make mistaeks, and learn from them. And get familiar with whatever documentation is available. Answering questions on SO helps too :) And there are a lot of people around here that are better at all this stuff than I am.

numbers1311407 · Accepted Answer · 2011-08-12 04:13:41Z

1

Essentially you're doing 5 separate searches. While making 5 separate SQL queries might not be the answer, you could always join the array, like:

scope :search_fruit, lambda {|*fruits|
  where fruits.flatten.map {|fruit| arel_table[:fruit].matches("%#{fruit}%") }.inject(&:or)
}

and

Food.search_fruit(params[:fruit])

edited Aug 12, 2011 at 4:13

answered Aug 12, 2011 at 3:36

numbers1311407

34.2k9 gold badges94 silver badges93 bronze badges

Collectives™ on Stack Overflow

Rails 3 Searching through a string using an array

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related