Server-Side Pagination

We did all pagination client-side which wasn't be possible to keep doing as we accumulate more data


  • The Back-end is now done for HTML representation
  • The Back-end was already done for JSON representation, now done in the dataTables way
  • The Front-end is done: 50 records at a time :)
  • ToDo:
    • Push from Dev to Live
    • Render as .represent
    • Render the ID as View/Edit button depending on perms
      • Done via a .represent for RMS
    • Return RMS to Live (1 line in Controller functions: response.s3.pagination = True)
    • Handle Component Views (use list_fields instead of all readable)
    • Sort Columns (orderby) need to enhance sorting for reference columns: it is expected to be alphabetic, and now it is per foreign ids
    • Search (filtering) need to include reference columns (lookup tables at least) which are being ignored currently
    • XSLT to generate dataTables-compliant JSON? (update: was not needed for the current functionality)

Front-end implementation

Client-side, we need to add these options to views/dataTables.html [make them Optional per-REST Controller?]:

    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "{{=URL(c='module', r='resource', vars={'...':'...'})}}"

Server-side, we need to understand these vars:

# Pagination
iDisplayStart - maps to 'start'
iDisplayLength - maps to 'limit'
# Ordering
iSortDir_x <<<<--------- now sSortDir_x since jquery.dataTables.fixed.js 1.6.1 
                         need to watch out for the future upgrades compatibility!
# Filtering (Search) - across all fields!

response should be sent in this format:

"sEcho": 1,
"aaData": [
    [, row1.field2, ...],
    [, row2.field2, ...],
    [, row3.field2, ...]
"iTotalRecords": 3,
"iTotalDisplayRecords": 3

e.g. using a function like (although we've integarted into models/

def callback():
    iDisplayStart = int(request.vars.iDisplayStart)
    iDisplayLength = int(request.vars.iDisplayLength)
    from gluon.serializers import json
    _table = '%s_%s' % (request.controller, request.function)
    table = db[_table]
    query = ( > 0)
    rows = db(query).select(limitby = (iDisplayStart, iDisplayStart + iDisplayLength))
    r = dict(sEcho = 1,
           iTotalRecords = len(rows),
           iTotalDisplayRecords = len(rows),
           aaData = [[row[f] for f in table.fields if table[f].readable] for row in rows])
    return json(r)


JSON Back-end implementation

  • Support already present in modules\
    • http://S3_PUBLIC_URL/module/resource.json?limit=x&start=y
  • needs patching for dataTables-expectations:
    if "start" in self.request.vars:
        start = int(self.request.vars["start"])
    elif "iDisplayStart" in self.request.vars:
        # dataTables
        start = int(self.request.vars["iDisplayStart"])
        start = None
    if "limit" in self.request.vars:
        limit = int(self.request.vars["limit"])
    elif "iDisplayLength" in self.request.vars:
        # dataTables
        limit = int(self.request.vars["iDisplayLength"])
        limit = None
  • actually needs a complete new representation unless this could be done via XSLT!?
    • still needs extension adding to shn_xml_export_formats in models\

HTML Back-end implementation

Patched the models\01_RESTlike_Controller to support http://S3_PUBLIC_URL/module/resource?limit=x&start=y

     if request.vars.limit:
        limit = int(request.vars.limit)
        if request.vars.start:
            start = int(request.vars.start)
            limitby = (start, start + limit)
            limitby = (0, limit)
        limitby = None
items =, query=query,

Other Options


Last modified 10 years ago Last modified on 08/07/11 17:23:11
Note: See TracWiki for help on using the wiki.