Filter post-processing and highlighting
Filter post-processing and highlighting
Hi everyone
I'm rather new to datatables and kind of overwhelmed with the amount of customization it supports. I'm also kind of lost in the documentation and currently I'm stuck on something that might sound trivial to most of you. But sitll I need help on implementing this.
I have a datatable with 3 columns on which I want to apply a filter based on a textbox. Two of the three columns are hidden so I don't mind about those, however I want to change the display output of the third column on the fly.
What I want to do is known to most of you. If I have cell data such as "abcde" and type "ab" in the filter, I want to have "ab" shown in bold (or simply a different class). I've added a custom fnRender in the aoColumns spec while initializing and it works fine. However, it seems the fnRender function is not called again upon the datable's redrawing when the filter is changed.
I suppose I'll need to add some code in the fnDrawCallback to alter the display, but I cannot figure out how to reference the table cells properly.
Any help please?
I'm rather new to datatables and kind of overwhelmed with the amount of customization it supports. I'm also kind of lost in the documentation and currently I'm stuck on something that might sound trivial to most of you. But sitll I need help on implementing this.
I have a datatable with 3 columns on which I want to apply a filter based on a textbox. Two of the three columns are hidden so I don't mind about those, however I want to change the display output of the third column on the fly.
What I want to do is known to most of you. If I have cell data such as "abcde" and type "ab" in the filter, I want to have "ab" shown in bold (or simply a different class). I've added a custom fnRender in the aoColumns spec while initializing and it works fine. However, it seems the fnRender function is not called again upon the datable's redrawing when the filter is changed.
I suppose I'll need to add some code in the fnDrawCallback to alter the display, but I cannot figure out how to reference the table cells properly.
Any help please?
This discussion has been closed.
Replies
Also, fnDraw() seems to ignore fnRender and whatever is inside.
So, I added the highlight code in there and everything worked fine.
Thanks!
CHgsd
CSS code:
[code]
.highlight { what you want to be done to your highlighted stuff }
[/code]
JS code:
[code]
$('.dataTables_filter input').keyup( function() {
$('#INSERTDATABLEID td').removeHighlight();
if ($('.dataTables_filter input').val() != "") {
$('#INSERTDATATABLEID td').highlight($('.dataTables_filter input').val());
}
});
[/code]
Note: This relies on the jQuery highlight plugin originally from here: http://gist.github.com/563055
(the original can be found here http://johannburkard.de/blog/programming/javascript/highlight-javascript-text-higlighting-jquery-plugin.html but the git version has some IE bugs fixed)
The reason I had to go with a .keyup() is that draws are only performed if what you are typing in the filter changes what you see. If you paginate on 10 entries, but your filter is 'abc', and only returns 5 entries, if you keep typing 'def' and the strings 'abcdef' are present, fnDrawCallback will not be triggered. I looked for some kind of callback function for when the filter is changed but couldn't find one. If anyone knows of one that would reduce the number of .keyup's
I hope this helps someone else looking for the same thing. Perhaps someone would integrate this as a plugin and do it more elegantly as I would imagine this would be a commonly desired feature.
Cheers,
CHgsd
Thanks for the code, tried it out, but when you have paging enabled, it only highlights the search results on the current page but not the rest of the pages, any solution for this?
thx
Thanks for pointing that out. That is a logical flaw on my part from when I changed implementations from fnDrawCallback to keyup. Because their are no keyup events fired on page changes, the highlighting is not being performed. The solution is simply the following however:
[code]
$('#INSERTDATATABLEID').dataTable({
[...]
"fnDrawCallback": function() {
$('#INSERTDATATABLEID td').removeHighlight();
if ($('.dataTables_filter input').val() != "") {
$('#INSERTDATATABLE td').highlight($('.dataTables_filter input').val());
}
},
[...]
});
$('.dataTables_filter input').keyup( function() {
$('#INSERTDATATABLEID td').removeHighlight();
if ($('.dataTables_filter input').val() != "") {
$('#INSERTDATATABLE td').highlight($('.dataTables_filter input').val());
}
});
[/code]
I am not pleased with this solution, or the previously pasted one for that matter, although they "work". For example I had someone test this in IE, and it triggers the "Stop running script?" dialog. Google Chrome and Mozilla Firefox handle it just fine however. I am running on local 4500 rows.
I welcome improvements, feedback, suggestions, etc... I thought I could hack this together quickly and it would "just" work but I see now that is not the case.
Cheers,
CHgsd
[code]
"fnRowCallback": function( nRow, aData, iDisplayIndex, iDisplayIndexFull ) {
var str = $('.dataTables_filter input').val();
var regex = new RegExp(str, "gi");
if (str != "") {
var colCount = aData.length;
for ( var i=0, len=colCount; i
[code]
$(document).ready(function() {
$('#example').dataTable({
"fnRowCallback": function( nRow, aData, iDisplayIndex, iDisplayIndexFull ) {
var settings = this.fnSettings();
var str = settings.oPreviousSearch.sSearch;
$('td', nRow).each( function (i) {
this.innerHTML = aData[i].replace( str, ''+str+'' );
} );
return nRow;
}
} );
} );
[/code]
Allan
Thank you not only for the tips, but also DataTables itself!
I made one small change to your code, which itself was a huge improvement over what I had done:
[code]
"fnRowCallback": function( nRow, aData, iDisplayIndex, iDisplayIndexFull ) {
var settings = this.fnSettings();
var str = settings.oPreviousSearch.sSearch;
$('td', nRow).each( function (i) {
this.innerHTML = aData[i].replace( new RegExp( str, 'i'), function(matched) { return ""+matched+"";} );
} );
return nRow;
},
[/code]
The problem I ran into with using yours verbatim was that it became case sensitive, so while the filtering is case insensitive, the highlighting was not. The above small change melds it all back together.
That all being said, I do not understand two aspects of how it works:
1) Even once the Rows are no longer being redrawn, the highlight continues to update as it should.
2) When backspacing the filter string, or highlighting something else, the highlighting is cleared on its own as it should be.
Both of those things are brilliant, I just don't understand how/what is happening.
And finally I have one IE question. For you, does that code trigger the IE "slow script" dialogue box? I am trying to figure out what is causing it, my table contains about 4500 rows, they are all local. Reading other threads I tried disabling bSort and bSortClasses and bSort being set to false does seem to resolve the problem. Because this data is static, more like a dictionary shall we say, I was hoping to avoid moving it to the server. I just want to make sure that in your opinion the highlight changes are not where the problem lies. Worst case I will disable Sorting conditionally while waiting for the time to implement some better solution.
Thanks again for all your help, I love reading the code samples you give people in their threads, it has helped me find a lot of features I didn't immediately notice reading the docs.
Cheers,
CHgsd
Nice update with the case-insensitive matching. And thanks for the kind words :-)
Regarding the two points, the answer is basically the same for each - the code I used above reads the information that DataTables has stored in its internal cache (which is used to speed up filtering, sorting etc), rather than reading from the DOM as you had before. Then it only updates the DOM, not the internal cache. So this has two benefits, one it's a lot faster than reading from the DOM, and secondly the internal cache isn't changes, so the original data is operated on each time.
The other reason it works (when paging etc) is that it is only operating on the rows which are currently visible. This is good again from a performance point of view, since it doesn't need to do all rows in a single shot, like it would if using fnDrawCallback.
Regarding the slow script issue you are seeing - did you see this without the highlighting? If so, then I rather suspect that it much have been close to it before, and this just "pushed it over the edge". The highlighting itself shouldn't add too much time to the draw (I'm sure there are ways it can be speed up - for example initialise the Regex only once, before the loop and reuse it, rather than creating a new one for each cell), but it looks like it will be fairly fast already (assuming you are only showing 10 or so rows at a time - obviously in increases for more rows).
Regards,
Allan
Thank you again kindly for your explanations. I am glad to now understand how/why your changes were working and now with that understanding I know I do not need to dig any deeper. I knew my methods were far from optimal, but I just didn't, and still don't, know the datatables code well enough to come up with clever solutions like the one you posted.
I have taken your latest recommendation about the Regex initialization into account and "finalized" the code below. I made an additional class name change to something less generic than "highlight". "filterMatches" is a little more descriptive and perhaps specific enough it will provide less chances of collisions for those who don't rename it.
And finally I have tested on a computer with IE both with and without highlighting. Both "fail" with 4500 rows as long as sorting is enabled. So it appears the rowcount is pushing it over the edge and not the highlighting. Reducing the rows solves the problem but isn't really an option now so I am conditionally disabling bSort for IE until I have a chance to do tests with server side.
Current code I am using to achieve as-you-type highlighting in the result rows:
JS Code
[code]
jQuery(document).ready(function(){
$('#INSERT_DATATABLES_ID').dataTable({
[...]
"fnRowCallback": function( nRow, aData, iDisplayIndex, iDisplayIndexFull) {
var settings = this.fnSettings();
var str = settings.oPreviousSearch.sSearch;
var regex = new RegExp( str, 'i');
$('td', nRow).each( function(i) {
this.innerHTML = aData[i].replace( regex, function(matched) { return ""+matched+"";});
});
return nRow;
}
[...]
});
});
[/code]
CSS Code
[code]
.filterMatches { background-color: #FFFF00; }
[/code]
Optional if you have too many rows for IE to behave properly: (please read the caveats below)
[code]
$('#INSERT_DATATABLES_ID').dataTable({
[...]
"bSort": $.browser.msie ? false : true,
[...]
});
[/code]
Caveats: jQuery strongly recommends against .browser in favor of .support for many reasons. Notably .browser is easily spoofed and for general purposes what you are really wanting to test against is support of a function not a specific browser/version. This is an exceptional case however because the "Stop Processing?" box is only thrown by IE. FireFox, Chrome & Opera handle 4500 rows with this code just fine. This basically just disables sorting for IE.
Thanky to everyone for the help and inspiration to do this. Improvements are always welcome if anyone thinks of them.
Cheers,
CHgsd
Thank for refining the code a bit - it's really rather cool seeing that in action - nice one. There isn't really a suitable place for posting this kind of thing on the DataTables site at the moment, since it isn't really a plug-in, nor, I think, can it really be wrapped up into one. So what I think I'll do is make up a new example with it, to show the API in action. Is there a particular credit (name / link) you want to go on the example?
Thanks,
Allan
None really necessary at the moment. If you were to feel obliged, you could credit it to GD. But you are the one to be credited because none of this could be done without you. When I set out to achieve what I was after, I was going to use xajax and push PHP generated HTML based on remote SQL and highlight the results through PHP in a similar way to something I had done on a project about 5 years ago (well before jQuery). Finding your project gave me a much better final output and all that was missing was the highlight filtering which I had done previously. It speaks volumes about your work on DataTables seeing how easy it is to integrate this functionality.
Would this be something you could (or would want) to integrate into the core directly and hide it behind a bHighlightMatches boolean instead of a plugin? Disabled by default naturally.
In any case thank you again for everything!
Cheers,
CHgsd
I just noticed that while using this functionality:
http://www.datatables.net/examples/api/multi_filter.html
Highlighting, with the code which evolved in this thread, does not occur for the column specific searches. Do the fnFilter() calls not trigger the fnRowCallback()'s?
I do not understand why this highlighting is not occurring and it isn't terribly important, it would just be nice for consistency sake.
After giving it a little thought, and still not understanding the "why", I had interesting idea. This may turn out to be hideously ugly and confusing, but once I understand why the other search boxes do not trigger the highlighting, would it be possible for each one of them to have a separate highlight class?
Thank you for your continued help, this is nothing short of amazing.
Cheers,
CHgsd
In my config I put:
[code]
"aoColumnDefs": [
{ "bSearchable": false, "aTargets": [ 'dt_no_search' ] }
],
[/code]
In the fnDrawCallback:
[code]
"fnDrawCallback": function() {
$('##mytable td').each( function() {
if ( !($('##mytable').find('th').eq($(this).index()).hasclass('dt_no_search')) ){
$(this).highlight(str);
}
});
} );
[/code]
Firebug throws an error on $('##mytable') , is there anything I'm doing wrong?
Thanks a lot
oTable.fnSettings().aoColumns[$(this).parent('tr').children().index($(this))].bSearchable
but I'm using ColVis with this so when I hide a column, the index gets messed up
Is there another way I can highlight only the searchable columns?
For the column index you could make use of the two internal functions _fnColumnIndexToVisible and _fnVisibleToColumnIndex . These can be assessed through the oApi object of the oTable variable (i.e. oTable.oApi._fnVisibleToColumnIndex( ... ); ).
Allan
I've just stumbled over the same issue. If I put an alert() into the fnDrawCallback and call fnFilter, it first shoots the alert popup, after closing it, things are getting filtered...
Allan, can you clear this up for us? Are we missing something?
Thank you for this very useful component!
@CHgsd
I'm using your code (that you adapted from allan) for filter highlighting. However, I noticed a bug there: the RegExp searches also inside HTML tags and you usually don't want to find matches for "span" :)
I modified the regexp fragment:
[code]
var regex = new RegExp( str + "(?!([^<]+)?>)", 'i');
[/code]
and it works well for me.
A huge advantage of switching to JSON is the new bDeferRender option which makes huge local datasets manageable even for IE it appears. This feature is available in >= 1.8.
The solution to the NULL value problem when using JSON, which is just an extra NULL check, as well as the fix mentioned by fr33rider for avoiding searching inside HTML tags and the fix for remnant spans is as follows:
JS Code[code]
jQuery(document).ready(function(){
$('#INSERT_DATATABLES_ID').dataTable({
[...]
"fnRowCallback": function( nRow, aData, iDisplayIndex, iDisplayIndexFull) {
var settings = this.fnSettings();
var str = settings.oPreviousSearch.sSearch;
if (str != "") {
var regex = new RegExp( str + "(?!([^<]+)?>)", 'i');
$('td', nRow).each( function(i) {
if (!!aData[i]) {
this.innerHTML = aData[i].replace( regex, function(matched) { return ""+matched+"";});
}
});
return nRow;
}
},
[...]
});
});
[/code]
@allan,
I am still unable to figure out how to highlight from the individual column searches. Highlighting only occurs from the main filter.
Cheers,
CHgsd
Post Scriptum: Updated for correctness and added the extra check on 'str'
Thanks for your work so far on search highlighting, it's great! I've modified it a little bit to gain a little performance (and I've also poured it into a plugin, but you can ignore that).
[code]jQuery.fn.dataTableExt.oApi.fnSearchHighlighting = function(oSettings){
oSettings.oPreviousSearch.oSearchCaches = {}
oSettings.fnRowCallback = function( nRow, aData, iDisplayIndex, iDisplayIndexFull) {
var settings = this.fnSettings();
var str = settings.oPreviousSearch.sSearch;
var cache = settings.oPreviousSearch.oSearchCaches
if (!cache[str]){
cache[str] = new RegExp( str + "(?!([^<]+)?>)", 'i');
}
if (str != "") {
var regex = cache[str]
$('td', nRow).each( function(i) {
if (aData[i]) {
this.innerHTML = aData[i].replace( regex, function(matched) {
return ""+matched+"";
});
}
});
}
return nRow;
};
return this;
}[/code]
Because the regexes are now cached, they don't have to be recompiled at every row. Thanks again for your work (and Allen too, of course)!
Thank you for your help. While reviewing your changes I realized some mistakes of my own which ended up being incorporated into your plugin. Additionally during the process of finding the best solution to those problems I decided to tackle the per-column filter highlighting in addition to the global highlighting which is currently implemented. I have been successful in solving all of the above but have not yet formatted it nicely into your same plugin format. I should have it done in a few for your review. You may be able to optimize some of my changes again.
Cheers and thank you again,
CHgsd
[WARNING] This is incompatible with ColVis, and appears to have always been, I just hadn't tried using them together at the same time. [/WARNING]
Plugin:[code]
jQuery.fn.dataTableExt.oApi.fnSearchHighlighting = function(oSettings) {
// Initialize regex cache
oSettings.oPreviousSearch.oSearchCaches = {}
oSettings.fnRowCallback = function( nRow, aData, iDisplayIndex, iDisplayIndexFull) {
// Initialize search string array
var searchStrings = [];
var settings = this.fnSettings();
var cache = settings.oPreviousSearch.oSearchCaches;
// Global search string
// If there is a global search string, add it to the search string array
if (settings.oPreviousSearch.sSearch) {
searchStrings.push(settings.oPreviousSearch.sSearch);
}
// Individual column search option object
// If there are individual column search strings, add them to the search string array
if ((settings.aoPreSearchCols) && (settings.aoPreSearchCols.length > 0)) {
for (i in settings.aoPreSearchCols) {
if (settings.aoPreSearchCols[i].sSearch) {
searchStrings.push(settings.aoPreSearchCols[i].sSearch);
}
}
}
// Create the regex built from one or more search string and cache as necessary
if (searchStrings.length > 0) {
sSregex = searchStrings.join("|");
if (!cache[sSregex]) {
cache[sSregex] = new RegExp("("+sSregex+")(?!([^<]+)?>)", 'i');
}
var regex = cache[sSregex];
}
// Loop through the rows/fields for matches
$('td', nRow).each( function(i) {
// Only try to highlight if the cell is not empty or null
if (aData[i]) {
// If there is a search string try to match
if ((typeof sSregex !== 'undefined') && (sSregex)) {
this.innerHTML = aData[i].replace( regex, function(matched) {
return ""+matched+"";
});
}
// Otherwise reset to a clean string
else {
this.innerHTML = aData[i];
}
}
});
return nRow;
};
return this;
}
[/code]
Initialization:[code]
jQuery(document).ready(function(){
var oTable = $('#INSERT_DATATABLES_ID').dataTable({});
oTable.fnSearchHighlighting();
});
[/code]
CSS:[code]
.filterMatches { background-color: #BFFF00 }
[/code]
Updates since martijnb's fixes, changes and improvements:
Fixes:
1. Empty search string now clears the highlighting
2. Remnant spans should no longer occur
3. Check sSregex for undefined as certain column filter types will cause that to happen
New Features:
- Highlighting is performed on both global and column based filters
Future thoughts:
- ColVis is broken when using this. Not sure how/why yet.
- Further integration would be nice with optional settings to:
- Choose CSS class to be used such as 'sHighlightClass': 'filterMatches'
- Or perhaps even different class per column in the case of multi column filtering.
- As the search strings are user input, I am not sure how "safe" it is to use them as array indexes for the cache as well as sending them straight to the regex. I am not sure what kind of checking should be done, but it just feels like something should be done.
I really appreciate everyone's feedback and improvements!
Cheers,
CHgsd
Edits:
- Numbered the fixes and added a new fix (3) both in the code and description.
- Discovered that this breaks ColVis. The .innerHTML calls seem somehow responsible.
1) how will it interact with jEditable?
modifying the cell contents with render code means the user will have to deal with HTML/render when editing a cell, unless you specify the "data" for the editable column. If the user specifies "bUseRendered": false, you can get the original data from fnGetData. I'm not sure you can make a plug-in that accounts for everything without limiting the user, so at least document for the user that they can use bUseRendered to get the orig data.
Also, after a jEditable edit process, specifying a callback to re-render using the original render function is advisable
[code]
"aoColumns": [
{ "sName": "id", "sWidth": 30 },
{ "sName": "customdata", "sWidth": 100, "bUseRendered": false, "fnRender": myRender},
// ... more columns
],
// ....
}).makeEditable({
"sUpdateURL": "qa_update.php?table=qafiles&",
"aoColumns": [
null, // id, not editable
{ // qaid
data: function() { return fnOrigData(this) }, // where fnOrigData gets data from oTable, not just from cell which may be rendered
callback: function () { fnRepaint(this) } // fnRepaint will look to see if a custom render function is specified for this object and repaint it
},
// ... more columns
// maybe we should submit this function to extension APIs
// repaint a cell with it's column's fnRender
function fnRepaint(nTd) {
if (typeof nTd === "undefined") nTd = this;
var aPos = oTable.fnGetPosition( nTd );
var iRow = aPos[0];
var iCol = aPos[1];
var oSettings = oTable.fnSettings();
var oCol = oSettings.aoColumns[iCol];
if (typeof oCol.fnRender === "function") {
var oData = oSettings.aoData[iRow]._aData;
var oObj = {
iDataRow: iRow,
iDataColumn: iCol,
aData: oData
}
nRow = oTable.fnGetNodes(iRow);
$('td', nRow).eq(iCol).html(oCol.fnRender(oObj));
}
// turn off "processing" message
$('.dataTables_processing').hide();
}
[/code]
2) using string replace/reg ex might not be advisable. you might have rendered HTML elements that match strings when that is not what is intended
consider if a cell contains "Dookie balls' and a search was sent out for "href". you don't want to highlight the 'href' within the tag.
You can either set up some complex reg ex's to ignore between angled brackets, or you could treat the target cell as a DOM element and traverse the node tree, only changing the innerText portions of the nodes.
On the subject of incompatibilities, I have resolved the problem with ColVis and discovered an additional problem that ColVis has with columnFiltering which I have also resolved. More on those two issues later as well as their solutions.
Now regarding the issue with ColVis and the highlighting plugin, the solution is the following:
[code]
jQuery.fn.dataTableExt.oApi.fnSearchHighlighting = function(oSettings) {
// Initialize regex cache
oSettings.oPreviousSearch.oSearchCaches = {}
oSettings.fnRowCallback = function( nRow, aData, iDisplayIndex, iDisplayIndexFull) {
// Initialize search string array
var searchStrings = [];
var oApi = this.oApi;
var cache = oSettings.oPreviousSearch.oSearchCaches;
// Global search string
// If there is a global search string, add it to the search string array
if (oSettings.oPreviousSearch.sSearch) {
searchStrings.push(oSettings.oPreviousSearch.sSearch);
}
// Individual column search option object
// If there are individual column search strings, add them to the search string array
if ((oSettings.aoPreSearchCols) && (oSettings.aoPreSearchCols.length > 0)) {
for (i in oSettings.aoPreSearchCols) {
if (oSettings.aoPreSearchCols[i].sSearch) {
searchStrings.push(oSettings.aoPreSearchCols[i].sSearch);
}
}
}
// Create the regex built from one or more search string and cache as necessary
if (searchStrings.length > 0) {
var sSregex = searchStrings.join("|");
if (!cache[sSregex]) {
// This regex will avoid in HTML matches
cache[sSregex] = new RegExp("("+sSregex+")(?!([^<]+)?>)", 'i');
}
var regex = cache[sSregex];
}
// Loop through the rows/fields for matches
$('td', nRow).each( function(i) {
// Take into account that ColVis may be in use
var j = oApi._fnVisibleToColumnIndex( oSettings,i);
// Only try to highlight if the cell is not empty or null
if (aData[j]) {
// If there is a search string try to match
if ((typeof sSregex !== 'undefined') && (sSregex)) {
this.innerHTML = aData[j].replace( regex, function(matched) {
return ""+matched+"";
});
}
// Otherwise reset to a clean string
else {
this.innerHTML = aData[j];
}
}
});
return nRow;
};
return this;
}
[/code]
It now works with ColVis and with the linked solution ColVis works with columnFilter as well.
First of all let me say that you guys are doing a realy great job. The datatables framework is great and this discussion about highlighting is very interesting.
But, i have a (little) issue.
I tested the above code from CHgsd and I discoverd that the value of checkboxes (selected or not) is made undone when i switch between multiple pages. When i disable the fnSearchHighlighting plugin the value of the checkboxes remains as selected. Anybody who can help me out here?
I really appreciate everyone's feedback!
Cheers, Doeskuh...
Thanks!
Kyle
I've minified the script to make it ultra small - the online minifiers kept breaking it, so I did it 'by hand'
[code]jQuery.fn.dataTableExt.oApi.fnSearchHighlighting=function(a){a.oPreviousSearch.oSearchCaches={};a.fnRowCallback=function(b,c){var d=[];var e=this.oApi;var f=a.oPreviousSearch.oSearchCaches;if(a.oPreviousSearch.sSearch){d.push(a.oPreviousSearch.sSearch)}if((a.aoPreSearchCols)&&(a.aoPreSearchCols.length>0)){for(i in a.aoPreSearchCols){if(a.aoPreSearchCols[i].sSearch){d.push(a.aoPreSearchCols[i].sSearch)}}}if(d.length>0){var h=d.join("|");if(!f[h]){f[h]=new RegExp("("+h+")(?!([^<]+)?>)",'i')}var k=f[h]}$('td',b).each(function(i){var j=e._fnVisibleToColumnIndex(a,i);if(c[j]){if((typeof h!=='undefined')&&(h)){this.innerHTML=c[j].replace(k,function(l){return""+l+""})}else{this.innerHTML=c[j]}}});return b};return this}[/code]
Note that just to save those last couple of bytes I changed the class name to hlt - you could even reduce it to a single char if you were so inclined.
Thanks to everyone who worked on the plugin.