How to order grouped columns in an ActiveRecord Postgres query

Question

I'm trying to build a sidebar for a blog that contains pretty standard archive information like:

August 2013: 3 posts

July 2013: 5 posts

June 2013: 4 posts

...etc

What ActiveRecord query will provide this information (month, year, count) sorted reverse-chronologically?

The Post model is very simple -- title, body, created_at, modified_at columns. I'm trying to write the ActiveRecord/Postgres query that gives me the count of posts grouped by month and year (as listed above). The following query does just that, successfully:

Post.select('count(*) as count','extract(year from created_at) as year', 'extract(month from created_at) as month').group('year','month')

But I want to explicitly sort the columns reverse chronologically (so August 2013 is above July 2013 in the listing) and that's where everything goes haywire. I tried the following query unsuccessfully, just to get started:

Post.select('count(*) as count','extract(year from created_at) as year', 'extract(month from created_at) as month').group('year','month').order(:year => :desc)

It produces the following SQL:

SELECT count(*) as count, extract(year from created_at) as year, extract(month from created_at) as month FROM "posts" GROUP BY year, month ORDER BY "posts"."year" DESC

And the following error:

PG::UndefinedColumn: ERROR: column posts.year does not exist

The query actually runs if I order by count using .order(:count => :desc) but it doesn't seem to actually be ordering in the way I would have expected (switching to :asc does nothing different).

I've scoured SO and google but to no avail. I've also tried sorting by created_at but it throws a ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "posts.created_at" must appear in the GROUP BY clause or be used in an aggregate function error. Ideally, I'd run a simple Post.order(:created_at => :desc), THEN run the grouping queries on that nicely ordered result, but I don't know how.

Very lost... how do I retrieve the posts with year and month and count columns but order the resulting groups reverse chronologically?

Many thanks in advance for your help!

Does .order('year desc') work? Which version of Rails is this? My 3.2 doesn't like your select or order calls at all. — mu is too short
– mu is too short, Commented Sep 2, 2013 at 0:48
That explains my problems testing things out, I don't have a Rails4 set up handy. I'm guessing that .order('year desc') will work as AR should pass the string through to the SQL unmolested (unlike when you use a Hash where AR thinks it knows what it is doing but doesn't really). — mu is too short
– mu is too short, Commented Sep 2, 2013 at 1:24

BellevueBob · Accepted Answer · 2013-09-02 00:51:45Z

1

Not all databases allow you to reference a derived column name in a GROUP or ORDER clause. I don't know PostgreSQL myself, but perhaps it supports relative column references. Try this:

SELECT count(*) as count
     , extract(year from created_at) as year
     , extract(month from created_at) as month 
FROM "posts" 
GROUP BY 2, 3 
ORDER BY 2 DESC, 3 DESC

If that doesn't work, this should:

SELECT count(*) as count
     , extract(year from created_at) as year
     , extract(month from created_at) as month 
FROM "posts" 
GROUP BY extract(year from created_at), extract(month from created_at)
ORDER BY extract(year from created_at) DESC, extract(month from created_at) DESC

answered Sep 2, 2013 at 0:51

BellevueBob

9,6345 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to order grouped columns in an ActiveRecord Postgres query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related