Rails/ActiveRecord Group by month+year with counts

Question

I have a table of Albums that has a date column named release_date.

I want to get a list of all the month + year combinations present along with the number of albums released in that month/year.

So, the output might be something like:

• November 2016 - 11
• October 2016 - 4
• July 2016 - 19
• December 2015 - 2

Ruby 2.3.1 w/ Rails 5 on Postgres 9.6, FWIW.

Wiper

It is quite simple using SQL: select to_char(release_date, 'Month YYYY'), count(*) from albums group by 1; – Carmon

Answer 1

I'm assuming your table is singular Album per Rails convention. If not, consider changing it.

 Album.all.map { |album| [Date::MONTHNAMES[album.date.month], album.date.year].join(' ') }
  .each_with_object(Hash.new(0)) { |month_year, counts| counts[month_year] += 1 }

Explanation:

The .map method iterates over the albums and returns an array of strings consisting of ["month year", "month year", ... ].

The .each_with_object method is a standard counting algorithm that returns a hash with a count for each unique array item.

Deirdre

Answer 2

Database layer is where this task belongs, not Ruby:

Album.group("TO_CHAR(release_date, 'Month YYYY')").count

Why using database layer? Simply because it is lightning fast compared to nearly anything else, it is resource-efficient especially compared to Ruby, it scales perfectly and because having tons of Album records you can simply overload memory and never actually finish the processing.

Daryldaryle