jquery datatables sorting neglect null value

Question

I'm using datatables and jQuery for making nice sortable tables. I now want to sort the rows an a value. This value is a numeric value. But it can also be not available, so at that point I will echo a dash.

When I now sort this column, all rows with the dash are on top. And than the rows with value 1, 3, 6, 8, 10 are shown. How do I change this so that the dash (-) are always on bottom of the table?

At the moment I put in a maximum number, what puts them on the bottom. However I don't want this value to be shown to the user. So I need a hidden sort column, or a other sorting method.

Thanks in advance!

Answer 1

Just came across this and wanted to share a much simpler solution (which perhaps wasn't available when this question was originally answered):

You can add the attribute data-order to each td tag, which will then be used to order. For example:

<td data-order="10">10</td>
<td data-order="9999">&ndash;</td>

... would sort the 10 as a 10, but the dash as if it were 9999. What you fill in for the dashes could be variable and depend on e.g. identifying the maximum value on the server side. Plus, this is much more flexible for all kinds of data. Check out the doc here:

https://datatables.net/examples/advanced_init/html5-data-options

Answer 2

See this: http://jsfiddle.net/CYubV/

The first column in the table works like a normal column; the second column works like you ask.

Try custom sorting, something like this:

$.fn.dataTableExt.oSort['nullable-asc'] = function(a,b) {
    if (a == '-')
        return 1;
    else if (b == '-')
        return -1;
    else
    {
        var ia = parseInt(a);
        var ib = parseInt(b);
        return (ia<ib) ? -1 : ((ia > ib) ? 1 : 0);
    }
}

$.fn.dataTableExt.oSort['nullable-desc'] = function(a,b) {
    if (a == '-')
        return 1;
    else if (b == '-')
        return -1;
    else
    {
        var ia = parseInt(a);
        var ib = parseInt(b);
        return (ia>ib) ? -1 : ((ia < ib) ? 1 : 0);
    }
}

$('#table').dataTable( {
    "bPaginate": false,
    "bFilter": false,
    "aoColumns": [
            null,
            {"bSortable": true, "sType": "nullable"}
                ],
} );