Elegant PostgreSQL Group by for Ruby on Rails / ActiveRecord

Question

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

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

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.

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)

Answer 1

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

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.

Answer 2

My solution:

def self.columns_list
   column_names.collect { |c| "#{table_name}.#{c}" }.join(",")
 end

 scope :selling, joins(:products).group(columns_list)

Simple and repeatable.