TL;DR
includes文ない時
Book.joins(
<<~SQL
LEFT OUTER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id` \
AND `reviews`.`approved` IS NOT NULL
SQL
).where.not(bookmarks: { id: nil }).order("`reviews`.`created_at` DESC").distinct
=> Book Load (1.5ms) SELECT DISTINCT `books`.* FROM `books` LEFT OUTER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id` AND `reviews`.`approved` IS NOT NULL WHERE `bookmarks`.`id` IS NOT NULL ORDER BY `bookmarks`.`created_at` DESC
includes文ある時
こうすれば、reviewsを何らかの処理をしたい時、２度ロードしなくて良いです。
Book.includes(:reviews)
.joins("LEFT OUTER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id` AND `reviews`.`approved` IS NOT NULL")
.where.not(bookmarks: { id: nil }).order( "reviews`.`created_at` DESC").distinct
=> Book Load (1.5ms) SELECT DISTINCT `books`.* FROM `books` LEFT OUTER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id` AND `reviews`.`approved` IS NOT NULL WHERE `bookmarks`.`id` IS NOT NULL ORDER BY `bookmarks`.`created_at` DESC
Review Load (0.3ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`book_id` IN (5, 9)
詳細
解説
Bookというモデルがあり、それにReviewが投稿されていて、Reviewには承認Flagapprovedがあります。Bookを抽出したいが、承認されたReviewの投稿日created_at順で並びたいです
Models
book.rb
class Book < ApplicationRecord
has_many :reviews
end
review.rb
class Review < ApplicationRecord
belongs_to :book
# `approved`というColumnで承認Flagを保持
end
Methods to SQL
INNER JOIN
Book.joins(:reviews)
=> Book Load (1.0ms) SELECT `books`.* FROM `books` INNER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id`
単純のLEFT OUTER JOIN
Book.left_joins(:reviews)
=> Book Load (1.0ms) SELECT `books`.* FROM `books` LEFT OUTER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id`
LEFT OUTER JOIN ??? AND ???
Book.joins("LEFT OUTER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id` AND `reviews`.`approved` IS NOT NULL")
=> Book Load (1.5ms) SELECT `books`.* FROM `books` LEFT OUTER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id` AND `reviews`.`approved` IS NOT NULL
上記仕様の実装例
Book.joins(
<<~SQL
LEFT OUTER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id` \
AND `reviews`.`approved` IS NOT NULL
SQL
).where.not(bookmarks: { id: nil }).order('`reviews`.`created_at` DESC').distinct
=> Book Load (1.5ms) SELECT DISTINCT `books`.* FROM `books` LEFT OUTER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id` AND `reviews`.`approved` IS NOT NULL WHERE `bookmarks`.`id` IS NOT NULL ORDER BY `bookmarks`.`created_at` DESC