Sorting question

nroekel

On my website "http://www.parkeerprijzen.nl" I use DataTables as you can see. I have only one problem when sorting the columns "Prijs"(which means price in Dutch) and "Duur transfer"(which means duration transfer in Dutch). When sorting the column "Duur transfer" in ascending order it will sort like: 10, 11, 12, 13, 2, 4, 6, 8 instead of 2, 4, 6, 8, 10, 11, 12, 13. And I have a similar problem when sorting the column "Prijs" in ascending order it will sort like: 100, 120, 140, 155, 50, 60, 70, 99 instead of 50, 60, 70, 99, 100, 120, 140, 155. I hope someone has a solution for my problem.

Kind regards,

Nick

allan

You've got non-numeric data in the column, hence why it is string sorting rather than numeric sorting. This plug-in will help: http://datatables.net/plug-ins/sorting#natrual

Allan

nroekel

Hi Allan,

thanks for your quick response. I added sorting natrual, but I still got the same problem. I think I have done something wrong. Can you please have a look at the code below?:
[code]
<!--Script natural sort-->

/*
* 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['natural-asc'] = function(a,b) {
return naturalSort(a,b);
};

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


<!--Script datatable-->


$(document).ready(function() {
$('#myTable').dataTable( {
/*"aaSorting": [[ 1, "asc" ],[2,'asc']],*/
"bPaginate": false,
"bFilter": false,
"bInfo": false,
"bJQueryUI": true,
"oLanguage": {
"sProcessing": "Bezig met verwerken...",
"sLengthMenu": "Toon _MENU_ rijen",
"sZeroRecords": "Geen resultaten gevonden",
"sInfo": "_START_ t/m _END_ van _TOTAL_ gevonden parkeerplaatsen",
"sInfoEmpty": "Er zijn geen records om te tonen",
"sInfoFiltered": "(gefilterd uit _MAX_ parkeerplaatsen)",
"sInfoPostFix": "",
"sSearch": "Zoek:",
"sUrl": "",
"oPaginate":
{
"sFirst": "Eerste",
"sPrevious": "Vorige",
"sNext": "Volgende",
"sLast": "Laatste"
},
"aoColumnsDefs": [
null,
{ "sType": "natural" },
{ "sType": "natural" },
null,
]
}
} );
} );

[/code]

Kind regards,

Nick

allan

Hi Nick,

The code you've got above looks good and I believe is operating correctly for what it does - but unfortunately, on digging deeper, it doesn't do what is needed :-(. For example in your Transfer column you have the following HTML:

[code]


2 minuten
[/code]
The natural sorting method above is using that whole string, so in fact it's the "walk.png" which is currently defining the sort!

There are a number of ways to address this - the first I would suggest is to simply remove the HTML before passing it on to the natural sorting:

[code]
jQuery.fn.dataTableExt.oSort['natural-asc'] = function(a,b) {
var x = a.replace( /<.*?>/g, "" ).toLowerCase();
var y = b.replace( /<.*?>/g, "" ).toLowerCase();
return naturalSort(x,y);
};

jQuery.fn.dataTableExt.oSort['natural-desc'] = function(a,b) {
var x = a.replace( /<.*?>/g, "" ).toLowerCase();
var y = b.replace( /<.*?>/g, "" ).toLowerCase();
return naturalSort(x,y) * -1;
};
[/code]
Other methods might be using a hidden column with numeric only data in it (iDataSort) or a different sorting plug-in sort as the hidden title option.

Regards,
Allan