Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.