Railsのwhere検索をまとめてみた

サンプルデータはこんな構造

<User:0x00007fc4bfb0a9d8
 id: 1,
 name: "千葉 優花",
 gender: "male",
 height: 150,
 profile: nil,
 created_at: Tue, 06 Nov 2018 15:03:40 JST +09:00,
 updated_at: Tue, 06 Nov 2018 15:03:40 JST +09:00>

演算子

=

> User.where(height: 150)
=>   User Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."height" = $1  [["height", 150]]

<=, =>

> User.where("height > ?", 150)
=>   User Load (1.0ms)  SELECT "users".* FROM "users" WHERE (height > 150)

> User.where("height <= ?", 150)
SELECT "users".* FROM "users" WHERE (height <= 150)

!= <>

> User.where.not(gender: 'male')
=>   User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."gender" != $1  [["gender", "male"]]

AND

User.where('height > ? and gender = ?', 170, 'male')
=>   User Load (1.4ms)  SELECT "users".* FROM "users" WHERE (height > 170 and gender = 'male')

> User.where('height > ?', 170).where(gender: 'male')
=>   User Load (0.4ms)  SELECT "users".* FROM "users" WHERE (height > 170) AND "users"."gender" = $1  [["gender", "male"]]

OR

> User.where('height > ? or gender = ?', 170, 'male')
=>   User Load (0.6ms)  SELECT "users".* FROM "users" WHERE (height > 170 or gender = 'male')

> User.where('height > ?', 170).or(User.where(gender: 'male'))
=>   User Load (0.8ms)  SELECT "users".* FROM "users" WHERE ((height > 170) OR "users"."gender" = $1)  [["gender", "male"]]

BETWEEN a AND b

> User.where(height: 170..180)
=>   User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."height" BETWEEN $1 AND $2  [["height", 170], ["height", 180]]

IN

> User.where(name: ["千葉 優花", "金子 茜", "山田 優奈"])
=>   User Load (1.0ms)  SELECT "users".* FROM "users" WHERE "users"."name" IN ($1, $2, $3)  [["name", "千葉 優花"], ["name", "金子 茜"], ["name", "山田 優奈"]]

LIKE

> User.where("name LIKE ?", "%子%")
=>   User Load (2.4ms)  SELECT "users".* FROM "users" WHERE (name LIKE '%子%')

> User.where("name LIKE ?", "_田%")
=>   User Load (0.4ms)  SELECT "users".* FROM "users" WHERE (name LIKE '_田%')