活动记录-其中IN有多列

Question

我有一个查询，需要从一个恰好满足两列要求的表中提取。因此，如果我有一个users表，其中包含列age和score。

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

在我的web应用程序中，我从ajax请求中获得了这些对，数量可能相当大。如何为此构造活动记录查询？。

我正在处理postgres数据库

Answer 1

理想情况下，我们将根据输入构建一个查询字符串。例如

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)

最后，您的查询将是

User.where(query_string)

您可以更正如何构建查询字符串的逻辑，因为ages_and_scores的格式与我的示例不同。

改进

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)

Answer 2

另一种方法，基于多列IN子句重建OP使用的精确查询。

一种更紧凑的SQL形式，具有更好的语义：

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})")

Answer 3

在rails 5中，您可以使用OR，因此您可以执行：

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)

我相信这是一个无sql注入的纯ActiveRecord。

Answer 4

嘿，你可以在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]}"})

在PG数据库中，您可以使用直接Concat

   (age || ' ,' || score)

Answer 5

另一个解决方案是使用Arel，这将有助于保持数据库的不可知性。

ages_and_scores = [ [5, 6], [9, 12], [22, 44] ]
first_age, first_score = ages_and_scores.shift
t = User.arel_table
users = ages_and_scores.inject(User.where(t[:age].eq(first_age)).where(t[:score].eq(first_score))) { |query, age_score| query.or(User.where(t[:age].eq(age_score[0])).where(t[:score].eq(age_score[1]))) }

基本原理：

1. 从数组中分离第一个值对
2. 获取arel表，以便构建查询
3. 使用Ruby的Enumerable模块中的inject方法迭代数组的其余部分，添加所有or条件。。。但是从第一个CCD_ 8查询中的初始两个值开始

最后，您将有一个Arel查询，它返回所有符合您的条件的用户。。。它应该可以在Rails（Arel）支持的任何DB中工作。

Answer 6

基于井上幸树的评论。我修改了一点，它运行得很顺利

ages_and_scores = [ [5, 6], [9, 12], [22, 44] ]
ages_and_scores.each do |age, score|
  User.where(age: age).map(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)