Rails5.2.4中如何通过Arel关联两查询获取ProductionJob最新版本

你写的@selected_jobs返回空是三个写法错误导致的：

• 不能直接把已经加了select、join、分页逻辑的ActiveRecord::Relation对象作为join参数传入，Rails不会自动将其识别为可关联的子查询，会直接拼接SQL导致关联逻辑完全失效
• 条件写法where(business_process_id: :bp_id, version: :max_version)语义完全错误，这个写法会被解析为查找字段值等于字符串bp_id、max_version的记录，根本不会关联你子查询里取的别名字段
• 你单独查的@recent_jobs没有加主查询里的pgnd(current_playground)、业务流权限过滤条件，就算关联逻辑写对，两边数据范围不匹配也会查不到结果

直接用Arel构造原生关联子查询，一次性完成过滤，不需要拆分三个独立查询，同时去掉原逻辑里多余的表关联：
首先调整private部分的方法：

private

def jobs
  ProductionJob.arel_table
end

def processes   # jobs are built on the processes
  BusinessProcess.arel_table
end

def flows       # flows provide a classifiaction to processes
  BusinessFlow.arel_table
end

def owners      # owner of the jobs
  User.arel_table.alias('owners')
end

# 构造每个BusinessProcess对应最大版本号的子查询，去掉多余的用户表关联
def latest_job_versions
  latest = jobs.alias('latest_jobs')
  latest.project(
    latest[:business_process_id],
    latest[:version].maximum.as('max_version')
  ).group(latest[:business_process_id]).as('latest_job_versions')
end

def production_jobs_join
  jobs.
  join(owners).on(jobs[:owner_id].eq(owners[:id])).
  join(processes).on(jobs[:business_process_id].eq(processes[:id])).
  join(flows).on(processes[:business_flow_id].eq(flows[:id])).
  # 直接关联最新版本子查询，匹配同业务流程ID+最大版本号
  join(latest_job_versions).on(
    jobs[:business_process_id].eq(latest_job_versions[:business_process_id])
    .and(jobs[:version].eq(latest_job_versions[:max_version]))
  ).
  join_sources
end

def job_index_fields
  [jobs[:id],
    jobs[:code].as("job_code"),
    jobs[:status_id],
    jobs[:created_at],
    jobs[:updated_by],
    jobs[:updated_at],
    jobs[:business_process_id],
    jobs[:version],
    processes[:code].as("process_code"),
    flows[:code].as("statistical_activity_code"),
    owners[:name].as("owner_name")]
end

def order_by
  [jobs[:code], jobs[:updated_at].desc]
end

然后简化index方法，一次查询返回最终分页结果：

# GET /production_jobs or /production_jobs.json
def index
  @selected_jobs = ProductionJob.joins(production_jobs_join).
    pgnd(current_playground).
    where("business_flows.code in (?)", current_user.preferred_activities).
    order(order_by).
    select(job_index_fields).
    paginate(page: params[:page], :per_page => params[:per_page])
end

如果不想重构原有代码结构，要保留@production_jobs、@recent_jobs的拆分逻辑，需要将Relation对象转为SQL子查询，同时对齐两边的过滤范围：

def index
  # 先定义公共查询范围，保证两边过滤条件一致
  base_scope = ProductionJob.joins(production_jobs).
    pgnd(current_playground).
    where("business_flows.code in (?)", current_user.preferred_activities)

  @production_jobs = base_scope.
    order(order_by).
    select(job_index_fields).
    paginate(page: params[:page], :per_page => params[:per_page])

  @recent_jobs = base_scope.
    select(recent_jobs_fields).
    group(:business_process_id, :code)

  # 手动写关联SQL，正确匹配子查询字段
  @selected_jobs = base_scope.
    joins("INNER JOIN (#{@recent_jobs.to_sql}) AS recent_jobs ON production_jobs.business_process_id = recent_jobs.bp_id AND production_jobs.version = recent_jobs.max_version").
    order(order_by).
    select(job_index_fields).
    paginate(page: params[:page], :per_page => params[:per_page])
end

这个写法会嵌套两层子查询，数据量较大时查询性能明显低于第一种原生Arel方案，优先推荐使用第一种实现。

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