Created
March 17, 2010 19:59
-
-
Save jondkinney/5461ba88c387ed275cf4 to your computer and use it in GitHub Desktop.
Crazy limit-offset pagination sql05
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment