ActiveRecord execute raw transaction typecasting?

Question

ActiveRecord::Base.connection.execute(sql)

The results are not typecasted so they are all strings as an example ActiveRecord::Base.connection.execute(sql).entries

=> [{"id" => "1", "length" => "120", "content" => "something"},{"id" => "2", "length" => "200", "content" => "blahblah"}]

Is it possible to execute raw transactions in activerecord and return typecasted results?

Answer 1

Consider manifesting your SQL statement as a view, and creating a new record to interface with the view.

Here's a project where I'm backing AR with a view: https://github.com/michaelkirk/household-account-mgmt/blob/develop/app/models/monthly_report.rb

class CreateMonthlyReports < ActiveRecord::Migration
  def up
    sql = <<-SQL
      create view monthly_reports as
        select date_part('year', created_at) as year, date_part('month', created_at) as month, sum(purchase_amount) as purchases_amount, sum(investment_amount) as investments_amount
          from (
            select * from transactions
              left join
                (select id as purchase_id, amount as purchase_amount from transactions where credit = false)
                as purchases on transactions.id = purchases.purchase_id
              left join
                (select id as investment_id, amount as investment_amount from transactions where credit = true)
                as investments on transactions.id = investments.investment_id)
              as classified_transactions
          group by year, month
          order by year, month
    SQL

    execute(sql)
  end

  def down
    sql = <<-SQL
      drop view monthly_reports
    SQL

    execute(sql)
  end

Then, since you've abstracted your complexity into a database view, which for all AR's intents/purposes works like a table your model and controller look completely vanilla.

class MonthlyReport < ActiveRecord::Base
  MONTHS = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]

  def time_period
    "#{month} #{year}"
  end

  def month
    MONTHS[self[:month] - 1]
  end

  def year
    self[:year].to_i
  end

end

Then you can do things like

class MonthlyReportsController < ApplicationController
  def index
    @monthly_reports = MonthlyReport.all
  end
end

Note that because this is a DB view, you won't be able to do inserts. I'm not sure what would happen if you tried.