Filtering using the rendered text, however sorting using the original value.

DanMorin

I have a table with a column displaying the date of an event. The server returns the date in the format GMT/UTC such as "2012-01-26T19:43". Therefore, I have to use I use fnRender() to display this date in a friendlier format, and also in the timezone of the user.

date.format('MMM dd, yyyy @ hh:mm') -> "Jan 26, 2012 @ 19:43"

The problem is once I use fnRender(), the sorting uses the new rendered value, which is incorrect. For instance, the month of April is sorted before January because A < J. When I set bUseRendered=false, the sorting is correct, however the filtering is incorrect. For instance, if I search for "Jan", the table returns nothing because it searches for "2012-01-26T19:43".

I wonder if a new flag could be added, such as bFilterUsingDisplayText=true so the search/filtering is always done according to what is rendered on the table. This way, if a user types something in the search box, he/she will see a filtering of the the content of the table rather than a filtering of the data sent by the server. In other words, I would like to have a mechanism to separate the sorting from the filtering.

If this is not possible, could you please tell me which line I need to add or modify to change the filtering behavior. Perhaps replacing the string 'filter' to 'display' when calling _fnGetRowData() or somewhere else could do the trick.

Thank you for your great work. I am very impressed by the ease of use of this DataTable. Keep up the good work.

allan

Hi!

Great question :-). I'm actually planning on writing a blog post about this exact topic in the next day or two, however, it is actually reasonably simple to use orthogonal data for sorting, filtering and display with DataTables 1.9 (currently in beta, but the final release should be next week: http://datatables.net/download ).

So the way that this can be done is that in 1.9 mDataProp can be used as a function, and that function will tell you what type of data the table is requesting (i.e. sorting data or display data etc).

This is how you might use different data for sorting/type detection (what sorting will be applied) and for filtering/display. I've used column index 0 below, but it can be adjusted as needed, and a date rendering function called renderDate():

[code]
$(document).ready(function() {
  var oTable = $('#example').dataTable( {
    "aoColumnDefs": [
    {
      "aTargets": [ 0 ],
      "mDataProp": function ( source, type, val ) {
        if (type === 'set') {
          source[0] = val;
          // Store the computed display for speed
          source.date_rendered = renderDate( val );
          return;
        }
        else if (type === 'display' || type === 'filter') {
          return source.date_rendered;
        }
        // 'sort' and 'type' both just use the raw data
        return source[0];
      }
    ]
  } );
} );
[/code]

Now whenever DataTables wants some data it will call your mDataProp function with the request type set and you return what data you want for it to act on for that type.

mDataProp is fully documented here: http://datatables.net/docs/DataTables/1.9.beta.3/DataTable.defaults.columns.html#mDataProp .

Regards,
Allan

DanMorin

Thank you Allan for your quick reply. Is there a way to define mDataProp to always use the rendered value, regardless of the column. I understand this solution works well for one column displaying the data, however there are other columns where the filtering would use the rendered value, such as an amount or a percentage. For instance, the server sends 0.456 as the percentage, however the table displays 45.6% to the user. If the user attempt to search for 45.6, the search fails because there is no raw value containing 45.6.

I am looking for a solution where I can modify the file jquery.datatables.js so it would work on all tables. I more than a dozen of pages using your tables, and some tables have a lot of columns, including dates, amounts $, percentage, and so on.

allan

aoColumnDefs allows you to define an mDataProp that will work for 1 or more columns by using aTargets, however, the difficulty is that mDataProp doesn't actually know what the original column was...!

So what I would suggest in this case is simply use a function to wrap the mDataProp function that I have above. For example:

[code]
function render( column, renderType ) {
return function ( source, type, val ) {
  if (type === 'set') {
    source[column] = val;
    // Store the computed display for speed
if ( renderType === "date" ) {
    source[column+'_rendered'] = renderDate( val );
}
else if ( renderType === "percentage" ) {
    source[column+'_rendered'] = renderPercent( val );
}
else {
    source[column+'_rendered'] = val;
}
    return;
  }
  else if (type === 'display' || type === 'filter') {
    return source[column+'_rendered'];
  }
  // 'sort' and 'type' both just use the raw data
  return source[column];
};
};

$(document).ready(function() {
  var oTable = $('#example').dataTable( {
    "aoColumns": [
     { "mDataProp": render( 0, 'date' ) },
     { "mDataProp": render( 1, 'percentage' ) }
null // regular column
]
  } );
} );
[/code]

This way you can easily reuse your rendering code and still take advantage of the flexibility of mDataProp.

Regards,
Allan

DanMorin

Thank you for your suggestion. Unfortunately, the code does not work. In summary, the variable type is always undefined. I set a couple breakpoints and the function( source, type, val ) is called, however the condition (type == 'set') or (type === 'display' || type === 'filter') is never called.

This is the code I used.

[code]
function render( column, renderType ) {
return function ( source, type, val ) {
if (type === 'set') {
source[column] = val;
// Store the computed display for speed
if ( renderType === "date" ) {
source[column+'_rendered'] = FormatDateTimeAgo( val );
}
else if ( renderType === "percentage" ) {
source[column+'_rendered'] = FormatPercentage( val );
}
else {
source[column+'_rendered'] = val;
}
return;
}
else if (type === 'display' || type === 'filter') {
return source[column+'_rendered'];
}
// 'sort' and 'type' both just use the raw data
return source[column];
};
};


$(document).ready(function()
{
var oTable = $('#test').dataTable(
{
"aoColumns":
[
null, // regular column
{ "mDataProp": render( 1, 'percentage' ) },
{ "mDataProp": render( 2, 'date' ) }
],
"aaData":
[
[ "Dan", .4, "2012-01-17T13:32:00" ],
[ "Jim", .25, "2012-01-16T20:32:43" ],
[ "Smith", .5, "2012-01-27T11:22:33" ],
[ "Bart", .99, "2012-01-22T02:03:00" ]
]
});

}); // ready()
[/code]

Ideally, it would be best to not have to change anything for the columns. Almost each column has a function fnRender(), many of them containing hyperlinks, which makes it very difficult to move into mDataProp. Also, I noticed that when I call fnRender(), the original data is destroyed in the original oData. For instance, I have a customer in [0] and his identifier is at [6]. Therefore I format the cell this way.

[code]
"fnRender": function(oObj)
{
return '' + oObj.aData[0] + '';
}
[/code]

The problem is if I have another fnRender() for another column, the content of oObj.aData[0] has been replaced with the hyperlink. Is there a way to render the data for the display cell, however preserve the original data for all calls to fnRender()?

allan

Are you using DataTables 1.9 beta 3 or newer? That is required for using mDataProp in this fashion.

> Is there a way to render the data for the display cell, however preserve the original data for all calls to fnRender()?

Yes - you can set the bUseRendered parameter for that column to false. That will stop the data being overwritten in the data source. However! As you have seen this effects both filtering and filtering...

This problem that you are seeing with fnRender is exactly the problem that mDataProp is trying to solve by providing the ability to separate the data used for sorting, filtering, display and type detection from each other, and potentially from the original source data as well.

So while I realise it might be a little bit of a brain bender to get going with mDataProp, and a nuisance to convert from fnRender to mDataProp, I think it provides a number of real advantages and allows you to do exactly what you are looking to do.

Regards,
Allan

nummer5

I'd say one would almost always want to *sort* based on the original data (e.g. date values) rather than the rendered cell content which might be formatted following local preferences. When *filtering*, however, you enter the values like displayed.

The only exception I can think of is numbers with thousands separator: You would want to enter the filter without the separator, or to have the separator ignored.

Thus, a bUseRendered which affects both sorting and filtering is not very useful IMO. This should be solved in a generic way based on data types.

Tobias

allan

Hi Tobias,

There are a number of other exceptions as well - telephone numbers, combining information from multiple columns into one where you would want to sort on the rendered data or transforming from one formatted data type to another (a translation perhaps). So bUseRendered certainly has a place, however it is not a complete solution as you point out yourself and as Dan also notes.

What is really needed is a method to allow complete separation between sorting, filtering and display and this is what mDataProp offers. bUseRendered is now outmoded and I think will be deprecated and ultimately removed from DataTables in favour of the more complete and flexible mDataProp method.

It would be possible (and I did consider this) having different rendering and 'use' flags for display, filtering, sorting and type detection, but I think that would ultimately be confusing and add potentially a large overhead to the initialisation code for - hence I went with the mDataProp as a function method.

Allan

DanMorin

Thank you for all your comments. I understand the issue of performance, both regarding the CPU and memory requirements of storing multiple values for the same cell.

My suggestion would be not storing additional data, however use different data based on the column flags (bUseRendered or bFilterUsingDisplayText) just before sorting and/or before filtering. For instance, if filtering is done using the display data, then the function building the array of strings could fetch the display data rather than the original data. This would not impact the table initialization at all, but only at the moment of filtering by adding a single "if" per cell in the table.

allan

The trouble with using a flag such as bFilterUsingDisplayText is that you would need flags for using sorting data for display, or for filtering, or type detection etc as well. This would become exceptionally complicated not only to write for DataTables core, but also for developers such as yourself using the documentation. There would be an unwieldy number of options available.

Using mDataProp as a function has no impact on memory over the alternative of using flags, since the data is going to be stored somewhere (be it by DataTables core you your mDataProp function - actually it doesn't need to store the data, it could just recalculate the data on each request - a tradeoff between computation and memory), and I think in terms of processing will have a good deal less overhead than the complex logic that would be needed for the number of flags needed to bring that ability up to that capability of mDataProp as it currently stands.

Allan

DanMorin

I upgraded to 1.9.0 and tried to use mDataProp, however there are still many issues. First, I would like to use a generic function mDataProp, such as:

{ "mDataProp": DataPropDate }

where
[code]function DataPropDate(source, type, val)
{
if (type === 'display' || type == 'filter')
return FormatDate(val);
return val;
}[/code]


This way, when I format a date, I do not have to create a new function each time. The problem is the parameter val is not defined when calling mDataProp.

One solution is fnRowDataCallback() which could solve many issues at once.
http://datatables.net/forums/discussion/8356/suggestion-fnrowdatacallback-to-fetch-the-row-data

allan

The third parameter (val) is only defined when the second parameter (type) is === "set". The function is effectively a getter and a setter rolled into one. If type === 'set' then it is a setter and the value must be stored somewhere (the 'source' most likely, although that doesn't strictly need to be the case). If type !== 'set' then it is a getter and the return must give the value you want to use for the given type.

The code that you posted before looks ideal (where you have { "mDataProp": render( 1, 'percentage' ) }).

Allan

DanMorin

Is there a way to supply the third parameter val when type !== 'set'? This would be tremendously useful.

allan

The thing is - what value? :-) The whole point of mDataProp as a getter is that it is providing the value to other parts of the table. When the table says "I need this data", it goes off to get it from mDataProp, so the value that is used is whatever you return, based on whatever logic you care to define. There isn't a "value" that can be given to the function when used as a getter.

Allan

DanMorin

I meant the original supplied value from aaData[]. Would it be possible to automatically pass the index of the column, so I do not have to create an extra function just to pass the column index which is quite error prone, especially when changing the order of the columns.

Example:

[code]function DataPropDate(source, iColumn, type, val)
{
if (type === 'set')
{
source[iColumn] = val;
return;
}
if (type === 'display' || type == 'filter')
return FormatDate(source[iColumn]);
return source[iColumn];
}[/code]

allan

> I meant the original supplied value from aaData[]

The thing is that the data might not come from aaData - nor does the code know which for the properties or array indexes in the source data that the data comes from. So I'm afraid I don't think this is possible.

> If this is not possible, could val contains the index of the column, so this parameter is available inside the function

I most certainly see the value in providing that information. At the moment, that information is not available to mDataProp (its like asking an array element what its index is, without being able to look it up!). I will look at adding the ability to pass that information through to the function in the next release as it could be exceptionally useful.

Allan

DanMorin

Sorry, I edited my post while you were replying.

I agree the mDataProp is the best solution in the long run. Calling mDataProp with the type 'sort' is far more efficient than parsing the dates each time a date comparison must be made. If sorting becomes slow, the mDataProp may called with the type 'sort' and then cache the values, then sort them using plain string comparison.

On a side note, I have written a genealogy software containing over 300,000 lines of C++ code, and for sorting purpose, I use a callback to get a string suitable for sorting. This string is often unreadable, however it makes sorting very efficient, because the processing is done only one per table cell, rather than having a callback to compare each table cell which may require significant processing just to fetch the value, and later more processing to compare with another cell. An example is sorting an array of the children age of a parent. First, fetching the array of children is costly, then computing the age of each child to the selected parent requires again more CPU because dates may contain approximate values. Then comparing this array is another story. My sort routine is O(n log n), so if I have to fetch the values each time I need to compare dates for sorting, it would be very slow. On the other hand, using a function to return a string for comparing the age of the children makes the process very snappy, even for large databases of over 100,000 individuals.

allan

Very interesting! DataTables 1.9 actually does something fairly similar in that there is now support for a "pre"-sort function - a function that can do any formatting required, allowing that formatting to occur only once per cell, regardless of how complex the sort itself turns out to be. The sorting plug-ins don't take advantage of that yet, but they could by just creating a 'pre' function with the formatting logic.

Allan

DanMorin

Just some feedback, I got EVERYTHING working. The mDataProp IS the way to go. Since I never use the type = 'set', the code implementing mDataProp may be as simple as a few lines of code.

For those having a similar problem and interested in a solution, I pasted below the full source code of my solution.

In short, I define aoColumns to the following:

[code]
"aoColumns":
[
{ "sClass": "NoNumber" },
{ "mDataProp": function(rgData, eType) { return FormatTypePercent(eType, 1 - (rgData[2] / rgData[1])); } },
{ "mDataProp": DataPropSales },
{ "mDataProp": DataPropCommissionPercent },
{ "mDataProp": DataPropCommission },
{ "mDataProp": DataPropNet },
],[/code]

The first column has a special class to not align the text to the right, as it is the case for the numbers. Since my tables contains mostly numbers, the CSS aligns all the cells to the right, and if a column display text, such as the Affiliate Name, then I use the class NoNumber.
To put some variety of implementation, the second column is an inline implementation of mDataProp displaying the profit %.
The third column calls the function DataPropSales():

[code]function DataPropSales(rgData, eType, val)
{
return FormatTypeAmount(eType, rgData[1]);
}
function FormatTypeAmount(eType, n)
{
if (eType === 'display')
return '$' + n;
if (eType == 'filter')
return n + ' $' + n;
return n;
}[/code]

Here is the full code. Notice, the code won't compile, as it uses functions from my main .js file, however you should get the idea how to use mDataProp.
[code]



Affiliate Name
Profits %
Sales
Commission %
Commission
Net






Total









function DataPropSales(rgData, eType, val)
{
return FormatTypeAmount(eType, rgData[1]);
}
function DataPropCommissionPercent(rgData, eType, val)
{
return FormatTypePercent(eType, rgData[2] / rgData[1]);
}
function DataPropProfitsPercent(rgData, eType, val)
{
return FormatTypePercent(eType, 1 - (rgData[2] / rgData[1]));
}
function DataPropCommission(rgData, eType, val)
{
return FormatTypeAmount(eType, rgData[2]);
}
function DataPropNet(rgData, eType, val)
{
return FormatTypeAmount(eType, rgData[1] - rgData[2]);
}

$(document).ready(function()
{
g_oTable = $('#test').dataTable(
{
"bJQueryUI": true,
"sScrollY": "150px",
"bPaginate": false,
"bScrollCollapse": true,

"fnFooterCallback": function(oRow, aaData, iStart, iEnd, aiDisplay)
{
var nTotalSales = 0;
var nTotalCommissions = 0;
for (var iRow = aiDisplay.length - 1; iRow >= 0; iRow--)
{
var rgData = aaData[aiDisplay[iRow]];
nTotalSales += rgData[1];
nTotalCommissions += rgData[2];
}
// Modify the footer row to display the sums and percentage
var nPercentCommission = nTotalCommissions / nTotalSales;
var rgCells = oRow.getElementsByTagName('th');
rgCells[1].innerHTML = FormatPercentage(1 - nPercentCommission);
rgCells[2].innerHTML = FormatAmount(nTotalSales);
rgCells[3].innerHTML = FormatPercentage(nPercentCommission);
rgCells[4].innerHTML = FormatAmount(nTotalCommissions);
rgCells[5].innerHTML = FormatAmount(nTotalSales - nTotalCommissions);
},

"aoColumns":
[
{ "sClass": "NoNumber" },
{ "mDataProp": function(rgData, eType) { return FormatTypePercent(eType, 1 - (rgData[2] / rgData[1])); } },
{ "mDataProp": DataPropSales },
{ "mDataProp": DataPropCommissionPercent },
{ "mDataProp": DataPropCommission },
{ "mDataProp": DataPropNet },
],
"aaData":
[
// Affiliate Name, Sales, Commission
[ "Dan", 1000000, 400000 ],
[ "Jim", 20000, 5000 ],
[ "Smith", 80000, 40000 ],
[ "Bart", 100000, 99000 ],
[ "Dan", 1000000, 400000 ],
[ "Jim", 20000, 5000 ],
[ "Smith", 80000, 40000 ],
[ "Mark", 500, 600 ]
]
});
}); // ready()
[/code]

allan

Awesome - thanks for posting your code. A really nice way of doing it :-)

Allan