Use the column().search() API to exclude rows

jLinux

I was wondering how I could use the column().search() to actually hide the rows that it matches instead of showing them.

I have a table that has a Deleted On column, and if its populated with a date, that means its a deleted record, if its empty, that means its not deleted. I was wondering how I could filter out the deleted rows, and only show the non-deleted rows (meaning null for the deleted:name column)

I would settle for either...

1. Search for any rows with the deleted:name column populated with a date, and exclude them
2. Search for any rows with the deleted:name column empty. I tried table.column( 'deleted:name' ).search( '' ).draw() with no avail, (also tried null instead of just empty quotes)

I realize it would be easy to find the deleted/non-deleted rows using the filter(), but I couldn't find a way to filter (in/out) the rows found via filter()

Thanks!

jLinux

After tinkering around, I found that this works ok

table.column( 'deleted:name' ).search( '^$', true, false ).draw();

But for future reference, is the first approach stated above possible at all?

And I guess while I'm at it, if I wanted to search for any deleted:name with a date in the format of YYYY-MM-DD, whats the regex pattern for it? I tried the following pattern, which I tested at regex101 and it worked fine, but it doesn't work via DataTables regex search:

table.column( 'deleted:name' ).search( '(\d{4})-(\d{2})-(\d{2})', true, false ).draw();

allan

The issue here is that the "search" isn't really a search in DataTables, it is a filter. So an empty string means no filter which is why ...search( '' )... doesn't work for you. I should probably have made it null originally, but the column filtering was added after the global search input, which when empty is an empty string of course.

So your regex of ^$ is the correct way to go currently. The only other option currently available is you use a custom filtering plug-in.

The next major version of DataTables will include the ability to pass a function is as the search parameter which could also be used to search for an empty string.

For the ISO8601 regex, you need to add a backslash before the dash - i.e.:

'(\d{4})\-(\d{2})\-(\d{2})'

It was valid before, but the dash is a range operator unless it is escaped. Also you don't need the parenthesis since you don't need to use the matching result parts. This should do fine:

'\d{4}\-\d{2}\-\d{2}'

Allan

jLinux

Ah, I actually did try that, I see now that I have to escape the back slashes..

table
   .column( 'deleted:name' )
   .search( '\\d{4}\\-\\d{2}\\-\\d{2}', true, false )
   .draw();

That works now, Thanks allan!

allan

Ah - thanks for posting back with that!

The next major update will allow a regex to be passed straight in (i.e. as a regex, not a string).

Allan