Day04 - Gem-activerecord-import 批次建立介绍与应用

前言

当需要大量建立资料时，可以选择逐笔建立，但会有 N+1 insert problem

透过 activerecord-import Gem 只要几条 SQL 便解决

如何安装

放在 Gemfile 档案中，可参考此 commit

推荐至 GitHub 看文件，写得很清楚，且有提供范例

使用方法

在 rails console --sandbox 中演练示范

# rails console --sandbox 可缩写成 rails c -s
# 不建议在 production 使用 sandbox ， 可能会造成 DB Lock，详情自行上网查

$ rails c -s

[1] pry(main)> Shop.count
  TRANSACTION (0.2ms)  BEGIN
   (11.7ms)  SELECT COUNT(*) FROM "shops"
0
[2] pry(main)> shops = []
[]
[3] pry(main)> 10.times { |i| shops << { name: "test_#{i}", email: 'test', note: nil } }
10
[4] pry(main)> Shop.import(shops)
  Shop Create Many (1.0ms)  INSERT INTO "shops" ("name","email","note","created_at","updated_at") VALUES ('test_0','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_1','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_2','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_3','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_4','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_5','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_6','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_7','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_8','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_9','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104') RETURNING "id"
#<Struct:ActiveRecord::Import::Result:0x00008098
    failed_instances = [],
    ids = [
        [0] 1,
        [1] 2,
        [2] 3,
        [3] 4,
        [4] 5,
        [5] 6,
        [6] 7,
        [7] 8,
        [8] 9,
        [9] 10
    ],
    num_inserts = 1,
    results = []
>
[5] pry(main)> Shop.count
   (0.4ms)  SELECT COUNT(*) FROM "shops"
10

注意

在使用 import 时，须留意不会检查 ActiveRecord 的 validates 与触发 callback，可参考这篇 GitHub Issue

以下为还原情境

# app/models/shop.rb
class Shop < ApplicationRecord
  strip_attributes
  validates :name, uniqueness: true
  after_commit :say_hello

  private

  def say_hello
    puts "hello"
  end
end

---

$ rails c -s

[1] pry(main)> Shop.count
  TRANSACTION (0.2ms)  BEGIN
   (1.1ms)  SELECT COUNT(*) FROM "shops"
0
[2] pry(main)> shops = []
[]
[3] pry(main)> 10.times { |i| shops << { name: "test", email: "test", note: nil } }
10
[4] pry(main)> Shop.import(shops)
  Shop Create Many (1.8ms)  INSERT INTO "shops" ("name","email","note","created_at","updated_at") VALUES ('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983') RETURNING "id"
#<Struct:ActiveRecord::Import::Result:0x000083b8
    failed_instances = [],
    ids = [
        [0] 1,
        [1] 2,
        [2] 3,
        [3] 4,
        [4] 5,
        [5] 6,
        [6] 7,
        [7] 8,
        [8] 9,
        [9] 10
    ],
    num_inserts = 1,
    results = []
>
[5] pry(main)> Shop.count
   (0.5ms)  SELECT COUNT(*) FROM "shops"
10
[6] pry(main)> Shop.pluck(:name)
   (0.3ms)  SELECT "shops"."name" FROM "shops"
[
    [0] "test",
    [1] "test",
    [2] "test",
    [3] "test",
    [4] "test",
    [5] "test",
    [6] "test",
    [7] "test",
    [8] "test",
    [9] "test"
]