如何将含子查询、多表关联的SQL查询转换为Ruby on Rails查询

嘿，这问题我熟！我来帮你把这段带多表关联、子查询和分组排序的SQL转换成标准的Rails Active Record查询，让代码更符合Rails的优雅风格～

首先先明确原SQL的核心逻辑：你要关联ProjectMilestone、Milestone和ProjectMilestoneReportStatus三张表，同时只保留每个ProjectMilestone对应的最新一条ProjectMilestoneReportStatus记录，对吧？下面分两种方式实现：

方式一：基于子查询关联（和原SQL逻辑一致） #

先定义子查询，用来获取每个project_milestone_id对应的最新状态记录的创建时间：

# 子查询：分组取每个里程碑的最新状态创建时间
latest_status_subquery = ProjectMilestoneReportStatus
  .group(:project_milestone_id)
  .select(:project_milestone_id, Arel.sql('MAX(created_at) AS latest_created_at'))

这里用Arel.sql是为了绕过Rails的SQL安全校验，毕竟我们用到了原生的MAX()函数。

然后构建主查询，关联所有需要的表，并通过子筛选出最新状态的记录：

# 主查询：关联表+筛选最新状态
project_milestones = ProjectMilestone
  # 关联已定义好模型关联的表（前提是你已经在模型里设置了belongs_to/has_many）
  .joins(:milestone, :project_milestone_report_statuses)
  # 关联子查询，匹配里程碑ID和最新创建时间
  .joins(
    "INNER JOIN (#{latest_status_subquery.to_sql}) AS latest_statuses
     ON project_milestone_report_statuses.project_milestone_id = latest_statuses.project_milestone_id
     AND project_milestone_report_statuses.created_at = latest_statuses.latest_created_at"
  )
  # 按需求添加分组和排序
  .group('project_milestones.id')
  .order('project_milestones.created_at DESC')

方式二：用窗口函数简化（推荐，数据库支持的话） #

如果你的数据库支持窗口函数（比如PostgreSQL、MySQL 8.0+），可以用更简洁的方式实现，不需要额外的子查询关联：

project_milestones = ProjectMilestone
  .joins(:milestone, :project_milestone_report_statuses)
  # 选择所需字段，同时添加窗口函数标记每条记录的分组排序序号
  .select(
    'project_milestones.*, milestones.*, project_milestone_report_statuses.*',
    Arel.sql('ROW_NUMBER() OVER (PARTITION BY project_milestone_report_statuses.project_milestone_id ORDER BY project_milestone_report_statuses.created_at DESC) AS rn')
  )
  # 只保留每个分组的第一条（也就是最新的那条记录）
  .where(Arel.sql('rn = 1'))
  # 按需添加分组和排序
  .group('project_milestones.id')
  .order('project_milestones.due_date DESC')

这种写法逻辑更直观，性能也往往更好，因为不需要额外的JOIN操作。

几个关键注意点 #

• 确保你的模型之间已经正确设置了关联：比如ProjectMilestone模型里要有belongs_to :milestone和has_many :project_milestone_report_statuses，这样joins方法才能自动生成正确的关联SQL；
• 如果你需要选择特定字段，不要直接用select('*')，最好明确列出需要的字段，避免冗余数据；
• 分组（GROUP BY）的字段要根据你的业务需求调整，比如如果要按项目分组，就改成group('projects.id')（前提是关联了Project表）。

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