Rails ActiveRecord group_by & sum db results for use with Lazy HighCharts

Question

I am completely new to RoR / Ruby, and I use the Lazy High Charts gem to create some clean charts based on some database information.

I tried the answers that were provided in the previous question, but I'm still a bit confused as to how to do this.

I need to sum the amount_used and billed_amount and group by month / year (e.g. Aug / 2012)

The end result will be something like a double-axis chart with two series of “Quantity Used” and “Cost”. This information relates to a specific account_id.

Invoice table

+---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | account_id | int(11) | YES | | NULL | | | invoice_date | varchar(255) | YES | | NULL | | | amount_used | float | YES | | NULL | | | billed_amount | float | YES | | NULL | | | comments | text | YES | | NULL | | | created_at | datetime | NO | | NULL | | | updated_at | datetime | NO | | NULL | | +---------------+--------------+------+-----+---------+----------------+ 

Controller Chart Code

 @account = Account.find(params[:id]) @invoices = Invoice.where("account_id = #{@account.id}").order("invoice_date DESC") @h = LazyHighCharts::HighChart.new('area') do |f| f.options[:chart][:defaultSeriesType] = "area" #Sample dates right now, should be the grouped_by :invoice_date f.xAxis( :categories => ['May', 'Jun', 'Jul'] ) f.yAxis([ { :title => { :text => "Amount Used" } }, { :title => { :text => "Cost" }, :opposite => true } ]) #Sample data right now, should be the summed amounts of the :amount_used correpsonding for each above grouped invoice_date f.series(:name => "Amount Used", :data => [100,300,500] ) #Sample data right now, should be the summed amounts of the :billed_amount correpsonding for each above grouped invoice date f.series(:name => "Cost", :yAxis => 1, :data => [200,400,600] ) end 

Answer 1

Everything seems to be in place. Here is how you can extract data from db:

 @aggregated_invoices = Invoice. where(:account_id => params[:id]). order("invoice_date DESC"). group("invoice_date"). select("DATE_FORMAT(invoice_date, '%Y-%m-01') AS invoice_date, sum(amount_used) AS amount_used, sum(billed_amount) AS billed_amount") # Then use these instead of sample data: @categories = @aggregated_invoices.map {|i| i.invoice_date.strftime("%b/%Y")} @amount_used_data = @aggregated_invoices.map(&:amount_used) @billed_amount_data = @aggregated_invoices.map(&:billed_amount)