How can I get jQuery DataTables to sort on hidden

Question

I have a simple DataTables grid which contains date columns. I have provided two values for the date in my JSON data set, one for display and one specifically designed so that DataTables can sort it. My web application allows users to choose a bunch of different date formats, so it needs to be flexible.

This is my JSON data that DataTables gets from from the web server via sAjaxSource.

{
  Reports : [
    { Date: { Sort = "20101131133000", Display : "11/31/2010 1:30 PM" } }, 
    { Date: { Sort = "20100912120000", Display : "1200 EST 2010-09-12" } }, 
  ]
}

It is easy to tell DataTables to sort based on the Date.SortValue property and to make the Display property visible to the user by using fnRender(). So this gets me halfway to my goal.

var dataTableConfig = {
  sAjaxSource: "/getreports",
  sAjaxDataProp: "Reports",
  aoColumns: [
    { mDataProp: "User" },
    { mDataProp: "Date.Sort", 
      bSortable: true, 
      sName: "Date", 
      bUseRendered: false, 
      fnRender: function (oObj) {
        return oObj.aData[oObj.oSettings.aoColumns[oObj.iDataColumn].sName].Display;
      }
    }
  ]
};

Here's my problem. I want to allow the user to enter a filter (using the built-in filter input that DataTables provides) based on the displayed value, but they cannot.

For example. If a user entered "EST", they would get zero results because datatables filters based on the value specified in mDataProp not based on the value returned from fnRender.

Can anyone help me figure out how to sort AND filter a date column? Thanks.

Answer 1

For those using an ajax data source you can use Orthogonal data. For instance, in your ajax response return one of your columns as an object with a display value and a sort value (similar to what the OP did):

{
   "data":[
      {
         "customer":"JOHN DOE",
         "rating":{
            "display": "★★★",
            "sort":"3"
         },
      },
      {
         "customer":"BILLY NOAH",
         "rating":{
            "display": "★★★★★",
            "sort":"5"
         },
      }
   ]
}

Now in your table options you can use columns() like this:

"columns" : [
   {
      "data":"customer"
   },
   {
      "data":"rating",
      "render":{
         "_":"display",
         "sort":"sort"
      }
   }
]

Answer 2

Umm...instead of going through all of this rigmarole just add a hidden span with your "Sort By" to the front:

<td><span style="display:none;">20101131133000</span>11/31/2010 1:30 PM</td>

Note: This does mean that they can search by either the hidden or shown value...this may be a consequence you can't live with.

Answer 3

I believe the existing answers are deprecated due to updates to DataTables. HTML5 supports attributes that DataTables can use to easily sort columns. Specifically the data-sort attribute. (See https://datatables.net/examples/advanced_init/html5-data-attributes.html)

I can easily sort tables like so:

<table>
  <thead>
    <tr>
      <td>Name</td>
      <td>Age</td>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>John Doe</td>
      <td data-sort="37">2/1/78 (37 years old)</td>
    </tr>
    <tr>
      <td>Jane Doe</td>
      <td data-sort="35">12/1/80 (35 years old)</td>
    </tr>
  </tbody>
</table>

It doesn't matter that the 'Age' column contains numbers, symbols, and letters, DataTables will sort using the 'data-sort' attribute.

Answer 4

Since you already have your data in sortable and displayable format, this is all code you need.

It will use Date.Sort for sorting and Date.Display for visuals. This is documented here.

columns: [{
    data: 'Date',
    render: {
        _:   'Display',
        sort: 'Sort'
    }
}]

Answer 5

Try a little bit different approach:

Put both columns in the table (I will call them DateDisplay and DateSort), do not use render function and just hide the DateSort column. Then in the aoColumns array for the column DateDisplay put { "iDataSort": 2 }, where 2 is an index of the DateSort column.

In this case, DateDisplay column will be shown in original data, and filter will be done by this column, but sorting will be done by the values in the DateSort column.

There are more details about the iDataSort property on the datatables site or in the http://www.codeproject.com/KB/scripting/JQuery-DataTables.aspx section"Configure sorting".

Answer 6

+1 JocaPC

I'd like to add to JocaPC's answer by reminding everyone that javascript utilizes zero-indexed arrays.

Example:

HiddenSortString (0) | Date (1)                   | Some Text (2)
...................................................................
1349035566           | September 30, 2012 2:06 pm | blah blah
1349118137           | October 01, 2012 1:02 pm   | blah blah
1349371297           | October 04, 2012 11:21 am  | blah blah
...................................................................

To sort the date field using the hidden string you would use the following.

$('.mytable').dataTable({
    "aoColumns": [{"bVisible": false},{"iDataSort": 0},null]
});