Sorting table results using numeric sorting

Sorting table results using numeric sorting

nickelmedianickelmedia Posts: 12Questions: 0Answers: 0
edited January 2013 in DataTables 1.9
I have a column that outputs multiple alphanumeric pairs per row. This data is pulled in from MySQL via a wordpress plugin. In every case, this data is out of numeric order and I'm hoping datatables might be able to solve it.

Example:

Table Cell currently contains: 9U 8U 12U 11U 10U
I want the cell to be sorted like: 8U 9U 10U 11U 12U

I was looking at the Sorting plugins (http://www.datatables.net/plug-ins/sorting) and think I'm on the right path, but don't really know how to implement. I've created a live site of the code I tried with some sample data as it displays on my site, but got no results. I'm clueless when it comes to jquery.

http://live.datatables.net/unixak/edit

Thanks!

Replies

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    It is out of numeric order because, of course, the data is not numeric - so DataTables is string sorting it.

    There are two options:

    1. Use the natural sorting plug-in, which I think is probably the best option here.
    2. If it is always going to be postfixed with 'U' you could strip the 'U' and then sort numerically on that - the formatted numbers plug-in should do that as it strips all non-numeric data.

    Allan
  • nickelmedianickelmedia Posts: 12Questions: 0Answers: 0
    Allan, I've got naturalSort in and half working. It sorts the column by the beginning number of each cell as I want it to, however the individual numbers within the cell are still out of order.

    Here a link to the site: http://www.floridausssasports.com/event-list/

    And here's the code dump:

    [code]

    /*
    * Natural Sort algorithm for Javascript - Version 0.6 - Released under MIT license
    * Author: Jim Palmer (based on chunking idea from Dave Koelle)
    * Contributors: Mike Grier (mgrier.com), Clint Priest, Kyle Adams, guillermo
    */
    function naturalSort (a, b) {
    var re = /(^-?[0-9]+(\.?[0-9]*)[df]?e?[0-9]?$|^0x[0-9a-f]+$|[0-9]+)/gi,
    sre = /(^[ ]*|[ ]*$)/g,
    dre = /(^([\w ]+,?[\w ]+)?[\w ]+,?[\w ]+\d+:\d+(:\d+)?[\w ]?|^\d{1,4}[\/\-]\d{1,4}[\/\-]\d{1,4}|^\w+, \w+ \d+, \d{4})/,
    hre = /^0x[0-9a-f]+$/i,
    ore = /^0/,
    // convert all to strings and trim()
    x = a.toString().replace(sre, '') || '',
    y = b.toString().replace(sre, '') || '',
    // chunk/tokenize
    xN = x.replace(re, '\0$1\0').replace(/\0$/,'').replace(/^\0/,'').split('\0'),
    yN = y.replace(re, '\0$1\0').replace(/\0$/,'').replace(/^\0/,'').split('\0'),
    // numeric, hex or date detection
    xD = parseInt(x.match(hre)) || (xN.length != 1 && x.match(dre) && Date.parse(x)),
    yD = parseInt(y.match(hre)) || xD && y.match(dre) && Date.parse(y) || null;
    // first try and sort Hex codes or Dates
    if (yD)
    if ( xD < yD ) return -1;
    else if ( xD > yD ) return 1;
    // natural sorting through split numeric strings and default strings
    for(var cLoc=0, numS=Math.max(xN.length, yN.length); cLoc < numS; cLoc++) {
    // find floats not starting with '0', string or 0 if not defined (Clint Priest)
    oFxNcL = !(xN[cLoc] || '').match(ore) && parseFloat(xN[cLoc]) || xN[cLoc] || 0;
    oFyNcL = !(yN[cLoc] || '').match(ore) && parseFloat(yN[cLoc]) || yN[cLoc] || 0;
    // handle numeric vs string comparison - number < string - (Kyle Adams)
    if (isNaN(oFxNcL) !== isNaN(oFyNcL)) return (isNaN(oFxNcL)) ? 1 : -1;
    // rely on string comparison if different types - i.e. '02' < 2 != '02' < '2'
    else if (typeof oFxNcL !== typeof oFyNcL) {
    oFxNcL += '';
    oFyNcL += '';
    }
    if (oFxNcL < oFyNcL) return -1;
    if (oFxNcL > oFyNcL) return 1;
    }
    return 0;
    }


    jQuery.fn.dataTableExt.oSort['string-asc'] = function(a,b) {
    return naturalSort(a,b);
    };

    jQuery.fn.dataTableExt.oSort['string-desc'] = function(a,b) {
    return naturalSort(a,b) * -1;
    };


    $(document).ready(function() {
    var oTable = $('#example').dataTable({
    "bPaginate": false,
    "oSearch": {"sSearch": $.urlParam('search')},
    "aoColumnsDefs": [
    null,
    null,
    null,
    { "sType": "natural" }
    ]
    } );
    $('#district').change( function () {
    oTable.fnFilter( $(this).val() );


    } );
    } );

    [/code]

    Thanks!
  • nickelmedianickelmedia Posts: 12Questions: 0Answers: 0
    Can anyone help me with this?
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    The numbers in the cell such as:

    > 8U 14U 13U 12U 11U 10U

    ?

    DataTables will never sort information inside a cell I'm afraid. You'd need to modify whatever script you are using to generate that HTML to have it sorted correctly. An `order by` in an SQL statement I'd guess?

    Allan
  • nickelmedianickelmedia Posts: 12Questions: 0Answers: 0
    Thank you!
This discussion has been closed.