ActiveRecordのPreloadメソッドを使用した際の発行クエリ
(User 1:N Post)
User.preload(:posts)
SELECT `users`.* FROM `users`
SELECT `posts`.* FROM `posts` WHERE `posts`.`user_id` IN [ usersのidすべて ]
(Company.preload 1:N User, Company.preload 1:N Department)
Company.preload(:users, :departments)
SELECT `companies`.* FROM `companies`
SELECT `users`.* FROM `users` WHERE `users`.`company_id` IN ※※※
SELECT `departments`.* FROM `departments` WHERE `departments`.`company_id` IN ※※※
(Department 1:N DepartmentUserMaps, User 1:N DepartmentUserMaps)
Department.preload(:users)
SELECT `departments`.* FROM `departments`
SELECT `department_user_maps`.* FROM `department_user_maps` WHERE `department_user_maps`.`department_id` IN [ departmentsのidすべて ]
SELECT `users`.* FROM `users` WHERE `users`.`id` IN [ department_user_mapsのuser_idの全て ]
(Post N:1 User)
Post.eager_load(:user)
SELECT
"posts"."id" AS t0_r0,
"posts"."name" AS t0_r1,
"posts"."user_id" AS t0_r2,
"posts"."created_at" AS t0_r3,
"posts"."updated_at" AS t0_r4,
"users"."id" AS t1_r0,
"users"."name" AS t1_r1,
"users"."created_at" AS t1_r2,
"users"."updated_at" AS t1_r3
FROM "posts" LEFT OUTER JOIN "users" ON "users"."id" = "posts"."user_id"
User.left_joins(:photos, :movies).preload(:posts, :photos, :movies).merge(Photo.where(id: 2).or(Movie.where(id: 3)))
SELECT
"users"."id" AS t1_r0,
"users"."name" AS t1_r1,
"users"."created_at" AS t1_r2,
"users"."updated_at" AS t1_r3,
"photos"."id" AS t0_r0,
"photos"."name" AS t0_r1,
"photos"."user_id" AS t0_r2,
"photos"."created_at" AS t0_r3,
"photos"."updated_at" AS t0_r4,
"movies"."id" AS t1_r0,
"movies"."name" AS t1_r1,
"movies"."user_id" AS t0_r2,
"movies"."created_at" AS t1_r2,
"movies"."updated_at" AS t1_r3
FROM “users”
LEFT OUTER JOIN "photos" ON "users"."id" = "photos"."user_id"
LEFT OUTER JOIN "movies" ON "users"."id" = "movies"."user_id"
WHERE "photos"."id" = 2
AND WHERE "movies"."id" = 3
SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = [ 上記で取得したusersのidの全て ]
SELECT "photos".* FROM "photos" WHERE "photos"."user_id" = [ 上記で取得したusersのidの全て ]
SELECT "movies".* FROM "movies" WHERE "movies"."user_id" = [ 上記で取得したusersのidの全て ]
User.left_joins(:photos, :movies).preload(:posts, :photos, :movies).merge(Photo.where(id: 2).or(Movie.where(id: 3)))
SELECT "users".*
FROM "users"
LEFT OUTER JOIN "photos" ON "photos"."user_id" = "users"."id"
LEFT OUTER JOIN "movies" ON "movies"."user_id" = "users"."id"
WHERE ("photos"."id" = 2 OR "movies"."id" = 3)
SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (2, 3)
SELECT "photos".* FROM "photos" WHERE "photos"."user_id" IN (2, 3)
SELECT "movies".* FROM "movies" WHERE "movies"."user_id" IN (2, 3)
WHERE users.id = 2 の条件が適用された場合、結合されるのは users テーブルに id = 2 のレコードが存在する posts のみになります。LEFT OUTER JOIN の「関連がない場合でもデータを返す」という特徴が意味をなさなくなり、実質的には INNER JOIN と同じ動作になるため、最初から INNER JOIN を使う方が 意図に沿った結果 になります。