代码之家 › 专栏 › 技术社区 › David Hempy

Rails SQL跨多个列“select in”:其中(code1,code2)in((“A”,1),(“A”,3),(“Q”,9))

activerecord postgresql ruby ruby-on-rails sql

David Hempy · 技术社区 · 6 年前

我有一个业务需求,需要根据一个表中的两个字段选择记录:code1和code2。选择是复杂的、硬编码的,没有可编码的押韵或理由,包括大约12对,而表中实际存在的100对。

C、 1个
C、 2
J、 9号
Z、 0个

请注意,表中还有其他“C”代码,例如(C,3)。没有组合字段将两者都捕获为值,例如“C3”。

SQL支持以下查询: Two columns in subquery in where clause e、 g。

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

有没有一种方法可以在不使用原始SQL的情况下使用Rails和ActiveRecord的ORM做到这一点?

如果有必要的话,我正在用Postgres运行Rails 4.2.9。

*你为什么不*

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

使用原始SQL:是的。。。如果我不能通过ORM做到这一点,我可能会这么做。

2 回复 | 直到 2 年前

mu is too short 6 年前

如果您确实想要这种结构,那么您可以这样做:

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

再一次,你想把这种丑陋隐藏在一个范围内。

David Hempy 6 年前

*这是一个不错的解决方法,但并不完全是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中找到如何做到这一点,或者阅读关于解决问题的完全不同方法的建议。由于它都封装在一个作用域和一个常量中,所以以后重构就很简单了,将常量和作用域分开将允许进行无痛的测试。