Rails 3.2：含主表条件的has_many :through关联预加载问题求助

为提升查询效率，我尝试预加载基于主表条件的has_many :through关联，现有模型定义如下：

class Scope
  has_many :bought_diamonds, class_name: 'Diamond'
end

class ScopeCache
  belongs_to :scope
  has_many :bought_diamonds, through: :scope 
end

需要为ScopeCache#bought_diamonds添加基于diamonds.created_at和scope_caches.snapshot_date的过滤条件，尝试了两种方案均出现错误：

方案1：静态SQL条件 #

has_many :bought_diamonds, through: :scope, 
  conditions: 'diamonds.created_at >= DATE_SUB(scope_caches.snapshot_date, INTERVAL 1 WEEK) and diamonds.created_at < scope_caches.snapshot_date'

错误表现： #

• ScopeCache.includes(:bought_diamonds).last 报错：

  ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'diamonds.created_at' in 'where clause': SELECT `scopes`.* FROM `scopes`  WHERE `scopes`.`id` IN (34) AND (diamonds.created_at >= DATE_SUB(scope_caches.snapshot_date, INTERVAL 1 WEEK) and diamonds.created_at < scope_caches.snapshot_date)
  

• ScopeCache.eager_load(:bought_diamonds).last 能正常返回实例，但调用bought_diamonds.count时报错：

  ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'scope_caches.snapshot_date' in 'where clause': SELECT COUNT(*) FROM `diamonds` INNER JOIN `scopes` ON `diamonds`.`scope_id` = `scopes`.`id` WHERE `scopes`.`id` = 34 AND (diamonds.created_at >= DATE_SUB(scope_caches.snapshot_date, INTERVAL 1 WEEK) and diamonds.created_at < scope_caches.snapshot_date)
  

• ScopeCache.preload(:bought_diamonds).last 同样触发未知列错误。

方案2：Proc动态条件 #

has_many :bought_diamonds, through: :scope, 
  conditions: proc { "diamonds.created_at >= #{self.snapshot_date-1.week} AND diamonds.created_at < #{self.snapshot_date}" }

错误表现： #

所有预加载方式均触发NoMethodError，提示类或关联对象上找不到snapshot_date方法：

NoMethodError: undefined method `snapshot_date' for #<Class:0x0055b073211398>

后续尝试直接关联Diamond的写法，也出现类似错误：

has_many :bought_diamonds, class_name: "Diamond", foreign_key: "scope_id", primary_key: "scope_id", 
  conditions: proc{['diamonds.created_at >= DATE_SUB(scope_caches.snapshot_date, INTERVAL 1 WEEK) and diamonds.created_at < scope_caches.snapshot_date']}

问题根源是has_many :through关联中，直接引用主表字段会导致预加载时表关联逻辑缺失，而Proc会在类上下文执行，无法访问实例属性。以下是几种可行的解决方式：

1. 关联扩展+实例方法（内存过滤） #

在关联上定义扩展方法，结合实例属性实现过滤，适合数据量不大的场景：

class ScopeCache
  belongs_to :scope
  has_many :bought_diamonds, through: :scope do
    def for_snapshot(snapshot_date)
      where('diamonds.created_at >= ? AND diamonds.created_at < ?', snapshot_date - 1.week, snapshot_date)
    end
  end

  def filtered_bought_diamonds
    bought_diamonds.for_snapshot(snapshot_date)
  end
end

预加载后可在内存中过滤避免N+1：

scope_caches = ScopeCache.includes(:bought_diamonds).all
scope_caches.each do |sc|
  filtered_diamonds = sc.bought_diamonds.select { |d| 
    d.created_at >= sc.snapshot_date - 1.week && d.created_at < sc.snapshot_date 
  }
end

2. 显式表关联的Lambda作用域 #

通过Lambda作用域显式关联scope_caches表，确保字段能被正确解析：

class ScopeCache
  belongs_to :scope
  has_many :bought_diamonds, through: :scope, 
    ->(sc) { 
      joins('JOIN scope_caches ON scope_caches.scope_id = scopes.id')
        .where('diamonds.created_at >= DATE_SUB(scope_caches.snapshot_date, INTERVAL 1 WEEK) AND diamonds.created_at < scope_caches.snapshot_date')
        .where('scope_caches.id = ?', sc.id)
    }
end

查询时推荐用eager_load确保表关联正确：

ScopeCache.eager_load(:scope, :bought_diamonds).last

3. 改用实例方法+手动预加载 #

放弃has_many关联定义，直接用实例方法调用Scope的关联并过滤，同时手动实现预加载避免N+1：

class Diamond
  scope :within_snapshot_window, ->(start_date, end_date) {
    where(created_at: start_date...end_date)
  }
end

class ScopeCache
  belongs_to :scope

  def bought_diamonds
    scope.bought_diamonds.within_snapshot_window(snapshot_date - 1.week, snapshot_date)
  end
end

# 手动预加载示例
scope_caches = ScopeCache.all.to_a
scope_ids = scope_caches.pluck(:scope_id)
# 批量查询所有相关钻石并按scope_id分组
diamonds_grouped = Diamond.where(scope_id: scope_ids).group_by(&:scope_id)

scope_caches.each do |sc|
  # 为每个实例动态定义bought_diamonds方法
  sc.define_singleton_method(:bought_diamonds) do
    diamonds_grouped[sc.scope_id]&.select { |d| 
      d.created_at >= sc.snapshot_date - 1.week && d.created_at < sc.snapshot_date 
    } || []
  end
end

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