Rails 5 更新 has_many


#1

在Rails 5中 belongs_to 會預設加上 required: true,
因此在更新時自己出現了n+1 query

2.4.1 :029 > User.second.books = Book.first(5)
  User Load (0.2ms)  SELECT  "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT ? OFFSET ?  [["LIMIT", 1], ["OFFSET", 1]]
  Book Load (0.2ms)  SELECT  "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT ?  [["LIMIT", 5]]
  Book Load (0.2ms)  SELECT "books".* FROM "books" WHERE "books"."owner_id" = ?  [["owner_id", 2]]
   (0.1ms)  begin transaction
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  SQL (0.3ms)  UPDATE "books" SET "owner_id" = ?, "updated_at" = ? WHERE "books"."id" = ?  [["owner_id", 2], ["updated_at", "2017-08-23 04:49:04.836573"], ["id", 1]]
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  SQL (0.1ms)  UPDATE "books" SET "owner_id" = ?, "updated_at" = ? WHERE "books"."id" = ?  [["owner_id", 2], ["updated_at", "2017-08-23 04:49:04.840473"], ["id", 2]]
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  SQL (0.1ms)  UPDATE "books" SET "owner_id" = ?, "updated_at" = ? WHERE "books"."id" = ?  [["owner_id", 2], ["updated_at", "2017-08-23 04:49:04.844188"], ["id", 3]]
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  SQL (0.1ms)  UPDATE "books" SET "owner_id" = ?, "updated_at" = ? WHERE "books"."id" = ?  [["owner_id", 2], ["updated_at", "2017-08-23 04:49:04.847134"], ["id", 4]]
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  SQL (0.1ms)  UPDATE "books" SET "owner_id" = ?, "updated_at" = ? WHERE "books"."id" = ?  [["owner_id", 2], ["updated_at", "2017-08-23 04:49:04.850176"], ["id", 5]]
   (0.9ms)  commit transaction

不死心加上 optional: true

2.4.1 :001 > User.first.books = Book.first(5)
  User Load (0.2ms)  SELECT  "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT ?  [["LIMIT", 1]]
  Book Load (0.1ms)  SELECT  "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT ?  [["LIMIT", 5]]
  Book Load (0.2ms)  SELECT "books".* FROM "books" WHERE "books"."owner_id" = ?  [["owner_id", 1]]
   (0.1ms)  begin transaction
  SQL (0.4ms)  UPDATE "books" SET "owner_id" = ?, "updated_at" = ? WHERE "books"."id" = ?  [["owner_id", 1], ["updated_at", "2017-08-23 04:50:23.576743"], ["id", 1]]
  SQL (0.1ms)  UPDATE "books" SET "owner_id" = ?, "updated_at" = ? WHERE "books"."id" = ?  [["owner_id", 1], ["updated_at", "2017-08-23 04:50:23.579088"], ["id", 2]]
  SQL (0.1ms)  UPDATE "books" SET "owner_id" = ?, "updated_at" = ? WHERE "books"."id" = ?  [["owner_id", 1], ["updated_at", "2017-08-23 04:50:23.580454"], ["id", 3]]
  SQL (0.1ms)  UPDATE "books" SET "owner_id" = ?, "updated_at" = ? WHERE "books"."id" = ?  [["owner_id", 1], ["updated_at", "2017-08-23 04:50:23.581663"], ["id", 4]]
  SQL (0.1ms)  UPDATE "books" SET "owner_id" = ?, "updated_at" = ? WHERE "books"."id" = ?  [["owner_id", 1], ["updated_at", "2017-08-23 04:50:23.582872"], ["id", 5]]
   (1.2ms)  commit transaction

更新時使用的應該是update 造成每次都會去做驗證 產生了每改一次就去查一次的問題,但這時找到更邪的問題

2.4.1 :003 > User.first.books = []
  User Load (0.2ms)  SELECT  "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT ?  [["LIMIT", 1]]
  Book Load (0.2ms)  SELECT "books".* FROM "books" WHERE "books"."owner_id" = ?  [["owner_id", 1]]
   (0.1ms)  begin transaction
  SQL (0.6ms)  UPDATE "books" SET "owner_id" = NULL WHERE "books"."owner_id" = ? AND "books"."id" IN (1, 2, 3, 4, 5)  [["owner_id", 1]]
   (2.9ms)  commit transaction
 => []

os: 所以你知道可以用update_all 啊

總結來說好像只能靠自己
Books.first(5).update_all(owner_id: 1) 這招連 驗證都會跳過,一次可以解掉兩個問題 XD
而不使用 user.books =

Book.where(id: [1,2,3,5,9,7]).update_all(owner_id: 1)
  SQL (3.4ms)  UPDATE "books" SET "owner_id" = 1 WHERE "books"."id" IN (1, 2, 3, 5, 9, 7)
 => 6

以下附上code

migration

    create_table :users do |t|
      t.string :name
      t.timestamps
    end
    create_table :books do |t|
      t.bigint :owner_id
      t.string :title
      t.timestamps
    end
    add_foreign_key :books, :users, column: :owner_id, primary_key: :id

Model

class User < ApplicationRecord
  has_many :books, foreign_key: :owner_id
end

class Book < ApplicationRecord
  belongs_to :owner, class_name: 'User', optional: true
end

Github請參考


#2

以這個特殊狀況來說應該不是 N + 1 的問題才是,因為你用的是 update

寫得亂糟糟,簡單的來說你的語法完全等同於

user = User.order('id').offset(1).first #User.second
books = Book.order('id').limit(5) #Book.first(5)
books.each do |book|
  book.update_attributes(owner_id: user.id)  #callback update
end

所以 update callback 這件事情是必然發生的,因為每個都是獨立的修改事件哩,而 Rails 要讓 callback 發生也必須 select 出來才行,so~

而你後來用的 update_all 本來就不會觸發 callback 了 … 你可以看這邊(Skipping Callbacks 系列)

http://guides.rubyonrails.org/active_record_callbacks.html

應該就這樣而已唄,是我應該都自幹而已,不會用那怪怪的寫法 X"D ,以上


#3

應該先說 book_ids 會直接傳進來,
本來以為rails 中直接指定 類似 user.books = Book.where(id: params[:book_ids])
他可以直接 一行sql ,

其實我想討論的是它有時候是 each + update 去做 在想可能是他覺得每個都需要去做validation

將owner_id 設成nil 時,就不需要驗證 ,因此改用 update_all
這想法真的很妙。。。

不過其實因為中間還要加入一堆callback
看來只好 分開寫在用transcation綁起來了。