Pagination With Web2Py

I was developing a web2py blog application to be a teaching one for the web2py beginners. After completing many parts, the next part was pagination. I have thought on how to do it. The normal select statement on web2py is as the following statement:

db(db.post.id > 0).select()

If you want to limit a select then you should use the following:

db(db.post.id > 0).select(limitby(0,10))

With this statement you select the first 10 posts in your database. So 10 is your LIMIT and 0 is your OFFSET. If you try to do set 5 as OFFSET and 10 AS LIMIT, you will be disappointed because it won't work as you expect because of the 2846th line in sql.py. You will see the following code:

sql_o += ' LIMIT %i OFFSET %i' % (lmax - lmin, lmin)

This line makes your query LIMIT 5 OFFSET 5. The issue here is that you should make it like (lmax, lmin) and it will work as expected.

Here is the pagination part:

perpage = 3
totalposts = db(db.post.id > 0).count()
totalpages = totalposts / perpage
if totalposts > perpage and totalpages == 1 and totalpages * perpage != totalposts:
    totalpages = 2
page = int(request.vars.page) if request.vars.page else 1
limit = int(page - 1) * perpage
posts = db(db.post.id > 0).select(orderby=~db.post.id, limitby=(limit, perpage))

And in your view you should use the following codes:

{{ if totalpages > 1:}}
<div class="pages">
{{ for i in range(1, totalpages+1): }}
    <div class="page">{{ if postpage == i: }} {{=i}} {{ else: }}<a href="/blog/?page={{=i}}">{{=i}}</a>{{ pass }}</div>
{{ pass }}
    <div style="clear: both;"></div>
</div>
{{ pass }}