Customising the way the filter works

Nigel

DataTables is already very impressive. Hopefully answering this will make it even more versatile...

I would like to be able to customise the way in which the filter works. I hope to be able to Filter using the left-hand character as a start character. So for example, filtering a table list of 4 fruits such as:
apples
grapefruits
grapes
pineapples
.. by entering the characters 'ap' into the filter box, would reduce the list to just 'apples' (as 'ap' is at the start of the word 'apples') instead of coming back with all four fruits. This would be useful to filter large lists of telephone numbers for example - which contain area-codes at the start.

It might be useful too, to be able to define a wildcard (for example a %) in order to be able to enter this kind of thing into the search box, for example: '%app' - in order to reduce the table list to just 'apples' and 'pineapples'.

Is this possible?

I saw a section in the API called "DataTables custom filtering (range filtering) example" - which gave me the impression it might be possible - but I'm really not sure how.

Any clues would be welcome. Thanks

Nigel

allan

Hi Nigel,

Thanks for your question - it's certainly a good one. This is indeed something that DataTables can do, and it can do it in either of two ways :-)

1. Using the custom filtering API
2. Regex

I'd strongly recommend using the second option here as you can very easily do what you are looking for. For example "^ap" or ".*app" etc. To use regular expression filtering take a look at the fnFilter API function: http://datatables.net/api#fnFilter . You might also want to take a look at the fnSetFilteringDelay() plug-in ( http://datatables.net/plug-ins#api_fnSetFilteringDelay ) which demonstrates how you might unbind the standard event handler from the filtering box and then apply your own custom one.

Hope this helps,
Allan

Nigel

Thanks Allan

I suspect I'm being dumb here - but I couldn't work out (from the regular expression filtering example in the fnFilter section of the API) just where to set up the regular expression.

Where do you add the regular expression so that the filter box behaves in the manner you want?

Regards
Nigel

allan

Hi Nigel,

The key is what you pass to fnFilter. For example:

oTable.fnFilter( "^ap", null, false );

This will filter the table on all columns (the null), with the string (the "^ap") and it will treat that string as a regular expression (the false).

So what you need to do is get the value of the search box, and then manipulate it however you want (prefix with a "^" for example) and then pass it on to fnFilter().

Does that help?
Allan

brianmm

Out of interest... (kind of related)...

Is there a visual guide as to which columns are enabled in the filter? so the user knows what columns are being searched? Also :) is it possible to alow them to select if a column is included or not say by a tick box?

(none of this required for me, just thoughts).

allan

Hi Brian,

This isn't built into DataTables (the default filter is for all columns to be filtered on, so there is no need for an indicator there), but it would most certainly be possible via the API. fnFilter does either global or individual column filtering so you could limit it to that - however, if you want the user to have checkboxes to do a limited global filter (i.e. only some columns" then this would be the prefect place to use a custom filter (like my range search demo). You could then apply a custom class to the columns based on which checkboxes are enabled.

How does that sound?
Allan

Nigel

Hmmm... my attempts so far don't work too well (specifically when the fruits are in a column that is not the 1st column)

// Add some custom aspects to the filtering...
jQuery('#wide_table_main_filter input').keyup( function () {
newFilterExp = "^"+this.value; // Prepend the ^ char to get only data that starts with the filter
// alert(newFilterExp);
oTable.fnFilter( newFilterExp, null, false );
} );

Then, inputting any character(s) into the filter box (such as 'a') returns no rows of data. What am I doing wrong?

Nigel

allan

Yes indeed - there is a trick here (when is there not....). When you don't specify a column, then the filter is done of the data from the whole row concatenated together (space separated). So if you have:

| apple | banana | kiwi |

Then the search string is "apple banana kiwi". As such the query "^ap" will match but "^bana" will not!

You just need to modify the regular expression to take account of this. You'll need to forgive me because my regular expressions aren't that hot (without a textbook next to me ;-) ) but perhaps something like this will do what you are looking for (if you are looking for "bana"...

^(bana)|( bana)

ie. match "bana" at the start or with a space in front of it.

Allan

Nigel

Hi Allan

It doesn't really work. I suspect it may have something to do with the Smart Filtering you have going on (where you can enter space separated words - and the Filter looks for them in any order).

The problem is the inability to find patterns (in any table columns that are not the first column) that START with the characters entered into the filter box...

With a 3-column data table such as :

09876543211 bananas theother
09345123456 pineapples another
08000123456 pineapples other
07899945123 oranges oneother

I have tried:

1) newFilterExp = "^("+this.value+")|( "+this.value+")";
- This finds lines containing 'bananas' but incorrectly still finds lines with 'ananas' (entered in the filter box)

and

2) newFilterExp = "^(.* )?"+this.value;
- This also finds lines containing 'bananas' but incorrectly still finds lines with 'ananas' (entered in the filter box)

Do you think it is falling foul of the smart filtering you have going on? You got any ideas? (as potentially this could be a really powerful utility)?

Nigel

allan

Hi Nigel,

I think you are right - this is running aground on my smart filtering - however, there might be a work around for you.

Basically the problem is that the input search string is split on " " (space), and as such the regular expression that you used in 1) will be broken up into multiple parts - hence the incorrect match. A further problem is that it isn't actually possible to do a match at the start of the string (with ^) since DataTables actually injects something at the start of the regular expressions.

If you know that you are looking for something which is not in the first column you could do:

newFilterExp = "\s"+this.value;

If you are looking to match on the first column as well, you could either stick a space at the front of all data in that column, or you could use the DataTables custom filtering API to construct the kind of filter you want (might be best for a long term solution?).

How does this sound?
Allan

Tom Clark

This (probably inefficient) code will take a value ("foo bar" in this example; replace with $("#foo").val() or whatever) and turn it into a form which should be parsed as an OR regex for words.

[code]"foo bar".replace(/(\w+)/g, "($1)").replace(/\s+/, "|")[/code]

This should be better to catch malformed data:

[code]" foo bar baz ".replace(/^(\s+)|(\s+)$/g, "").replace(/(\w+)/g, "($1)").replace(/\s+/g, "|")[/code]

You could use this in a custom filter like so (1.5 b11):

[code]
var oTable = $("#foo".dataTable({
"sDom": "lrtip", // Hide the original search bar
...
});

$("#my_search_bar").keyup(function() {
oTable.fnFilter(this.value.replace(/^(\s+)|(\s+)$/g, "")
.replace(/(\w+)/g, "($1)")
.replace(/\s+/g, "|"), null, false);
});
[/code]

I have used this to use my own search bar, which is far away from the table DOM-wise (since DataTables can't place it somewhere far away [can it?]), and it works great; nice and fast!

Tom Clark

Accidental double-post

allan

Hi Tom,

Thanks very much for posting your code - looks good.

Regarding your question about DataTables placing the filtering element - it's positioning is limited to how you use the sDom parameter - so no - it can't be put far away in the DOM. However, as your example shows it's nice an easy to add fnFilter as an event handler to any input element.

Regards,
Allan

Tom Clark

Interestingly enough, the code I posted has a funny way of searching, probably because "... DataTables actually injects something at the start of the regular expressions."

Here's what happens: Given three fields, "John Smith", "Mary Jones", and "Tom Clark", if you type "Jo Tom" it will find all three, but if the second, third, etc. words entered do not occur at the start of the fields, then it won't find them. In the given example, "Jo Clark" won't find the "Tom Clark" field.

It wouldn't be as much of a problem if the first part wouldn't match inside strings, but the change of behavior would be confusing to users.

This behavior is a bit odd, and I should probably look at the DataTable code to see what is injected into my custom regex, but that's something that will have to wait until perhaps later on tonight.

allan

Hi Tom,

Here is the key line in DataTables for what you are looking at:

[code]
var sRegExpString = '^(?=.*?'+asSearch.join( ')(?=.*?' )+').*$';
[/code]
There is a comment to go with it:

[code]
/* Generate the regular expression to use. Something along the lines of:
* ^(?=.*?\bone\b)(?=.*?\btwo\b)(?=.*?\bthree\b).*$
*/
[/code]
You could modify the source for your application, or you can built up your own filtering function using the API to match your specific needs. The reason for doing this little 'trick' with the regular expressions is to help make the global sorting easier.

Regards,
Allan