CODEWITHSUNDEEP
jquerysortingdatatablesnumbers
Jean-Yves Deman
Jean-Yves Deman

Reputation: 55

datatables... Sort don't work with numeric-comma... Why?

I use the jquery datatables with server_processing. This reminds me server_processing in datatables numbers (or words?) with the ',' separator for decimal.

$ inter = trim ($ aRow ['carat']);
$ row [] = number_format ($ inter, 2, ',', '');

When I click on the arrows ascending and descending, sorting does not occur in the numbers method, but the method texts. ex. 10.01> 9.99 (in numbers)

How make it work with my numbers?

Upvotes: 2

Views: 11465

Answers (5)

mathias.horn
mathias.horn

Reputation: 251

Try inserting the following code on your head. Remeber to change the "example" for your datatable name.

$(document).ready(function() {
    $('#example').DataTable( {
        "language": {
            "decimal": ",",
            "thousands": "."
        }
    } );
} );

Upvotes: 2

Vinicius José Latorre
Vinicius José Latorre

Reputation: 2801

The latest dataTables version (1.10.4) handles formatted numeric sorting without any extension, that is, puts $1,000.00 before $900.00 when sorting in descending order and puts $900.00 before $1,000.00 when sorting in ascending order.

See num-fmt in http://datatables.net/reference/option/columns.type.

Upvotes: 0

Marcelo
Marcelo

Reputation: 71

I tried the example of datatables page. It works fine to me.

The data should be decimal format

https://datatables.net/examples/plug-ins/sorting_sType.html

$(document).ready(function() {

    jQuery.fn.dataTableExt.oSort['numeric-comma-asc'] = function(a, b) {
        var x = (a == "-") ? 0 : a.replace(/,/, ".");
        var y = (b == "-") ? 0 : b.replace(/,/, ".");
        x = parseFloat(x);
        y = parseFloat(y);
        return ((x < y) ? -1 : ((x > y) ? 1 : 0));
    };

    jQuery.fn.dataTableExt.oSort['numeric-comma-desc'] = function(a, b) {
        var x = (a == "-") ? 0 : a.replace(/,/, ".");
        var y = (b == "-") ? 0 : b.replace(/,/, ".");
        x = parseFloat(x);
        y = parseFloat(y);
        return ((x < y) ? 1 : ((x > y) ? -1 : 0));
    };

    oTable = $('#grid').dataTable({
        "bJQueryUI": true,
        "sPaginationType": "full_numbers",
        "aoColumns": [
            null,
            null,
            null, {
                "sType": "numeric-comma"
            },
            null,
            null
        ]
    });
});

Upvotes: 2

Jay Rizzi
Jay Rizzi

Reputation: 4304

if you are doing server-side processing implementation of Datatables, you cannot use any sort plugins or sType variables, because they all reside client side

You would need to update your database query to properly sort a column as a number/string, then use mRender or javascript to update the column format, so that the sort really has noting todo with the column format

example : returning money as a number from the database, then using mRender to add commas/currency types

Upvotes: 0

Sergio Pinto
Sergio Pinto

Reputation: 76

In JavaScript only dots are valid as decimal separator. As DataTables uses JavaScript to sort columns values, it will consider number with comma separator as strings. However, this problem can be easily overtaken with a sort plug-in.

jQuery.extend(jQuery.fn.dataTableExt.oSort, {
    "numeric-comma-pre": function (a) {
        // prepare number
        a = +(a.replace(",", "."));
        a = (isNaN(a)) ? Number.MAX_VALUE : a;
        return a;
    },
    "numeric-comma-asc": function (a, b) {
        return ((a < b) ? -1 : ((a > b) ? 1 : 0));
    },
    "numeric-comma-desc": function (a, b) {
        return ((a < b) ? 1 : ((a > b) ? -1 : 0));
    }
});

The previous code block defines a new data type, to be used by DataTables to sort columns. This code is an adaptation of an example that can be found in DataTables official site.

The first function in the plug-in object, is the one that converts the cell value to number, so that it can be sorted by the following functions. In this case I followed a suggestion from this post, and used the unary operator to convert the value to number. If the value is not a number then NaN is returned, and I change it to Number.MAX_VALUE. I do it this way because I choose to move invalid numbers last when sorting ascending.

After that, only remains create the datatable, and define the new data type for the columns we want.

jQuery("#myTable").dataTable( {
    "aoColumnDefs": [
        { "sType": "numeric-comma", "aTargets": [2,3] }
    ]
});

Supposing that third and fourth columns have comma separated numbers, they now must be sorted correctly.

Upvotes: 2

Related Questions