CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecordruby-on-rails-4
Erik Trautman
Erik Trautman

Reputation: 6029

How to order grouped columns in an ActiveRecord Postgres query

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!

Upvotes: 0

Views: 771

Answers (1)

BellevueBob
BellevueBob

Reputation: 9618

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

Upvotes: 1

Related Questions