ruby ActiveRecord中的向上插入(Rails 6)

t5fffqht 于 2023-02-03 发布在 Ruby

关注(0)|答案(1)|浏览(175)

我知道过去在这个主题上有一些问题，但是在Rails6发布之后，我希望事情有所改变。

- 目标**

有了Role和Effect ActiveRecord模型，我想临时设置关联（即应该有role.add_effect(effect)和role.remove_effect(effect)方法）来跟踪给定角色当前激活的效果。请注意，效果生存期超出了这些临时关联，许多角色可能在某个时候激活相同的效果。
假设中间对象（是否有此类模型的术语？）

class RoleEffect < ApplicationRecord
  belongs_to :role
  belongs_to :effect
end

class CreateRoleEffects < ActiveRecord::Migration[6.0]
  def change
    create_table :role_effects do |t|
      t.belongs_to :role, null: false, foreign_key: true
      t.references :effect, null: false, foreign_key: true
      t.integer :counter, null: false, default: 0

      t.timestamps
    end
    add_index :role_effects, [:role_id, :effect_id], unique: true
  end
end

就SQL而言，它类似于

INSERT INTO role_effects (role_id, effect_id, counter, created_at, updated_at)
VALUES (:role_id, :effect_id, :delta, :ts, :ts)
ON CONFLICT (role_id, effect_id)
DO UPDATE SET counter = counter + excluded.counter, updated_at = :ts

"所以问题是"
现在是什么情况（从Rails6开始）实现此类add/remove方法的正确方法，以便它们是原子。我看到有upsert方法可用，但来自文档（也不知道源代码）我不明白如何执行counter = counter + excluded.counter位。我也看到了使用https://github.com/zdennis/activerecord-import gem的建议，但是让第三方包只执行一个SQL查询似乎有些过头了。
如果upsert方法的当前实现不适用于此类用例，我不介意将其作为自定义SQL查询，但我也不明白什么是适当的API来安全地执行它，安全地替换（净化）所有这些值，最好是通过名称而不是匿名的"？"和排序来提及。
我最初的尝试是

def add_effect(effect)
  change_effect_counter(effect, 1)
end

def remove_effect(effect)
  change_effect_counter(effect, -1)
end

private

def change_effect_counter(effect, delta)
  RoleEffect.connection.exec_insert(<<~SQL.strip, {role_id: self.id, effect_id: effect.id, delta: delta, ts: Time.now})
    INSERT INTO role_effects (role_id, effect_id, counter, created_at, updated_at)
    VALUES (:role_id, :effect_id, :delta, :ts, :ts)
    ON CONFLICT (role_id, effect_id)
    DO UPDATE SET counter = counter + :delta, updated_at = :ts
  SQL
  nil
end

但是由于与预期的绑定格式（相当难看的对数组）不匹配，它失败得很惨。
由于PostgreSQL和SQLite都支持这样的查询，我希望解决方案对它们都有效。

- 更新**（第一次注解后）

我当然有

has_many :role_effects, dependent: :destroy
has_many :effects, through: :role_effects

但这不提供修改关联的原子操作。
我可以放弃相反的想法，删除唯一性索引并添加role.effects << effect这样的效果，但删除一个关联将是棘手的，因为它将需要DELETE ... LIMIT 1，这在SQLite中是默认不可用的（我知道它可以用一个特殊的标志重新编译，但这对开发环境来说是太多的要求）。
或者我可以用

RoleEffect.find_or_create_by!(role: role, effect: effect).increment!(:counter, delta)

但我不确定这两个调用是否都能保证并发情况下的正确行为，即使是这样，它也使用多个查询而不是INSERT ... ON CONFLICT UPDATE。

ruby

来源：https://stackoverflow.com/questions/64069531/upsert-in-activerecord-rails-6

1条答案

按热度按时间

pb3skfrl1#

为了以防万一，我会发布我的发现，尽管结果是相当不完美的（例如，插入没有返回模型对象，这可能是意外的，像role.role_effects这样的缓存可能是陈旧的，等等）。

class CreateRoleEffects < ActiveRecord::Migration[6.0]
  def change
    create_table :role_effects do |t|
      t.belongs_to :role, null: false, foreign_key: true
      t.references :effect, null: false, foreign_key: true
      t.integer :counter, null: false, default: 0

      t.timestamps
    end
    add_index :role_effects, [:role_id, :effect_id], unique: true
  end
end

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  def self.exec_custom_insert(sql, vars)
    sql = ActiveRecord::Base::sanitize_sql_array([sql, vars])
    connection.exec_insert(sql, 'SQL')
  end
end

class RoleEffect < ApplicationRecord
  belongs_to :role
  belongs_to :effect

  scope :active, -> { where('counter > 0') }
  scope :for_role, ->(role) { where(role: role) }

  def self.increment(role, effect)
    change(role, effect, 1)
  end

  def self.decrement(role, effect)
    change(role, effect, -1)
  end

  private_class_method def self.change(role, effect, delta)
    exec_custom_insert(<<~SQL.strip, {role_id: role.id, effect_id: effect.id, delta: delta, ts: Time.now})
      INSERT INTO role_effects (role_id, effect_id, counter, created_at, updated_at)
      VALUES (:role_id, :effect_id, :delta, :ts, :ts)
      ON CONFLICT (role_id, effect_id)
      DO UPDATE SET counter = counter + excluded.counter, updated_at = excluded.updated_at
    SQL
    nil
  end
end

class Role < ApplicationRecord
  ...
  has_many :role_effects, dependent: :destroy
  has_many :active_effects, -> { RoleEffect.active }, through: :role_effects, source: :effect

  def add_effect(effect)
    RoleEffect.increment(self, effect)
  end

  def remove_effect(effect)
    RoleEffect.decrement(self, effect)
  end
end

用途

irb(main):127:0> reload!; r = Role.first; e = Effect.first
Reloading...
   (0.0ms)  SELECT sqlite_version(*)
  Role Load (0.1ms)  SELECT "roles".* FROM "roles" ORDER BY "roles"."id" ASC LIMIT ?  [["LIMIT", 1]]
  Effect Load (0.1ms)  SELECT "effects".* FROM "effects" ORDER BY "effects"."id" ASC LIMIT ?  [["LIMIT", 1]]
=> #<Effect id: 1, name: "The Effect", created_at: "2020-09-25 08:42:54", updated_at: "2020-09-25 08:42:54">
irb(main):128:0> r.active_effects
  Effect Load (0.1ms)  SELECT "effects".* FROM "effects" INNER JOIN "role_effects" ON "effects"."id" = "role_effects"."effect_id" WHERE "role_effects"."role_id" = ? AND (counter > 0) LIMIT ?  [["role_id", 1], ["LIMIT", 11]]
=> #<ActiveRecord::Associations::CollectionProxy []>
irb(main):129:0> r.add_effect(e)
  SQL (1.2ms)  INSERT INTO role_effects (role_id, effect_id, counter, created_at, updated_at)
VALUES (1, 1, 1, '2020-09-27 12:16:55.639693', '2020-09-27 12:16:55.639693')
ON CONFLICT (role_id, effect_id)
DO UPDATE SET counter = counter + excluded.counter, updated_at = excluded.updated_at
=> nil
irb(main):130:0> r.active_effects
  Effect Load (0.3ms)  SELECT "effects".* FROM "effects" INNER JOIN "role_effects" ON "effects"."id" = "role_effects"."effect_id" WHERE "role_effects"."role_id" = ? AND (counter > 0) LIMIT ?  [["role_id", 1], ["LIMIT", 11]]
=> #<ActiveRecord::Associations::CollectionProxy [#<Effect id: 1, name: "The Effect", created_at: "2020-09-25 08:42:54", updated_at: "2020-09-25 08:42:54">]>
irb(main):131:0> r.remove_effect(e)
  SQL (8.1ms)  INSERT INTO role_effects (role_id, effect_id, counter, created_at, updated_at)
VALUES (1, 1, -1, '2020-09-27 12:17:08.148215', '2020-09-27 12:17:08.148215')
ON CONFLICT (role_id, effect_id)
DO UPDATE SET counter = counter + excluded.counter, updated_at = excluded.updated_at
=> nil
irb(main):132:0> r.active_effects
  Effect Load (0.2ms)  SELECT "effects".* FROM "effects" INNER JOIN "role_effects" ON "effects"."id" = "role_effects"."effect_id" WHERE "role_effects"."role_id" = ? AND (counter > 0) LIMIT ?  [["role_id", 1], ["LIMIT", 11]]
=> #<ActiveRecord::Associations::CollectionProxy []>

2023年1月31日更新

Rails 7添加了一个选项来指定ON CONFLICT行为：

RoleEffect.upsert(
  {role_id: role.id, effect_id: effect.id, counter: delta},
  unique_by: %i[role_id effect_id],
  on_duplicate: Arel.sql(
    "counter = counter + EXCLUDED.counter, updated_at = EXCLUDED.updated_at"
  )
)

老实说，我希望不需要显式的:unique_by，但它是：至少对于SQLite，生成的SQL具有ON CONFLICT ("id")，忽略了表具有的唯一索引。
无论如何，看起来比原始SQL更好。

赞(0）回复(0）举报 2023-02-03

我来回答

ruby ActiveRecord中的向上插入(Rails 6)

1条答案

相关问题

热门标签

最新问答