Rails SQL 跨几列"选择 in"：其中 (code1， code2) in (("A"， 1)， ("A"， 3)， ("Q"， 9))

Question

我有一个业务要求，即根据一个表中的两个字段选择记录：code1 和 code2。选择是复杂和硬编码的，没有可编码的押韵或原因，并且包括表中实际存在的一百对中的十几对。

• C， 1
• C， 2
• J， 9
• Z， 0

请注意，表中还有其他"C"代码，例如 (C， 3(。没有组合字段将它们捕获为值，例如"C3"。

SQL 支持这样的查询：子查询中的两列 where 子句，例如

SELECT * from rejection_codes
where (code1, code2) in (("A", 1), ("A", 3), ("Q", 9))

有没有办法用Rails和ActiveRecord的ORM做到这一点，而不诉诸原始SQL？

如果重要的话，我正在使用Postgres运行Rails 4.2.9。

*你为什么不... *

添加字段：我无法控制数据库架构。如果我这样做了，我会添加一个新列作为该组的标志。或者将值连接成字符串的计算列。什么的...但我不能。

使用原始 SQL：是的...如果我不能通过ORM做到这一点，我可能会这样做。

Answer 1

如果你想要这个结构，那么你可以做这样的事情：

pairs = [['A', 1], ['A', 3], ['Q', 9]]
RejectionCode.where('(code1, code2) in ((?), (?), (?))', *pairs)

当然，pairs.length大概不会总是三个，所以你可以说：

pairs = [['A', 1], ['A', 3], ['Q', 9]]
placeholders = (%w[(?)] * pairs.length).join(', ')
RejectionCode.where("(code1, code2) in (#{placeholders})", *pairs)

是的，这是使用字符串插值来构建 SQL 代码段，但在这种情况下它是完全安全的，因为您正在构建所有字符串并且您确切地知道其中的内容。如果你把它放在一个范围内，那么至少丑陋会被隐藏起来，你可以很容易地用你的测试套件来覆盖它。

或者，您可以利用一些等效性。in是一种花哨的or所以它们做大致相同的事情：

c in (x, y, z)
c = x or c = y or c = z

记录(甚至是匿名记录(是逐列比较的，因此这些记录是等效的：

(a, b) = (x, y)
a = x and b = y

这意味着像这样的事情：

pairs = [['A', 1], ['A', 3], ['Q', 9]]
and_pair = ->(a) { RejectionCode.where('code1 = ? and code2 = ?', *a) }
and_pair[pairs[0]].or(and_pair[pairs[1]]).or(and_pair[pairs[2]])

应该给你相同的结果。或者更一般地说：

pairs = [['A', 1], ['A', 3], ['Q', 9], ... ]
and_pair = ->(a) { RejectionCode.where('code1 = ? and code2 = ?', *a) }
query = pairs[1..-1].inject(and_pair[pairs.first]) { |q, a| q.or(and_pair[a]) }

同样，你想把这种丑陋隐藏在一个范围内。

Answer 2

*这是一个不错的解决方法，但不完全是 ORM 问题的解决方案 *

未能在ActiveRecord中找到正确的方法来执行此操作，我只是猜测，希望最好：

class ApprovalCode < ActiveRecord::Base
REJECTION_CODES = [
['A', '0'],
['R', '1'],
['R', '5'],
['R', '6'],
['X', 'F'],
['X', 'G']
]
scope :rejection_allowed, -> { where([:code, :sub_code], REJECTION_CODES) }  # This didn't work.
end

那行不通。因此，我在作用域中使用了原始SQL，这确实有效：

scope :rejection_allowed, -> { where("(code, sub_code) in (#{rejection_list})") }
def self.rejection_list
REJECTION_CODES
.map{|code, sub_code| "('#{code}', '#{sub_code}')"}
.join(', ')
end

我仍然希望在ORM中找到如何做到这一点，或者阅读有关解决问题的完全不同的方法的建议。由于它都封装在一个作用域和一个常量中，因此以后重构将变得微不足道，并且将常量和作用域分开将允许进行无痛的测试。