More than 5 years have passed since last update.

eager_load + find_by でクエリが2回発行される件の対策

Last updated at 2019-11-16Posted at 2019-11-15

結論

よくある1:NのRelationがある時

class User < ApplicationRecord
  has_many :comments
end

class Comment < ApplicationRecord
  belongs_to :user
end

User.eager_load(:comments).find_by(id: 1)

とかすると

User.find_by(id: 1) のSQL
User.eager_load(:comments) のSQL
の2回SQLが発行される

User.eager_load(:comments).where(id: 1).load.first

とかするとクエリが1回になる。
(.load は .to_a とかでもOK)

ruby 2.5.1
rails 5.0.6/6.0.1(下記ログは6.0.1のものです)

で確認済み。

詳細

普通にやってみたりしたけど、どうしてもただのfindっぽいクエリと、
まとめて情報をとってくるクエリの2回が発行された

User.eager_load(:comments).find(1)
# SELECT DISTINCT `users`.`id` FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 LIMIT 1
# SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`created_at` AS t0_r2, `users`.`updated_at` AS t0_r3, `comments`.`id` AS t1_r0, `comments`.`user_id` AS t1_r1, `comments`.`created_at` AS t1_r2, `comments`.`updated_at` AS t1_r3 FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 AND `users`.`id` = 1

User.eager_load(:comments).find_by(id: 1)
# SELECT DISTINCT `users`.`id` FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 LIMIT 1
# SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`created_at` AS t0_r2, `users`.`updated_at` AS t0_r3, `comments`.`id` AS t1_r0, `comments`.`user_id` AS t1_r1, `comments`.`created_at` AS t1_r2, `comments`.`updated_at` AS t1_r3 FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 AND `users`.`id` = 1

User.eager_load(:comments).where(id: 1).first
# SELECT DISTINCT `users`.`id` AS alias_0, `users`.`id` FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 ORDER BY `users`.`id` ASC LIMIT 1
# SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`created_at` AS t0_r2, `users`.`updated_at` AS t0_r3, `comments`.`id` AS t1_r0, `comments`.`user_id` AS t1_r1, `comments`.`created_at` AS t1_r2, `comments`.`updated_at` AS t1_r3 FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 AND `users`.`id` = 1 ORDER BY `users`.`id` ASC

WHERE `users`.`id` = 1 AND `users`.`id` = 1 とかめっちゃ冗長そうなクエリがある。
２回目のクエリだけでほしい情報は取れるはず
なんとかして1回のクエリにしたい

User.eager_load(:comments).where(id: 1) の場合は、クエリが1回なので、
これの時点でクエリを無理やり発火させてから、firstを取ればいいのでは？

User.eager_load(:comments).where(id: 1).load.first
# SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`created_at` AS t0_r2, `users`.`updated_at` AS t0_r3, `comments`.`id` AS t1_r0, `comments`.`user_id` AS t1_r1, `comments`.`created_at` AS t1_r2, `comments`.`updated_at` AS t1_r3 FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1

ｷﾀ━━━━(ﾟ∀ﾟ)━━━━!!

おわり。

クエリ整形ver

User.eager_load(:comments).find(1)
# SQL (4.0ms)  SELECT DISTINCT `users`.`id` FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 LIMIT 1
# SQL (2.8ms)  SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`created_at` AS t0_r2, `users`.`updated_at` AS t0_r3, `comments`.`id` AS t1_r0, `comments`.`user_id` AS t1_r1, `comments`.`created_at` AS t1_r2, `comments`.`updated_at` AS t1_r3 FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 AND `users`.`id` = 1
# -- pretty format.
# SELECT
#     DISTINCT `users`.`id`
# FROM
#     `users`
#     LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id`
# WHERE
#     `users`.`id` = 1
# LIMIT
#     1;
#
# SELECT
#     `users`.`id` AS t0_r0,
#     `users`.`name` AS t0_r1,
#     `users`.`created_at` AS t0_r2,
#     `users`.`updated_at` AS t0_r3,
#     `comments`.`id` AS t1_r0,
#     `comments`.`user_id` AS t1_r1,
#     `comments`.`created_at` AS t1_r2,
#     `comments`.`updated_at` AS t1_r3
# FROM
#     `users`
#     LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id`
# WHERE
#     `users`.`id` = 1
#     AND `users`.`id` = 1;

# => #<User id: 1, name: "hogehoge", created_at: "2019-11-15 04:54:42", updated_at: "2019-11-15 04:54:42">

User.eager_load(:comments).find_by(id: 1)
#  SQL (1.0ms)  SELECT DISTINCT `users`.`id` FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 LIMIT 1
#  SQL (2.7ms)  SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`created_at` AS t0_r2, `users`.`updated_at` AS t0_r3, `comments`.`id` AS t1_r0, `comments`.`user_id` AS t1_r1, `comments`.`created_at` AS t1_r2, `comments`.`updated_at` AS t1_r3 FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 AND `users`.`id` = 1
# SELECT
#     DISTINCT `users`.`id`
# FROM
#     `users`
#     LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id`
# WHERE
#     `users`.`id` = 1
# LIMIT
#     1;
#
# SELECT
#     `users`.`id` AS t0_r0,
#     `users`.`name` AS t0_r1,
#     `users`.`created_at` AS t0_r2,
#     `users`.`updated_at` AS t0_r3,
#     `comments`.`id` AS t1_r0,
#     `comments`.`user_id` AS t1_r1,
#     `comments`.`created_at` AS t1_r2,
#     `comments`.`updated_at` AS t1_r3
# FROM
#     `users`
#     LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id`
# WHERE
#     `users`.`id` = 1
#     AND `users`.`id` = 1;
#
# => #<User id: 1, name: "hogehoge", created_at: "2019-11-15 04:54:42", updated_at: "2019-11-15 04:54:42">

User.eager_load(:comments).where(id: 1).first
#  SQL (0.6ms)  SELECT DISTINCT `users`.`id` AS alias_0, `users`.`id` FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 ORDER BY `users`.`id` ASC LIMIT 1
#  SQL (0.6ms)  SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`created_at` AS t0_r2, `users`.`updated_at` AS t0_r3, `comments`.`id` AS t1_r0, `comments`.`user_id` AS t1_r1, `comments`.`created_at` AS t1_r2, `comments`.`updated_at` AS t1_r3 FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 AND `users`.`id` = 1 ORDER BY `users`.`id` ASC
#
# SELECT
#     DISTINCT `users`.`id` AS alias_0,
#     `users`.`id`
# FROM
#     `users`
#     LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id`
# WHERE
#     `users`.`id` = 1
# ORDER BY
#     `users`.`id` ASC
# LIMIT
#     1;
#
# SELECT
#     `users`.`id` AS t0_r0,
#     `users`.`name` AS t0_r1,
#     `users`.`created_at` AS t0_r2,
#     `users`.`updated_at` AS t0_r3,
#     `comments`.`id` AS t1_r0,
#     `comments`.`user_id` AS t1_r1,
#     `comments`.`created_at` AS t1_r2,
#     `comments`.`updated_at` AS t1_r3
# FROM
#     `users`
#     LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id`
# WHERE
#     `users`.`id` = 1
#     AND `users`.`id` = 1
# ORDER BY
#     `users`.`id` ASC;
# => #<User id: 1, name: "hogehoge", created_at: "2019-11-15 04:54:42", updated_at: "2019-11-15 04:54:42">

User.eager_load(:comments).where(id: 1).load.first
#  SQL (1.0ms)  SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`created_at` AS t0_r2, `users`.`updated_at` AS t0_r3, `comments`.`id` AS t1_r0, `comments`.`user_id` AS t1_r1, `comments`.`created_at` AS t1_r2, `comments`.`updated_at` AS t1_r3 FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `users`.`id` = 1
# SELECT
#     `users`.`id` AS t0_r0,
#     `users`.`name` AS t0_r1,
#     `users`.`created_at` AS t0_r2,
#     `users`.`updated_at` AS t0_r3,
#     `comments`.`id` AS t1_r0,
#     `comments`.`user_id` AS t1_r1,
#     `comments`.`created_at` AS t1_r2,
#     `comments`.`updated_at` AS t1_r3
# FROM
#     `users`
#     LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id`
# WHERE
#     `users`.`id` = 1;
# => #<User id: 1, name: "hogehoge", created_at: "2019-11-15 04:54:42", updated_at: "2019-11-15 04:54:42">

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up