如何让jQuery DataTables对隐藏值进行排序，而搜索显示值?

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.

我有一个简单的datatable网格，其中包含日期列。我在JSON数据集中为日期提供了两个值，一个用于显示，另一个是专门设计的，以便DataTables对其进行排序。我的web应用程序允许用户选择一系列不同的日期格式，因此它需要灵活。

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

这是DataTables通过sAjaxSource从web服务器获得的JSON数据。

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

告诉DataTables基于日期排序是很容易的。SortValue属性，并通过使用fnRender()使显示属性对用户可见。这让我达到了我的目标。

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.

这是我的问题。我希望允许用户根据显示的值输入一个过滤器(使用DataTables提供的内置过滤器输入)，但他们不能。

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.

为例。如果用户输入“EST”，他们将得到零结果，因为datatables基于mDataProp中指定的值进行筛选，而不是基于fnRender返回的值。

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

谁能帮我弄清楚如何对日期列进行排序和筛选吗?谢谢。

7 个解决方案

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

在最近版本的DataTables中，不赞成使用buserendering和fnRender。

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

mRender是做这种事情的新方法，并且有一个稍微不同的方法。

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)

我认为现有的答案由于数据更新的更新而被弃用。HTML5支持DataTables属性，可以方便地对列进行排序。具体数据排序属性。(参见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.

“年龄”列包含数字、符号和字母并不重要，DataTables使用“数据排序”属性进行排序。

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

将这两列都放到表中(我将它们称为DateDisplay和DateSort)，不要使用呈现函数，只隐藏DateSort列。然后在列DateDisplay的aoColumns数组中放入{“iDataSort”:2}，其中2是DateSort列的索引。

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.

在本例中，DateDisplay列将显示在原始数据中，过滤器将由该列完成，但排序将由DateSort列中的值完成。

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

在datatable站点或http://www.codeproject.com/KB/scripting/JQuery-DataTables.aspx节“配置排序”中有关于iDataSort属性的更多细节。

#4

+1 JocaPC

+ 1 JocaPC

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

我想通过提醒大家javascript使用了零索引数组来补充JocaPC的答案。

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

#5

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.

注意:这确实意味着他们可以通过隐藏或显示的值进行搜索……这可能是你无法忍受的后果。

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

必须按隐藏列(sort)对列进行排序。为此，您必须包含一个包含排序数据的列，隐藏列，并通过隐藏列对显示列进行排序。

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

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

#1