Posted at

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 '_田%')