Sending Array of Values to a SQL Query in Ruby

Sending array of values to a sql query in ruby?

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

adding array as parameter to sql query in ruby

As others said, you can't parametrise a whole array. Use this instead:

ziparray = ["95626", "95645", "95837"]
zip_placeholders = ziparray.map.with_index(1) { |_, i| "$#{i}" }.join(', ')
sql = "SELECT * from table_name WHERE code in (#{zip_placeholders});"
# => "SELECT * from table_name WHERE code in ($1, $2, $3)"

Then you can use the normal parameter binding.

Rails SQL query, passing array of ids to sql WHERE...IN clause

Try:

my_query = "DELETE from contacts_tags where tag_id = 99 AND contact_id IN (#{ids_for_delete.join(', ')})"

use the method join to generate string like "1, 2, 3, 4.."

How to loop in array inside sql query in ruby

1. Selecting all records is always an anti-pattern. One should select only records of interest. If you have problems writing the query selecting only records of interest, you should revise your database structure.

2. write a SQL query in a simple way

That is relatively easy. For single lang to check it would be:

%q|SELECT * FROM users WHERE languages LIKE "%#{lang}%"|

3. Even using all, your method is abusing each for reducing the input.

In ruby it should be written as:

def get_users_by_filtered_langauges(*langs)
  # single lang
  # User.all.reject { |u| u.languages.include?(lang) }

  # disjunction
  User.all.reject { |u| (u.languages & langs).empty? }
end

How to pass rails array mysql Where IN condition

You are passing a string representation of the array to MySQL, which doesn't work. You need to insert the values in the array into the query. This can be done by escaping the skills, and joining them:

skills.map { |s| "'#{s}'" }.join(', ')

This produces 'ruby', 'Ruby on Rails', which is a valid argument for the IN statement.

A better approach however is to not write the raw SQL at all, but rely on ActiveRecord to generate it. This is the more maintainable and readable approach.

Question.joins(:quest_answers).where(category: skills)

Passing an array to where converts it automatically into a subset condition.

---

Related Topics