Custom SQL Query Without Find_by_sql

• Tweet
• 

        What if you need to call a custom query in Rails? In most cases you'll be content with ditching ActiveRecord::Base.find and going with .find_by_sql. But instantiating an Active Record per row is expensive. What if you want something faster, but you still want an OO feel? Pop this in your environment.rb (or a separate file) and give this a whirl:

# OO-ified connection.select_all
require 'ostruct'
module ActiveRecord
  class Base
    class << self
      def select_all(query)
        rows = connection.select_all(query)
        rows.map! do |row|
          row = OpenStruct.new(row)
          table = row.send(:table)
          table.each {|k, v| table[k] = select_type_cast(v) }
          row
        end
        rows
      end
      def select_one(query)
        select_all(query).first
      end
      def select_value(query)
        select_type_cast(connection.select_value(query))
      end
      def select_type_cast(v)
        return unless v
        if md = v.match(/^(\d{4})-(\d{2})-(\d{2})$/)
          Date.new(*md.captures.map(&:to_i)) rescue v
        elsif md = v.match(/^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})$/)
          Time.local(*md.captures.map(&:to_i)) rescue v
        elsif v =~ /^\d+$/
          v.to_i
        elsif v =~ /^\d+(?:\.\d+)+$/
          v.to_f
        else
          v
        end
      end
    end
  end
end

Tags:
• ActiveRecord
• extensions
• Rails
• SQL