Skip to content

Datatables

This code example shows how to build interactive tables using DataTables's server-side mode, with full support for pagination, sorting, and searching.

employee.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
from utils import render
from models import Employee

class Employee_list(object):
    def view(self, ctx):
        # Loads the initial HTML page
        ctx.go_to = render.as_view(ctx, "employee_list")

    def ajax(self, ctx):
        # DataTables standard parameters
        request = ctx.getRequest()
        draw = int(request.getParameter("draw"))
        start = int(request.getParameter("start"))
        length = int(request.getParameter("length"))

        # Build WHERE clause for global search
        query = "1=1"
        params = []

        # Global search
        global_search = request.getParameter("search[value]") or ""
        if global_search:
            query = query + " AND (name LIKE ? OR department LIKE ? OR email LIKE ?)"
            wildcard = "%s%%" % global_search
            params.extend([wildcard, wildcard, wildcard])

        # Sorting
        order_col_index = request.getParameter("order[0][column]")
        order_col_name = ["id", "name", "department", "email"][int(order_col_index)] if order_col_index else "name"
        order_dir = request.getParameter("order[0][dir]") or "asc"
        order_clause = "%s %s" % (order_col_name, order_dir.upper())

        total_records = Employee.count()
        filtered_records = Employee.count(query, *params) if query != "1=1" else total_records

        employees = Employee.where(query, *params).offset(start).limit(length).orderBy(order_clause)

        data = []
        for emp in employees:
            data.append([emp.get("id"), emp.get("name"), emp.get("department"), emp.get("email")])

        result = {
            "draw": draw,
            "recordsTotal": total_records,
            "recordsFiltered": filtered_records,
            "data": data
        }
        ctx.go_to = render.as_json(ctx, result)
employee_list.html
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title>Employee List</title>
  <link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.3.0/css/bootstrap.min.css" rel="stylesheet">
  <link href="https://cdn.datatables.net/v/bs5/jq-3.7.0/dt-2.3.2/datatables.min.css" rel="stylesheet">

  <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.3.0/js/bootstrap.bundle.min.js"></script>
  <script src="https://cdn.datatables.net/v/bs5/jq-3.7.0/dt-2.3.2/datatables.min.js"></script>
</head>

<body>
  <div class="container-fluid">
    <h1>Employee List</h1>

    <table id="employeeTable" class="table table-sm table-striped">
      <thead>
        <tr>
          <th style="1px">ID</th>
          <th>Name</th>
          <th>Department</th>
          <th>Email</th>
        </tr>
      </thead>
    </table>

  </div>

  <script>
    document.addEventListener("DOMContentLoaded", function () {
      var table = $('#employeeTable').DataTable({
        "processing": true,
        "serverSide": true,
        "lengthChange": false,
        "ajax": "/t/example/employee_list/ajax",
        "columns": [
          { "title": "ID" },
          { "title": "Name" },
          { "title": "Department" },
          { "title": "Email" }
        ]
      });
    });
  </script>
</body>

</html>

CREATE TABLE employee ( 
    id int NOT NULL AUTO_INCREMENT, 
    name varchar(300), 
    department varchar(300), 
    email varchar(300), 
    PRIMARY KEY (id) 
);

INSERT INTO employee (name, department, email) VALUES
('Olivia Chen', 'Marketing', '[email protected]'),
('Benjamin Carter', 'IT', '[email protected]'),
('Sophia Rodriguez', 'Sales', '[email protected]'),
('Liam Goldberg', 'Finance', '[email protected]'),
('Ava Nguyen', 'HR', '[email protected]'),
('Noah Patel', 'IT', '[email protected]'),
('Isabella Kim', 'Marketing', '[email protected]'),
('Mason Williams', 'Sales', '[email protected]'),
('Mia Garcia', 'Finance', '[email protected]'),
('James Johnson', 'IT', '[email protected]'),
('Charlotte Martinez', 'HR', '[email protected]'),
('William Davis', 'Sales', '[email protected]'),
('Amelia Lee', 'Marketing', '[email protected]'),
('Elijah Hernandez', 'IT', '[email protected]'),
('Harper Gonzalez', 'Finance', '[email protected]'),
('Lucas Wilson', 'Sales', '[email protected]'),
('Evelyn Anderson', 'HR', '[email protected]'),
('Alexander Thomas', 'IT', '[email protected]'),
('Abigail Moore', 'Marketing', '[email protected]'),
('Henry Taylor', 'Finance', '[email protected]'),
('Emily Jackson', 'Sales', '[email protected]'),
('Michael White', 'IT', '[email protected]'),
('Sofia Harris', 'HR', '[email protected]'),
('Daniel Martin', 'Marketing', '[email protected]'),
('Madison Thompson', 'Finance', '[email protected]'),
('Jacob Clark', 'IT', '[email protected]'),
('Ella Lewis', 'Sales', '[email protected]'),
('Logan Walker', 'Marketing', '[email protected]'),
('Victoria Hall', 'HR', '[email protected]'),
('David Allen', 'IT', '[email protected]');

How It Works

  • Sorting: User clicks a column header → DataTables sends column index + sort direction → server applies ORDER BY
  • Global Search: Search bar above table triggers across all columns