How to group by day and year in Active Record in Rails3

Question

I'm trying to recreate the following mysql query in rails 3

 select count(id), year(created_at), month(created_at) from table where published_state = 'published' group by year(created_at), month(created_at);

I've tried something like:

results = Table.select('count(id), year(created_at), month(created_at)').where('published_state LIKE ?', 'published').group('year(created_at), month(created_at)')

but it doesn't return what I want.

I just get this in return

[#<Table >, #<Table >, #<Table >, #<Table >, #<Table >, #<Table >,
 #<Table >, #<Table >, #<Table >, #<Table >, #<Table >, #<Table >, #<Table >, #<Table >, #<Table >, #<Table >, #<Table >]

How can I accomplish this?

Thanks!

Answer 1

Try this:

Table.where(:published_state => 'published').
  group('year(created_at)').group('month(created_at)').count(:id)

The format of the results is not quite what I was expecting (it's a hash with array keys like [year, month] and count values) but maybe it will serve your purposes?