Crazy limit-offset pagination sql05

config-initializers-will_paginate.rb

require 'will_paginate/core_ext'

module WillPaginate
  module Finder
    module ClassMethods

      #NOTE: jonk (03/16/2010) => I wanted to define my own limit_offset method rather than override the regular one for Active Record, 
      # because I really only want this code to execute when I specifically need it, aka... during pagination. For whatever reason
      # active record calls add_limit! on every query, so I didn't want this dirtying up a normal User.first for example.
      
      #NOTE: jonk (03/16/2010) => Inspired by: http://alexle.net/archives/tag/mislav-will_paginate-sqlserver-2005
      def add_sql05_limit_offset!(sql, options)  
        # puts sql
        sql = sql.gsub(/(\]|\[)/, '') #NOTE: jonk (03/16/2010) => added to remove the brackest around everything if they are there [users].[id] etc
        options[:offset] ||= 0  
        options_limit = options[:limit] ? "TOP #{options[:limit]}" : ""
        options[:order] ||= if order_by = sql.match(/ORDER BY(.*$)/i)
                              order_by[1]
                            else
                              #NOTE: jonk (03/17/2010) => Allow table names to be aliased
                              begin
                                #NOTE: jonk (03/17/2010) => find the full matching string
                                mahsql = (sql.downcase.match('from (.+?)\b[a-zA-Z]+')[0]).to_s
                                #NOTE: jonk (03/17/2010) => split it on spaces like ("from" "users" "u") or ("from" "users" "where")
                                mahsql2 = mahsql.split(" ")[2] + ".id"
                                if mahsql2.include?("where")
                                  #NOTE: jonk (03/17/2010) => then the table name isn't aliased so we want to get [1] which is the full table name
                                  sql.downcase.match('from (.+?)\b')[1] + ".id"
                                else
                                  #NOTE: jonk (03/17/2010) => get [2] which is the alias, which we then concat to the .id field for some default ordering
                                  mahsql2
                                end
                              rescue
                                #NOTE: jonk (03/17/2010) => it must not have an alias or a where clause so just get the table name and append ".id"
                                sql.downcase.match('from (.+?)\b')[1] + ".id"
                              end
                            end
        sql.sub!(/ORDER BY.*$/i, '')
        #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, '')        
        sql.sub!(/SELECT/i, "SELECT #{options_distinct} #{options_limit} * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY #{options[:order]} ) AS row_num, ")
        sql << ") AS t WHERE row_num > #{options[:offset]}"
        # puts sql
        sql
      end      
      
      def paginate_by_sql(sql, options)
        WillPaginate::Collection.create(*wp_parse_options(options)) do |pager|
          query = sanitize_sql(sql.dup)
          original_query = query.dup
          # add limit, offset
          #NOTE: jonk (03/16/2010) => had to assign the return of this method to a var so I could pass it to the pager. This wasn't done before... bug?
          #NOTE: jonk (03/17/2010) => add the :order => options[:order] at the end, duh!
          mahsqloff = add_sql05_limit_offset! query, :offset => pager.offset, :limit => pager.per_page, :order => options[:order]
          # perfom the find
          #NOTE: jonk (03/16/2010) => Pass the new limited/offsetted query to the pager instead of the original like it was coded as.
          pager.replace find_by_sql(mahsqloff)

          unless pager.total_entries
            #NOTE: jonk (03/16/2010) => downcase the query and regex text. Also allow for tables to be aliased by regexing for "." just before \w in the order by as well
            count_query = original_query.downcase.sub /\border\s+by\s+[.\w`,\s]+$/mi, ''
            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