Active Recordクエリーを理解する。
SQLをActiveRecordを使って表現するとどうなるのか。学習ログとして残していきたいと思う。
- INNER JOIN
- 一つのテーブルから一つのデータを取得する場合
- 二つのテーブルからデータを取り出したいデータをクエリーする場合
- LEFT OUTER JOIN
- COUNT
- DISTINCT
- GROUP BY
- 関連レコードがあるかに関わらずデータセットを取得する場合
- 複数のテーブルからのデータをフィルタして取得する場合
- 独自のSQLを使ってデータを取得したい場合
- 指定したカラムの値の配列を、対応するデータ型で返したい場合
- PLUCK
- MAP
- mapとpluckのコードを比較する
- map
- pluck
学べるSQLは以下の通り
- INNER JOIN
- LEFT OUTER JOIN
- COUNT
- DISTINCT
- GROUP BY
- PLUCK
- MAP
下記モデルのように、エンティティ同士が関連付されているていることを前提とする。
class Category < ApplicationRecord
has_many :articles
end
class Article < ApplicationRecord
belongs_to :category
has_many :comments
has_many :tags
end
class Comment < ApplicationRecord
belongs_to :article
has_one :guest
end
class Guest < ApplicationRecord
belongs_to :comment
end
class Tag < ApplicationRecord
belongs_to :article
end
INNER JOINとOUTER JOINを理解する上で、下記の図を頭に入れておきたい。
INNER JOIN: ③の部分
テーブルをくっつけて共通項を探す
OUTER JOIN: (①②③)全部
テーブルをくっつけて共通項と片方しか該当しないデータも探す
INNER JOIN
一つのテーブルから一つのデータを取得する場合
期待する動作
「記事 (article) のあるすべてのカテゴリーを含む、Categoryオブジェクトを1つ返す」
すでにCategory has many articlesで関連づけられているから下記のような記述になる。
#ActiveRecord
Category.joins(:articles)
#SQL
SELECT categories.* //*は全てという意味
FROM categories
INNER JOIN articles ON articles.category_id = categories.id
直訳:
articlesテーブルとcategoryテーブルの、articles.category_idとcategories.idが一致する場所から、categoriesテーブルのカラムに入っている値全てを取得する。
二つのテーブルからデータを取り出したいデータをクエリーする場合
期待する動作
「カテゴリーが1つあり、かつコメントが1つ以上ある、すべての記事を返す」
# ActiveRecord
Article.joins(:category, :comments)
#SQL文
SELECT articles.* //*は全てという意味
FROM articles
INNER JOIN categories ON articles.category_id = categories.id // article-category
INNER JOIN comments ON comments.article_id = articles.id //article-comment
直訳:
articlesテーブルとcategoryテーブル,commentテーブルの、
①articlesテーブルとcategoryテーブルのarticles.category_idとcategories.idが一致する場所
②articlesテーブルとcommentsテーブルのcommets.article_idとarticle.idが一致する場所
から、articlesテーブルのカラムに入っているデータ全てを取得する
LEFT OUTER JOIN
COUNT
DISTINCT
GROUP BY
関連レコードがあるかに関わらずデータセットを取得する場合
期待する動作
「著者 (authors) が記事 (posts) を持っているかどうかにかかわらず、すべての著者とその記事の数を返す」
// ActiveRecord
Author.left_outer_joins(:posts).distinct
.select('authors.*, COUNT(posts.*) AS posts_count')
.group('authors.id')
//発行されるSQL
SELECT DISTINCT authors.*,
COUNT(posts.*) AS posts_count
FROM "authors"
LEFT OUTER JOIN posts
ON posts.author_id = authors.id
GROUP BY authors.id
直訳:
authorsテーブルにおいて、
postテーブルの
post.author_idとauthor.idが一緒の部分をauthors.idで括った状態で連結させて
重複しないauthorsカラムとpostカラムそれぞれの値を全て取得する。
複数のテーブルからのデータをフィルタして取得する場合
期待する動作
「１週間前より過去につけられたコメントがある記事の、idとtitleとcomment内容を取得したい」
// ActiveRecord
Article
.select('articles.id, articles.title, comments.text')
.joins(:comments)
.where('comments.created_at > ?', 1.week.ago)
//発行されるSQL
SELECT articles.id, articles.name, comments.text
FROM articles
INNER JOIN comments
ON comments.article_id = article.id
WHERE comments.created_at > '2021-09-11'
直訳:
articleテーブルの
①comments.article_idとarticle.idが一致する
②comments.created_atが2021/09/11以前の
articles.id, articles.name, comments.textを取得したい。
独自のSQLを使ってデータを取得したい場合
ActiveRecordで用意されたメソッド「find_by_sql」を使って、独自のSQLでデータをクエリーすることができる。
Article.find_by_sql("SELECT * FROM articles
INNER JOIN comments ON articles.id = comments.article_id
ORDER BY articles.created_at desc")
# => [
# #<Article id: 1, name: "Human Being" >,
# #<Article id: 2, name: "Genome Editing" >,
# ...
# ]
指定したカラムの値の配列を、対応するデータ型で返したい場合
pluckを使用することで、いちいちmapやeachを使用せずに値をハッシュ形式で取り出すことができる。
Article.where(active: true).pluck(:id)
# SELECT id FROM articles WHERE active = 1
# => [1, 2, 3]
Person.distinct.pluck(:role)
# SELECT DISTINCT role FROM people
# => ['admin', 'member', 'guest']
Client.pluck(:id, :name)
# SELECT clients.id, clients.name FROM clients
# => [[1, 'David'], [2, 'Jeremy'], [3, 'Jose']]
PLUCK
MAP
mapとpluckのコードを比較する
map
各要素に対してブロックを評価した結果を全て含む配列を返します。
使用例
# すべて 3 倍にした配列を返す
p (1..3).map {|n| n * 3 } # => [3, 6, 9]
p (1..3).collect { "cat" } # => ["cat", "cat", "cat"]
pluck
指定したカラムのレコードの配列を取得
使用例
Person.pluck(:id, :name)
# SELECT people.id, people.name FROM people
# [[1, 'David'], [2, 'Jeremy'], [3, 'Jose']]
使用例を他のデータを使って比較してみると、pluckの方が記述量が少なくていいように見える。
Client.select(:id).map { |c| c.id }
# または
Client.select(:id).map(&:id)
# または
Client.select(:id, :name).map { |c| [c.id, c.name] }
上のコードは下のように置き換えることができる。
Client.pluck(:id)
# または
Client.pluck(:id, :name)
