如何让 rails 以正确的数据类型而不是字符串返回 SUM(columnName) 属性? [英] How to get rails to return SUM(columnName) attributes with right datatype instead of a string?

问题描述

假设有以下形式的查询

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"}
      )

现在数量被定义为数据库架构中的十进制字段.例如Rails 迁移中的定义是

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">.

为什么 Sum/aggregate 列作为字符串返回，而不是与汇总数据库列的数据类型相同?我想避免将 .to_s 或 .to_f 洒在任何地方来解决这个问题.有什么想法吗?

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 |

推荐答案

对于基本上需要整个自定义 SQL 语句的自定义查询(您上面的发现并没有完全从您那里抽象出来)我喜欢设置一个快速的小新代表新信息的模型.即

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 %>

或类似的东西.:)

这篇关于如何让 rails 以正确的数据类型而不是字符串返回 SUM(columnName) 属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！