Implementing a custom sSortType and sort function for jQuery dataTables

Question

I'm having a hard time following the instructions on the documentation page. I have a table displaying the average time durations in one column, in the HH:MM format, e.g 10:45 means ten hours and forty-five minutes. I would like to be able to sort my 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 to be the proper way to add a new sort function and a new sSortType into 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 suppose there is a much better way for extracting the number of minutes than the way I do it, but let's suppose my algorithm is valid. What must I do to initialize my dataTable properly and integrate this sort function and data type into it? The table itself renders properly, but when I try to sort the column in question, it sort itself lexicographically, as if it were a string. Any ideas?

Answer 1

You should provide both a <type>-asc method and a <type>-desc method.

The sort 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 a previous answer, here is how I completely solved my issue:

• First, when dealing with a custom data type, one needs a type detection method, like the one found on the dedicated doc page. The method must be placed BEFORE the dataTable init method. Here's what my method looks like, keeping in mind the HH:MM format I need to analyse:

  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 the ascending and descending sort functions such as the one I typed in the question (now edited correctly), which should ALSO be placed before the init method.

• Finally, you must tell the dataTable init method to expect the datatype inside the column you wish. This is done in the aoColumns array. In my case, this would mean:

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

  The asc and desc will be appended once the user clicks on the header of the column, and that is why the I only wrote duration in the last line.