I'm not able to officially release it as I don't have the time to
support it. I can provide the (very scrappy) code if you want to play
around with it, at your own risk! Usage is pretty basic. First,
define model with some aggregates:
class SalaryReport < ActiveRecord::Base
self.aggregates = {
:salary => 'sum(salary)',
:average_salary => 'avg(salary)'
}
end
Then, call group_by(columns) to get something you can turn into a report:
SalaryReport.group_by(:country_id, :department_id)
It will select all the aggregated columns you've defined, as well as
the grouped attributes, so the example above is similar to
SELECT country_id, department_id, sum(salary) salary. avg(salary) average_salary
FROM salary_report
GROUP BY country_id, department_id
You can also include conditions if you wish, similar to those for the
find method:
SalaryReport.group_by(:country_id, :conditions => {:region_id => 'EUROPE'})
Here's the actual code:
module Tomafro::ActiveRecord::GroupBy
def self.included(base)
base.extend(ClassMethods)
end
module ClassMethods
def group_by(*groups)
groups = groups.dup.flatten
options = groups.last.is_a?(Hash) ? groups.pop : {}
scope = scope(:find)
aggregate_columns = self.aggregates.collect do |key, value|
"#{value} #{key}"
end
group_columns = groups.collect(&:to_s)
sql = "SELECT #{(group_columns + aggregate_columns).join(",\n ")} "
sql << "FROM #{table_name} "
add_conditions!(sql, options[:conditions], scope)
sql << " GROUP BY #{group_columns.join(", ")}"
sql << " WITH ROLLUP" if options[:rollup]
records = find_by_sql(sql)
records.each { |record| record.readonly! }
records
end
def aggregates
@aggregates || {}
end
def aggregates=(aggregates)
@aggregates = aggregates
end
end
end
ActiveRecord::Base.send(:include, Tomafro::ActiveRecord::GroupBy)