ruby-on-rails - 如何使用 ActiveRecord 返回父级唯一的最新子级

Question

一篇文章有_many 条评论。一条评论属于一个帖子。我正在尝试取回最新评论的列表，但每个帖子只有一个。

以下SQL返回PostgreSQL中正确的评论结果集:

SELECT
  comments.*
FROM
  (SELECT
     post_id, MAX(created_at) AS created_at
   FROM
     comments
   GROUP BY
     post_id) AS latest_comments
INNER JOIN
  comments
ON
  comments.post_id = latest_comments.post_id AND
  comments.created_at = latest_comments.created_at

如何定义一个查询来返回与 ActiveRecord 相同的结果集？

Answer 1

这不完全等同于您的 SQL，但我相信也应该有效:

Comment.where(
  created_at: Comment.select('max(created_at)').group(:post_id)
)
# Comment Load (6.9ms)  SELECT  "comments".* FROM "comments" WHERE "comments"."created_at" IN (
#   SELECT max(created_at) FROM "comments" GROUP BY "comments"."post_id"
# ) LIMIT $1  [["LIMIT", 11]]