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屋！