Groupdate - A Ruby Gem For Grouping By Month or Year

I love stats and analytics pages -- it's why I got into programming in the first place. Before I knew Ruby, I had a list of saved SQL queries in TextExpander I would paste into Sequel Pro to generate reports.

When I started learning Rails, I had a tough time translating what I knew about SQL into ActiveRecord. ActiveRecord seemed slow, both in writing it and in running it. So early on, there was a lot of this littered throughout my models:

Model.find_by_sql(****)

A few days ago, Andrew Kane (ankane) released a gem, called "Groupdate" that simplifies the way to group objects by timeframes (by day, week, month, or year).

Here's a horrific slice of code I wrote that groups donations by the last 12 months and sums up the gross amount for that month:

def self.last_12_monthly
  start_date = (Time.now - 11.months).beginning_of_month
  end_date   = Time.now.end_of_month
  h = Donation.calculate(:sum, :gross_amount, conditions: {
    created_at: (start_date..end_date) },
    order:      "date_trunc('month', created_at)",
    group:      ["date_trunc('month', created_at)"])
  h = h.to_a.map {|k,v| [k.to_date, v]}
  num_of_months = 0..11
  m = Hash[(num_of_months.to_a.reverse).map { |month| [month.months.ago.beginning_of_month.to_date, 0]}].merge(Hash[h])
  Hash[*m.to_a.flatten]
end

Ugly right? Here's the new code:

def self.last_12_months
  range = (Time.now.beginning_of_month - 11.months)..Time.now.end_of_month
  group_by_month(:created_at, Time.zone, range).sum(:gross_amount)
end

Simple and readable.

If you're a developer, you're probably wondering why I wrote such bad code. Well if there weren't any objects found for a particular month, instead getting back a result of '0', I just wouldn't get any data back for that month. The graphing library I was using couldn't have missing months, so I needed to pad the missing months with a 0.

And that's one of the things I love about Groupdate. If you pass in a range argument, Groupdate will return '0' for any month where an object was not created.

The second best thing I love about the gem, is that it supports both PostgreSQL and MySQL. I've used both databases and it's frustrating to search for something, find out it's exactly what you need, but it's for MySQL only...

You can find more documentation about Groupdate here: https://github.com/ankane/groupdate

Thank you Andrew for your contribution to the open source community. I'm appreciative.