CODEWITHSUNDEEP
jqueryjquery-pluginsdatatables
jessegavin
jessegavin

Reputation: 75650

How can I get jQuery DataTables to sort on hidden value, but search on displayed value?

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.

Upvotes: 41

Views: 57963

Answers (9)

rémy
rémy

Reputation: 1052

Use the data-sort attribute on the td, like

<td data-sort="12342345434">Thursday, May 9th 11</td>

Upvotes: 5

You Old Fool
You Old Fool

Reputation: 22940

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

Upvotes: 3

Martin Wickman
Martin Wickman

Reputation: 19905

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

Upvotes: 4

user3114969
user3114969

Reputation: 13

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

Upvotes: 0

Jovan MSFT
Jovan MSFT

Reputation: 14610

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

Upvotes: 20

user183240
user183240

Reputation:

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.

Upvotes: 86

Serj Sagan
Serj Sagan

Reputation: 30208

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.

Upvotes: 19

CWSpear
CWSpear

Reputation: 3270

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

Upvotes: 25

Brian
Brian

Reputation: 1025

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

Upvotes: 11

Related Questions