Datatables sort type asc / desc

ECEGROUPE

Hi,

Link to the test case : https://live.datatables.net/xawerapu/1/edit

To explain the situation : I have a date at the format (DD/MM) that i transform to the format MM/DD when sorting to get it sort well but still display at the format (DD/MM) -> that work as intended.

The problem that i m facing is there is also empty value in that column. I want to sort those null value in last position in both case (sort asc / sort desc) but it look like it doesn't work -> still sort first when asc. Can you explain what i m doing wrong ?

After some test, for me it look like if (type === "asc") and if (type === "asc") doesn't work...

Thank

allan

After some test, for me it look like if (type === "asc") and if (type === "asc") doesn't work...

It wouldn't - the type parameter is never passed those values for the rendering function. Indeed, further up you have type === 'sort' which will evaluate to true, meaning that a check for a different value of type in that condition will always be false!

What you need is a sorting plug-in - they handle the asc and desc cases.

Have a look at this blog post which basically does what you need. Just have your rendering function return a number for the type === 'sort' case (or null) and use the absoluteOrder option described in that post.

Allan

ECEGROUPE

ok it work with the absolute plugin for the above case thx !

Now i have another question, is it possible to use the absolute plugin when the column have date as full format ( DD/MM/YYYY) and also emty value :

i use this but it doesn't work, null value go to the bottom everytime (ok) but the date are not sorting correcty ( they are sorting by DD and not the whole date anymore DD/MM/YYYY) :

    var NullValueLast = $.fn.dataTable.absoluteOrder( {
            value: null, position: 'bottom'
    } );

kthorngren

I would convert the date to a timestamp so its a number. I updated your example to use moment.js to convert DD/MM/YYYY dates to a timestamp. Also added the absolute.js plugin.
https://live.datatables.net/xawerapu/2/edit

Kevin

ECEGROUPE

this could work BUT i will lose the searchbuilder calendar because the column is not a datetime anymore and i realy need this feature...

kthorngren

It's always something. After a bit of research I found that the Ultimate sorting plugin returns a columns.type of 'moment-'+format:

$.fn.dataTable.ext.type.detect.unshift( function ( d ) {
    return moment( d, format, locale, true ).isValid() ?
        'moment-'+format :
        null;
} );

This SearchBuilder example indicates that the columns.type will be moment.

I took the absolute.js plugin code and defined the name as moment-DD/MM/YYYY which is what is returned for the columns.type.

    var o = {
        name: 'moment-DD/MM/YYYY',
        alwaysTop: {},
        alwaysBottom: {},
        asc: function (a, b, isNumber) { },
        desc: function (a, b, isNumber) { },
    };

SearchBuilder now uses the date picker for the date column. Here is the updated example:
https://live.datatables.net/xawerapu/3/edit

The plugin code is at the top of the Javascript tab and commented out the script tag to load it. The other change is I removed the .valueOf() from the columns.render function. I used initComplete output the settings to validate the columns.type setting:

{
    "idx": 4,
    "aDataSort": [
        4
    ],
    "asSorting": [
        "asc",
        "desc"
    ],
    "bSearchable": true,
...
    "_sManualType": "moment-DD/MM/YYYY",
...
    "sType": "moment-DD/MM/YYYY",
...
    "name": "Start date (DD/MM)",
    "type": "moment-DD/MM/YYYY"
}

@allan can confirm if this is a valid option.

If you use Luxon then probably something similar can be done but return the type as luxon.

Kevin

allan

That does complicate things a bit. It might be that your best option is to modify the built in DataTables' sorting for dates to basically act like the absolute ordering plug-in.

Can you show me the current state of your page please? I.e. what data and initialisation code you are using?

Allan

ECEGROUPE

My data are pretty basic : i get them from a json file, date are at the format 30/11/2023 (DD/MM/YYYY) -->

{ data: "DTBILANPREVU_SM2", name:"DTBILANPREVU_SM2", className: "editable", searchBuilderTitle: "[BILAN] Date prévue"

can you show me how to modify the built in DataTables' sorting for dates to act like the absolute ordering plug-in pls ?

i tryed ur solution @kthorngren everything look like it work but when i search for a date with searchuilder, it had a 0 at the end of my date :

i think it's because i still have this in plugin code :

var o = {
        name: 'moment-DD/MM/YYYY' + _unique++,
        alwaysTop: {},
        alwaysBottom: {},
        asc: function (a, b, isNumber) { },
        desc: function (a, b, isNumber) { },
    };

because if i delete _unique+++ there is no 0 anymore but the sorting doesn't work like it should ( null value doesn't go the end). I don't understand because i have all set up like ur exemple -> i took the javascript to replace my absolute plugin code i have set up my table with those setting :

// Test tri
    var NullValueLast = $.fn.dataTable.absoluteOrder( {
        value: null, position: 'bottom'
    } );

and for the column inside my table :

{ data: "DTBILANPREVU_SM2", name:"DTBILANPREVU_SM2", className: "editable flagDEPASSEE", searchBuilderTitle: "[BILAN] Date prévue",
            type: NullValueLast,
            render: function (data, type){
                if (type === "sort") {
                    if (data) {
                    return moment(data, 'DD/MM/YYYY');
                    }
                return null;
                }
            return data;
            }
        },