Rails ActiveRecord group_by& sum db结果用于Lazy HighCharts [英] Rails ActiveRecord group_by & sum db results for use with Lazy HighCharts
问题描述
我已经尝试过答案了,我已经尝试了答案这是在前一个问题中提供的,但我仍然有点困惑,因为如何做到这一点。
我需要将amount_used,billed_amount和group by month /年(例如:2012年8月)
最终结果将类似于具有两个系列已用金额和成本的双轴图表。信息是特定于某个account_id的。
发票表
+ ------------- - + -------------- + ------ + ------ + --------- --------- + ------- +
|字段|类型|空| Key |默认|额外|
+ --------------- + -------------- + ------ + ----- + - -------- + ---------------- +
| id | int(11)| NO | PRI | NULL | auto_increment |
| account_id | int(11)|是| | NULL | |
| invoice_date | varchar(255)|是| | NULL | |
| amount_used | float |是| | NULL | |
| billed_amount | float |是| | NULL | |
|评论|文字|是| | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
+ --------------- + -------------- + ------ + ----- + - -------- + ---------------- +
控制器图表代码
@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日期现在,应该是grouped_by:invoice_date
f.xAxis(:categories => ['
f.yAxis([
{
:title => {:text =>已用金额)
},
{
:title => {:text =>Cost},
:opposite => true
}
])
#Sample数据现在应该是上述每个分组invoice_date
f.series(:name =>Amount Used,:data => [100,300,500])的amount_used correpsonding的总和:
#Sample数据应该是上述每个分组发票日期的billed_amount correpsonding的总和
f.series(:name =>Cost,:yAxis => 1, :data => [200,400,600])
end
看起来你已经拥有了一切。以下是如何从数据库中提取数据:
@aggregated_invoices =发票。
其中(:account_id => params [:id])。
订单(invoice_date DESC)。
组(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)
#这些而不是样本数据:
@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)
I am completely new to RoR/Ruby and i am using Lazy High Charts gem to generate some purdy charts based on some database information.
I have tried the answers that were provided in a previous question but i am still a bit confused as to how to do this..
I need to sum amount_used, and billed_amount and group by month/year (e.g; Aug/2012)
The end result will be something similar to a dual axis chart with two series "Amount Used", and "Cost".. This information is specific to a certain account_id.
Invoices 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
It looks like you have everything in place. Here's how you can pull 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)
这篇关于Rails ActiveRecord group_by& sum db结果用于Lazy HighCharts的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!