Skip to content

Instantly share code, notes, and snippets.

@kpheasey
Last active August 29, 2015 14:14
Show Gist options
  • Save kpheasey/475dc21c78fa700f924e to your computer and use it in GitHub Desktop.
Save kpheasey/475dc21c78fa700f924e to your computer and use it in GitHub Desktop.
SELECT SUM and SELECT SUM CASE ActiveRecord methods
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment