[Ruby] [Rails] Avoid SELECT issued when creating a model in which belongs_to is defined!

4 minute read

Problem

Did you know that a SELECT statement is executed when you create a model with belongs_to defined in Rails?

For example, consider the following model.

def User <ApplicationRecord
  has_many :reviews
end

def Review <ApplicationRecord
  belongs_to :user
  belongs_to :book
end

def Book <ApplicationRecord
  has_many :reviews
end

If you create a review at this time, SQL will be issued as shown below.

# user and book with id=1 must exist
irb(main):001:0> Review.create!(user_id: 1, book_id: 1)
   (0.5ms) BEGIN
  User Load (0.5ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
  Book Load (0.5ms) SELECT `books`.* FROM `books` WHERE `books`.`id` = 1 LIMIT 1
  Review Create (0.6ms) INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 1, '2020-06-30 14:31:27.343637', '2020- 06-30 14:31:27.343637')
   (2.0ms) COMMIT

You have SELECTed users and books where belongs_to is defined.

The reason for SELECT is simple. By default belongs_to requires the existence of a related model. So what are the required checks? Just like before, SELECT is done just before creating to check the existence.

By the way, if the existence of the related model is optional, write belongs_to :user, optional: true. If you write like this, SELECT is not executed. See: Rails Guide https://railsguides.jp/association_basics.html#optional

So, the way to avoid SELECT in the title is to set optional: true! …Not! !!

Certainly SELECT will not be issued, but if the related model is required, setting optional: true is not appropriate.

Solution

So how to avoid SELECT without setting optional: true? All you have to do is pass the object to create.

irb(main):002:0> user = User.first
  User Load (0.8ms) SELECT `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1
=> #<User id: 1, name: "1234567890", created_at: "2019-12-12 05:43:52", updated_at: "2019-12-12 05:43:52">
irb(main):003:0> book = Book.first
  Book Load (0.6ms) SELECT `books`.* FROM `books` ORDER BY `books`.`id` ASC LIMIT 1
=> #<Book id: 1, title: "book1", created_at: "2020-06-15 14:21:15", updated_at: "2020-06-15 14:21:15">
irb(main):004:0> review = Review.create!(user: user, book: book)
   (0.4ms) BEGIN
  Review Create (0.6ms) INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 1, '2020-06-30 14:32:14.911478', '2020- 06-30 14:32:14.911478')
   (2.0ms) COMMIT

Since the existence can be confirmed without passing SELECT by passing the object, the SELECT statement is not issued. However, in the case of this example, the number of SQLs is the same because each model is separately SELECTed just before! It seems that you will receive a lot of SQL, but when you make multiple reviews of the same user, the number of SQL is completely different.

Below is the code that creates a review of book1-5 for user1 (some are omitted for readability). First, specify id and create.

irb(main):039:0> user = User.first
irb(main):040:0> books = Book.where(id: [1, 2, 3, 4, 5])
irb(main):042:0> books.each do |book|
irb(main):043:1* Review.create!(user_id: user.id, book_id: book.id)
irb(main):044:1> end
  Book Load (0.8ms) SELECT `books`.* FROM `books` WHERE `books`.`id` IN (1, 2, 3, 4, 5)
   (0.3ms) BEGIN
  User Load (0.5ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
  Book Load (0.4ms) SELECT `books`.* FROM `books` WHERE `books`.`id` = 1 LIMIT 1
  Review Create (0.8ms) INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 1, '2020-06-30 15:10:00.009569', '2020- 06-30 15:10:00.009569')
   (3.6ms) COMMIT
   (0.3ms) BEGIN
  User Load (0.4ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
  Book Load (0.4ms) SELECT `books`.* FROM `books` WHERE `books`.`id` = 2 LIMIT 1
  Review Create (0.4ms) INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 2, '2020-06-30 15:10:00.020722', '2020- 06-30 15:10:00.020722')
   (1.8ms) COMMIT
   (0.4ms) BEGIN
  User Load (0.6ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
  Book Load (0.4ms) SELECT `books`.* FROM `books` WHERE `books`.`id` = 3 LIMIT 1
  Review Create (0.4ms) INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 3, '2020-06-30 15:10:00.029827', '2020- 06-30 15:10:00.029827')
   (1.9ms) COMMIT
   (0.3ms) BEGIN
  User Load (0.4ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
  Book Load (0.3ms) SELECT `books`.* FROM `books` WHERE `books`.`id` = 4 LIMIT 1
  Review Create (0.3ms) INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 4, '2020-06-30 15:10:00.037725', '2020- 06-30 15:10:00.037725')
   (1.7ms) COMMIT
   (0.3ms) BEGIN
  User Load (0.5ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
  Book Load (0.3ms) SELECT `books`.* FROM `books` WHERE `books`.`id` = 5 LIMIT 1
  Review Create (0.4ms) INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 5, '2020-06-30 15:10:00.045389', '2020- 06-30 15:10:00.045389')
   (1.6ms) COMMIT

Then pass the object and create it.

irb(main):045:0> user = User.first
irb(main):046:0> books = Book.where(id: [1, 2, 3, 4, 5])
irb(main):047:0> books.each do |book|
irb(main):048:1* Review.create!(user: user, book: book)
irb(main):049:1> end
  Book Load (0.8ms) SELECT `books`.* FROM `books` WHERE `books`.`id` IN (1, 2, 3, 4, 5)
   (0.5ms) BEGINReview Create (0.5ms)  INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 1, '2020-06-30 15:12:05.610003', '2020-06-30 15:12:05.610003')
   (2.8ms)  COMMIT
   (0.4ms)  BEGIN
  Review Create (0.4ms)  INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 2, '2020-06-30 15:12:05.617125', '2020-06-30 15:12:05.617125')
   (1.7ms)  COMMIT
   (0.3ms)  BEGIN
  Review Create (0.5ms)  INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 3, '2020-06-30 15:12:05.622432', '2020-06-30 15:12:05.622432')
   (1.8ms)  COMMIT
   (0.4ms)  BEGIN
  Review Create (0.5ms)  INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 4, '2020-06-30 15:12:05.627957', '2020-06-30 15:12:05.627957')
   (2.0ms)  COMMIT
   (0.4ms)  BEGIN
  Review Create (0.6ms)  INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 5, '2020-06-30 15:12:05.634191', '2020-06-30 15:12:05.634191')
   (1.8ms)  COMMIT

2つ目の実装だと10本のSELECTが省略できていますね!! これを知っているかどうかで発行されるSQLが変わってくるので覚えておきましょう!

おまけ

前述の通りcreateをする時にbelongs_toの存在チェックをしているわけですが、createで渡したモデルや直前でSELECTした結果は存在チェックに使うだけではなく、きちんとアソシエーションにセットされているようです。

createで作成したモデルのアソシエーションを参照してもSQLが発行されません。

irb(main):051:0> review = Review.create!(user: user, book: book)
   (2.3ms)  BEGIN
  Review Create (0.6ms)  INSERT INTO `reviews` (`user_id`, `book_id`, `created_at`, `updated_at`) VALUES (1, 1, '2020-06-30 15:20:01.755647', '2020-06-30 15:20:01.755647')
   (2.5ms)  COMMIT
=> #<Review id: 36, content: "", user_id: 1, book_id: 1, status: "draft", created_at: "2020-06-30 15:20:01", updated_at: "2020-06-30 15:20:01">
# create時に渡したモデルが設定されているので、アソシエーションを参照してもSELECTが実行されない
irb(main):052:0> review.user
=> #<User id: 1, name: "1234567890", created_at: "2019-12-12 05:43:52", updated_at: "2019-12-12 05:43:52">
irb(main):053:0> review.book
=> #<Book id: 1, title: "book1", created_at: "2020-06-15 14:21:15", updated_at: "2020-06-15 14:21:15">