Problem

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

For example, suppose you have 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.

# id=1 user and book 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

It is SELECTed to users and books where belongs_to is defined.

The reason for being SELECTed is simple. belongs_to requires the existence of related models by default. So what about the required confirmations? Just before creating it as before, it is SELECTed and checked for existence.

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

So, the way to avoid the SELECT mentioned in the title is to set ʻoptional: true`! ... not! !!

Sure, SELECTs will no longer be issued, but setting ʻoptional: true` is not appropriate if the associated model is required.

Solution

So how do you 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

By passing the object, the existence can be confirmed without performing SELECT, so the SELECT statement will not be issued. However, in the case of this example, the number of SQL is the same because each model is separately selected just before !! However, the number of SQLs is completely different, for example, when creating multiple reviews of the same user.

Below is the code to create a review of books 1-5 for user1 (partially omitted for readability). The first is to specify the 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)  BEGIN
  Review 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

With the second implementation, 10 SELECTs can be omitted !! Keep in mind that the SQL issued depends on whether you know this or not!

bonus

As mentioned above, the existence of belongs_to is checked when creating, but it seems that the model passed by create and the result of SELECT just before are not only used for existence check but also set properly in the association.

SQL is not issued even if you refer to the model association created by create.

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">
#Since the model passed at the time of create is set, SELECT is not executed even if the association is referenced.
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">

[Rails] Avoid SELECT issued when creating a model with belongs_to defined!

Problem

Solution

bonus