Elegant PostgreSQL Group by for Ruby on Rails / ActiveRecord
- by digitalfrost
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)
