How can I get jQuery DataTables to sort on hidden value, but search on displayed value?
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.
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".
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
}
}
...