Rails 4.2下JSON序列化聚合查询一次性实现优化问询

问题背景 #

现有API端点用于拉取大型JSON数据，基于Rails内置关联实现可正常运行，但核心问题是json_aggregate实例方法会为每个MyModel单独执行查询，引发N+1性能问题。需要实现一次性批量查询聚合数据，兼容Rails 4.2，后端为PostgreSQL 13.x。

当前实现 #

MyModel代码 #

def self.json_report(p1, p2, p3)
  self.includes(:model_a, :model_b, :model_c, model_d: [:model_e])
      .where(model_a: { col_x: p1 })
      .where(model_b: { col_y: p2 })
      .where(model_e: { col_z: p3 })
      .to_json(
          only: [ :col_a, :col_b, :col_c ],
          include: {
              model_a: { only: [ :col_d, :col_e ] },
              model_b: { only: [ :col_f, :col_g ] },
              model_c: { only: [ :col_h, :col_i ] },
          },
          methods: [ :json_aggregate ]
      )
end

def json_aggregate()
    end_time = Time.zone.now.beginning_of_day
    start_time = end_time - 1.day

    h = self.model_x.where(time: start_time..end_time).group(:my_model_id)
        .select("SUM(agg_1) as c1,
                 SUM(agg_2) as c2,
                 AVG(agg_3) as c3").as_json.first
    h.delete("id") unless h.nil?
    h
end

尝试过的方案 #

直接JOIN子查询（序列化失败） #

尝试通过JOIN注入聚合字段，但JSON序列化器无法识别这些字段：

inner_sql = ModelX.group(:my_model_id)
                  .select("my_model_id as my_model_id,
                           SUM(agg_1) as c1,
                           SUM(agg_2) as c2,
                           AVG(agg_3) as c3").to_sql

join_sql = "LEFT OUTER JOIN (#{inner_sql}) t1 ON my_model.id = my_model_id"

关联改造初步尝试 #

试图通过has_one关联封装聚合查询，但未完成作用域实现：

# MyModel代码
has_one :model_x_aggregate, -> { aggregate }, class_name: ModelX.name

# ModelX代码
def aggregate()
    # 未实现逻辑
end

已实现的部分解决方案 #

通过has_one关联结合作用域实现了序列化兼容，但方案仍有优化空间：

MyModel代码 #

has_one :model_x_aggregate, -> { daily_aggregate }, class_name: ModelX.name

ModelX代码 #

scope :daily_aggregate, -> {
  end_time = Time.zone.now.beginning_of_day
  start_time = end_time - 1.day

  inner_sql = ModelX.where(time: start_time..end_time)
                    .group(:my_model_id)
                    .select("my_model_id as my_model_id",
                            "MAX(id) as id",
                            "SUM(agg_1) as c1",
                            "SUM(agg_2) as c2",
                            "AVG(agg_3) as c3").to_sql

  joins(<<~SQL.squish)
    RIGHT JOIN (#{inner_sql}) t1
      ON model_x.id = t1.id
      AND (model_x.time BETWEEN '#{start_time}' AND '#{end_time}')
  SQL
  .select("t1.*")
}

优化建议 #

方案1：主查询直接注入聚合字段（推荐） #

直接在json_report的主查询中通过LEFT JOIN子查询批量获取聚合数据，完全避免N+1查询，同时兼容JSON序列化：

def self.json_report(p1, p2, p3)
  end_time = Time.zone.now.beginning_of_day
  start_time = end_time - 1.day

  # 构建聚合子查询（参数化，避免SQL注入）
  aggregate_subquery = ModelX.where(time: start_time..end_time)
                            .group(:my_model_id)
                            .select(
                              "my_model_id",
                              "SUM(agg_1) as c1",
                              "SUM(agg_2) as c2",
                              "AVG(agg_3) as c3"
                            ).to_sql

  self.includes(:model_a, :model_b, :model_c, model_d: [:model_e])
      .joins("LEFT JOIN (#{aggregate_subquery}) agg ON my_models.id = agg.my_model_id")
      .where(model_a: { col_x: p1 })
      .where(model_b: { col_y: p2 })
      .where(model_e: { col_z: p3 })
      .select(
        "my_models.*",
        "agg.c1",
        "agg.c2",
        "agg.c3"
      )
      .to_json(
        only: [:col_a, :col_b, :col_c, :c1, :c2, :c3],
        include: {
          model_a: { only: [:col_d, :col_e] },
          model_b: { only: [:col_f, :col_g] },
          model_c: { only: [:col_h, :col_i] },
        }
      )
end

优势：无需额外关联或实例方法，一次性完成所有数据查询，性能最优；聚合字段直接作为模型属性被序列化器识别。

方案2：优化现有has_one关联作用域 #

简化关联作用域，无需关联真实model_x表，直接基于聚合子查询构建虚拟关联：

# ModelX代码
scope :daily_aggregate, -> {
  end_time = Time.zone.now.beginning_of_day
  start_time = end_time - 1.day

  subquery = ModelX.where(time: start_time..end_time)
                   .group(:my_model_id)
                   .select(
                     "my_model_id",
                     "SUM(agg_1) as c1",
                     "SUM(agg_2) as c2",
                     "AVG(agg_3) as c3"
                   ).to_sql

  # 直接从子查询构建虚拟ModelX记录
  from("(#{subquery}) as model_x")
}

# MyModel代码
has_one :model_x_aggregate, -> { daily_aggregate }, 
        class_name: "ModelX", 
        primary_key: :id, 
        foreign_key: :my_model_id

优势：保留关联语义，避免原方案中冗余的RIGHT JOIN和MAX(id)模拟；查询更轻量化。

通用优化：规避SQL注入风险 #

原方案中手动拼接时间字符串存在SQL注入风险，需改用Rails内置的参数化方式：

# 替换字符串拼接的时间参数
start_time_str = ActiveRecord::Base.connection.quote(start_time)
end_time_str = ActiveRecord::Base.connection.quote(end_time)

# 或使用sanitize_sql_array处理动态SQL
joins(sanitize_sql_array([
  "RIGHT JOIN (#{inner_sql}) t1 ON model_x.id = t1.id AND (model_x.time BETWEEN ? AND ?)", 
  start_time, 
  end_time
]))

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