01 Oct 2011

Using will_paginate and activerecord-sqlserver-adapter with SQL Server

Having Rails and Microsoft SQL Server communicate is much easier than it once was thanks to projects like TinyTDS, but sometimes things still don't work exactly right. We ran into an issue where pagination simply would not work. Below is what we came up with after some research.

This solution was based on the following:

This patch was tested on an app using Rails 2.3.x and SQL Server Adapter 2.3.15.

config/environment.rb
  require "#{RAILS_ROOT}/lib/monkey_patch_sql2005_limit.rb"
lib/monkey_patch_sql2005_limit.rb
  # monkey-patching SQLServerAdapter to support SQL Server 2005-style pagination
  # based on http://alexle.net/archives/tag/mislav-will_paginate-sqlserver-2005
  # - 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

  # produces paginated sql ( if set to 2 results per page) that looks like this:
  #
  # WITH cols AS ( 
  #   SELECT ROW_NUMBER() OVER(ORDER BY training_types.id) as seq, 
  #     * FROM [training_types] 
  # ) 
  # SELECT * FROM cols WHERE seq BETWEEN 1 AND 2 ORDER BY seq;

  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