From Koha Wiki

Jump to: navigation, search

Home > Development

DataTables HowTo

What is DataTables

DataTables is a jQuery plugin that can provide a lot of functionnality 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

Actually the DataTables library is not in Koha master, but you can look at Bug 6836 in Bugzilla. There is a patch for the library, and another patch use it in readingrec.pl.

There are 4 main files:

.../prog/en/lib/jquery/plugins/jQuery.datatables.min.js (the javascript library)
.../prog/en/includes/datatables-strings.inc (contain translatable strings)
.../prog/en/js/datatables.js (helper functions)
.../prog/en/css/datatables.css (CSS for tables)

Basic example

So, here are the basic steps to use DataTables in Koha with default functionnalities

Add these lines in the <head></head> section

 <link rel="stylesheet" type="text/css" href="/css/datatables.css" />
 <script type="text/javascript" src="/intranet-tmpl/prog/en/lib/jquery/plugins/jquery.dataTables.min.js"></script>	
 [% INCLUDE 'datatables-strings.inc' %]
 <script type="text/javascript" src="/intranet-tmpl/prog/en/js/datatables.js"></script>

Now, admit 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>

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.

Adding by-column filtering

But, 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 tell on which column datatables must do the search (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:

use CGI;
use C4::Context;
use C4::Utils::Datatables;  # contain helper function to build part of sql query from datatables parameters

# Récupère dans un hash les paramètres de DataTables
my %dtparam = C4::Utils::Datatables::dt_get_params($input);

my $dbh = C4::Context->dbh;

# Construction de la requête SQL
my $select = "SELECT SQL_CALC_FOUND_ROWS";  # SQL_CALC_FOUND_ROWS permit to retrieve total number of results mysql would have returned if there was no LIMIT
                                            # Save one query to be executed
$select .= "...";
my $from = " FROM table1, table2, ...";
my $where = " WHERE ...";

my ($filters, $filter_params) = dt_build_having(\%dtparam);  # Build having clause, based on mDataProp_* and *_filteron parameters 
my $having = " HAVING . join(" AND ", @$filters) if (@$filters);

my $order_by = dt_build_orderby(\%dtparam);  # Build order by clause, based on mDataProp_* and *_sorton parameters

my $limit = " LIMIT ?, ?";

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("SELECT FOUND_ROWS()");
$sth->execute;
my ($iTotalDisplayRecords) = $sth->fetchrow_array;

# Retrive total number of rows
my $select_total_count = "SELECT COUNT(*) ";
$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, \%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 => \@aaData,
);

# and print result
output_with_http_headers $input, $cookie, $template->output, 'json';

DataTables HowTo

From Koha Wiki

Contents

What is DataTables

Main features

How to use it in Koha

Basic example

Adding by-column filtering

Server-side processing

Views

Personal tools

Search

koha links

Navigation

Categories

Toolbox