Sending array of values to a sql query in ruby?

Question

I'm struggling on what seems to be a ruby semantics issue. I'm writing a method that takes a variable number of params from a form and creates a Postgresql query.

def self.search(params)
    counter = 0
    query = ""
    params.each do |key,value|
        if key =~ /^field[0-9]+$/
            query << "name LIKE ? OR "
            counter += 1
        end
    end
    query = query[0..-4] #remove extra OR and spacing from last

    params_list = []
    (1..counter).each do |i|
      field = ""
      field << '"%#{params[:field'
      field << i.to_s
      field << ']}%", '
      params_list << field
    end
    last_item = params_list[-1]
    last_item = last_item[0..-3] #remove trailing comma and spacing
    params_list[-1] = last_item

    if params
        joins(:ingredients).where(query, params_list)
    else
        all
    end
end

Even though params_list is an array of values that match in number to the "name LIKE ?" parts in query, I'm getting an error: wrong number of bind variables (1 for 2) in: name LIKE ? OR name LIKE ? I tried with params_list as a string and that didn't work any better either. I'm pretty new to ruby.

I had this working for 2 params with the following code, but want to allow the user to submit up to 5 ( :field1, :field2, :field3 ...)

def self.search(params)
    if params
        joins(:ingredients).where(['name LIKE ? OR name LIKE ?', 
            "%#{params[:field1]}%", "%#{params[:field2]}%"]).group(:id)
    else
        all
    end
end

Could someone shed some light on how I should really be programming this?

Answer 1

PostgreSQL supports standard SQL arrays and the standard any op (...) syntax:

9.23.3. ANY/SOME (array)

expression operator ANY (array expression)
expression operator SOME (array expression)

The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of ANY is "true" if any true result is obtained. The result is "false" if no true result is found (including the case where the array has zero elements).

That means that you can build SQL like this:

where name ilike any (array['%Richard%', '%Feynman%'])

That's nice and succinct so how do we get Rails to build this? That's actually pretty easy:

Model.where('name ilike any (array[?])', names.map { |s| "%#{s}%" })

No manual quoting needed, ActiveRecord will convert the array to a properly quoted/escaped list when it fills the ? placeholder in.

Now you just have to build the names array. Something simple like this should do:

fields = params.keys.select { |k| k.to_s =~ /\Afield\d+\z/ }
names  = params.values_at(*fields).select(&:present)

You could also convert single 'a b' inputs into 'a', 'b' by tossing a split and flatten into the mix:

names = params.values_at(*fields)
              .select(&:present)
              .map(&:split)
              .flatten

Answer 2

You can achieve this easily:

def self.search(string)
  terms = string.split(' ') # split the string on each space
  conditions = terms.map{ |term| "name ILIKE #{sanitize("'%#{term}%'")}" }.join(' OR ')
  
  return self.where(conditions)
end

This should be flexible: whatever the number of terms in your string, it should returns object matching at least 1 of the terms.

Explanation:

The condition is using "ILIKE", not "LIKE":

• "ILIKE" is case-insensitive
• "LIKE" is case-sensitive.

The purpose of the sanitize("'%#{term}%'") part is the following:

• sanitize() will prevent from SQL injections, such as putting '; DROP TABLE users;' as the input to search.

Usage:

User.search('Michael Mich Mickey')
# can return
<User: Michael>
<User: Juan-Michael>
<User: Jean michel>
<User: MickeyMouse>