How to sort strings when the first letter is a special character?

brunocloutier

I'm sorting columns in a pretty basic way. The website is in french, so some words start with special characters (ex. : Éducation, École, etc). These words get sorted at the end of the list, but I need them to be sorted alongside words starting with the letter E.

What should I look into to get started?

rf1234

Here is an example with auto-detection of columns which you might want to call 'mots-francais' instead of 'formatted-num'. You could put all of these letters (aren't on my key board unfortunately) into an array and search for them like I do for the ISO currency codes. If a column qualifies you would return 'mots-francais' for example.

 var cur = [  'EUR', 'USD', 'GBP', 'CHF', 'JPY', 'AUD', 'CAD', 'CNY', 
                 'DKK', 'SEK', 'NOK', 'CZK', 'PLN', 'HUF', 'RUB'         ];

    //auto detection only works if ALL cells of a column comply with the criterion !!!
    //fields must contain an ISO currency code or space to be detected as formatted-numbers
    //since all table fields with ISO codes always contain a number this works.
    //in addition German number fields should be detected as formatted-num regardless of
    //whether they contain an ISO code or not
    $.fn.dataTable.ext.type.detect.unshift( function ( data ) {
//        if (typeof data !== 'undefined' && data !== null) {
//            if ( isNaN(data) ) {
        if (typeof data !== 'undefined') {
            if ( data != null )  {
                var i=0;
                while ( cur[i] ) {
                    if ( isNaN(data) ) {  //search may only be performed on non-numeric fields
                        if ( data.search( cur[i] ) > -1 )   {
                            return 'formatted-num';
                        }
                    }
                    i++;
                }
                if ( data === '') {
                    return 'formatted-num';
                }                
                if (lang == 'de') {
    //we are not using strict mode in moment.js so that 28.12.2017 09:42 is also
    //recognized as a date!!
                    if ( ! moment(data, 'L').isValid() ) {
                        if ( isNaN(data) ) {
                            data = data.replace( /[\.]/g, "" );
                            data = data.replace( /[\,]/g, "." );
                            if ( ! isNaN(data) ) {
                                return 'formatted-num';
                            }
                        }
                    }
                }                    
            }
        }
        return null;
    } );

Subsequently you do the order extension where you return a "normalized" version of your words, eg. Ecole instead of École etc. you would call this 'mots-francais-pre', 'mots-francais-asc' and ... -desc.

//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;
        }
    } );

You can skip the first step of course and assign a class to your colum which you target in column defs like this

columnDefs: [       
    { type: 'mots-francais', targets: "myClassMotsFrancais" }
    ],

brunocloutier

Thank you for the answer! I'm currently working on implementing it.

allan

Have a look at this blog post which introduces a sorting plug-in for DataTables that uses the new Intl APIs in Javascript to perform locale based sorting. That is going to be integrated into DataTables core in future.

Allan

rf1234

That's really neat, Allan. I am currently only using this for numbers. But good to know that there is this solution for texts as well. And I see there is also a solution for German letters like Ä, ä, Ü, ü, Ö, ö and ß.

@brunocloutier Allan's suggestion is probably more appropriate than mine for the solution that you require! Sounds easier to me.

rf1234

A lot to read, Allan. But eventually I only needed these 3 lines in my code:

the plugin:

<!-- International Sorting-->
    <script src="https://cdn.datatables.net/plug-ins/1.10.16/sorting/intl.js"></script>

The code:

//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");
    }

The only difficult thing was to figure out which locale string to use. I found the German phonebook sorting in an example. I wasn't even aware that something other than the phonebook sorting existed in German ... The default sorting where ü sorts with u makes no sense to me.

brunocloutier

Sorry it took me quite a bit of time to get back in the discussion, I had to veer off on another project.

I have tried implementing the Intl API (based on @allan ’s suggested blog post), but with no success. I’m not getting any errors, nor do I seem to be getting any results. Results are not taking the É character in account. I'm having a hard time figuring out where to start if I wish to debug this.

First, I included the file listed in the blog post, except it's concatenated in my JS file.

//cdn.datatables.net/plug-ins/1.10.16/sorting/intl.js

After that, I call this bit of script to tell DataTables to sort using french rules.

$.fn.dataTable.ext.order.intl( 'fr' );

I get no errors, but I'm also not getting any results. Any ideas?

rf1234

I tried with
$.fn.dataTable.ext.order.intl("fr");
and it worked fine.

I had German words and two French words "École" and misspelled "Ecole". Without this École was at the end of the list. With the plugin École and Ecole were both on top of the list in sequence "Ecole" followed by "École".

So something else is probably wrong with your code.

I also tested with $.fn.dataTable.ext.order.intl("de-DE-u-co-phonebk"); and
$.fn.dataTable.ext.order.intl("en-GB");
Worked fine even with these languages and the two French words.

Please make sure that $.fn.dataTable.ext.order.intl("fr"); is above your table definitions in your code.

allan

@brunocloutier - Can you link to a page showing the issue please?

Allan

brunocloutier

@allan Here’s the still in development version of the website, containing the latest code.
http://dev.principal.principal.studio/projets/

@rf1234 , I made sure to include the part calling intl.js above my table definition.

I downloaded the intl.js script and it's concatenated in my main JS file. The scripts are minified/uglified, so I'll paste the relevant part down here.

$.fn.dataTable.ext.order.intl("fr");
        
$('#projects-table').dataTable({
  'order': [[4, 'desc']],
  'info': false,
  'paging': false,
  'searching': false,
  'columns': [
    null,
    null,
    { 'orderable': false },
    null,
    null,
    { 'orderable': false }
  ]
});

Again, thanks again for the help!

rf1234

First thing I would try is to call it
$('#projects-table').DataTable
with capital D. This does make a difference. I forgot why but Allan knows for sure.

Here I found something that explains the difference:
https://datatables.net/faqs/#api

allan

There is something really weird going on with the layout of the table there - a number of the cells are display:block and float:left which is making it really hard to understand what is going on.

Allan

brunocloutier

@rf1234, I tried calling it with a capital D. Unfortunately, it did not work.

@allan, we use a table markup structure to take advantage of DataTables sorting features, but we'd rather use divs because we want columns with precise widths. It's possible to use tables to achieve the same results, but then they can't be stretched.

I will try to look at other options. For example, could I store a value in a data attribute and use that as the sorting parameter for the column? I'll read further into the options DataTables provides.

brunocloutier

I'm currently using data-order on each <td>. I'm using sanitized values and so far it's working great to order rows correctly. I will go with this solution for now, will look into debugging the Intl option later on.

Again, thanks to both of you for your help!

allan

You can use table-layout: fixed to give exact control over column widths in a table.

Good to hear you've got it working though.

Allan