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

Perform a Rails find() and iterate over the resulting records in groups

mcmire () — Mon, 05 May 2008 15:35:43 GMT



module ActiveRecord

  class Base

    # This method lets you iterate over the results of a .find, in groups.

    # (Basically an interface to LIMIT.)

    # Anything you can pass as options to .find, you can pass here. 

    # Example 1:

    #   Order.each_by(100, :conditions => { :cc_processed_at => nil }) do |order|

    #     # do stuff with order

    #   end

    # Example 2:

    #   Person.each_by(50, :order => 'name') do |person, index|

    #     # do stuff with person and index

    #   end

    # Pass :update => true in the options to print a message before each group is

    # fetched from the db.

    #

    # Author: Elliot Winkler 

    # Source: http://snippets.dzone.com/posts/show/5461

    def self.each_by(group_size, options={}, &blk)

      update = options.delete(:update) || false

      num_records = count(options.except(:from))

      return 0 if num_records == 0

      #raise "Number of records: #{num_records}"

      also_pass_offset = (blk.arity == 2)

      0.step(num_records, group_size) do |offset|

        find_options = { :offset => offset, :limit => group_size }.merge(options)

        if update

          if num_records == 1

            puts ">> Reading the only record."

          else

            start_offset = offset + 1

            end_offset   = offset + group_size

            end_offset   = num_records if num_records < end_offset

            puts ">> Reading records #{start_offset}-#{end_offset}."

          end

        end 

        find(:all, find_options).each do |record|

          also_pass_offset ? blk.call(record, offset) : blk.call(record)

        end

      end

      num_records

    end

  end

end

Also see:

* http://weblog.jamisbuck.org/2007/4/6/faking-cursors-in-activerecord
* http://weblog.jamisbuck.org/2007/2/28/poor-man-s-pagination

DZone Snippets: activerecord code

Custom SQL query without find_by_sql

Perform a Rails find() and iterate over the resulting records in groups