はじめに
多くのRails初学者はSQL文をあまり意識せずActive Recordを使用してしまっているかと思います。しかしデバッグ作業や複雑な絞り込みには生のSQL文を利用する機会はそれなりに多く、また実行されているSQLを理解していないままだと気づかぬうちに非効率なコードを書いてしまっている可能性があります。
そこで本記事ではActiveReocordメソッドで実行されているSQL文をまとめてみました。
メソッドとSQL文
find
User.find(1)
# SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
User.find([2,3,4])
# SELECT `users`.* FROM `users` WHERE `users`.`id` IN (2, 3, 4)
findメソッドでは「引数をidに持つレコードを取り出す」というSQLが実行されています。
指定したidが単数の場合はSQL文にLIMIT 1 が付加されますが、配列で複数指定した場合は付加されません。
find_by
User.find_by(id:3)
# SELECT `users`.* FROM `users` WHERE `users`.`id` = 3 LIMIT 1
User.find_by(id:[3,4,6])
# SELECT `users`.* FROM `users` WHERE `users`.`id` IN (3, 4, 6) LIMIT 1
find_byメソッドではカラム名を指定してレコードを取り出します。条件ハッシュに配列を渡すことが可能ですが LIMIT 1が付加されるので一つのレコードしか呼び出されません。
where
User.where(email:"test@test.com")
# SELECT `users`.* FROM `users` WHERE `users`.`email` = 'test@test.com'
Client.where(created_at: (Time.now.midnight - 1.day)..Time.now.midnight)
# SELECT * FROM clients
WHERE (clients.created_at BETWEEN '2008-12-21 00:00:00' AND '2008-12-22 00:00:00')
whereメソッドは文字通りSQLのWHERE句の部分に相当します。
order
User.order(:username)
# SELECT `users`.* FROM `users` ORDER BY `users`.`username
orderメソッドではSQLのORDER BY句が実行され、指定された属性の昇順でレコードが並び替えられます。
first,last
User.first
# SELECT `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1
User.last
# SELECT `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1
firstメソッドではidで昇順に並べたレコードの最初の一つを取り出すSQLが実行されます。lastは降順の一番最初となります。
save,update
user = User.find(2)
user.username = "test"
user.save
# UPDATE `users`
SET `users`.`updated_at` = '2021-05-09 07:08:50', `users`.`username` = 'test'
WHERE `users`.`id` = 2
saveメソッドではインスタンス変数が変更された場合に、レコードを更新するSQLのUPDATE句が実行されます。
select, pluck
User.select(:username)
# SELECT `users`.`username` FROM `users`
selectメソッドではテーブから対象となるカラムのみを取り出します。pluckも同様のSQL文が発行されますが指定したカラムの値の配列を返すという点が異なります。
User.select("date(created_at) as ordered_date")
# SELECT date(created_at) as ordered_date FROM `users`
また、文字列でasを使った条件を渡すことでSQLの関数DATEの引数に渡したcreate_atをordered_dateという別の名前を付けて取得することができます。
destroy
User.find(3).destroy
# DELETE FROM `profiles` WHERE `profiles`.`id` = 3
destoryメソッドではSQLのDELETE文が実行されます。
limit, offset
User.limit(2).to_sql
# "SELECT `users`.* FROM `users` LIMIT 2"
limitメソッドでは指定された件数のみレコードが取り出されます。
User.offset(2).limit(3).to_sql
# "SELECT `users`.* FROM `users` LIMIT 3 OFFSET 2"
offsetメソッドではレコードを返す前にスキップするレコード数を指定することが可能です。
上のコードでは、2つのレコードがスキップされ、id=3から3つのレコードが取得されます。
また、これらを組み合わせることでページネーション機能を実装することが可能です。
例えば、ページネーションに使用されるgemであるkaminari を使用した場合
User.page(3).per(5)
# SELECT `users`.* FROM `users` LIMIT 5 OFFSET 10
このようなSQLが発行されます。
exsits?
User.exists?(id:1)
# SELECT 1 AS one FROM `users` WHERE `users`.`id` = 1 LIMIT 1
exists?メソッドは、渡された条件ハッシュを満たすレコードが存在するかどうかを確認するメソッドです。
WHERE句をみると。対象となるテーブルから条件を満たすレコードを一つ絞り込んでいます。そして1 AS oneから分かる通り、1をoneという別の名前を付けて取り出しています。
group, count ,having
User.select("date(updated_at) as date, sum(id)").group(:date)
# SELECT date(updated_at) as date, sum(id)
FROM `users`
GROUP BY `date`
Userの更新日のコレクションごとに更新日、コレクションのidの合計を検索する場合は上記のようなSQL文を発行します。
User.group("date(updated_at)").count
# SELECT COUNT(*) AS count_all,
date(updated_at) AS date_updated_at
FROM `users` GROUP BY date(updated_at)
上記ではgroupメソッドとcountメソッドを組み合わせることで、更新日のコレクションごとのレコード数,更新日を取得するSQL文を発行します。
User.select("date(updated_at) as date, sum(id)").group(:date).having("sum(id)>10")
# SELECT date(updated_at) as date, sum(id)
FROM `users`
GROUP BY `date` HAVING (sum(id)>10)
havingメソッドを使うことで集約した結果に対して条件指定を行うHAVING句が付加されます。
joins/left_outer_joins
以下のような二つのテーブルがあるとします。
Owner table
| id | ownername |
|---|---|
| 1 | ichijo |
| 2 | jiro |
| 3 | saburo |
Dog table
| id | owner_id | dogname |
|---|---|---|
| 1 | 1 | pochi |
| 2 | 2 | maru |
| 3 | 2 | hachi |
| 4 | 4 | ken |
SELECT * FROM owners INNER JOIN dogs ON owners.id = dogs.owner_id;
このようにINNER JOINが実行された場合は、結合キーとなっているそれぞれのテーブルのキー値のうち、両方のテーブルに存在する値を持つレコードのみが結合されます。
上記のSQL文ではそれぞれのキーの共通している値は1,2のみであるので、Dogテーブルからはowner_idが1,2のテーブル、Ownerテーブルからはidが1,2のテーブルが結合され、以下のようなテーブルが出来上がります。
| id | ownername | id | owner_id | dogname |
|---|---|---|---|---|
| 1 | ichiro | 1 | 1 | pochi |
| 2 | jiro | 2 | 2 | maru |
| 2 | jiro | 3 | 2 | hachi |
SELECT * FROM owners LEFT OUTER JOIN dogs ON owners.id = dogs.owner_id;
一方でOUTER JOINの場合はINNER JOINで結合されるレコードに加え、基準となるテーブル(LEFT OUTER JOINの場合は左側のテーブル)のキー値を持つテーブルが結合されます。
つまり、今回の場合は基準となるOwnerテーブルのデータはレコードは全て取得され、それに対応するDogsテーブルのレコードが結合されます。
| id | ownername | id | owner_id | dogname |
|---|---|---|---|---|
| 1 | ichiro | 1 | 1 | pochi |
| 2 | jiro | 2 | 2 | maru |
| 2 | jiro | 3 | 2 | hachi |
| 3 | saburo | null | null | null |
このように、基準となるテーブルのレコードは全件取得され、対応するレコードがない部分はnullとなります。
それぞれの結合に対応するActive Recordメソッドは以下のようになります。
Product.joins(:reviews)
# SELECT `products`.* FROM `products`
INNER JOIN `reviews`
ON `reviews`.`product_id` = `products`.`id`
ProductモデルがReviewsモデルに対してhas_manyの関係を持っている場合、joinsメソッドでは内部結合を行うINNER JOIN句を呼び出しています。
Product.left_outer_joins(:reviews)
# SELECT `products`.* FROM `products`
LEFT OUTER JOIN `reviews`
ON `reviews`.`product_id` = `products`.`id`
left_outer_joinメソッドでは左のテーブルを基準としたOUTER JOIN句が呼ばれます。
なお、joins, left_outer_joinsメソッドを使用した場合に発行されるSQL文から、SELECTで結合先のテーブルデータを取得していないことがわかります。selectメソッドを使用して以下のようにすると結合した全てのカラムデータを取得することができます。
Product.select("*").joins(:reviews)
# SELECT * FROM `products`
INNER JOIN `reviews` ON
`reviews`.`product_id` = `products`.`id`
Product.select("*").left_outer_joins(:reviews)
# SELECT * FROM `products`
LEFT OUTER JOIN `reviews`
ON `reviews`.`product_id` = `products`.`id`
参考
最近の未経験エンジニアはSQLの理解が浅すぎる！！【サーバーサイド言語Ruby/PHPを勉強している人向け】
Active Record クエリインターフェイス
SQL素人でも分かるテーブル結合(inner joinとouter join)
SQL ゼロからはじめるデータベース操作