How to get rails to return SUM(columnName) attributes with right datatype instead of a string?

Question

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 |

Answer 1

Active record is trying to return "Expense" objects built from the results of your query. But expense objects don't have a totalAmount field, so this is unrequested "bonus data" as far as ActiveRecord can tell. It coud just throw it out, but instead it keeps it for you, with the most forgiving type assumptions it can.

My suggestions:

• Don't do this; it isn't the way Active record is supposed to work; the total of the details belongs to whatever is rolling them up; it isn't a detail object itself.
• If you must treat it as a detail, name the field amount so ActiveRecord knows what to do with it.

• If you must have the total as its own field, add an accessor in your Expense model like so:

  def total_amount
      totalAmount.to_f
      end
  

  and use it like so: operatingExpenses[1].total_amount in your code.

Answer 2

Because with :select Rails gives you no way of telling it (or the driver) to what kind of native type to bind the result set's fields (once it's all retrieved -- although most of not all drivers will know, internally, what the column SQL types are) -- so it's all recorded as strings and the SQL type information is generally thrown away. This applies to all fields all the time. You have to perform the conversion manually (to_f), just like Rails' ActiveRecord.instantiate method has to, internally, when you use plain ActiveRecord.find without :select or :joins (and it has to populate a non-string attribute.)

You may confirm this by looking at your database driver's select_raw method.

Cheers, V.

Answer 3

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. :)