Ignore leading apostrophe in sort

kkittell518

I have a datatable that has plant names like 'Star' and Star. When sorting on that column, it puts all the names in the apostrophes first, then lists the rest of the names. How can I get 'Star' and Star to appear one after the other?
If you'd like to see what I mean, go to https://npgsweb.ars-grin.gov/gringlobal/search and type in star in the search box and hit search.

rf1234

This should help:
https://www.datatables.net/blog/2017-02-28

I am using it myself to sort differently depending on user language:

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

rf1234

Probably better to use this:

//sorting of leading apostrophe columns
$.extend( $.fn.dataTable.ext.type.order, { 
    "leading-apostrophe-pre": function ( a ) {
        if (a.substr(0, 1)  === "'") {
            return a.substr(1);
        }
        return a;
    },
    "leading-apostrophe-asc": function ( a, b ) {
        return a - b;
    },
    "leading-apostrophe-desc": function ( a, b ) {
        return b - a;
    }
} );

Then in columnDefs you would need to assign the right class to all of the cells of the respective column(s):

columnDefs: [
    //targets can be classes!
    { type: 'leading-apostrophe', targets: "lead_ap" }

So if you assign class "lead_ap" to the respective column(s) in your HTML this will assign the right class for sorting to all of the cells of the column(s).

rf1234

won't work for strings but this should work ...

//sorting of leading apostrophe columns
$.extend( $.fn.dataTable.ext.type.order, {
    "leading-apostrophe-pre": function ( a ) {
        if (a.substr(0, 1)  === "'") {
            return a.substr(1);
        }
        return a;
    },
    "leading-apostrophe-asc": function ( a, b ) {
        return ((a < b) ? -1 : ((a > b) ? 1 : 0));
    },
    "leading-apostrophe-desc": function ( a, b ) {
        return ((a < b) ? 1 : ((a > b) ? -1 : 0));
    }
} );

more here: https://datatables.net/plug-ins/sorting/

kkittell518

columnDefs: [
//targets can be classes!
{ type: 'leading-apostrophe', targets: "lead_ap" }

The above has me confused. I know it's column 2 that will have the leading apostrophe. And where does "lead_app" tell it to sort using the function?

kkittell518

$.extend($.fn.dataTable.ext.type.order, {
"name-pre": function (a) {
if (a.substr(0, 1) === "'") {
return a.substr(1);
}
return a;
},
"name-asc": function (a, b) {
return a - b;
},
"name-desc": function (a, b) {
return b - a;
}
});

and in columnDefs:
{
type: 'name', targets: 2,
}

I changed the names only, but it doesn't work. Did I do something wrong?

kthorngren

The columnDefs.targets docs explain all the options you can use. A string represents a class name assigned to one or more columns. You can use column indexes instead.

Kevin

rf1234

Did I do something wrong?

Yes, you took my first wrong example and not the second one ... Please check again!
You need "return ((a < b) ? -1 : ((a > b) ? 1 : 0));" instead of "return a - b;" etc..

The above has me confused. I know it's column 2 that will have the leading apostrophe. And where does "lead_app" tell it to sort using the function?

So if you assign class "lead_ap" to the respective column(s) in your HTML this will assign the right class for sorting to all of the cells of the column(s).

"lead_app" tells it when you assign it to your column in your HTML ... otherwise it doesn't. But you can also hard code column 2. I don't do that because if I insert anoter column into the table this is very likely to cause trouble. Up to you.

kkittell518

Awesome! When I saw your 3rd comment "won't work with strings, but this should work," I didn't realize you meant your previous comment.
Thank you!

rf1234

Never mind! Glad you got it fixed. And I learned something, too.

kkittell518

I take that back. It's only partly working.
My data looks like this: (desc)
STAR ROG
'STAR'
Satin leaf
Star Apple
'Star Witch'
'Star'

So the leading apostrophes aren't all grouped together, but not alphabetizing correctly. I guess my problem isn't only leading apostrophes. I need it sorted regardless of case and apostrophes. Not sure why 'Star Witch' should come before 'Star'. When there are a list of names without apostrophes, it's sorted correctly.
Am I going to need something that makes everything sort lower case as well?

rf1234

Well, now you know how to do that ...

Just adjust this to return lower case and replace all apostrophes for example. Experimenting is the fun about coding! Enjoy!

"leading-apostrophe-pre": function ( a ) {
    if (a.substr(0, 1)  === "'") {
        return a.substr(1);
    }
    return a;
},

Maybe this works? Just give it a try!

"leading-apostrophe-pre": function ( a ) {
    var str = a.split("'").join(""); //get rid of all apostrophes
    return str.toLowerCase();
},

rf1234

I found this here https://datatables.net/manual/plug-ins/sorting and it is confusing me:

So if you really can't use a pre-formatter in this case then my suggestion above should be rewritten as:

function format( a ) {
    a = a.split("'").join(""); //get rid of all apostrophes;
    return a.toLowerCase();
}

//sorting of leading apostrophe columns
$.extend( $.fn.dataTable.ext.type.order, {
    "leading-apostrophe-asc": function ( a, b ) {
        a = format( a );
        b = format( b );
        return ((a < b) ? -1 : ((a > b) ? 1 : 0));
    },
    "leading-apostrophe-desc": function ( a, b ) {
        a = format( a );
        b = format( b );
        return ((a < b) ? 1 : ((a > b) ? -1 : 0));
    }
} );

kkittell518

Thanks again! That's probably as good as I'm going to get when I have
'Star', 'STAR', and STAR.
I get 'STAR', 'Star', 'Star', STAR, 'Star'...
I'll have to ask the powers-that-be if this ok.

rf1234

Well, you could play around with the a and b variables BEFORE formatting and not return 0 when Star and STAR are compared but rather -1 etc.

Maybe you just check how it works with only removing the apostrophes:

function format( a ) {
    return a.split("'").join(""); //get rid of all apostrophes;
}