Custom SQL query without find_by_sql

mcmire () — Mon, 02 Jun 2008 13:04:39 GMT

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

DZone Snippets: sql code

Custom SQL query without find_by_sql