How can I get jQuery DataTables to sort on hidden

问题:

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.

回答1:

This is an old post, but hopefully this will help someone else that comes here.

In a more recent version of DataTables, bUseRendered and fnRender are deprecated.

mRender is the new way of doing this sort of thing and has a slightly different approach.

You can solve your issue with something along the lines of:

...
{ mDataProp: "Date.Sort"
  bSortable: true, 
  sName: "Date", 
  // this will return the Display value for everything
  // except for when it's used for sorting,
  // which then it will use the Sort value
  mRender: function (data, type, full) {
    if(type == 'sort') return data.Sort;
    return data.Display
  }
}
...

回答2:

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.

回答3:

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".

回答4:

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.

回答5:

+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]
});

回答6:

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'
    }
}]

回答7:

You have to sort column by a hidden column (Sort). To to this you have to include a column that contains the sort data, hide the column and sort the Display Column by the hidden column.

   "aoColumnDefs:[
    {"sTitle": "Display", "iDataSort":1, "aTargets":[2]},
    {"bVisible": false, "aTargets":[2]}
    ], 

aoColumns: [
    { mData: "User" },
    { mData: "Display"},
    { mData: "Sort"}
  ]

回答8:

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"
      }
   }
]