【Rails】Active Recordで作ったオブジェクトを検索しよう！（find/where）

Ruby on Railsでデータベースを操作するのに使うActive Record。

今回は、findやwhereを使って実現するオブジェクトの検索方法を紹介します。

今回利用するテーブル情報はこちらです。

基本的な使い方

検索するメソッドはいっぱいあるのですが、よく使う項目は以下のようなのがあります。

• find
• find_by
• find_by!
• first
• last
• where

1件だけ取得したり、条件に応じて複数データ取得できたりします。

これらの違いを一つずつ実行しながら見ていきたいと思います。

条件に合致したデータを1件取得する

これらのメソッドは条件に合致したデータを1件取得するメソッドです。

• first
• last
• find
• find_by
• find_by!
• take

idが一番最初のデータを取得する（first）

> NewUser.first
  NewUser Load (0.4ms)  SELECT  "new_users".* FROM "new_users" ORDER BY "new_users"."id" ASC LIMIT $1  [["LIMIT", 1]]
=> #<NewUser:0x00007f8f237f3758 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, token: nil>

idが一番最後のデータを取得する（last）

> NewUser.last
  NewUser Load (0.4ms)  SELECT  "new_users".* FROM "new_users" ORDER BY "new_users"."id" DESC LIMIT $1  [["LIMIT", 1]]
=> #<NewUser:0x00007f8f08033420 id: 10, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Thu, 10 Oct 2019 19:26:05 JST +09:00, updated_at: Thu, 10 Oct 2019 19:26:05 JST +09:00, token: "PXFWyWaE5KfYUtjMxE8DNSDF">

指定したidのデータを取得する（find）

> NewUser.find 1
  NewUser Load (0.5ms)  SELECT  "new_users".* FROM "new_users" WHERE "new_users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
=> #<NewUser:0x00007f8f2478bd28 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, token: nil>

条件に合致したデータを取得する（find_by）

# 仮に複数件マッチした場合でも1件のみ取得します
> NewUser.find_by(name: 'opiyo')
  NewUser Load (0.4ms)  SELECT  "new_users".* FROM "new_users" WHERE "new_users"."name" = $1 LIMIT $2  [["name", "opiyo"], ["LIMIT", 1]]
=> #<NewUser:0x00007f8f09483498 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, token: nil>
> NewUser.find_by(name: 'opiyoopiyoopiyo')
  NewUser Load (0.4ms)  SELECT  "new_users".* FROM "new_users" WHERE "new_users"."name" = $1 LIMIT $2  [["name", "opiyoopiyoopiyo"], ["LIMIT", 1]]
=> nil

find_byと同じだが取得できない場合は例外が発生（find_by!）

> NewUser.find_by!(name: 'opiyoopiyoopiyo')
  NewUser Load (0.5ms)  SELECT  "new_users".* FROM "new_users" WHERE "new_users"."name" = $1 LIMIT $2  [["name", "opiyoopiyoopiyo"], ["LIMIT", 1]]
ActiveRecord::RecordNotFound: Couldn't find NewUser
from /Users/tnakano/rails/iemiru/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.3/lib/active_record/core.rb:217:in `find_by!'

条件に合致したデータを複数取得する（where）

whereを使うと条件に応じたデータを複数件取得することができます。

> NewUser.where(name: 'opiyo')
  NewUser Load (0.5ms)  SELECT "new_users".* FROM "new_users" WHERE "new_users"."name" = $1  [["name", "opiyo"]]
=> [#<NewUser:0x00007f8f26d165e8 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f26d16458 id: 4, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, updated_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f26d162c8 id: 7, name: "opiyo", age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:29:20 JST +09:00, updated_at: Mon, 07 Oct 2019 22:32:12 JST +09:00, token: nil>

> NewUser.where(name: 'opiyo', age: 2)
  NewUser Load (2.0ms)  SELECT "new_users".* FROM "new_users" WHERE "new_users"."name" = $1 AND "new_users"."age" = $2  [["name", "opiyo"], ["age", 2]]
=> [#<NewUser:0x00007f8f093894e8 id: 7, name: "opiyo", age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:29:20 JST +09:00, updated_at: Mon, 07 Oct 2019 22:32:12 JST +09:00, token: nil>]

また、条件関係なく全データ取得したい場合は、allを使います。

> NewUser.all
  NewUser Load (0.5ms)  SELECT "new_users".* FROM "new_users"
=> [#<NewUser:0x00007f8f26f57fa0 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f26f57e10 id: 2, name: nil, age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:41:35 JST +09:00, updated_at: Mon, 07 Oct 2019 21:41:35 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f26f57c80 id: 3, name: nil, age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:42:31 JST +09:00, updated_at: Mon, 07 Oct 2019 21:42:31 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f26f57af0 id: 4, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, updated_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f26f57960 id: 5, name: nil, age: 1, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:22:18 JST +09:00, updated_at: Mon, 07 Oct 2019 22:22:18 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f26f577d0 id: 6, name: nil, age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:23:25 JST +09:00, updated_at: Mon, 07 Oct 2019 22:23:25 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f26f57640 id: 7, name: "opiyo", age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:29:20 JST +09:00, updated_at: Mon, 07 Oct 2019 22:32:12 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f26f574b0 id: 8, name: "not_token", age: nil, gender: nil, birthday: nil, email: nil, created_at: Thu, 10 Oct 2019 19:25:07 JST +09:00, updated_at: Thu, 10 Oct 2019 19:25:07 JST +09:00, token: nil>]

whereで取得した以外のデータを取得する（where.not）

> NewUser.where.not(name: 'opiyo')
  NewUser Load (0.4ms)  SELECT "new_users".* FROM "new_users" WHERE "new_users"."name" != $1  [["name", "opiyo"]]
=> [#<NewUser:0x00007f8f08858268 id: 8, name: "not_token", age: nil, gender: nil, birthday: nil, email: nil, created_at: Thu, 10 Oct 2019 19:25:07 JST +09:00, updated_at: Thu, 10 Oct 2019 19:25:07 JST +09:00, token: nil>]

グルーピング（group）

# 人毎の年齢合計
> NewUser.group(:name).sum(:age)
   (2.5ms)  SELECT SUM("new_users"."age") AS sum_age, "new_users"."name" AS new_users_name FROM "new_users" GROUP BY "new_users"."name"
=> {nil=>3, "opiyo"=>2, "not_token"=>0}

テーブルの結合（joins）

> users = NewUser.select('new_users.name, posts.title').joins(:posts).where("posts.title = '1'")
  NewUser Load (0.6ms)  SELECT new_users.name, posts.title FROM "new_users" INNER JOIN "posts" ON "posts"."new_user_id" = "new_users"."id" WHERE (posts.title = '1')
=> [#<NewUser:0x00007f8f09b63e20 id: nil, name: "opiyo">]

> users.each do |user|
*   puts user.name  
*   puts user.title
* end  
opiyo
1

件数を取得する（count）

> NewUser.where(name: 'opiyo').count
   (0.5ms)  SELECT COUNT(*) FROM "new_users" WHERE "new_users"."name" = $1  [["name", "opiyo"]]
=> 3

重複を削除（distinct）

> NewUser.select(:name).distinct
  NewUser Load (0.4ms)  SELECT DISTINCT "new_users"."name" FROM "new_users"
=> [#<NewUser:0x00007f8f1e892ee8 id: nil, name: nil>, #<NewUser:0x00007f8f1e88a108 id: nil, name: "opiyo">, #<NewUser:0x00007f8f1e870c08 id: nil, name: "not_token">]

並び順の指定（order）

# 昇順
> NewUser.where(name: 'opiyo').order(:id)
  NewUser Load (0.5ms)  SELECT "new_users".* FROM "new_users" WHERE "new_users"."name" = $1 ORDER BY "new_users"."id" ASC  [["name", "opiyo"]]
=> [#<NewUser:0x00007f8f0844a6d0 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f0844a540 id: 4, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, updated_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f0844a3b0 id: 7, name: "opiyo", age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:29:20 JST +09:00, updated_at: Mon, 07 Oct 2019 22:32:12 JST +09:00, token: nil>]

# 降順
> NewUser.where(name: 'opiyo').order(id: :desc)
  NewUser Load (0.4ms)  SELECT "new_users".* FROM "new_users" WHERE "new_users"."name" = $1 ORDER BY "new_users"."id" DESC  [["name", "opiyo"]]
=> [#<NewUser:0x00007f8f092892a0 id: 7, name: "opiyo", age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:29:20 JST +09:00, updated_at: Mon, 07 Oct 2019 22:32:12 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f09289110 id: 4, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, updated_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f09288f80 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, token: nil>]

# 複数指定する場合は文字列で囲み、カンマ区切りで複数記述します
> NewUser.where(name: 'opiyo').order('id desc, created_at')
  NewUser Load (0.5ms)  SELECT "new_users".* FROM "new_users" WHERE "new_users"."name" = $1 ORDER BY id desc, created_at  [["name", "opiyo"]]
=> [#<NewUser:0x00007f8f09032290 id: 7, name: "opiyo", age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:29:20 JST +09:00, updated_at: Mon, 07 Oct 2019 22:32:12 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f09032100 id: 4, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, updated_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, token: nil>,
 #<NewUser:0x00007f8f09031f70 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, token: nil>]