Curia

I recently had to implement pagination in a TG 2 project that I’m almost finished with. Although I had previously glanced at the paginate webhelper module, I first turned to the updated TurboGears documentation to see what was “officially” written up about this subject.

A lot of work has been going into the TG docs in preparation for the upcoming v2.1 launch, and one little gem that was recently added was the Pagination Quickstart (thanks Lukas). While this sent me down the right path towards pagination perfection, there were a few things that came up that I felt like deserved further explanation:

1. The @paginate() decorator is an extremely simple wrapper for the paginate webhelper module.
2. When paginating SQLAlchemy queries, paginate runs a duplicate count() query to figure out its current position in the data collection. While the magic of this is nice, the possible performance issues with duplicating complex SQL queries on a high-traffic website could be undesirable.
3. It’s common to have controller methods dynamically switch to a “feed template” when doing AJAX pagination. Don’t worry, paginate + override_template makes this a piece of cake!

The @paginate() decorator makes pagination a breeze

The @paginate decorator is a wrapper for the paginate module that can be added to your controller methods. Simply specify your data collection’s name as the first argument of the decorator, and pagination is “automagically” setup for you.

An example controller method with pagination would look like this:

# dead-simple pagination in TurboGears 2
 
@expose('myproject.templates.posts')
@paginate("posts")
def posts(self):
    posts = DBSession.query(Posts)
    return dict(posts=posts)

Really? That was easy!

The documentation on this decorator seems quite clear to me, so instead of repeating its content here I will simply give you a link for further reading.

SQLAlchemy pagination performance wizardry

The main issue here is that the paginate module has to figure out its current position in the data collection in order to be able to spit out the right “chunk” of data (as well as to provide you with fancy stuff like “page 3 of 6″). So, by default when you give it an SQLAlchemy query object, it runs a count() query to figure this out. This results in two practically identical SQL queries (one to get the total count, and the other to return your actual collection of data). While this may not be an issue on simple queries, duplicating complex queries on a high-traffic website could be disastrous.

Remember, magic can be unpredictable, so before we dive into this topic there are a few things you should take into consideration:

• This technique is not compatible with the @paginate() decorator due to the fact that the current page arg isn’t passed into the controller method when using the decorator, so be ready to get your hands dirty with the full-blown paginate module.
• The SQLAlchemy all() query method results in immediate query execution. This may have undesirable side-effects.
• I’m sure there is a cleaner/sexier way to do this, but for the sake of time and in an effort to be verbose this is what I have come up with so far. Please leave a helpful comment below if your magic is cleaner than mine.

Ok, so now that we are familiar with the problem, what is a good solution? Well, a rough hack to work around this issue is to use SQLAlchemy’s limit() and offset() query methods combined with the all() method to get SQLAlchemy to return a list instead of a query object. This puts paginate to work on the list instead of on the database, avoiding the extra count() query. However, as tricky as this may be, there are still problems:

1. In order for paginate to calculate a correct current position based on the “page” it thinks it’s on, you’ve got to pad the beginning of the list you send to the paginate.Page() method with enough items to accurately reflect the “current” size of the data collection.
2. In order for paginate to know there are more records waiting to be loaded, you’ve got to pad the end of the list you send to the paginate.Page() method with at least one more item than each page’s specified item limit.

This may seem confusing at first, but an example controller method should help clear things up for you:

# import the paginate webhelper
from webhelpers import paginate
# specify the number of items you want per page
items_per_page = 20
 
@expose('myproject.templates.posts')
def posts(self, page=1):
    # specify the limit, plus 1 so the Page() method can tell if extra data is available
    limit = items_per_page + 1
    # specify the offset to start with
    offset = (int(page) - 1) * items_per_page
 
    # if the offset is greater than 0, we have to pad the beginning of the list
    if offset: posts = [x for x in xrange(offset)]
    # otherwise we're starting at the beginning (with an empty list)
    else: posts = list()
 
    # load the DB data into the list
    posts.extend(DBSession.query(Posts).limit(limit).offset(offset).all())
 
    # setup the pagination object
    pagination = paginate.Page(posts, page, items_per_page=items_per_page)
    # either use tmpl_context or return the object in the return dict
    tmpl_context.pagination = pagination
 
    return dict(posts=pagination.items)

Voilà! Now that you’re dealing with a list, pagination should work as expected without the extra “count()” query.

Use override_template() for dynamic template rendering

AJAX pagination is a common practice and often provides a richer user experience than “static” pagination. Fortunately, AJAX pagination is almost as easy to setup as normal pagination, and only requires a few extra tweaks to an already simple configuration.

The only “tricky” thing here is if you’re accessing the same controller method for partial data as well as the full page. In such a case, you’ll need to have the controller method render two separate templates depending on if you’re asking for the “full” page, or just the listing of data. Don’t worry though, paginate automatically provides you with a “partial” arg that you can check against, and override_template() allows you to override whatever template you have defined in the @expose() decorator.

Modifying the previous example’s controller method for this type of functionality looks like this:

# add the override_template import
from tg import override_template
 
# add the "partial" arg to the controller method
def posts(self, page=1, partial=0):
    # ...snip...
 
    if partial:
        override_template(self.posts, 'mako:myproject.templates.post_feed')
 
    return dict(posts=pagination.items)

More information on override_template() can be found in the docs.

I hope this tutorial has been helpful. I will be submitting a TG docs pull request soon, so please leave comments if you have any suggestions or feedback.