Active Record - Where IN with multiple columns

In rails 5, you can use OR, so you can do:

ages_and_scores = [ [5, 6], [9, 12], [22, 44] ]
ages_and_scores.map do |age, score|
  User.where(age: age).where(score: score)
end.reduce(&:or)
# => should produce something like:
# SELECT * FROM users WHERE (`users`.`age` = 5 AND `users`.`score` = 6 OR `users`.`age` = 9 AND `users`.`score` = 12 OR `users`.`age` = 22 AND `users`.`score` = 44)

I believe this is sql-injection free and pure ActiveRecord.


Another method, to reconstruct the exact query used by the OP based on a multicolumn IN clause.

A more compact form of SQL, with arguable better semantics:

ages_and_scores = [ [5, 6], [9, 12], [22, 44] ]
query_string    = ages_and_scores.map { |pair| "(#{pair[0]},#{pair[1]})" }.join(",")
User.where("(age, score) IN (#{query_string})")

Ideally, we will build a query string based on the input. Eg

ages_and_scores = [ [5, 6], [9, 12], [22, 44] ]
query_string = ages_and_scores.map do |pair|
  "(age = #{pair[0]} AND score = #{pair[1]})"
end.join(" OR ")
# => (age = 5 AND score = 6) OR (age = 9 AND score = 12) OR (age = 22 AND score = 44)

Finally, your query will be

User.where(query_string)

You may correct the logic of how to build the query string since ages_and_scores is in a different format to my example.

Improvement

ages_and_scores = [ [5, 6], [9, 12], [22, 44] ]
query_params = []

query_template = ages_and_scores.map{ |_| "(age = ? AND score = ?)" }.join(" OR ")
# => (age = ? AND score = ?) OR (age = ? AND score = ?) OR (age = ? AND score = ?)

User.where(query_template, *ages_and_scores.flatten)

Hey you can try this way in mysql:

ages_and_scores = [ [5, 6], [9, 12], [22, 44] ]

User.where("CONCAT(age,',', score) in (?)",ages_and_scores.map{|b| "#{b[0]},#{b[1]}"})

In PG database you can directly Concat using:

   (age || ' ,' || score)