Implementing custom sSortType function and sorting for jQuery dataTables

Question

I find it difficult to follow the instructions on the page. I have a table showing the average lengths of time in one column, in the format HH: MM, for example, 10:45 means ten hours and forty-five minutes. I would like to be able to sort the entire table by the values ​​in this column.

Here is my initialization code:

var aoTable = $("#TableStatistic").dataTable({ "bDestroy": true, "sDom": "<'row-fluid dt-header'<'span6'f><'span6'T>>t<'row-fluid dt-footer'<'span6'i><'span6'p>>", "oTableTools": { "aButtons": ["xls", "pdf", "print"], "sSwfPath": "../Content/media/swf/copy_csv_xls_pdf.swf" }, "aaData": statisticsModel.byCategoriesList(), "aaSorting": [[0, "desc"]], "bPaginate": false, "aoColumns": [ { "mDataProp": "CategoryName", "sTitle": "Reports.CategoryName" }, { "mDataProp": "AverageTime", "sTitle": "Reports.AverageTime", "sSortDataType": "duration-desc"}, { "mDataProp": "NumberOfProblemsSolved", "sTitle": "Reports.NumberOfProblemsSolved" } ], "oLanguage": MeridianTranslation.DataTable }); 

Here is what I ASSUME is - the right way to add a new sort and a new sSortType to my table:

 jQuery.extend(jQuery.fn.dataTableExt.oSort['duration-desc'] = function (x, y) { var xHours = parseInt(x.slice(0, x.indexOf(':'))); var xMinutes = parseInt(x.slice(x.indexOf(':') + 1, x.length)) + xHours * 60; var yHours = parseInt(y.slice(0, y.indexOf(':'))); var yMinutes = parseInt(y.slice(y.indexOf(':') + 1, y.length)) + yHours * 60; return ((xMinutes < yMinutes) ? -1 : ((xMinutes > yMinutes) ? 1 : 0)); }); jQuery.extend(jQuery.fn.dataTableExt.oSort['duration-asc'] = function (x, y) { var xHours = parseInt(x.slice(0, x.indexOf(':'))); var xMinutes = parseInt(x.slice(x.indexOf(':')+1, x.length)) + xHours * 60; var yHours = parseInt(y.slice(0, y.indexOf(':'))); var yMinutes = parseInt(y.slice(y.indexOf(':')+1, y.length)) + yHours * 60; return ((xMinutes < yMinutes) ? 1 : ((xMinutes > yMinutes) ? -1 : 0)); }); 

I assume that there is a much better way to extract the number of minutes than the way I do it, but suppose my algorithm is valid. What should I do to properly initialize my DataTable and integrate this sorting function and data type into it? The table itself is displayed correctly, but when I try to sort the column in question, it sorts itself lexicographically, as if it were a row. Any ideas?

Answer 1

You must specify both the <type>-asc method and the <type>-desc .

Sorting is then based on the sType property of the column:

 jQuery.fn.dataTableExt.oSort["duration-desc"] = function (x, y) { ... }; jQuery.fn.dataTableExt.oSort["duration-asc"] = function (x, y) { ... } var oTable = $("#products").dataTable({ "aaData": [ [1, "Dinner", "0:40"], [2, "Study", "11:25"], [3, "Sleep", "7:30"] ], "aoColumns": [{ ... }, { ... }, { ... "bSortable": true, "sType": "duration" }] }); 

Here is a simple jsFiddle example.

Answer 2

To expand on what MasterAM said in the previous answer, here is how I completely solved my problem:

• First , when you are working with a custom data type, you need a type discovery method, such as the one found in the highlighted document page . The method must be placed BEFORE the dataTable initialization method. Here's what my method looks like, bearing in mind the HH: MM format that I need to analyze:

   jQuery.fn.dataTableExt.aTypes.push(function (sData) { var sValidChars = "0123456789:"; var Char; for (i = 1 ; i < sData.length ; i++) { Char = sData.charAt(i); if (sValidChars.indexOf(Char) == -1) { return null; } } if (sData.charAt(1) == ':' || sData.charAt(2) == ':') { return 'duration'; } return null; }); 

  I named my data type duration accordingly.

• Second , you need to implement ascending and descending sorting functions, such as the one I introduced into the question (now edited correctly), which should also be placed before the init method.

• Finally , you must tell initTableTable to expect the data type inside the column that you want. This is done in the aoColumns array. In my case, this would mean:

   { "mDataProp": "AverageTime", "sTitle": "Reports.AverageTime", 'sType': 'duration' } 

  asc and desc will be added after the user clicks on the column heading, which is why I wrote only duration in the last row.