Here are links to (and notes about) some key pages from the DataTables documentation and web site. The documentation is extensive – and sometimes feels like a bit of a sprawl. These are items I have been referring to more frequently recently.

Iterating

Iterators in DataTables

A blog post covering:

rows().every(), columns().every() and cells().every(),

each()

iterator()

The differences, and when to use each one.

But it may be easier to iterate over a single column. The following function sums up values in the first column, if the checkbx in the second column is checked:

function doSum() {
  var table = $('#example').DataTable();
  var checkboxNodes = table.column(0).nodes();
  var amountData = table.column(1).data();

  var sum = 0;

  for (var i = 0; i < checkboxNodes.length; i++) {
    if( $(checkboxNodes[i]).find("input").is(':checked') ) {
	  sum += amountData[i];
	}
  }

  console.log(sum);
}

Utility Functions

map() – Create a new API instance with the result set defined by the values returned from the callback function.

toArray() – Create a native Javascript array object from an API instance.

to$() – Convert the API instance to a jQuery object, with the objects from the instance’s result set in the jQuery result set.

Server Side

The main server side processing manual page.

Sent parameters – the data which DataTables automatically sends to the server.

Returned data – the data which is passed from the server to DataTables.

Understanding the two sets of data which are passed to and from DataTables is key to understanding how to make server-side processing work.

A simple JSON structure sent from DataTables to the server might look like this:

{
    "draw": "5",
    "columns[0][data]": "id",
    "columns[0][name]": "",
    "columns[0][searchable]": "true",
    "columns[0][orderable]": "true",
    "columns[0][search][value]": "",
    "columns[0][search][regex]": "false",
    "columns[1][data]": "name",
    "columns[1][name]": "",
    "columns[1][searchable]": "true",
    "columns[1][orderable]": "true",
    "columns[1][search][value]": "",
    "columns[1][search][regex]": "false",
    "columns[2][data]": "description",
    "columns[2][name]": "",
    "columns[2][searchable]": "true",
    "columns[2][orderable]": "true",
    "columns[2][search][value]": "",
    "columns[2][search][regex]": "false",
    "order[0][column]": "1",
    "order[0][dir]": "asc",
    "start": "30",
    "length": "10",
    "search[value]": "",
    "search[regex]": "false"
}

And a simple JSON structure sent from the server to DataTables might look like this:

{
    "draw": 1,
    "recordsTotal": 1000,
    "recordsFiltered": 1000,
    "data": [{
        "id": 1,
        "name": "widget_1",
        "description": "This is a description for widget 1"
    }, {
        "id": 2,
        "name": "widget_2",
        "description": "This is a description for widget 2"
    }, { ...
    }, {
        "id": 10,
        "name": "widget_10",
        "description": "This is a description for widget 10"
    }]
}

I don’t use PHP, but a PHP example can be seen in the “Server-side script” tab on this page.

That script uses this:

require( 'ssp.class.php' );

You can see the referenced script in GitHub here.

Other approaches which may (or may not) help for large volumes of data:

Buttons

buttons.exportData( [options] ) – choose which data to export from a DataTable to a spreadsheet, PDF, etc.

Excel options – In particular, see the customize and exportOptions descriptions:

customize – modify the XSLX file that is created by Buttons. It takes 3 parameters:

xlsx – object containing the excel files (see below)
btn – the button configuration object
tbl – the table’s DataTables API instance

exportOptions – selects the data to be exported, using the buttons.exportData() function described above.

Excel built-in styles – a short list of formatting styles provided in the spreadsheet created by DataTables.

Excel Files

The structure of the Excel files used in the above customize parameter:

{
    "_rels": {
        ".rels": xml
    },
    "xl": {
        "_rels": {
            "workbook.xml.rels": xml
        },
        "workbook.xml": xml,
        "styles.xml": xml,
        "worksheets": {
            "sheet1.xml": xml
        }

    },
    "[Content_Types].xml": xml
}

This is how the xlsx.xl.worksheets['sheet1.xml'] reference is constructed (see example below).

Example Code

(document).ready(function() {

  $('#example').DataTable( {

    dom: 'Bfrtip',
    buttons: [
      {
        extend: 'excelHtml5',
        title: '', // no title row in excel sheet
        text: 'Excel', // label for the export button
        // export everything except the first row and first col:
        exportOptions: {
          rows: function ( idx, data, node ) {
            if (idx > 0) {
              return data;
            }
          },
          columns: function ( idx, data, node ) {
            if (idx > 0) {
              return data;
            }
          }
        },
        // format the output with a thin black border on each cell:
        customize: function ( xlsx, btn, tbl ) {
          var sheet = xlsx.xl.worksheets['sheet1.xml'];
          $( 'row c', sheet ).attr( 's', '25' );
        }
      }
    ]

  } );

} );

Another exportOptions fragment – this time for modifying header data (and there is also format.body and format.footer):

exportOptions: {
  format: {
    header: function ( data ) {
      var n = data.indexOf("<select>");
      if (n > -1) {
        return data.substring(0, n);
      } else {
        return data;
      }
    }
  }
}

Search Plug-In

The DataTables search plug-in is accessed using the following:

$.fn.dataTable.ext.search

This allows you to provide completely customized searching.

The official example for a range search uses the following code:

$.fn.dataTable.ext.search.push(
    function( settings, searchData, index, rowData, counter ) {
        var min = parseInt( $('#min').val(), 10 );
        var max = parseInt( $('#max').val(), 10 );
        var age = parseFloat( searchData[3] ) || 0; // using the data from the 4th column

        if ( ( isNaN( min ) && isNaN( max ) ) ||
             ( isNaN( min ) && age <= max ) ||
             ( min <= age   && isNaN( max ) ) ||
             ( min <= age   && age <= max ) )
        {
            return true;
        }
        return false;
    }
);

Be aware: $.fn.dataTable.ext.search is an array – which is why you push() your search function onto it. If you want to use multiple different custom search functions, you need to pop() the unwanted one off the array first:

while($.fn.dataTable.ext.search.length > 0) {
  $.fn.dataTable.ext.search.pop();
}

Or, you can create two or more separate functions:

var filter = $.fn.dataTable.ext.search.push(
  function( settings, data, dataIndex ) {
    return (data[colIdx] < threshold);
  }
);

var reset = $.fn.dataTable.ext.search.push(
  function( settings, data, dataIndex ) {
    return true;
  }
);

Column Render Functions

See the documentation.

An example:

$('#example').dataTable( {
  "columnDefs": [ {
    "targets": 0,
    "data": "download_link",
    "render": function ( data, type, row, meta ) {
      return '<a href="'+data+'">Download</a>';
    }
  } ]
} );

Custom Renderers

You can create a reusable renderer.

Example:

$.fn.dataTable.render.myfunction = function ( inputParam ) {
  return function ( data, type, row ) {
    // custom logic in here
  };
};

The function can then be used as follows:

{
  data: 'description',
  render: $.fn.dataTable.render.myfunction( 10 )
}

Drilling Down into JSON

DataTables uses the standard JavaScript dotted object notation to drill down into JSON data structures.

Nested Objects

Accessing nested objects is described here.

Example:

{
  "name": "Tiger Nixon",
  "hr": {
    "position": "System Architect",
    "salary": "$3,120",
    "start_date": "2011/04/25"
  },
  "contact": [
    "Edinburgh",
    "5421"
  ]
}

And:

"columns": [
  { "data": "name" },
  { "data": "hr.position" },
  { "data": "contact.0" },
  { "data": "contact.1" },
  { "data": "hr.start_date" },
  { "data": "hr.salary" }
]

Note how contact.0 refers to the first element of the contact array. This is a DataTables extension to the JavaScript dot-notation syntax.

Nested Arrays

For arrays, there is an additional array bracket syntax, which can be used to concatenate array values.

Example:

{
  "name": [
    "Nixon",
    "Tiger"
  ]
}

And:

"columns": [
  { "data": "name[, ]" }
]

This results in each cell containing the concatenated values from the array, with each value separated by a comma and a space (, ):

Nixon, Tiger

See here for details.

Limitations

JavaScript dotted notation has some limitations:

When working with dot notation, property identifiers can only be alphanumeric (and _ and $). Properties cannot start with a number, or contain periods.

If a JSON key contains dots as part of its text, for example…

"foo" : { "baz.bat": "value" }

…then this will interfere with the dot notation process.

Alternatively, you can use JavaScript bracket notation to navigate into a data hierarchy:

foo["baz.bat"]

Naming Standards

DataTables has two naming standards:

older Hungarian notation names (for versions prior to 1.10)
newer camelCase names (from 1.10 onwards)

If you are using the latest version of DataTables (from version 1.10 onwards), you can use either the new naming standard or the old one:

Naming in DataTables 1.10

There is a page in the official documentation which documents the mapping between the two naming standards:

Converting parameter names for 1.10

Download Builder

Download builder: https://datatables.net/download/

Compatibility

Compatibility charts: https://datatables.net/download/compatibility

Orthogonal Data

Orthogonal data is described here. It allows you to store different versions of a data item. For example, the display value of a date may be Mon 23rd Feb, 2019 but the sort value may be 20190223.

Orthogonal data has the following types:

display
sort
filter
type

The first three are self-explanatory. The type type is used for “type detection”.

What does that mean?

DataTables supports automatic detection of different data types. See columns.type for background. The type type, as described on this page:

should normally match the sort value – see the type detection plug-in documentation.

The type detection plug-in documentation does not shed much additional light on this. An answer from the DataTables forum indicates that this is not something we should typically need to handle, outside of the above advice to “match the sort value”.

The DataTables API

There are three main ways to access the DataTables API:

$( selector ).DataTable();
$( selector ).dataTable().api();
new $.fn.dataTable.Api( selector );

See here for details and notes explaining the differences between these syntaxes. In summary:

The result from each is an instance of the DataTables API object which has the tables found by the selector in its context. It is important to note the difference between $( selector ).DataTable() and $( selector ).dataTable(). The former returns a DataTables API instance, while the latter returns a jQuery object.

If you need to access the DataTables API from within a DataTable callback function, you can use the JavaScript this keyword.

A DataTables example is shown here:

$(document).ready(function() {
    $('#example').DataTable( {
        "initComplete": function () {
            var api = this.api();    // <-- this.api()
            api.$('td').click( function () {
                api.search( this.innerHTML ).draw();
            } );
        }
    } );
} );

Dates and Times

There are various ways to handle the display, filtering, and sorting of datetimes in DataTables.

moment.js

The monent.js library can be used:

https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.27.0/moment.min.js

See the monent.js documentation. It also has extensive internationalization support.

Assume a field containing the following date:

`1`	`<td>25-12-2019</td>`

The following render function can be used in combination with DataTables orthogonal data support:

$('#example').DataTable( {
  columnDefs:[{
    targets: 4,
    render: function ( data, type, row ) {
      //console.log(data); // the raw date as a string
      var d = moment(data, 'DD-MM-YYYY'); // converted to a moment object
      var sortString = moment(d).format('YYYYMMDD'); // converted to a new string format
      //console.log(sortString);
      var cellContents = '<center>' + data + '</center>';
      if ( type === 'display' || type === 'filter' ) {
        return cellContents;
      } else {
        return sortString;
      }
    }
  }]
} );

DateTime Plug-In

This is introduced here. The plug-in can be incorporated using this – but also relies on the above moment.js library:

https://cdn.datatables.net/plug-ins/1.10.22/sorting/datetime-moment.js

Example:

$(document).ready(function() {
  $.fn.dataTable.moment( 'HH:mm MMM D, YY' ); // 04:01 Jan 27, 15
  $.fn.dataTable.moment( 'dddd, MMMM Do, YYYY' ); // Tue, February 3rd, 2015

  $('#example').DataTable();
} );

DataTables will automatically checking to see if the data in a column matches any of the given datetime types you have registered using $.fn.dataTable.moment().

It also uses the DataTables ordering extension ($.fn.dataTable.ext.type.order) to automatically provide sorting for your registered date formats.

HTML5 Attributes

Another “orthogonal data” approach is to use DataTables support for HTML5 attributes. For example:

<tr>
    <td data-search="Tiger Nixon">T. Nixon</td>
    <td>System Architect</td>
    <td>Edinburgh</td>
    <td>61</td>
    <td data-order="1303682400">Mon 25th Apr 11</td>
    <td data-order="3120">$3,120/m</td>
</tr>

DataTables has built-in support for the following:

data-sort and data-order – for ordering data
data-filter and data-search – for searching data

Read more about these HTML5 custom data-* attributes here.

Alternatives to moment.js

As mentioned on the official moment.js web site, this library is now a legacy project

We now generally consider Moment to be a legacy project in maintenance mode. It is not dead, but it is indeed done.

They recommend a few more modern alternatives.

Source Code

The various download packages are available from the main web site here.

The GitHub repositories are here.

In GitHub the PHP source files for server-side processing examples are here. This is where you can find the ssp.class.php file which is used by this example.

Column Filters

An example which places the filters in the footer.

An example which places the filters in the header. This example also happens to use fixed headers.

Add to favorites

Spread the love

Author: Shahzad Khan

Software developer / Architect View all posts by Shahzad Khan