Sorting Hyperlinked DATE mm/dd/yyyy

mrr2ro

Hello Data Tables Forum!

I have a particular issue and after searching the web and this forum and I see lots of suggestions but none of them, have helped me yet.

One of my reports, have a Column that is a yyyy/mm/dd date format, it is generated by smarty / php and via smarty the date is reformatted to be mm/dd/yyyy becasue it comes off the database in the yyyy/mm/dd format.

When the report first loads, the date sort works very well, whether if it ascending or descending.

If you sort by date ---3rd column, I can see when reloading the page that the date is sorted correctly grouped correctly by year, then month, then day -- ascending or descending but when jquery kicks in and create the wrap around for the static header, the date gets resorted and it does not sort correctly.

I suspect the reason is because the date has an HREF tag attached to it. If I removed the HREF, then it sorts just fine --- I NEED the date to be a hyperlink, and to add pain to injury, not all rows have a date on them so I use &nbsp, as filler so the table formatting does not collapse.

Data Tables forums suggests to force the type using sType, and I have tried the suggestions below.
http://www.datatables.net/forums/discussion/4904/blanks-in-the-date-column-causes-string-sort/p1

This is how the HTML gets pumped to the browser
[code]
04/15/2009 
[/code]

I could detect blank records and insert the &nbsp where there is no date, so far every date gets a nbsp appended to it in the HTML template to preserve table formatting.

And here is the final futile attempt that it just spins (processing) and does noes nothing.


[code]
<!--###############################
Begin DataTables-->


{literal}


/* Data Tables configuration -- Basic initialization*/


jQuery.noConflict();
/*
jQuery.fn.dataTableExt.aTypes.unshift(
function ( sData ) {
if (sData !== null && sData.match(/^(0[1-9]|[12][0-9]|3[01])\/(0[1-9]|1[012])\/(19|20|21)\d\d$/))
{
alert(sData);
return 'uk_date';
// return 'date';
}
return null;
}
);
*/

jQuery.fn.dataTableExt.oSort['uk_date-asc'] = function(a,b) {
var ukDatea = a.split('/');
var ukDateb = b.split('/');

var x = (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
var y = (ukDateb[2] + ukDateb[1] + ukDateb[0]) * 1;

return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};


jQuery(document).ready( function() {
jQuery('#theTable').dataTable( {


/*BEGIN Fixing the index row so they are not sorted -r2ros */
"fnDrawCallback": function ( oSettings ) {
var that = this;
/* Need to redo the counters if filtered or sorted */
if ( oSettings.bSorted || oSettings.bFiltered )
{
this.$('td:first-child', {"filter":"applied"}).each( function (i) {
that.fnUpdate( i+1, this.parentNode, 0, false, false );
} );
}
},

"aoColumnDefs": [
{"bSortable": false, "aTargets": [ 0 ] },
{"sType": "uk_date-asc", "aTargets": [ 3 ] }
],

[/code]



Any guidance will be much appreciate it.

/* includes the buttons to export or save as excel, csv, copy, and print -r2ros */

allan

Hi,

I actually think you might be really close with what you've got at the moment - there are a few issues however:

1. Use "uk_date" for the column's sType rather than with "-asc" postfixed. DataTables will automatically add "-asc" or "-desc" depending on the sort type that is required.

2. You are missing the "desc" sort function - its basically the same, but you will want the sort result to be reversed.

3. The HTML needs to be stripped from the data so it can be sorted by date only.

Here is an updated version of your code which I think should do the business:

[code]
jQuery.fn.dataTableExt.oSort['html-date-pre'] = function( s ) {
var a = s.replace( /<.*?>/g, "" ).split('/');
return parseInt( a[2]+a[0]+a[1], 10 );
};

jQuery.fn.dataTableExt.oSort['html-date-asc'] = function( s ) {
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};

jQuery.fn.dataTableExt.oSort['html-date-desc'] = function( s ) {
return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};

jQuery(document).ready( function() {
jQuery('#theTable').dataTable( {
/*BEGIN Fixing the index row so they are not sorted -r2ros */
"fnDrawCallback": function ( oSettings ) {
var that = this;
/* Need to redo the counters if filtered or sorted */
if ( oSettings.bSorted || oSettings.bFiltered )
{
this.$('td:first-child', {"filter":"applied"}).each( function (i) {
that.fnUpdate( i+1, this.parentNode, 0, false, false );
} );
}
},

"aoColumnDefs": [
{ "bSortable": false, "aTargets": [ 0 ] },
{ "sType": "html-date", "aTargets": [ 3 ] }
],
...
} );
} );

[/code]

Note that in this code I've used the special "-pre" function as well as "-asc" and "-desc" - this is something that DataTables 1.9 uses to improve the performance of the sort - it ensures that each data element is only "prepared" for sorting once, rather than many times as can happen in the case were the data formatting is put into the sorting function itself.

I've also renamed the sort to be "html-date" and reordered the way the numbers were being used in the array formatting (to match the 'mm/dd/yyyy' format.

I don't have your data to test it on of course, but I think that should do it :-). If it doesn't, could you run your table through the debugger (link below the reply box on this thread) so I can see what might be happening.

Finally, if you are interested in taking this further, have a look at this blog post which describes a nice clean way of using JSON data to provide different data for sorting, filtering, display etc: http://datatables.net/blog/Orthogonal_data .

Regards,
Allan