DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Custom SQL Query Without Find_by_sql

06.02.2008
| 11612 views |
  • submit to reddit
        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