ActiveRecord Where clause using items of array in OR operator-CodePudding

I have an array like this

arr = %w[android ios]

and I want to use these values in Where clause in a query like below:

SELECT * FROM controls
WHERE company_id = '12345' AND (ios > 13 OR android > 14)

where the fields inside ( ) are the values of array .. so if i have only one value in array it would be

 WHERE company_id = '12345' AND (ios > 13)

for example

Is it possible using Ruby on rails ActiveRecord?

CodePudding user response：

Looks like you need not array but hash like this

more_than = {
  ios: 13,
  android: 14
}

You can build SQL condition string with sanitize_sql_for_conditions from array with placeholders

sql_condition_array =
  more_than.each_with_object([]) do |(atr, value), sql_array|
    if sql_array.empty?
      sql_array[0] = "#{atr} > ?"
    else
      sql_array[0] << " OR #{atr} > ?"
    end

    sql_array << value
  end
# => ["ios > ? OR android > ?", 13, 14]

sql_condition = Control.sanitize_sql_for_conditions(sql_condition_array)
# => "ios > 13 OR android > 14"

or may be directly

sql_condition = more_than.map { |atr, value| "#{atr} > #{value}" }.join(" OR ")
# => "ios > 13 OR android > 14"

And then

Control.where(company_id: 12345).where(sql_condition)

The query will be like this:

SELECT "controls".* FROM "controls"
WHERE "controls"."company_id" = 12345
AND (ios > 13 OR android > 14);

If that hash will consist of just one element, there will be not OR used like this:

SELECT "controls".* FROM "controls"
WHERE "controls"."company_id" = 12345
AND (ios > 13);

CodePudding user response：

define a hash instead of an array, hash = { 'android' => 14, 'ios' => 13}

and then form a string that can be passed to where

Complete code looks like this

hash = { 'android' => 14, 'ios' => 13}
str = ''
hash.each do |k, v|
    str  = k   ' > '   v.to_s   ' OR '
end
Control.where(company_id: '12345').where(str[0..-5])