用于 Ruby on Rails / ActiveRecord 的优雅 PostgreSQL Group by

尝试使用 PostgreSQL 检索按日期分组的 ActiveRecord 对象数组。

更具体地说，我正在尝试翻译以下 MySQL 查询：

@posts = Post.all(:group => "date(date)", 
   :conditions => ["location_id = ? and published = ?", @location.id, true], 
   :order => "created_at DESC")

我知道 PostgreSQL 对 SQL 标准的解释比 MySQL 更严格，因此这种类型的查询不起作用......并且已经阅读了 StackOverflow 和其他地方关于该主题的许多帖子 - 但它们似乎都不是关于这个问题的明确答案

我尝试过使用 group by 和不同子句进行各种查询组合，但没有太多乐趣 - 目前我有一个相当不优雅的 hack，尽管它有效，但当我看到它时，我会脸红。

使用 Rails 和 PostgreSQL 进行此类查询的正确方法是什么？ （忽略了这一事实，这肯定应该在 ActiveRecord 级别进行抽象）

您要在此处使用的 PostgreSQL 功能是DISTINCT ON。通过 ActiveRecord 进行此查询有两种基本方法。

第一种方法是只指定:select and :order选项。当您有一个相当简单的查询而没有任何信息时，这非常有用:joins or :include.

Post.all(
  :select => 'DISTINCT ON (date::date) *',
  :order => 'date::date DESC, created_at DESC'
)

如果您有一个更复杂的查询，其中 ActiveRecord 生成自己的查询SELECT子句中，您可以使用子查询来选择目标记录。

Post.all(
  :joins => 'INNER JOIN (SELECT DISTINCT ON (date::date) id FROM posts ORDER BY date::date DESC, created_at DESC) x ON x.id = posts.id'
)

请注意，根据您的数据，这可能比第一种方法慢一些。我只会在需要时使用此方法。请务必使用类似生产的数据进行基准测试。