monkey-patch for SQLServerAdapter to support SQL Server 2005-style pagination

monkey_patch_sql2005_limit.rb

# monkey-patching SQLServerAdapter to support SQL Server 2005-style pagination
# inspired by 
# - http://alexle.net/archives/tag/mislav-will_paginate-sqlserver-2005
# - http://www.sqlservercentral.com/articles/T-SQL/66030/
# - http://stackoverflow.com/questions/4871523/sql-server-2008-r2-pagination/4871591#4871591
# - https://gist.github.com/335683

# place the following at the bottom of environment.rb: 
# require "#{RAILS_ROOT}/lib/monkey_patch_sql2005_limit.rb"

module ActiveRecord


  module ConnectionAdapters
    class SQLServerAdapter  < AbstractAdapter
      def add_limit_offset!(sql, options)
        if options[:limit].blank?
        
          super
        
        else
          options[:offset] ||= 0
        
          options[:limit] ||= 1000

          table = sql.match(/FROM\s+\[(\w+)/i)[1]
          options[:order] ||= "#{table}.id"
          sql.sub!(/ORDER BY.*$/i, '')
        
          sql.sub!(/SELECT/i, "WITH cols AS ( SELECT ROW_NUMBER() OVER(ORDER BY #{options[:order]}) as seq, ")
       
          sql << ") SELECT * FROM cols WHERE seq BETWEEN #{options[:offset]+1} AND #{options[:offset]+options[:limit]} ORDER BY seq"
    
          sql
        end
     
      end
      
    end
  end
end