Rails - Nov 2007 - SQL Server Pagination code here if you need it

#
# 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 %>
  <br>
  PAGES
  <% @pages.links.each do |lnk| %>
    <%= eval lnk %>
  <% end %>
  <br>
<% end %>
<% if @recs %>
<%= render(:partial => ''account'') %>


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---