An Example

Assume we have a data column containing different way of showing no data in a cell - for example, an empty string "" or simply the null keyword. We want to ensure that all such data is always sorted to the bottom of the table when sorting on that column - regardless of whether the sort order is ascending or descending.

One approach is to define custom sorting logic using $.fn.dataTable.ext.type.order.

Here is a complete example, with notes following:

 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

$( document ).ready(function() {

  $.extend( $.fn.dataTable.ext.type.order, {
    "custom-sort-asc": function ( val_1, val_2 ) {
      let x = formatAsc( val_1 );
      let y = formatAsc( val_2 );
      return (x < y) ? -1 : ((x > y) ? 1 : 0);
    },

    "custom-sort-desc": function ( val_1, val_2 ) {
      let x = formatDesc( val_1 );
      let y = formatDesc( val_2 );
      return (x < y) ? 1 : ((x > y) ? -1 : 0);
    }
  } );

  function formatAsc( val ) {
    return val && val.trim() !== '' ? val : '~'; // choose whatever you prefer here
  }  

  function formatDesc( val ) {
    return val && val.trim() !== '' ? val : ''; // choose whatever you prefer here
  }  

  $('#example').DataTable( {
    "data": data,
    "columnDefs": [ {
      "targets": 0,
      "type": 'custom-sort'
    } ]
  } );

});

Some basic sample data:

 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
50

var data = [
    [
        "Jim Winters",
        "Director 1",
        "Edinburgh",
        "8422",
        "2011/07/25",
        "$5,300"
    ],
    [
        null,
        "System Architect",
        "Edinburgh",
        "5421",
        "2011/04/25",
        "$3,120"
    ],
    [
        "Garrett Winters",
        "Director 2",
        "Edinburgh",
        "8422",
        "2011/07/25",
        ""
    ],
    [
        "",
        "Z Director 3",
        "Edinburgh",
        "8422",
        "2011/07/25",
        ""
    ],
    [
        "Jim West",
        "Director 3",
        "Edinburgh",
        "8422",
        "2011/07/25",
        ""
    ],
    [
        "Sandra Brown",
        "Director 2",
        "Edinburgh",
        "8422",
        "2011/07/25",
        ""
    ]  
]

Custom Data Types

Starting from the bottom of the code example, we see the following in the DataTables configuration:

1
2
3
4

"columnDefs": [ {
  "targets": 0,
  "type": 'custom-sort'
} ]

This defines a custom data type using the name custom-sort. We can provide any name we like, as long as it is not one of the built-in type names already used by DataTables.

We are, effectively, defining a new type of our own, here - one specifically used for sorting our data.

ext.type.order

Now going back to the start of the code example, we can see the DataTables $.fn.dataTable.ext.type.order function and see how we are adding two new named functions of our own to its array of functions:

• custom-sort-asc
• custom-sort-desc

These names use the custom-sort prefix we defined in our column type option, with suffixes of -asc and -desc.

There is a third suffix which can also be used: -pre. In the current release of DataTables, this suffix cannot be combined with the other two.

In our case we need to use -asc and -desc, because we have different logic in each case.

Related documentation is here.

Note that the function needs to be defined before the table is declared, so that the table can use the function, if needed, for its initial display ordering.

The Sorting Functions

The sorting functions are based on JavaScript’s Array.prototype.sort() method.

In our examples we are using the version of the method which takes two arguments (val_1, val_2) and returns a numeric value which is:

• greater than 0 if val_2 should be sorted before val_1
• less than 0 if val_2 should be sorted after val_1
• 0 if the two values are the same

In our specific case, we replace empty strings and null values with either ~ or '' for sorting purposes, depending on whether the asc or desc function is being executed.

The ~ was chosen in the above example because it is sorted after all other ASCII characters; and ’’ was chosen because it is sorted before all printable characters.

If your data contains characters outside the ASCII range, then you would need to find a better character - for example the Unicode character for “undefined”: U+10ffff:

1

console.log('\uD803\uDFFF'); // Prints: 𐿿

Orthogonal Data

The type type makes an appearance in the DataTables columns.render function where it can be used for type detection.

But note that our use of "type": 'custom-sort' is not relevant here. Our use was for sorting - this use is for type detection.