如何获得轨返回SUM（COLUMNNAME）右数据类型，而不是字符串属性？ [英] How to get rails to return SUM(columnName) attributes with right datatype instead of a string?

问题描述

假设以下形式的查询

  operatingExpenses = Expense.find（：所有，
      {：选择=＆gt;中categories.activityType，categories.name标题，总和（金额）总金额，
      ：加入=＆GT; 内部连接上CATEGORY_ID = categories.id EXPENSE_CATEGORIES类别，
      ：组=＆GT; categories.activityType，categories.name
      ：为了=＆GT; categories.activityType，总金额DESC}
      ）
 

现在量被定义为在数据库模式小数字段。例如在Rails迁移定义是

  CREATE_TABLE：费用做|表|
  TABLE.COLUMN：created_at，：时间戳：空=＆GT;假
  TABLE.COLUMN：描述：字符串：上限=＆GT; 100：空=＆GT;假
  TABLE.COLUMN：额，：十进制：规模=＆GT; 2，：precision =＆GT; 10：空=＆GT;假
  TABLE.COLUMN：CATEGORY_ID，整数，{：空=＆GT;假：默认=＆GT; 1}
结束
 

现在的记录集查询返回失败下面的断言

  assert_equal 800，operatingExpenses [1] .totalAmount

＆LT; 800＆GT;预计却被＆LT;800.00取代。
 

为什么总和/总列返回一个字符串，而不是相同的数据类型的总结数据库列？我想，以避免洒 .to_s 或 .to_f 到处来解决这个问题。任何想法？

我试图让这个彩虹的尽头是一个现金流上市这样的 - 。（对指定DATERANGE ..一个附加条件，以上面列出的查询）

 猫型标题总金额
|操作| ExpCatX001 | 4000 |
| | ExpCatX002 | 200 |
|融资| ExpCatX003 | 1000 |
|投资| ExpCatX004 | 4234 |
| | ExpCatX005 | 201 |
 

解决方案

有关要求基本上全自定义SQL语句的自定义查询（你的发现上面并没有完全抽象的多少你）我想建立一个快速的小新模型重新presents新的信息。即。

 类OperatingExpenseReportDatum
  attr_accessor：类型：标题，总数：

  高清初始化（行）
    从行号的设定值，如
    @total =行[总]。to_f
  结束
结束
 

，然后写一个辅助方法，到模型中，是这样的：

 类消费＆LT; AR :: Base的
  ...
  高清self.operating_expenses
    行= connection.select_allSQL语句HERE
    rows.map {|行| OperatingExpenseReportDatum.new（行）}
  结束
结束
 

那么你的报告生成是所有尼斯：

  #controller
@expenses = Expense.operating_expenses

＃视图
＆LT;％@ expenses.each办|费用| ％＆GT;
  ＆其中;％= expense.type％计算值：＆其中;％= expense.total％GT;
＆LT;％结束％GT;
 

或类似的东西。 ：）

Assume a query of the following form

operatingExpenses = Expense.find(:all,
      {:select=>"categories.activityType, categories.name heading, sum(amount) totalAmount",
      :joins => "inner join expense_categories categories on category_id = categories.id ",
      :group => "categories.activityType, categories.name",
      :order => "categories.activityType, totalAmount DESC"}
      )

Now amount is defined as a decimal field in the database schema. e.g. definition in the Rails Migration would be

create_table :expenses do |table|
  table.column :created_at, :timestamp, :null=>false
  table.column :description, :string, :limit=>100, :null=>false
  table.column :amount, :decimal, :scale=>2, :precision=>10, :null=>false
  table.column :category_id, :integer, {:null=>false, :default =>1} 
end

Now the set of records returned by the query fails the following assert

assert_equal 800, operatingExpenses[1].totalAmount

<800> expected but was <"800.00">.

Why is the Sum/aggregate column returned as a string instead of the same datatype as the summed up database column? I'd like to avoid sprinkling .to_s or .to_f everywhere to get around this. Any ideas ?

What I'm trying to get at the end of this rainbow is a cashflow listing like this - (for a specified daterange.. an additional condition to the query listed above).

   cat Type      Heading      TotalAmount 
|  Operating |  ExpCatX001  | 4000 |
|            |  ExpCatX002  |  200 |
|  Financing |  ExpCatX003  | 1000 |
|  Investing |  ExpCatX004  | 4234 |
|            |  ExpCatX005  |  201 |

解决方案

For custom queries that require basically a whole custom SQL statement (your find above doesn't exactly abstract much from you) I like to set up a quick little new model that represents the new information. i.e.

class OperatingExpenseReportDatum
  attr_accessor :type, :heading, :total

  def initialize(row)
    # set values from row, like
    @total = row["total"].to_f
  end
end

and then write a helper method into the model, something like:

class Expense < AR::Base
  ...
  def self.operating_expenses
    rows = connection.select_all "SQL STATEMENT HERE"
    rows.map { |row| OperatingExpenseReportDatum.new(row) }
  end
end

Then your report generation is all nice:

#controller
@expenses = Expense.operating_expenses

#view
<% @expenses.each do |expense| %>
  <%= expense.type %>: <%= expense.total %>
<% end %>

Or something similar. :)

这篇关于如何获得轨返回SUM（COLUMNNAME）右数据类型，而不是字符串属性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！