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?
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); 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]) 
-5?
-5 is for ignoring last four characters from str which will be ' OR 'Array#join method for thisA more ActiveRecord approach:
Control.where(company_id: 12345).where("ios > ?", 13).or(Control.where(company_id: 12345).where("android > ?", 14)) Better way:
controls = Control.where(company_id: 12345) controls = controls.where("ios > ?", 13).or(controls.where("android > ?", 14)) This generates the next SQL query:
SELECT "controls".* FROM "controls" WHERE "controls"."company_id" = 12345 AND (ios > 13 OR android > 14) 