railsでSQLをActiveRecordクエリインターフェースのメソッドを使って
実現する方法の基礎っぽいところです。まだ勉強したてなので間違っている
ところがあったら教えて下さい。
前提知識
- railsのhas_manyとbelogs_toが何か知ってること
- SQLで簡単なSELECT、WHERE、JOINあたりは知ってること
わかること
- railsでデータベースからデータ取得するときに使う、select、where、or、mergeの基本的な使い方
使用するテーブルとデータと関係
usersテーブルとcommentsテーブルは、1対多の関係
やりたいこと1(答えは確認事項7)
下記のデータを取得する
- 元旦にコメントした人の名前(usersテーブルのname列)
- その人の本文(commentsテーブルのbody列)
ここで学びたいのは、2つのテーブルを結合して、
結合した側のテーブル(comments)の条件で取得する方法
やりたいこと2(答えは確認事項8)
下記のデータを取得する
- usersテーブルのステータスが1、かつ、元旦にコメントした人の名前と本文
ここで学びたいのは、2つのテーブルを結合して、
結合された側と、した側の両方のAND条件で取得する方法
やりたいこと3(答えは確認事項9)
- usersテーブルのステータスが1、または、元旦にコメントした人の名前と本文
ここで学びたいのは、2つのテーブルを結合して、
結合された側と、した側の両方のOR条件で取得する方法
確認環境
冒頭のテーブルとデータを用意し、railsコンソールを使って確認していきます。
以下、冒頭のやりたいことへ向かって色々確認しながら進めます。
確認事項1 ALLメソッド
User.allは内部でSQLを実行しています。
User.all
=> SELECT "users".* FROM "users"
取得結果
allだけでなく、以降出てくるwhereやjoinsなども内部でSQLを実行しています。
確認事項2 取得したデータの内容確認方法
# 方法1
User.all.first.name # 1行目を取り出すならfirst、2行目ならsecond・・・
=> "user1"
# 方法2
User.all.pluck(:name)
=> ["user1", "user2"]
以降、お好みの方法で取得した結果の中身を確認してみて下さい。
確認事項3 whereメソッド
まず、commentsテーブルから元旦の行を取得してみます。
Comment.where(created_at: Time.zone.parse("2019/01/01 00:00:00"))
# 発行されるSQL
=> SELECT "comments".* FROM "comments" WHERE "comments"."created_at" = ? [["created_at", "2019-01-01 00:00:00"]]
取得結果
単一のテーブルからの条件付き取得なので難しくないと思います。
確認事項4 orメソッド
commentsテーブルから元旦と2日の行を取得してみます。
allメソッドではない、2つの方法でやります。
# 条件を配列で渡す方法
Comment.where(created_at: [Time.zone.parse("2019/01/01 00:00:00"),Time.zone.parse("2019/01/02 00:00:00")])
# 発行されるSQL
SELECT "comments".* FROM "comments" WHERE "comments"."created_at" IN ('2019-01-01 00:00:00', '2019-01-02 00:00:00')
# orメソッドを使う方法
Comment.where(created_at: Time.zone.parse("2019/01/01 00:00:00")).or(Comment.where(created_at: Time.zone.parse("2019/01/02 00:00:00")))
# 発行されるSQL
SELECT "comments".* FROM "comments" WHERE ("comments"."created_at" = ? OR "comments"."created_at" = ?) [["created_at", "2019-01-01 00:00:00"], ["created_at", "2019-01-02 00:00:00"]]
取得結果
方法1も2もComent.allをするのと同じ結果になりますが、発行されるSQLが異なります。
ここでは、後で使うorメソッドの使い方を頭に入れておいて下さい。
なお、方法2は次のように見やすく書くと、わかりやすいですかね。
# 元旦の条件
Comment.where(created_at: Time.zone.parse("2019/01/01 00:00:00"))
.or(
# 2日の条件
Comment.where(created_at: Time.zone.parse("2019/01/02 00:00:00"))
)
確認事項5 joinsメソッド
usersテーブルとcommentsテーブルをINNER JOINします。
OUTER JOINしたい場合は、left_outer_joinsもあります。
User.joins(:comments)
# 発行されるSQL
=> SELECT "users".* FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id"
取得結果
発行SQLの「SELECT "usres.*"」からわかるように、joinsで結合しても、
commentsテーブルの情報は、この時点では保持されていません。
よって、例えば下記のように1行目のcommentsテーブルのbody列を
取得しようとしてもエラーになります。
User.joins(:comments).first.body
# => NoMethodError: undefined method `body' for #<User:0x0000556c1c5513f0>
一方、usersテーブルの列は取得できます。
User.joins(:comments).first.name
# => "user1"
commnetsテーブルからの情報も保持するには次のselectで明示します。
確認事項6 selectメソッド
usersテーブルとcommentsテーブルをINNER JOINし、必要な項目のみ取得します。
User.joins(:comments).select("users.name, comments.body")
# 発行されるSQL
=> SELECT users.name, comments.body FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id"
取得結果
確認事項7 mergeメソッド
確認事項6の結果から、さらにcommentsテーブル(結合する側)のcreated_at列が
元旦の行だけ取得します。これがやりたいこと その1の答えになります。
最初にやりたくなりそうな間違ったコードを示します。
# 間違ったコード
User.joins(:comments).select("users.name, comments.body").where(created_at: Time.zone.parse("2019/01/01 00:00:00"))
# => <ActiveRecord::Relation []> # => 結果は空
# 発行されるSQL
=> SELECT users.name, comments.body FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id" WHERE "users"."created_at" = '2019-01-01 00:00:00'
確認事項3の
Comment.where(created_at: Time.zone.parse("2019/01/01 00:00:00"))
と同じ要領でやると結果は空になります。
発行SQL文を確認すると、WHERE "users"."created_at" = '2019-01-01 00:00:00'
となっており、commentsテーブルのcreated_atが条件になっていないためです。
これは、User.joins(:comments)というコードが、Userをベースに読み込んでいること
を示しています。
正しくは下記です。mergeメソッドを使わない方法1と使う方法2があります。
# 方法1(whereの引数でcommentsテーブルに対する条件であることを明示している)
User.joins(:comments).select("users.name, comments.body").where(comments: {created_at: Time.zone.parse("2019/01/01 0:00:00")})
# 発行SQL
=> SELECT users.name, comments.body FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id" WHERE "comments"."created_at" = ? [["created_at", "2019-01-01 00:00:00"]]
# 方法2(mergeは結合する側のテーブルの条件を指定できる）
User.joins(:comments).select("users.name, comments.body").merge(Comment.where(created_at: Time.zone.parse("2019/01/01 0:00:00")))
# 発行SQL
=> SELECT users.name, comments.body FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id" WHERE "comments"."created_at" = ? [["created_at", "2019-01-01 00:00:00"]]
取得結果
上記の方法1と方法2は発行SQLを見てもわかるとおり、同等です。
どちらも、結合した側のテーブルの条件を指定する方法です。
個人的には方法2のほうが、
mergeを使っている => 結合する側のテーブルの条件を指定している
とぱっと見わかっていいかなと思っています。
確認事項8 whereのAND版
確認事項6の結果から、さらにusersテーブルのステータスが1、かつ
commentsテーブルのcreated_at列が元旦の行だけ取得します。
これがやりたいこと その2の答えになります。
# 方法1
User.joins(:comments).select("users.name, comments.body").where(status: 1, comments: { created_at: Time.zone.parse("2019-01-01 00:00:00") })
# 発行されるSQL
=> SELECT users.name, comments.body FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id" WHERE "users"."status" = ? AND "comments"."created_at" = ? [["status", 1], ["created_at", "2019-01-01 00:00:00"]]
# 方法2(mergeが出てきたら結合する方のテーブルの条件！）
User.joins(:comments).select("users.name, comments.body").where(status: 1).merge(Comment.where(created_at: Time.zone.parse("2019-01-01 00:00:00")))
# 発行されるSQL
=> SELECT users.name, comments.body FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id" WHERE "users"."status" = ? AND "comments"."created_at" = ? [["status", 1], ["created_at", "2019-01-01 00:00:00"]]
取得結果
こちらも確認事項7と同様、方法1も方法2も同等です。
確認事項9 whereのOR版
確認事項6の結果から、さらにusersテーブルのステータスが1、または
commentsテーブルのcreated_at列が元旦の行だけ取得します。
これがやりたいこと その3の答えになります。
User.joins(:comments).select("users.name, comments.body").where(status: 1).or(User.joins(:comments).select("users.name, comments.body").merge(Comment.where(created_at: Time.zone.parse("2019/01/01 0:00:00"))))
# 発行されるSQL
=> SELECT users.name, comments.body FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id" WHERE ("users"."status" = ? OR "comments"."created_at" = ?) [["status", 1], ["created_at", "2019-01-01 00:00:00"]]
取得結果
コードを1行で無理に書くとわかりにくいですね。見やすくしてみます。
# usersテーブルのステータスが1
User.joins(:comments).select("users.name, comments.body").where(status: 1)
.or(
# commentsテーブルのcreated_atが元旦(mergeが出てきたら結合する方のテーブルの条件！）
User.joins(:comments).select("users.name, comments.body").merge(Comment.where(created_at: Time.zone.parse("2019/01/01 0:00:00")))
)
実際にプログラムを書く時は下記のようにするとわかりやすいですかね。
time = Time.zone.parse("2019/01/01 0:00:00")
# 条件1 usersテーブルのステータスが1
r1 = User.joins(:comments).select("users.name, comments.body").where(status: 1)
# 条件2 commentsテーブルのcreated_atが元旦(mergeが出てきたら結合する方のテーブルの条件！）
r2 = User.joins(:comments).select("users.name, comments.body").merge(Comment.where(created_at: time))
# 条件1 or 条件2
r3 = r1.or(r2)
# 内容確認
r3.pluck(:name,:body)
=> [["user1", "あけまして"], ["user1", "今日から"], ["user2", "おめでとう"]]