基于多引用属性的ExerciseDrill记录及关联删除方案咨询

我有一个处理归属逻辑的关联表exercise_drills（开发中未定稿），用于关联Exercise、Drill和Organization，同时支持基于已有记录复制生成新记录（仅修改Drill并添加alt_name，Exercise保持不变）。

现有表结构 #

create_table :exercise_drills do |t|
  t.references :exercise, foreign_key: true, null: false
  t.references :drill, foreign_key: true, null: false
  t.references :organization, foreign_key: true, null: false
  t.bigint :exercise_drill_reference
  t.boolean :editable, default: false, null: false
  t.boolean :deleted, default: false, null: false
  t.boolean :exercise_deleted, default: false, null: false
  t.boolean :reference_deleted, default: false, null: false
  t.string :alt_name

  t.timestamps

  t.index [:exercise_id, :drill_id, :organization_id], unique: true, name: "index_guild_exercise_drill_uniqueness"
end

现有模型关联 #

class ExerciseDrill < ApplicationRecord
  belongs_to :exercise
  belongs_to :drill
  belongs_to :organization
end

class Organization < ApplicationRecord
  before_destroy :mark_exercise_delete

  has_many :exercise_drills
  has_many :exercises, through: :exercise_drills, dependent: :restrict_with_exception

  private

  def mark_exercise_delete
    exercise_drills.find_each do |ed|
      ed.update_attribute(deleted: true)

      # 尝试处理关联的复制记录
      r_id = exercise_drills.pluck(:id)
      ExerciseDrill.where(exercise_drill_reference: r_id).find_each do |ed|
        update_attribute(exercise_deleted: true)
      end
    end
  end
end

class Drill < ApplicationRecord
  has_one :exercise_drill
  has_one :exercise, through: :exercise_drill
end

class Exercise < ApplicationRecord
  has_one :exercise_drill, dependent: :destroy
  has_one :drill, through: :exercise_drill
  delegate :alt_name, :editable?, to: :exercise_drill
end

核心字段说明 #

• exercise_drill_reference：标记当前记录是从哪条ExerciseDrill复制而来
• editable：标记是否为新创建的独立记录（无exercise_drill_reference时为true）

删除规则要求 #

1. 保留规则：当ExerciseDrill仍被exercise_id对应的Exercise引用时，无论Organization、Exercise、源ExerciseDrill是否被删除，该记录都不能销毁
2. 销毁规则：当ExerciseDrill不再被exercise_id引用时，只要满足以下任一条件，就需要销毁该记录：
   • 所属Organization被删除
   • 关联的Exercise被删除
   • 源ExerciseDrill（exercise_drill_reference指向的记录）被删除

目前有两种初步方案，但不确定如何落地符合规则的完整逻辑：

方案A：标记布尔值+定时清理 #

通过标记deleted/exercise_deleted/reference_deleted三个布尔字段，每月定时任务删除三个标记均为true的记录：

def self.perform
  e = ExerciseDrill.where(deleted: true, exercise_deleted: true, reference_deleted: true)
  e.exercise.destroy
  e.destroy
end

方案B：关联置空+定时清理 #

修改关联为dependent: :nullify/on_delete: :nullify，或把exercise_drill_reference改为正式外键，定时删除满足条件的记录：

# 方案B1
def self.perform
  ExerciseDrill.where(exercise_delete: true, organization: null, reference_deleted: true).destroy_all
end

# 方案B2（修改表结构后）
# 表结构调整：t.references :exercise_reference, to_table: :exercise_drill, on_delete: :nullify
def self.perform
  ExerciseDrill.where(exercise_delete: true, organization: null, exercise_reference: null).destroy_all
end

结合业务规则和现有结构，推荐采用标记布尔值+关联校验+定时清理的方案，同时优化模型逻辑和表结构，确保规则严格执行：

1. 表结构优化 #

将exercise_drill_reference改为正式外键，自动处理源记录删除的关联逻辑：

# 迁移文件
def change
  # 移除旧字段（若已存在）
  remove_column :exercise_drills, :exercise_drill_reference, :bigint
  # 添加带外键约束的源记录引用，源记录删除时自动置空
  add_reference :exercise_drills, :source_exercise_drill, class_name: "ExerciseDrill",
                foreign_key: { on_delete: :nullify }, null: true
end

2. 模型关联与逻辑优化 #

ExerciseDrill模型 #

class ExerciseDrill < ApplicationRecord
  belongs_to :exercise
  belongs_to :drill
  belongs_to :organization, optional: true
  belongs_to :source_exercise_drill, class_name: "ExerciseDrill", optional: true

  # 校验是否仍被Exercise引用：关联Exercise存在且其exercise_drill指向当前记录
  def still_referenced_by_exercise?
    exercise.present? && exercise.exercise_drill_id == id
  end

  # 判断是否满足销毁条件
  def eligible_for_destroy?
    !still_referenced_by_exercise? && (deleted? || exercise_deleted? || source_exercise_drill.nil?)
  end

  # 批量清理符合条件的记录
  def self.cleanup_eligible_records
    find_each do |ed|
      next unless ed.eligible_for_destroy?
      ed.destroy!
    end
  end
end

Organization模型 #

修正删除时的标记逻辑，正确处理自身关联记录及复制记录：

class Organization < ApplicationRecord
  before_destroy :mark_organization_deleted

  has_many :exercise_drills, dependent: :nullify # 删除组织时置空关联，避免直接删除
  has_many :exercises, through: :exercise_drills, dependent: :restrict_with_exception

  private

  def mark_organization_deleted
    exercise_drills.find_each do |ed|
      ed.update!(deleted: true)
      # 标记所有从当前记录复制的子记录
      ExerciseDrill.where(source_exercise_drill_id: ed.id).find_each do |child_ed|
        child_ed.update!(reference_deleted: true)
      end
    end
  end
end

Exercise模型 #

修改删除逻辑，标记关联的ExerciseDrill：

class Exercise < ApplicationRecord
  has_one :exercise_drill, dependent: :nullify # 删除Exercise时置空关联
  has_one :drill, through: :exercise_drill
  delegate :alt_name, :editable?, to: :exercise_drill, allow_nil: true

  before_destroy :mark_exercise_deleted

  private

  def mark_exercise_deleted
    # 标记当前Exercise关联的所有ExerciseDrill
    ExerciseDrill.where(exercise_id: id).find_each do |ed|
      ed.update!(exercise_deleted: true)
    end
  end
end

3. 定时清理任务 #

用以下逻辑替换原有定时任务，每月执行：

# 示例：Sidekiq Cron定时任务
class CleanupExerciseDrillsJob
  include Sidekiq::Job

  def perform
    ExerciseDrill.cleanup_eligible_records
  end
end

4. 规则验证 #

• 保留规则：当still_referenced_by_exercise?返回true时，eligible_for_destroy?必然为false，确保记录不会被误删
• 销毁规则：当记录不再被Exercise引用时，只要满足组织删除（deleted?）、Exercise删除（exercise_deleted?）、源记录删除（source_exercise_drill.nil?）任一条件，就会被定时任务清理

内容的提问来源于stack exchange，提问作者Chrismisballs