HOWTO: Sort on a column where the numeric part is prefixed by a String

HOWTO: Sort on a column where the numeric part is prefixed by a String

LindsayLindsay Posts: 5Questions: 2Answers: 0

I have a set of data where the first column contains a string prefix (in this case PL-) and then a number. Using the natural sort (ascending) I would see PL-1, PL-10, PL-11, PL-2 .... PL-9

My approach was to find a plugin that removed the unwanted text for the sort (I still want to be able to display the PL- portion for human consumption).

I came across the Anti-the plugin that states : This plug-in will strip the word "the" from the start of a string and sort on what is left.

Perfect. Replace 'the' with 'PL-' and I have a soluton. But it does not seem to work.

The modified plugin code:

DataTable.ext.type.order['anti-prefix-pre'] = function (a) {
return a.replace(/^PL-/i, '');
};

Everthing else in the plugin code is as per : https://cdn.datatables.net/plug-ins/2.0.7/sorting/anti-the.js

(QUESTION 1: why is it anti-prefix-pre ? I've tried with and without the -pre in my Datatable initialisation - but both not working.

My code to use this plugin is now:

table = $('#ticketTable').DataTable( {
    "data": tableData.rows,
    "columns": columns,
    "language": {
      "emptyTable": "You have no current open tickets"
    },
    "oLanguage": {
          "sLengthMenu": "Total Tickets : " + tableData.rows.length 
    },
    "columnDefs":[{targets:0, type: 'anti-prefix', // PL-1, PL-10, PL-11, PL-2 .... PL-9 etc
                   targets:[1,2,3,4], render:function(data){return data;}}, 
                  {targets:5, render:function(data){return data;}}, 
                  {targets:6, render:function(data){return data.split('@')[0];}}, 
                  {targets:7, "visible" : false, render:function(data){return data;}},                    
                 ],
    "order": [[0,"asc"]]
});

QUESTION 2: what am I missing in making this plugin work ? Or am I barking up the wrong tree and mis-understood things ?

I feel that wanting to order by number where there is a string prefix to the data must be a reasonably common use case - but searching this forum doesn't seem to provide a direct match (some close, but no cigar. Lots of talk about Orthogonal Data but I dont think this is the direction to head as its ony the sort I want to specify and the display is fine out of the box)

All help, pointers, dorections, examples will be gratefully received.

This question has an accepted answers - jump to answer

Answers

  • LindsayLindsay Posts: 5Questions: 2Answers: 0

    Update : so stepping through code with the debugger I can see that the 'anti-prefix' function is being registered alongside all the other datatypes (num, html etc)

    But the registered function is never called - suggesting I am doing something wrong in the declaration of my column datatype being anti-prefix type.

  • LindsayLindsay Posts: 5Questions: 2Answers: 0

    SOLVED:

    Discovered type-o in my initialisaton and updated plugin code:

    function _anyNumberSort(a, b) {

    const numA = parseInt(a.split("-")[1], 10);
    const numB = parseInt(b.split("-")[1], 10);
    return numA - numB;
    

    }

    DataTable.ext.type.order['prefix-number-asc'] = function (a, b) {
    return _anyNumberSort(a, b);
    };
    DataTable.ext.type.order['prefix-number-desc'] = function (a, b) {
    return _anyNumberSort(b,a);
    };

    AND

    table = $('#ticketTable').DataTable( {
    "data": tableData.rows,
    "columns": columns,
    "language": {
    "emptyTable": "You have no current open tickets"
    },
    "oLanguage": {
    "sLengthMenu": "Total Tickets : " + tableData.rows.length
    },
    "columnDefs":[{targets:0, type: 'prefix-number'},
    {targets:[1,2,3,4], render:function(data){return data;}}, {targets:5, render:function(data){return data;}}, //DATE
    {targets:6, render:function(data){return data.split('@')[0];}},
    {targets:7, "visible" : false, render:function(data){return data;}},
    ],
    "order": [[0,"asc"]]
    });

    Hopefull this will be useful for some other poor soul

  • allanallan Posts: 63,434Questions: 1Answers: 10,458 Site admin
    Answer ✓

    Hi,

    I'd recommend using the natrual sorting plug-in for this sort of thing.

    Allan

Sign In or Register to comment.