CODEWITHSUNDEEP
javascriptsortingdatatables
Stefan Johansson
Stefan Johansson

Reputation: 13

HTML5/Javascript datatables order/sort of columns

I'm currently using the datatables from http://datatables.net. I have a number of columns containing "unknown" data (i.e cannot pre-set content type)

Before using datatables this was just a standard simple table where sorting was done on server side using ASP.NET/C#/LINQ orderby (ordinal ordering) and this worked perfect for almost any case.

Now, with the datatable the sorting is mostly wrong. Especially numbers and mix of strings and numbers are wrong.

My error-cases:

  1. Empty strings are always first in sorting, I'd like to have them last. Could live with this one though.
  2. Numbers/currency should be ordered correctly. Currently ordering is like this 500,12 400,00 1.123,00 223.441,00

I'm fairly new to this datatables/JavaScript world. For error-case I have added all plugins I can think of and I have tested them all with no success of getting the list ordered correctly.

I think the sorting I'd like to have is ordinal (like ASCII binary) and I could live by implementing my own sort-function if I just could understand how in JS to do the following:

int c = String.Compare(a, b, StringComparison.Ordinal);

Upvotes: 1

Views: 12362

Answers (1)

rf1234
rf1234

Reputation: 1605

had a similar challenge with Data Tables a while ago. And this was with dates, formatted numbers and also with special characters in German. Your name sounds like you are from Scandinavia; might be relevant for you too I guess ...

You would need the following data tables plug ins to do all of this:

This one is for date time sorting: https://datatables.net/plug-ins/sorting/datetime-moment It also requires moment.js which I strongly recommend. https://momentjs.com/

This one is for international sorting when using special characters such as ä, ö ü etc. https://datatables.net/plug-ins/sorting/intl

And this one is for formatted number sorting: https://datatables.net/plug-ins/sorting/formatted-numbers

The examples below are about auto-detection of the respective fields depending on the user language.

Implementation examples:

So this is about formatted numbers. English format would be 1,000,000.99. German format is 1.000.000,99. It also takes care of empty fields.

//sorting of formatted numbers in English and German format
$.extend( $.fn.dataTable.ext.type.order, { 
    "formatted-num-pre": function ( a ) {
        if (lang == 'de') {
            a = a.toString().replace( /[\.]/g, "" );
            a = a.toString().replace( /[\,]/g, "." );
        } else {
            a = a.toString().replace( /[\,]/g, "" );
        }
        a = a.toString().replace( /[^\d.-]/g, "" );
        a = parseFloat(a);
        if ( ! isNaN(a) ) {
            return a;
        } else {
//14 digit negative number to make sure empty cells always stay at the bottom / top
            return -99999999999999;
        }
    },
    "formatted-num-asc": function ( a, b ) {
            return a - b;
    },
    "formatted-num-desc": function ( a, b ) {
            return b - a;
    }
} );

This is about international sorting:

//sorting:
//Use the phonebook variant of the German sort order, 
//which expands umlauted vowels to character pairs: ä → ae, ö → oe, ü → ue.
if (lang === 'de') {
    $.fn.dataTable.ext.order.intl("de-DE-u-co-phonebk");
} else {
    $.fn.dataTable.ext.order.intl("en-GB");
}

And finally this is about date sorting:

//should be positioned after the number formatting to make sure
//date columns don't accidentally are assigned formatted-num
//set parms for date sorting using moment.js
$.fn.dataTable.moment( 'L', momentLocale );

the variable momentLocale in my example is either 'de' or 'en-gb'. The respective date formats are 10.11.2018 (German) or 10/11/2018 (English UK). (As opposed to 11/10/2018 in the American format which I think requires momentLocale to be 'en').

Upvotes: 1

Related Questions