Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DataTables sorts strings instead of numeric

I am using jquery.datatables to show numbers in datatables columns. Numbers are formatted to have spaces in between thousands unit (like 123 456 789). Unfortunately, this number formatting provoques a string sorting instead of a number sorting (see the screenshot at the end of this question).

I have identified that:

  • function _fnSort(oSettings, bApplyClasses) { is the core function for sorting.
  • In this function, the dynamic function sorting approach is used (the one executed if if (!window.runtime) { is true)
  • The string sorting functions used are the two following functions.

    /*
    * text sorting
    */
    "string-asc": function(a, b) {
        var x = a.toLowerCase();
        var y = b.toLowerCase();
        return ((x < y) ? -1 : ((x > y) ? 1 : 0));
    },
    
    "string-desc": function(a, b) {
        var x = a.toLowerCase();
        var y = b.toLowerCase();
        return ((x < y) ? 1 : ((x > y) ? -1 : 0));
    },
    

My knowledge in javascript is pretty poor, what would be the best approach here?

  1. Tweak the string sorting function to detect the number thousands formatted case, and do the comparison (I guess this would be pretty slow on large data set).
  2. Provide a numerical sorting function dedicated for number thousands formatted? In that case
    • how would you code that?
    • how could I indicate to the core sorting function, to use this special numerical sorting function?

Here is what the sorting look like by now:

enter image description here

like image 836
Patrick from NDepend team Avatar asked Jul 05 '12 09:07

Patrick from NDepend team


1 Answers

To sort this kind of values, you can use this sort function :

var sortFunction=function(a, b)​{
    var ia = parseInt(a.split(' ').join(''), 10);
    var ib = parseInt(b.split(' ').join(''), 10);
    return ia-ib;
};

Test :

var data = ['3 333', '100 333', '22 000', '1 333'];
console.log(data.sort(sortFunction));

With a reasonable number of values, this will be fast enough. You shouldn't try to enrich the data if you don't detect performance problems.

EDIT :

In fact, the documentation proposes an appropriate (similar) sorting function :

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
    "formatted_numbers-pre": function ( a ) {
        a = (a==="-") ? 0 : a.replace( /[^\d\-\.]/g, "" );
        return parseFloat( a );
    },

    "formatted_numbers-asc": function ( a, b ) {
        return a - b;
    },

    "formatted_numbers-desc": function ( a, b ) {
        return b - a;
    }
} );

After having added this extension, you just have to set the sType of your column.

like image 160
Denys Séguret Avatar answered Nov 09 '22 07:11

Denys Séguret