SELECT SUM and SELECT SUM CASE ActiveRecord methods

active_record_extension.rb

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)