ruby-on-rails – Rails ActiveRecord group_by和sum db结果,用
发布时间：2020-12-17 04:05:08 所属栏目：百科 来源：网络整理
导读：我是RoR / Ruby的新手,我正在使用Lazy High Charts gem根据一些数据库信息生成一些purdy图表. 我已经尝试过前一个问题中提供的答案,但我仍然对如何做到这一点感到困惑. 我需要按月/年(例如; 2012年8月)对amount_used,billed_amount和group进行求和 最终结果
|
我是RoR /
Ruby的新手,我正在使用Lazy High Charts gem根据一些数据库信息生成一些purdy图表.
我已经尝试过前一个问题中提供的答案,但我仍然对如何做到这一点感到困惑. 我需要按月/年(例如; 2012年8月)对amount_used,billed_amount和group进行求和 最终结果类似于双轴图表,其中包含两个系列“已使用金额”和“成本”.此信息特定于某个account_id. 发票表 +---------------+--------------+------+-----+---------+----------------+ | 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 | | +---------------+--------------+------+-----+---------+----------------+ 控制器图表代码 @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
解决方法
看起来你已经掌握了一切.以下是从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)
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |