Datatables sort type asc / desc

Datatables sort type asc / desc

ECEGROUPEECEGROUPE Posts: 69Questions: 23Answers: 1
edited November 2023 in DataTables

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 :)

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 60,903Questions: 1Answers: 9,927 Site admin
    Answer ✓

    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

  • ECEGROUPEECEGROUPE Posts: 69Questions: 23Answers: 1
    edited November 2023

    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'
        } );
    
  • kthorngrenkthorngren Posts: 19,864Questions: 26Answers: 4,685

    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

  • ECEGROUPEECEGROUPE Posts: 69Questions: 23Answers: 1

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

  • kthorngrenkthorngren Posts: 19,864Questions: 26Answers: 4,685
    edited November 2023

    It's always something. :smile: 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

  • allanallan Posts: 60,903Questions: 1Answers: 9,927 Site admin

    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

  • ECEGROUPEECEGROUPE Posts: 69Questions: 23Answers: 1
    edited November 2023

    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;
                }
            },
    
Sign In or Register to comment.