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…

Accepted Answer

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
```

Sending array of values to a sql query in ruby?

Solution

Problem