Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rails/ActiveRecord Group by month+year with counts

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.

like image 961
Shpigford Avatar asked Nov 10 '16 23:11

Shpigford


2 Answers

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.

like image 186
Andrey Deineko Avatar answered Sep 30 '22 04:09

Andrey Deineko


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.

like image 40
moveson Avatar answered Sep 30 '22 06:09

moveson