优雅的PostgreSQL集团通过为Ruby on Rails的/ ActiveRecord的 [英] Elegant PostgreSQL Group by for Ruby on Rails / ActiveRecord

问题描述

试图检索与PostgreSQL的分组按日期ActiveRecord对象的数组。

Trying to retrieve an array of ActiveRecord Objects grouped by date with PostgreSQL.

更具体地说我想翻译以下MySQL查询：

More specifically I'm trying to translate the following MySQL query:

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

据我所知，PostgreSQL的跨$ P $的SQL标准ptation是比MySQL和因此这种类型的查询将无法工作更严格......，看了一些关于StackOverflow的职位，并在这个问题在其他地方 - 但他们都不是明确的答案就这个问题

I am aware that PostgreSQL interpretation of the SQL standard is stricter than MySQL and that consequently this type of query won't work...and have read a number of posts on StackOverflow and elsewhere on the subject - but none of them seem to be the definitive answer on this subject

我试过用组查询和不同的子句的各种组合没有太多的喜悦 - 和目前我有一个相当不雅砍这虽然作品让我看它我脸红。

I've tried various combinations of queries with group by and distinct clauses without much joy - and for the moment I have a rather inelegant hack which although works makes me blush when I look at it.

什么是正确的方法，使使用Rails和PostgreSQL这样的查询？ （忽略了肯定这应该是抽象化了的ActiveRecord的水平的事实）

What is the proper way to make such a query with Rails and PostgreSQL ? (Ignoring the fact that surely this should be abstracted away at the ActiveRecord Level)

推荐答案

您想在这里使用PostgreSQL的功能是的 DISTINCT ON 。有两种基本的方式去使通过ActiveRecord的这个查询。

The PostgreSQL feature you want to use here is DISTINCT ON. There are two basic ways to go about making this query via ActiveRecord.

第一种方法是只指定：选择和：为了选项。这个伟大的工程，当你有一个有相当简单的查询无：加入或：包括

The first method is to just specify the :select and :order options. This works great when you have a fairly simple query with no :joins or :include.

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

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

If you have a more complex query where ActiveRecord generates its own SELECT clause, you can use a subquery to select the target records.

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'
)

请注意，这可能是比具体取决于您的数据的第一个方法一个公平的有点慢。我想，如果只需要使用此方法。一定要与基准生产数据等。

Note that this could be a fair bit slower than the first method depending on your data. I would only use this method if required. Be sure to benchmark with production-like data.

这篇关于优雅的PostgreSQL集团通过为Ruby on Rails的/ ActiveRecord的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！