rails中关联范围中左联接的子查询

Question

我将Rails 4.2.0与ActiveRecord一起使用。我有Case模型，它是has_many :notes。

我的主要目标是在一个查询中获得每个案例的最新注释。注意字段应该是可排序的（例如ORDER BY notes.created_at）我可以通过这样的SQL查询来实现这一点：

SELECT cases.*, notes.* FROM cases
LEFT OUTER JOIN notes ON cases.id = notes.case_id AND notes.id = (
  SELECT id FROM notes 
  WHERE notes.case_id = cases.id 
  ORDER BY notes.created_at DESC 
  LIMIT 1
)
ORDER BY notes.created_at DESC

我当前的case.rb代码如下：

has_one :latest_note, -> do
  where('id' => Note.where('"case_id" = "cases"."id"').order(created_at: :desc).limit(1))
end, inverse_of: :case, class_name: 'Note'

使用eager_load时工作良好：Case.eager_load(:latest_note).order('notes.created_at DESC')

SELECT "cases"."id" AS t0_r0, "cases"."created_at" AS t0_r1, "cases"."updated_at" AS t0_r2, "notes"."id" AS t1_r0, "notes"."case_id" AS t1_r1, "notes"."created_at" AS t1_r2, "notes"."updated_at" AS t1_r3
FROM "cases" LEFT OUTER JOIN "notes" 
  ON "notes"."case_id" = "cases"."id" 
  AND "notes"."id" IN (
    SELECT  "notes"."id" FROM "notes" 
    WHERE ("case_id" = "cases"."id")  
    ORDER BY "notes"."created_at" DESC 
    LIMIT 1
)  ORDER BY notes.created_at DESC

但在没有eager_load的情况下引发错误SQLite3::SQLException: no such column: cases.id：Case.first.latest_note

SELECT  "notes".* FROM "notes" 
WHERE "notes"."case_id" = ? AND "notes"."id" IN (
  SELECT  "notes"."id" FROM "notes" 
  WHERE ("case_id" = "cases"."id")  
  ORDER BY "notes"."created_at" DESC 
  LIMIT 1
) LIMIT 1

附言：是否存在没有关联范围的解决方案？通过joins的原始左联接不适用于eager_load。如果没有eager_load，它就不会将数据库中的值映射到对象——日期保持为字符串，而不是DateTime对象。

Answer 1

我刚刚找到了解决方法：

从我发现的文档中，该范围可以接受owner对象作为参数：

class User < ActiveRecord::Base
  has_many :birthday_events, ->(user) { where starts_on: user.birthday }, class_name: 'Event'
end

所以，我的关系变成了：

has_one :latest_note, ->(kase) do
  subquery = Note.order(created_at: :desc).limit(1)
  subquery = kase.is_a?(Case) ? subquery.where(case_id: kase.id) : subquery.where('"case_id" = "cases"."id"')
  where('id' => subquery)
end, inverse_of: :case, class_name: 'Note'

然而，Rails 4.2.0输出：

DEPRECATION警告：关联作用域"latest_note"依赖于实例（作用域块采用参数）。预加载发生在创建单个实例之前。这意味着没有实例被传递到关联作用域。这很可能会导致不正常或不正确的行为。不赞成加入、预加载和急于加载这些关联，将来将删除这些关联。

此外，此解决方法不适用于preload（单独的SQL查询）