Currently Reading

ActiveRecord is great.  It helps hide away a lot of trivial SQL when creating applications with Rails.  It’s fairly good at what it does to and it’s constantly growing and improving.  However, it falls short when it comes to creating a query that combines multiple calculations.  As an example, let’s get an array of the total sales for every month for the last 12 months of Orders.

totals = []

12.times do |i|
  month = i.months.ago
  totals << Order.where('created_at >= ? AND created_at <= ?', month.beginning_of_month, month.end_of_month).sum(:total)
end

This is going to run 12 queries in order to get the data.  That’s 12 round trips on the same table to accomplish the same goal.  On a large table (500K+ rows) this can be very expensive.  This was a problem I ran into when generating real time report data on a recent project.  So, I extended ActiveRecord with the following two methods to allow multiple SUM and SUM by CASE operations in a single query.

module ActiveRecordExtension
 
  extend ActiveSupport::Concern
 
  module ClassMethods
 
    # Add a SELECT SUM() by CASE to query.
    #
    # @param cases [Hash(:attr, :val, :operand), Array(Hash(:attr, :val, :operator))]
    # @param sum_field [Symbol, String]
    # @param as [String, nil]
    def select_sum_case(cases, sum_field, as = nil)
      cases = [cases] unless cases.is_a? Array
      sum_field = sum_field.to_s
      case_strings = []
      case_as = []
 
      cases.each do |case_hash|
        case_attr = case_hash[:attr].to_s
        case_as << "#{case_attr}_#{case_hash[:val]}"
        case_strings << "#{case_attr} #{(case_hash[:operand] ? case_hash[:operand] : '=')} '#{case_hash[:val]}'"
      end
 
      if as.blank?
        as = "sum_#{"#{case_as.join('_')}_#{sum_field}".downcase.gsub(' ', '_').gsub(/\W/,'')}"
      else
        as = as.to_s
      end
 
      return select("COALESCE(SUM(CASE WHEN #{case_strings.join(' AND ')} THEN #{sum_field} ELSE 0 END), 0) AS #{as}")
    end
 
    # Add a SELECT SUM() to query.
    #
    # @param sum_field [Symbol, String]
    # @param as [String, nil]
    def select_sum(sum_field, as = nil)
      sum_field = sum_field.to_s
 
      if as.blank?
        as = "sum_#{sum_field}".downcase.gsub(' ', '_').gsub(/\W/,'')
      else
        as = as.to_s
      end
 
      return select("COALESCE(SUM(#{sum_field}), 0) AS #{as}")
    end
 
  end
end
 
# include the extension
ActiveRecord::Base.send(:include, ActiveRecordExtension)

With these new methods, we rewrite the above, and grab all the values with a single query.

totals = Order.all

12.times do |i|
  month = i.months.ago
  totals = totals.select_sum_case([{ :created_at, month.beginning_of_month, '>=' }, { :created_at, month.end_of_month, '<='}], :total)
end

totals.first.attributes.except('id')

Now we achieved the same result with a single query.  Make no mistake, this can save a lot of time.  In the real time report generation case, execution time was cut from 70-98%!  Reports that required 150+ queries could be condensed into 2 queries.  This use case is on the extreme end of number crunching with Rails, so results may not be typical.  However, even the simple example above, 12 round trips down to 1, will definitely save some time.

The above example demonstrates the select_sum_case() method.  You specify each condition or case, as a hash and pass them as an array along with the attribute to be summed and an optional name for the sum.  There is also a select_sum() statement which simply takes an attribute name to sum and an optional name for the summed value.

The gist of this can be found here – https://gist.github.com/475dc21c78fa700f924e.git

Just place it in /lib or anywhere you want.