假设以下形式的查询
operatingExpenses = Expense.find(:所有,
{:选择=>中categories.activityType,categories.name标题,总和(金额)总金额,
:加入=> 内部连接上CATEGORY_ID = categories.id EXPENSE_CATEGORIES类别,
:组=> categories.activityType,categories.name
:为了=> categories.activityType,总金额DESC}
)
现在量被定义为在数据库模式小数字段。例如在Rails迁移定义是
CREATE_TABLE:费用做|表|
TABLE.COLUMN:created_at,:时间戳:空=>假
TABLE.COLUMN:描述:字符串:上限=> 100:空=>假
TABLE.COLUMN:额,:十进制:规模=> 2,:precision => 10:空=>假
TABLE.COLUMN:CATEGORY_ID,整数,{:空=>假:默认=> 1}
结束
现在的记录集查询返回失败下面的断言
assert_equal 800,operatingExpenses [1] .totalAmount
< 800>预计却被<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
结束
结束
,然后写一个辅助方法,到模型中,是这样的:
类消费< AR :: Base的
...
高清self.operating_expenses
行= connection.select_allSQL语句HERE
rows.map {|行| OperatingExpenseReportDatum.new(行)}
结束
结束
那么你的报告生成是所有尼斯:
#controller
@expenses = Expense.operating_expenses
#视图
<%@ expenses.each办|费用| %>
&其中;%= expense.type%计算值:&其中;%= expense.total%GT;
<%结束%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. :)