How to make work DataTables with Natural sort plugin?

1
votes

I've seen some similar questions, but no correct answer in my case.

I use the DataTables plugin to sort some tables.

But we can't sort datas like numerics while they are not (nut ;-p). For example, we can't sort formatted prices like those :

  • 2 150 000 €
  • 4 500 000 €
  • 225 000 €

So I tried to include a DataTables plugin called "Natural sort". But it doesn't seems to work, I may do some mistakes, but I can't find them.

Any help will be appreciated.

All you need to see/test it : JSfiddle (try to sort the columns...)

2
jquerydatatablesnatural-sort

2 Answers

3
votes

So it looks like you have two problems. First, dT isn't picking up your custom sort - it's just ignoring it and using the standard sort. I was able to fix this by specifying the sort for each column:

$('#table_list').dataTable({
    "aoColumns": [null, {"sType": "natural"}, null, null],
    "aaSorting": [[ 1, "asc" ]],
    "sScrollX": "100%",
});

Second, natural sort won't sort those numbers the way you expect. It will take the first number section up until the space and sort on that, like so:

2 150 000 €
4 750 000 €
210 000 €

So you probably just want to write your own sort. Here's an example I tried:

function testSort( a, b ) {
    var aa = a.replace(/[ \.]/g,''), bb = b.replace(/[ \.]/g,'');
    aa = parseInt( aa.substring( 0, aa.length - 1 ) );
    bb = parseInt( bb.substring( 0, bb.length - 1 ) );
    return aa == bb ? 0 : ( aa < bb ? -1 : 1 );
}

The key is removing the spaces, periods and euro signs so that it can be read as a number.

1
votes

Thanks @Ryan P my sType declarations were wrong...

Finaly, I needed to add more plugins to make it work, I share it if it can help someone :

  • Natural sort : for the varchar values
jQuery.fn.dataTableExt.oSort['natural-asc']  = function(a,b) {
    return naturalSort(a,b);
};

jQuery.fn.dataTableExt.oSort['natural-desc'] = function(a,b) {
    return naturalSort(a,b) * -1;
};
  • Unshift "null" types into formated-num :
jQuery.fn.dataTableExt.aTypes.unshift(  
    function ( sData )  
    {  
        var deformatted = sData.replace(/[^\d\-\.\/a-zA-Z]/g,'');
        if ( $.isNumeric( deformatted ) ) {
            return 'formatted-num';
        }
        return null;  
    }  
);
  • Formatted-num : for... formated numbers values
jQuery.fn.dataTableExt.oSort['formatted-num-asc'] = function(a,b) {
    /* Remove any formatting */
    var x = a.match(/\d/) ? a.replace( /[^\d\-\.]/g, "" ) : 0;
    var y = b.match(/\d/) ? b.replace( /[^\d\-\.]/g, "" ) : 0;

    /* Parse and return */
    return parseFloat(x) - parseFloat(y);
};

jQuery.fn.dataTableExt.oSort['formatted-num-desc'] = function(a,b) {
    var x = a.match(/\d/) ? a.replace( /[^\d\-\.]/g, "" ) : 0;
    var y = b.match(/\d/) ? b.replace( /[^\d\-\.]/g, "" ) : 0;

    return parseFloat(y) - parseFloat(x);
};

Working example here : http://jsfiddle.net/DzaQe/3/