inserting variable into find conditions => SQL query

Question

ok, so probably best if i just paste code and then explain

   search = "your horses"

   exp1 = ""
   exp2 = ""

   myarray = search.split(/ /)
   mylength = myarray.length           #this would return 2 in this case

   mylength.times do
    exp1 += "AND name LIKE ? "         #this gives--> AND name LIKE ? AND name LIKE ?
   end

   for i in 0..(mylength - 1)
    exp2 += ("%#{myarray[i]}%, ")    #and this gives--> your, horses,
   end

find(:all, :conditions => ["#{exp1}",  exp2])

and here at the end i get a problem because exp2 becomes 'your, horses,' inside the find function. what do i have to do that inside these conditions there would be no additional single quote marks inserted?

or maybe i should do it some other way?

thank you very much for your answers!

Answer 1

I have no idea how to write this in Ruby, however as a general rule when compiling comma separated values, I've found it easiest to put the comma at the beginning when you are appending like so:

var = '';

var += ',name1';
var += ',name2';
var += ',name3';

print substring(var, 1);

This has always been easier for me, rather than trying to detect the extra comma and chop it off of the end. Hopefully there's a substring command in Ruby. :)

Answer 2

Try removing your last for block and doing this instead:

find(:all, :conditions => [exp1,  *exp2])

This should put your "?" replacements in as the final elements of the array, which is the correct Rails syntax.

(Also, no idea why you converted exp1, a string, into a string by interpolating itself - "#{expr1}" is the same as expr1.)

Answer 3

   search = "your horses"
   myarray = search.split(/ /)
   mylength = myarray.length    
   find(:all, :conditions => [myarray.map{|x| 'name like ?'}.join(' AND '),  *(myarray.map{|x| "%#{x}%"})])

Answer 4

I've used a technique of compiling a set of conditions incrementally and then feeding that through to the find call on many occasions an it works especially well for this sort of thing:

search = "your horses"

# Establish a template conditions set
conditions = [ [ ] ]

# For each part of the name, add an element
# to the conditions.
search.split(/\s+/).each do |name|
  conditions[0] << 'name LIKE ?'
  conditions << "%#{name}%"
end

# Collapse the array into a single string
conditions[0] = conditions[0].join(' AND ')

# Run the query
find(:all, :conditions => conditions)

The key is to start with an empty Array with a single empty Array in it. The WHERE conditions are appended to the first element, and the placeholders are appended to the overall conditions Array.

This is flexible in that you can choose to use OR or AND as required and when augmented with named scopes, can do even more things.

Answer 5

I know this is already answered, but you are looking for several words on a name, aren't you?

In that case I think you can do the SQL query with just one LIKE condition. In other words:

search = "your horses"
searchWithPercents = "%#{search.split.join('%')}%" # --> '%your%horses%'
find(:all, :conditions => ['name LIKE ?', searchWithPercents])

No need to create a complicated query with ANDS.