Allows will_paginate to work with the row_number function for both normal pagination and when passed custom sql through the paginate_by_sql method

will_paginate_init.rb

module ActiveRecord
  module Associations
    module ClassMethods
      def construct_finder_sql_for_association_limiting(options, join_dependency)
        scope       = scope(:find)
        
        # Only join tables referenced in order or conditions since this is particularly slow on the pre-query.
        tables_from_conditions = conditions_tables(options)
        tables_from_order      = order_tables(options)
        all_tables             = tables_from_conditions + tables_from_order
        distinct_join_associations = all_tables.uniq.map{|table|
          join_dependency.joins_for_table_name(table)
        }.flatten.compact.uniq
        
        order = options[:order]
        if scoped_order = (scope && scope[:order])
          order = order ? "#{order}, #{scoped_order}" : scoped_order
        end
        
        is_distinct = !options[:joins].blank? || include_eager_conditions?(options, tables_from_conditions) || include_eager_order?(options, tables_from_order)
        
        sql = "SELECT "
        if is_distinct
          sql << connection.distinct("#{connection.quote_table_name table_name}.#{primary_key}", order)
          #NOTE: jonk (04/17/2010) => append the order into the select so that it is valid in the order by clause
          if sql.match(/ORDER BY/i)
            sql << ", #{order.gsub(/\s+desc|\s+asc/i,'')}"
          end
        else
          #TODO: jonk (04/17/2010) => not sure what to do here
          sql << primary_key
        end
        sql << " FROM #{connection.quote_table_name table_name} "
        
        if is_distinct
          sql << distinct_join_associations.collect { |assoc| assoc.association_join }.join
          add_joins!(sql, options[:joins], scope)
        end
        
        add_conditions!(sql, options[:conditions], scope)
        
        add_group!(sql, options[:group], options[:having], scope)
        
        if order && is_distinct
          connection.add_order_by_for_association_limiting!(sql, :order => order)
        else
          add_order!(sql, options[:order], scope)
        end
        
        add_limit!(sql, options, scope)
        
        return sanitize_sql(sql)
      end
    end
  end
end


# monkey-patching SQLServerAdapter to support SQL Server 2005-style pagination
module ActiveRecord
  module ConnectionAdapters
    class SQLServerAdapter
      def find_table_primary_key_columns(table_name)
        @find_table_primary_key_columns_cache ||= {}
        if @find_table_primary_key_columns_cache[table_name].nil?
          
          table_name = table_name.split('.').last.delete("[]")
          sql = "SELECT KU.COLUMN_NAME
                  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
                  ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
                  TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
                  WHERE KU.TABLE_NAME = '#{table_name}'
                  ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION"
          primary_columns = select_rows(sql)
          
          @find_table_primary_key_columns_cache[table_name] =
            primary_columns.collect {|row| "[#{table_name}].[#{row[0]}]"}.join ', '
        end
        
        @find_table_primary_key_columns_cache[table_name]
      end
      
      
      def add_limit_offset!(sql, options)
        #NOTE: jonk (04/14/2010) => for debugging the sql before it is being transformed.
        # if options[:raise] == true
        #   raise options.inspect + ' |--| ' + sql.to_s
        # end
                
        #NOTE: jonk (03/17/2010) => add the ability to parse out and re-apply the distinct keyword in the proper place if it's in a query
        options_distinct = "DISTINCT" if sql.match(/DISTINCT/i)
        #NOTE: jonk (03/17/2010) => remove the word distinct from the query before re-applying it below with the #{options_distinct} variable
        sql.sub!(/DISTINCT\s+/i, '')
        
        
        if options[:offset] #we'll need to relocate the order by if one exists and create one if it doesn't
          options[:order] ||= if order_by = sql.match(/ORDER BY (.*$)/i)
                                order_by[1]
                              else
                                #NOTE: jonk (04/14/2010) => Inject an order by clause that won't fail assuming the following are true (if you disobey any of these guidelines the query will fall on it's face):
                                  # 1) There are no sub selects anywhere in the query or the where clause
                                  # 2) The table initially being selected FROM can't be aliased as it won't be able to know what it's ordering by
                                  #     note: So instead of "FROM users u" you must do "FROM users" and specify users.name, etc in any reference to the initial select from's table.
                                  #     (cont) This is because when we look up the table name we don't have any of knowing how you aliased it when providing the order by and it won't be able to be bound
                                  # 3) Pagination will be by ID (since you didn't specify it) so unless you display an ID column in the view containing the list of records (to show the sequencial order), 
                                  #    this won't make much sense to users
                                  #    note: You're much better off just specifying an order when using paginate_by_sql directly.
                                  
                                table_name = sql.match('FROM ([\[\]\w\.]+)')[1]
                                
                                find_table_primary_key_columns(table_name)
                              end
                              
          #NOTE: jonk (04/14/2010) => now that we have the order preference stored we need to remove it so that we can re append it in the right place later
          # which is... in the ROW_NUMBER() OVER( ORDER BY junk if we're on a page other than the first page OR at the very end of the query if we ARE on the first page
          # we also want to check for any GROUP BY and remove that
          sql.sub!(/\s+ORDER BY.*$|\s+GROUP BY.*$/i, '')
          
          if options[:offset] > 0 #we're on page 2 or greater
                     
            sql.sub!(/^SELECT/i, "SELECT * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY #{options[:order] } ) AS row_num, \n   ")
            sql << ") AS t \nWHERE row_num > #{options[:offset]} ORDER BY row_num"
          else
            sql << "\n    ORDER BY #{options[:order]}"
          end
        end
        
        sql.sub!(/^SELECT/i, "SELECT #{options_distinct} TOP #{options[:limit]}") if options[:limit]
        sql
        
        #NOTE: jonk (04/14/2010) => for debugging the sql before after it has been transformed.
        # if options[:raise] == true
        #   raise sql.to_s
        # end
      end
      
      
      
      #NOTE: jonk (04/19/2010) =>  didn't modify this method, but just traced the query through it to see what was happening
      def add_order_by_for_association_limiting!(sql, options)
        # Disertation http://gist.github.com/24073
        # Information http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx
        return sql if options[:order].blank?
        columns = sql.match(/SELECT\s+DISTINCT(.*?)FROM/)[1].strip
        sql.sub!(/SELECT\s+DISTINCT/,'SELECT')
        sql << "GROUP BY #{columns} ORDER BY #{order_to_min_set(options[:order])}"
      end
    end
  end
end




#NOTE: jonk (04/14/2010) => monkey patching will_paginate for when paginate_by_sql is specified
require 'will_paginate/core_ext'
module WillPaginate
  module Finder
    module ClassMethods      
      def paginate_by_sql(sql, options)
        WillPaginate::Collection.create(*wp_parse_options(options)) do |pager|
          query = sanitize_sql(sql.dup)
          original_query = query.dup
          # raise original_query.to_s
          
          # NOTE: jonk (04/14/2010) => needed to add order here as an option that can be passed in (below).
          # Added raise option too so that I could determine for which query I wanted to raise and inspect in the add_limit_offset! method above.
          # Just pass :raise => true in a paginate_by_sql:  Model.paginate_by_sql(sql, :page => params[:page], :per_page => 25, :raise => true)
          # Then you can raise the sql in the method above to inspect how it was wrapped. This Helped debug queries a lot.
          
          # add limit, offset 
          add_limit_offset! query, :offset => pager.offset, :limit => pager.per_page, :order => options[:order], :raise => options[:raise]
          # perfom the find
          pager.replace find_by_sql(query)
          
          unless pager.total_entries
            #NOTE: jonk (04/14/2010) => the regex here was too eager, see here: count_query = original_query.sub /\bORDER\s+BY\s+[\w`,\s]+$/mi, '' 
            # It would stop on an aliased table name like "pl.lab_date". I modified it to match on anything after order by (including new lines, etc)
            count_query = original_query.sub /ORDER BY (.*$)/i, ''
            count_query = "SELECT COUNT(*) FROM (#{count_query})"
            
            unless self.connection.adapter_name =~ /^(oracle|oci$)/i
              count_query << ' AS count_table'
            end
            # perform the count query
            pager.total_entries = count_by_sql(count_query)
          end
        end
      end
    end
  end
end