views:

45

answers:

0

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

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

@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 querry with Rails and PostgreSQL ? (Ignoring the fact that surely this should be abstracted away at the ActiveRecord Level)