Datatables tools

Overview

The datatables module provides seamless server-side processing for the DataTables jQuery plugin within CherryPy applications. It bridges SQLAlchemy queries with the DataTables server-side protocol, handling sorting, global search, per-column filtering, pagination, and JSON serialization automatically.

How It Works

The module operates through two complementary layers:

1. DataTableQuery — Core Processing Class

DataTableQuery wraps a SQLAlchemy Query object and processes it according to DataTables request parameters. When instantiated, it immediately:

1. Counts total records from the original unfiltered query.

2. Applies sorting based on order[0][column] and order[0][dir] parameters.

3. Applies global search across all configured search_columns using ILIKE (case-insensitive partial match).

4. Applies per-column search for each columns[N][search][value] parameter, supporting both plain ILIKE matching and regex patterns.

5. Counts filtered records after all search/filter conditions are applied.

Calling .paginate() then slices the result set with OFFSET/LIMIT and serializes each row into a plain Python dict, returning the standard DataTables response envelope:

{
  "draw": 1,
  "recordsTotal": 100,
  "recordsFiltered": 42,
  "data": []
}

2. datatables_out — CherryPy Tool

datatables_out is a CherryPy tool registered at the before_handler hook (priority 29). It wraps the original page handler using a handler replacement pattern (similar to json_out):

• The original handler is called first and must return a SQLAlchemy Query object.

• The wrapper intercepts this Query, extracts DataTables parameters from the request, runs DataTableQuery, and returns the response dict.

• The json_out tool (applied alongside) converts the dict to a JSON HTTP response.

This design keeps your handler clean — it only builds and returns a query, with zero boilerplate for DataTables protocol handling.

Setup & Usage

Registration

The tool is registered automatically when the module is imported:

import cherrypy_foundation.tools.datatables  # noqa

After import, cherrypy.tools.datatables_out is available globally.

@cherrypy.tools.datatables_out() Decorator

Wraps a handler so that its returned Query is processed by DataTableQuery.

Must be paired with @cherrypy.tools.json_out().

Parameters

Parameter	Type	Default	Description
search_columns	list | None	None	SQLAlchemy column expressions to include in global search. If None, global search is silently skipped.
max_length	int	100	Maximum number of rows returned per page, regardless of the length parameter sent by the client.
default_dir	str	"desc"	Default sort direction ("asc" or "desc") when no valid order[0][dir] is provided.
default_col	int	0	Default column index for sorting when no valid order[0][column] is provided.
debug	bool	False	Emit a log message when the handler is replaced.

---

DataTables Request Parameters (Client → Server)

The tool reads standard DataTables server-side parameters from the request:

Parameter	Description
draw	DataTables draw counter (echoed back).
start	Row offset for pagination.
length	Number of rows to return (capped at max_length).
order[0][column]	Column index to sort by.
order[0][dir]	Sort direction: asc or desc.
search[value]	Global search string.
columns[N][search][value]	Per-column search string for column N.
columns[N][search][regex]	"true" to treat per-column value as a regex.

---

DataTableQuery — Direct Usage

You can also use DataTableQuery directly, outside of the CherryPy tool, for example in non-standard handlers or tests.

result = DataTableQuery(
    query=db.session.query(Message, UserObject.username).join(UserObject),
    params=request.params,
    search_columns=[Message.body, UserObject.username],
    max_length=50,
    default_dir="asc",
    default_col=0,
).paginate(draw=1, start=0, length=25)

Query Requirements

• Use explicit columns in your query (via .with_entities() or direct attribute selection). Queries returning full model instances (e.g., session.query(MyModel)) are supported for serialization but not for index-based column sorting or per-column search.

• When using multi-column queries (named tuples), all selected expressions must be resolvable column attributes for sorting and filtering to work correctly.

Complete Example

app.py:

from sqlalchemy import Column, Integer, String
import cherrypy
import cherrypy_foundation.tools.datatables  # noqa — registers cherrypy.tools.datatables_out

env = cherrypy.tools.jinja2.create_env(
    package_name=__package__,
)

Base = cherrypy.db.base

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String)
    email = Column(String)

@cherrypy.tools.jinja2(on=False, env=env)
class Root:

    @cherrypy.expose
    @cherrypy.tools.jinja2(template='index.html')
    def index(self):
        # Serve your HTML page that initialises DataTables with serverSide: true
        return {}

    @cherrypy.expose
    @cherrypy.tools.json_out()
    @cherrypy.tools.datatables_out(
        search_columns=[User.username, User.email],
        max_length=50,
        default_dir="asc",
        default_col=0,
    )
    def data_json(self, **kwargs):
        # Return a SQLAlchemy Query — the tool does the rest
        return User.query.with_entitied(User.id, User.username, User.email)


cherrypy.tree.mount(Root(), '/')
cherrypy.engine.start()
cherrypy.engine.block()

index.html:

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>test-datatables</title>
        {{ catalog.render_assets() }}
    </head>
    <body>
        {% set columns = [
            {'name':'id', 'data':'id', 'title':_('ID')},
            {'name':'username', 'data':'username', 'title':_('Username')},
            {'name':'email', 'data':'email', 'title':_('Email')},
        ] %}
        <cf:Datatable data="/data.json" :columns="columns" :server-side="True" :page-length="10"/>
    </body>
</html>