Over a million developers have joined DZone.

Custom SQL Query Without Find_by_sql

·
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
Topics:

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}