如何在has_many关联场景下用Arel按chart_axis.slug分组计算SQL AVG

正确实现代码 #

你原有SQL的关联逻辑存在错误，charts和chart_axis_values的关联条件应为cav.chart_id = c.id而非cav.id IN c.chart_axis_value_ids，修正后基于Arel的实现如下：

# /models/chart.rb
class Chart < ApplicationRecord
  has_many :chart_axis_values

  # 类方法：统计所有Chart对应的轴均值，按slug分组返回{slug => 均值}格式哈希
  def self.averages
    # 初始化各表的Arel实例
    charts = arel_table
    cav = ChartAxisValue.arel_table
    ca = ChartAxis.arel_table

    # 构造内连接逻辑
    join_cav = charts.join(cav, Arel::Nodes::InnerJoin).on(cav[:chart_id].eq(charts[:id]))
    join_ca = join_cav.join(ca, Arel::Nodes::InnerJoin).on(ca[:id].eq(cav[:chart_axis_id]))

    # 执行查询、聚合、分组
    result = joins(join_ca.join_sources)
      .select(ca[:slug], Arel::Nodes::Avg.new(cav[:value]).as('axis_average'))
      .group(ca[:slug])

    # Rails 6+ 直接转哈希
    result.to_h { |row| [row.slug, row.axis_average] }
    # Rails 5及更早版本替换上面一行为：
    # result.pluck(ca[:slug], 'axis_average').to_h
  end
end

拓展说明 #

如果需要统计单个Chart的轴均值，可新增对应实例方法，额外增加当前Chart的ID过滤条件即可：

# 实例方法：统计当前Chart的轴均值
def averages
  charts = self.class.arel_table
  cav = ChartAxisValue.arel_table
  ca = ChartAxis.arel_table

  join_cav = charts.join(cav, Arel::Nodes::InnerJoin).on(cav[:chart_id].eq(charts[:id]))
  join_ca = join_cav.join(ca, Arel::Nodes::InnerJoin).on(ca[:id].eq(cav[:chart_axis_id]))

  result = self.class.joins(join_ca.join_sources)
    .where(id: self.id)
    .select(ca[:slug], Arel::Nodes::Avg.new(cav[:value]).as('axis_average'))
    .group(ca[:slug])

  result.to_h { |row| [row.slug, row.axis_average] }
end

如果value字段存在空值，可在聚合前加where(cav[:value].not_eq(nil))过滤空值，避免统计结果偏差。

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