Rails中按has_many关联最新记录属性排序Box的实现问题

适合绝大多数场景，依赖自增主键ID默认和创建时间正序的特性，代码示例如下：

class Box < ApplicationRecord
  has_many :breed_cycles

  # 按最新关联的breed_cycle的breed_start_date倒序排序，空值排最后
  def self.order_by_latest_breed_start_date
    # 子查询获取每个box对应的最新breed_cycle的主键ID
    latest_breed_cycle_ids = BreedCycle.select("MAX(id) as id").group(:box_id)
    # 左连接保证无关联breed_cycle的box也会被返回
    joins(
      "LEFT JOIN breed_cycles 
      ON breed_cycles.box_id = boxes.id 
      AND breed_cycles.id IN (#{latest_breed_cycle_ids.to_sql})"
    )
    # PostgreSQL 直接支持NULLS LAST语法
    .order("breed_cycles.breed_start_date DESC NULLS LAST")
    # 若使用MySQL，替换上面的order语句为以下内容
    # .order("IF(breed_cycles.breed_start_date IS NULL, 1, 0), breed_cycles.breed_start_date DESC")
    # 可选：查询结果追加最新的breed_start_date属性，方便直接调用
    .select("boxes.*, breed_cycles.breed_start_date as latest_breed_start_date")
  end
end

调用方式非常简单：Box.order_by_latest_breed_start_date即可拿到排序后的结果。

如果有更复杂的最新记录筛选规则，推荐用窗口函数实现：

class Box < ApplicationRecord
  has_many :breed_cycles

  def self.order_by_latest_breed_start_date
    joins(
      "LEFT JOIN (
        SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY box_id ORDER BY created_at DESC) AS row_num
        FROM breed_cycles
      ) latest_cycles 
      ON latest_cycles.box_id = boxes.id AND latest_cycles.row_num = 1"
    )
    .order("latest_cycles.breed_start_date DESC NULLS LAST")
    .select("boxes.*, latest_cycles.breed_start_date as latest_breed_start_date")
  end
end

• 若不需要保留无关联breed_cycle的Box，把语句中的LEFT JOIN替换为INNER JOIN即可。
• 若最新记录的判断规则不是按创建时间，调整窗口函数或子查询中的排序字段即可，比如把ORDER BY created_at DESC换成ORDER BY updated_at DESC。
• 排序升降序可按需调整，把DESC替换为ASC即可改为正序排列。

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