CODEWITHSUNDEEP
mysqlruby-on-railsruby-on-rails-3activerecordgroup-by
content01
content01

Reputation: 3225

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

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!

Upvotes: 6

Views: 5754

Answers (2)

Sia
Sia

Reputation: 9212

If you're using PostgreSQL, you can also use the date_trunc function (splitting lines for readability but probably won't work in real Ruby):

Table.select('count(id), date_trunc('month', created_at) as month')
  .where('published_state LIKE ?', 'published').group('month')

Basically, it truncates or cuts off the parts of the date that are no longer significant. For example, if you choose 'month', it still preserves the year with the month but not the day and time.

I would probably also order it like so:

Table.select('count(id), date_trunc('month', created_at) as month')
  .where('published_state LIKE ?', 'published').group('month')
  .order('month' DESC)
  .limit(24)

Read more in the docs here.

Upvotes: 0

gregates
gregates

Reputation: 6714

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?

Upvotes: 17

Related Questions