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)