ActiveRecordのarel_tableから作れる条件式まとめ

$VERBOSE = true
require "active_record"
ActiveRecord::VERSION::STRING   # => "6.0.0"
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Migration.verbose = false
ActiveRecord::Schema.define do
  create_table :users do |t|
  end
end
class User < ActiveRecord::Base; end
def _(v) v.to_sql.remove('"', "users.") end

User.arel_table[:x].instance_eval do
  _ eq(nil)                        # => "x IS NULL"
  _ eq(0)                          # => "x = 0"
  _ eq_any([0, 1])                 # => "(x = 0 OR x = 1)"
  _ eq_all([0, 1])                 # => "(x = 0 AND x = 1)"

  _ not_eq(nil)                    # => "x IS NOT NULL"
  _ not_eq(0)                      # => "x != 0"
  _ not_eq_any([0, 1])             # => "(x != 0 OR x != 1)"
  _ not_eq_all([0, 1])             # => "(x != 0 AND x != 1)"

  _ gt(0)                          # => "x > 0"
  _ gt_any([0, 1])                 # => "(x > 0 OR x > 1)"
  _ gt_all([0, 1])                 # => "(x > 0 AND x > 1)"
  _ gteq(0)                        # => "x >= 0"
  _ gteq_any([0, 1])               # => "(x >= 0 OR x >= 1)"
  _ gteq_all([0, 1])               # => "(x >= 0 AND x >= 1)"

  _ lt(0)                          # => "x < 0"
  _ lt_any([0, 1])                 # => "(x < 0 OR x < 1)"
  _ lt_all([0, 1])                 # => "(x < 0 AND x < 1)"
  _ lteq(0)                        # => "x <= 0"
  _ lteq_any([0, 1])               # => "(x <= 0 OR x <= 1)"
  _ lteq_all([0, 1])               # => "(x <= 0 AND x <= 1)"

  _ self.in(0)                     # => "x IN (0)"
  _ self.in([0, 1])                # => "x IN (0, 1)"

  _ in_any([[0, 1], [2, 3]])       # => "(x IN (0, 1) OR x IN (2, 3))"
  _ in_all([[0, 1], [2, 3]])       # => "(x IN (0, 1) AND x IN (2, 3))"

  _ not_in(0)                      # => "x NOT IN (0)"
  _ not_in([0, 1])                 # => "x NOT IN (0, 1)"
  _ not_in_any([[0, 1], [2, 3]])   # => "(x NOT IN (0, 1) OR x NOT IN (2, 3))"
  _ not_in_all([[0, 1], [2, 3]])   # => "(x NOT IN (0, 1) AND x NOT IN (2, 3))"

  _ matches("a")                   # => "x LIKE 'a'"
  _ matches_all(["a", "b"])        # => "(x LIKE 'a' AND x LIKE 'b')"
  _ matches_any(["a", "b"])        # => "(x LIKE 'a' OR x LIKE 'b')"

  _ does_not_match("a")            # => "x NOT LIKE 'a'"
  _ does_not_match_all(["a", "b"]) # => "(x NOT LIKE 'a' AND x NOT LIKE 'b')"
  _ does_not_match_any(["a", "b"]) # => "(x NOT LIKE 'a' OR x NOT LIKE 'b')"

  _ between(0..1)                  # => "x BETWEEN 0 AND 1"
  _ between(0...1)                 # => "x >= 0 AND x < 1"

  _ not_between(0..1)              # => "(x < 0 OR x > 1)"
  _ not_between(0...1)             # => "(x < 0 OR x >= 1)"

  # Float::INFINITY を使ったときに意図した通りの SQL になるのは嬉しい
  I = Float::INFINITY
  _ between(0..I)                  # => "x >= 0"
  _ between(-I..0)                 # => "x <= 0"
  _ between(-I...0)                # => "x < 0"
  _ between(-I..I)                 # => "1=1"

  _ not_between(0..I)              # => "x < 0"
  _ not_between(-I..0)             # => "x > 0"
  _ not_between(-I...0)            # => "x >= 0"
  _ not_between(-I..I)             # => "1=0"

  # 次の2つは動くけど非推奨です。警告がでます。
  _ self.in(0..1)                  # => "x BETWEEN 0 AND 1"
  _ not_in(0..1)                   # => "(x < 0 OR x > 1)"
  # Passing a range to `#in`     is deprecated. Call `#between`, instead.
  # Passing a range to `#not_in` is deprecated. Call `#not_between`, instead.

  # なんで動かない！？
  _ eq([0, 1]) rescue $!           # => #<TypeError: can't quote Array>
  _ eq(0..1)   rescue $!           # => #<TypeError: can't quote Range>
end

たまにやってしまう間違い

eq(nil) なら IS NULL にしてくれるので eq は万能だと勘違いして Array や Range を渡して次のように怒られることがあります。

_ eq([0, 1]) rescue $!           # => #<TypeError: can't quote Array>
_ eq(0..1)   rescue $!           # => #<TypeError: can't quote Range>

この場合、生成されるSQLを意識しつつ、配列なら in に、範囲なら between にする必要があります。

_ self.in([0, 1])                # => "x IN (0, 1)"
_ between(0..1)                  # => "x BETWEEN 0 AND 1"

簡単に書くなら arel_table ではなく where を使って普通に where(x: [0, 1]) や where(x: 0..1) と書けば良いです。 配列なら in で、範囲なら between に切り替えてくれます。