Skip to content

Instantly share code, notes, and snippets.

@jondkinney
Created April 19, 2010 20:15
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save jondkinney/371535 to your computer and use it in GitHub Desktop.
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
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment