What is DataTables

DataTables is a jQuery plugin that can provide a lot of functionality to HTML tables.

Main features

Pagination
Sorting
Filtering
Server-side processing (ajax)

See DataTables official site for a full list of features and official documentation

How to use it in Koha

Here are the basic steps to use DataTables in Koha with default functionality

Add this line to the jsinclude block at the bottom of the template:

  [% INCLUDE 'datatables.inc' %]

Now, assume you have a table in your page like this:

  <table id="mytable">
    <thead>
      <tr>
        <th>First column header</th>
        <th>Second column header</th>
        <!-- ... -->
      </tr>
    </thead>

    <tbody>
      <tr>
        <td>Line 1, Col 1</td>
        <td>Line 1, Col 2</td>
        <!-- ... -->
      </tr>

      <tr>
        <td>Line 2, Col 1</td>
        <td>Line 2, Col 2</td>
        <!-- ... -->
      </tr>

      <!-- ... -->
    </tbody>
  </table>

Note that the header row must be enclosed in <thead> and the body of the table must be enclosed in <tbody>. If your table is not marked up in this way you will get a JavaScript error.

Now put this in your $(document).ready function:

$(document).ready(function() {
  var my_table = $("#mytable").dataTable($.extend(true, {}, dataTablesDefaults, {
    // dataTablesDefaults contain default options and strings defined in datables-strings.inc
    // You can put your specific datatables options here
  }));
});

and it's done. You have a beautiful paginated table which can be sorted and filtered. This minimal configuration adds the following features above the table:

Sorting on all columns, with the initial sort on the first column (ascending).
A note is added, "Showing 1 to 20 of X" where X is the total number of table rows. This note is also appended at the bottom of the table.
An option to show 10, 20, 50, 100 or all rows in the table at a time.
"Previous" and "next" buttons for paging through results. These buttons also appear at the bottom of the table.
A search form which allows you to filter rows based on the contents of any cell.

Configuring Column Display

Since Bug 10212 being pushed, there is a method for allowing any datatables table to be configured for which columns are displayed by default and which can be displayed optionally.

Changing table sorting controls

To add table sorting to a table without adding pagination or displaying the controls described above, add two lines to your table's sorting configuration:

$(document).ready(function(){
    $("#mytable").dataTable($.extend(true, {}, dataTablesDefaults, {
        "dom": 't',
        "paging": false
    }));
});

The sDom configuration option allows you to choose which features appear and in what position in relation to the table.

To extend the pagination buttons to include "First" and "Last:"

$(document).ready(function(){
    $("#mytable").dataTable($.extend(true, {}, dataTablesDefaults, {
        "pagingType": "four_button"
    }));
});

To further extend the pagination buttons to include numbered links:

$(document).ready(function(){
    $("#mytable").dataTable($.extend(true, {}, dataTablesDefaults, {
        "pagingType"   : "full_numbers"
    }));
});

Excluding columns from sorting

Often a table will have columns which should be excluded from sorting because they contain inputs or other controls. To exclude a column from sorting add a "nosort" class to the table header cell:

<thead>
  <tr>
   <th class="nosort">Don't sort this column</th>
   <th>Sort this column</th>
   <th>Sort this column</th>
   ...

In the configuration for that table indicate that columns with class "nosort" should be excluded from sorting:

$(document).ready(function(){
    $("#mytable").dataTable($.extend(true, {}, dataTablesDefaults, {
        "columnDefs": [
            { 'sortable': false, 'targets': [ 'nosort' ] }
        ]
    }));
});

Excluding articles when sorting

Many tables will contain data, like titles, which should be sorted in a way that ignores initial articles (In English, "a," "an" and "the"). The datatables configuration include file (datatables.inc) contains a translatable string which is intended to be translated for each separate language installation. Koha includes a custom DataTables configuration which makes available an "anti-the" sorting plugin for use on columns containing title information. To exclude articles from sorting add an "antithe" class to the table header cell:

<thead>
  <tr>
   <th class="antithe">Exclude "a," "an," and "the" when sorting this column</th>
   <th>Sort this column normally</th>
   <th>Sort this column normally</th>
   ...

In the configuration for that table indicate that columns with class "antithe" should exclude articles when sorting:

$(document).ready(function(){
    $("#mytable").dataTable($.extend(true, {}, dataTablesDefaults, {
        "columnDefs": [
            { 'type': "anti-the", 'targets' : [ 'antithe'] }
        ]
    }));
});

Sorting dates regardless of date format preference

In order to avoid complicated date parsing sorting algorithms tables can be configured to have date columns sorted based on an unformatted date hidden in the markup. Ideally the template in question receives ISO-formatted dates from the Perl script and formats those dates for output using the KohaDates plugin.

To trigger this sorting method add a "data-order" attribute to the table cell:

   <td>[% issueloo.issuingbranchname | html %]</td>
   <td data-order="[% issueloo.issuedate | html %]">[% issueloo.issuedate | $KohaDates%]</td>
   <td>[% issueloo.itemcallnumber | html %]</td>

The data-order attribute contains the template variable which will output an ISO-formatted date, "2003-06-09." The visible contents of the table cell will be the date formatted according to Koha's dateformat system preference.

No additional options need to be added to the table's DataTable configuration.

Other sorting options

title-numeric - Like "title-string," but sorted numerically.
uk_date - Obsolete - for sorting dates which are formatted "metric." Use the title-string method as described above.
natural - For sorting data which contains both numbers and letters in a human-friendly way.

Obsolete configuration methods

Many templates in Koha use index-based configurations, setting sorting options based on the numeric position of the column header. This method is not preferred because of the complications of handling varying column counts based on different system preferences and other factors. In this example the last and second-to-last columns are configured to be unsortable:

    $(document).ready(function() {
        $("#mytable").dataTable($.extend(true, {}, dataTablesDefaults, {
            "columnDefs": [
                { "targets": [ -1, -2 ], "sortable": false, "searchable": false }
            ]
        ...

Other configurations explicitly target the column header position. Again, this method should not be used. The class name configuration option should be used.

    $(document).ready(function() {
        $("#mytable").dataTable($.extend(true, {}, dataTablesDefaults, {
            "columns": [
                { "type": "title-string" },{ "type": "anti-the" },null,{ "type": 
                "title-string" },null,null,null,null,null,null
            ],
        ...

Adding by-column filtering

At this time filtering is done on the whole table using a unique text field and it's often useful to provide a way to search only in one column. Let's see how to do that.

First, add a text field in your page (you can add it in the table footer for example)

<input type="text" class="filter" data-column_num="1" placeholder="Search on 2nd column" />
<!-- data-column_num attribute indicates which column datatables will do the filtering (starting from 0) -->

and add this line just after the dataTable call:

my_table.fnAddFilters("filter", 500);    // 2nd parameter is optional

It will add an event on all text inputs that have the class "filter". Now you can type in the text box, and the table will be updated automatically. The 2nd parameter is a delay. In this example, the table won't be updated unless you stop typing during 500ms.

Note: delay is also applied to the global search box.

Server-side processing

For huge tables, it's often better to retrieve only data for lines which will be displayed. We can achieve that by using server-side processing. It's a little bit more complex to set up, as we'll see.

Following code is code I wrote for issues table in members/moremember.pl

koha-tmpl/intranet-tmpl/prog/en/modules/members/moremember.tt:

var dt_issues = $("#issuest").dataTable($.extend(true, {}, dataTablesDefaults, {
  'bAutoWidth': false,                   // Disable columns width calculation, it's faster and sometimes produce very tiny columns.
  'bProcessing': true,                   // Show "processing..." message during script processing
  'bServerSide': true,                   // Tell datatables we'll use server-side processing
  'sAjaxSource': '/cgi-bin/koha/members/tables/issues.pl',  // name of Perl script which will return data
  // following parameter define 'by column' properties
  // 'mDataProp' property permit to give a name to the column
  // this name is used to do the link between columns and returned data
  // it's also used to sort and filter if it's an existing sql column name
  'aoColumns': [
    { 'mDataProp': 'date_due' },
    { 'mDataProp': 'title' },
    { 'mDataProp': 'collection' },
    { 'mDataProp': 'stocknumber' },
    { 'mDataProp': 'barcode' },
    { 'mDataProp': 'itemtype_description' },
    { 'mDataProp': 'issuedate' },
    { 'mDataProp': 'itemcallnumber' },
    { 'mDataProp': 'charge' },
    { 'mDataProp': 'replacementprice' },
    { 'mDataProp': 'renewals', 'bSortable': false, 'bSearchable': false },
    { 'mDataProp': 'checkin', 'bSortable': false, 'bSearchable': false }
  ],
  'fnServerData': function(sSource, aoData, fnCallback) {
    // Redefine function which will send data to the script
    // Useful to send extra parameters
    aoData.push({
      'name': 'borrowernumber',
      'value': '[% borrowernumber %]'
    });
    // you can define on what data a column will be filtered and sorted
    aoData.push({
      'name': 'title_filteron'     // general form is mDataProp+'_filteron'
      'value': 'biblio.title biblio.author'    // it's common to display author in the same cell, so filter on that too
    },{
      'name': 'title_sorton'       // general form is mDataProp+'_sorton'
      'value': 'biblio.title'      // if mDataProp is the same than sql column name, this is useless
    });
    // ajax call
    $.ajax({
      'dataType': 'json',
      'type': 'POST',
      'url': sSource,
      'data': aoData,
      'success': fnCallback
    });
  },
  'sPaginationType': 'full_numbers'
}});

Script must return a JSON object with 4 keys:

sEcho: just a number sent to the script and which must be returned untouched
iTotalRecords: total number of elements in table
iTotalDisplayRecords: total number of elements, after filtering (if not filtered, must be equal to iTotalRecords)
aaData: the list of rows to display

We can write this JSON object in a template file:

WARNING: H::T::P has been dropped from the official codebase so what follows is not applicable to current coding standards... you have been warned.

koha-tmpl/intranet-tmpl/prog/en/modules/members/tables/issues.tmpl:

{
  "sEcho": "[% sEcho %]",
  "iTotalRecords": "[% iTotalRecords %]",
  "iTotalDisplayRecords": "[% iTotalDisplayRecords%]", 
  "aaData": [
    [% FOREACH aData IN aaData %]
      {
        "date_due":
          "[% aData.date_due %]",
        
        "title":
          "<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=[% aData.biblionumber %]\">...</a>",
          // WARNING: html code must hold on one single line, JSON format does not allow multi-lines values
        
        // ...
        
        "checkin":
          "<input type="checkbox" />"
      }
    [% END %]
  ]
}

And now the script

members/tables/issues.pl:

<span class="kw2">use</span> CGI<span class="sy0">;</span> <span class="kw2">use</span> C4<span class="sy0">::</span><span class="me2">Context</span><span class="sy0">;</span> <span class="kw2">use</span> C4<span class="sy0">::</span><span class="me2">Utils</span><span class="sy0">::</span><span class="me2">Datatables</span><span class="sy0">;</span> <span class="co1"># contain helper function to build part of sql query from datatables parameters</span>   <span class="co1"># Récupère dans un hash les paramètres de DataTables</span> <span class="kw1">my</span> <span class="re0">%dtparam</span> <span class="sy0">=</span> C4<span class="sy0">::</span><span class="me2">Utils</span><span class="sy0">::</span><span class="me2">Datatables</span><span class="sy0">::</span><span class="me2">dt_get_params</span><span class="br0">(</span><span class="re0">$input</span><span class="br0">)</span><span class="sy0">;</span>   <span class="kw1">my</span> <span class="re0">$dbh</span> <span class="sy0">=</span> C4<span class="sy0">::</span><span class="me2">Context</span><span class="sy0">-></span><span class="me1">dbh</span><span class="sy0">;</span>   <span class="co1"># Construction de la requête SQL</span> <span class="kw1">my</span> <span class="re0">$select</span> <span class="sy0">=</span> <span class="st0">"SELECT SQL_CALC_FOUND_ROWS"</span><span class="sy0">;</span> <span class="co1"># SQL_CALC_FOUND_ROWS permit to retrieve total number of results mysql would have returned if there was no LIMIT</span> <span class="co1"># Save one query to be executed</span> <span class="re0">$select</span> <span class="sy0">.=</span> <span class="st0">"..."</span><span class="sy0">;</span> <span class="kw1">my</span> <span class="re0">$from</span> <span class="sy0">=</span> <span class="st0">" FROM table1, table2, ..."</span><span class="sy0">;</span> <span class="kw1">my</span> <span class="re0">$where</span> <span class="sy0">=</span> <span class="st0">" WHERE ..."</span><span class="sy0">;</span>   <span class="kw1">my</span> <span class="br0">(</span><span class="re0">$filters</span><span class="sy0">,</span> <span class="re0">$filter_params</span><span class="br0">)</span> <span class="sy0">=</span> dt_build_having<span class="br0">(</span><span class="re0">\%dtparam</span><span class="br0">)</span><span class="sy0">;</span> <span class="co1"># Build having clause, based on mDataProp_* and *_filteron parameters </span> <span class="kw1">my</span> <span class="re0">$having</span> <span class="sy0">=</span> <span class="st0">" HAVING . join("</span> AND <span class="st0">", @$filters) if (@$filters);
 
my $order_by = dt_build_orderby(<span class="es0">\%</span>dtparam); # Build order by clause, based on mDataProp_* and *_sorton parameters   my $limit = "</span> LIMIT <span class="sy0">?,</span> <span class="sy0">?</span><span class="st0">";
 
my $query = $select.$from.$where.$having.$order_by.$limit;
my @bind_params;
push @bind_params, $where_clause_params, @$filter_params, $dtparam{'iDisplayStart'}, $dtparam{'iDisplayLength'};
my $sth = $dbh->prepare($query);
$sth->execute(@bind_params);
my $results = $sth->fetchall_arrayref({});
 
# Retrieve total filtered rows thanks to SQL_CALC_FOUND_ROWS
$sth = $dbh->prepare("</span>SELECT FOUND_ROWS<span class="br0">(</span><span class="br0">)</span><span class="st0">");
$sth->execute;
my ($iTotalDisplayRecords) = $sth->fetchrow_array;
 
# Retrive total number of rows
my $select_total_count = "</span>SELECT COUNT<span class="br0">(</span><span class="sy0">*</span><span class="br0">)</span> <span class="st0">";
$sth = $dbh->prepare($select_total_count.$from.$where);
$sth->execute($borrowernumber);
my ($iTotalRecords) = $sth->fetchrow_array;
 
# Build the data
my @aaData;
foreach(@$results) {
  my %row = %{$_};
  $row{'date_due'} = C4::Dates->new($row{'date_due'}, 'iso')->output();
  # ...
  push @aaData, <span class="es0">\%</span>row; }   # Call HTML::Template my ($template, $loggedinuser, $cookie, $flags) = get_template_and_user({ template_name => 'members/tables/issues.tmpl', query => $input, type => 'intranet', authnotrequired => 0, flagsrequired => { circulate => 'circulate_remaining_permission' }, });   $template->param( sEcho => $dtparam{'sEcho'}, iTotalRecords => $iTotalRecords, iTotalDisplayRecords => $iTotalDisplayRecords, aaData => <span class="es0">\@</span>aaData, );   # and print result output_with_http_headers $input, $cookie, $template->output, 'json';</span>

DataTables HowTo

Contents