will_paginate_by_sql_initializer.rb

#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 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 j2fly_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 (though it seems like it's not actually doing anything)
        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
          sql.sub!(/\s+ORDER 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
      
      
      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 
          j2fly_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