Active Record - Where IN with multiple columns

Question

I have a query that needs to fetch from a table that meet two columns requirements exactly. So if I have users table with columns, age and score.

SELECT * FROM users where (age, score) IN ((5,6), (9,12), (22,44)..)

In my web app I am getting this pairs from an ajax request, and the number could be quite big. How do I construct an Active Record query for this?.

I am working on postgres database

Answer 1

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)