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
Here is a complete example, with notes following:
Some basic sample data:
Custom Data Types
Starting from the bottom of the code example, we see the following in the DataTables configuration:
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.
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:
These names use the
custom-sort prefix we defined in our column
type option, with suffixes of
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
-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
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_2should be sorted before
- less than 0 if
val_2should be sorted after
- 0 if the two values are the same
In our specific case, we replace empty strings and null values with either
'' for sorting purposes, depending on whether the
desc function is being executed.
~ 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”:
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.