Here is a SQL Server halfway decent working pagination I recently
wrote. Out of the box Rails
pagination was not working very well at all. I found little on the
internet and thought
if I could give something back to the Rails community in some way,
that would be cool.
It was hastily written and there’s at least one bug on the
pagination link for page 2.
I also think that ODBC driver for SQL Server might be better than
ADO
I put the code in application.rb
class MyPagination
def links()
start = @cur_page - 2
lend = @cur_page + 2
start = 1 if (start < 0)
lend = @num_pages if (lend > @num_pages)
range = start…lend
list = range.to_a
needs to eval to ‘…’, use a do nothing method like cap() etc
dot = ‘"…".capitalize’
list = [1,dot] + list if start != 1
list += [dot,@num_pages] if lend != @num_pages
arr = []
prev = 0
list.each do |pag|
pstr = pag.to_s
prfx = “”
if (pag.class != String) && (pag != @cur_page)
str = "link_to " + “’” + pstr + “’”
@url.each do |ky,val|
str += “,:#{ky.to_s} => ‘#{val}’”
end
str += “, :page => ‘#{pag}’”
#puts “add #{str} to arr”
arr << str
else
arr << pstr
end
end
arr
end
def recs()
@recs
end
def initialize(parm)
@parm = parm
@pkey = parm[:pkey]
@rec_class = parm[:rec]
@url = parm[:url]
end
def pages()
nil
end
def page_count
@num_pages
end
def paginate (page_sz, page_wanted, order)
page_wanted = 1 if !page_wanted
page = nil
if @rec_class
if @parm[:conditions]
puts “cond: #{@parm[:conditions]}”
size = @rec_class.count(:conditions => @parm[:conditions])
else
size = @rec_class.count
end
@num_pages = size / page_sz
@last_page_sz = size.remainder(page_sz)
if @last_page_sz > 0
@num_pages += 1
end
puts “call get”
get_page(page_sz, page_wanted, order)
end
end
private
def get_page(page_sz,page_wanted, order)
=begin
SELECT TOP 50 *
FROM mytab
WHERE (EndUser NOT IN
(SELECT TOP 15000 enduser
FROM mytab
ORDER BY enduser))
ORDER BY EndUser
=end
page_wanted = 1 if !page_wanted
@cur_page = page_wanted
recs_wanted = page_sz
recs_wanted = @last_page_sz if page_wanted == @num_pages
puts “page_sz:#{page_sz} last_sz:#{@last_page_sz}
page:#{page_wanted} recs_wanted:#{recs_wanted}”
block_sz = ((page_wanted - 1) * page_sz)
#block_sz += recs_wanted if page_sz != recs_wanted
rec_name = @rec_class.table_name
order_clause = “”
order_clause = “order by #{order}” if order
cond2 = “”
cond1 = “”
if @parm[:conditions]
cond2 = @parm[:conditions]
cond1 = cond2
cond1 += " and " if page_wanted.to_i > 1
cond2 = "where " + cond2
end
inner_sql = “”
if page_wanted.to_i > 1
inner_sql = " not in " +
" (select top #{block_sz} #{@pkey} " +
" from #{rec_name} " + cond2 + " " + order_clause +
") "
end
sql =
"select top #{recs_wanted} * " +
" from #{rec_name} "
if cond1 != “”
sql += "where #{cond1} "
end
if inner_sql != “”
sql += " (#{@pkey} " + inner_sql + ") "
end
sql += " " + order_clause
puts sql
@recs = @rec_class.find_by_sql(sql)
puts “got #{@recs.size} records”
end
end
###################################################################
Controller example:
def index
page = params[:page]
=begin
@pages, @rec = paginate(:MyRec,
:order => “account DESC”,
:per_page => 50)
=end
@pages = MyPagination.new(:rec => Account, :pkey =>
‘AccountID’ , :url => {:controller => ‘account’}, :conditions =>
“status = ‘corrected’”)
page = params[:page].to_i if params[:page]
@pages.paginate(50,page , “accountNum Desc”)
@recs = @pages.recs()
end
####################################################33
template example
<% if @pages && @pages.page_count > 1 %>
PAGES
<% @pages.links.each do |lnk| %>
<%= eval lnk %>
<% end %>
<% end %>
<% if @recs %>
<%= render(:partial => ‘account’) %>