Having trouble with custom filtering a column by a range

Evan S. Dictor

I am using the $.fn.dataTable.ext.search.push method for filtering data. However, what I'm finding is that the records that my function is being given to evaluate are already filtered to matching the latest value entered.

Here are some details ...

In the initComplete I am replacing the default input elements with alternatives if the column is set to 'bool' or 'date-us'. For 'bool' I am providing buttons to filter for YES, NO, and ALL. For date-us I am using an input-group for the bootstrap-datepicker as an input-daterange. In both cases, any change will result in the table.draw() being called.

Here is the filtering logic:

$.fn.dataTable.ext.search.push(
    function (settings, data, dataIndex) {
        var showRow = true;

        $(settings.aoColumns).each(function (i, v) {
            switch (v.sType) {
                case 'bool':
                    if (v.bSearchable) {
                        var $selected = $($('.cmClientTable tfoot th')[i]).find('.btn-selected');
                        if (showRow && $selected.hasClass('clFilterYes') && (data[i] != 'YES')) {
                            showRow = false;
                        } else if (showRow && $selected.hasClass('clFilterNo') && (data[i] != 'NO')) {
                            showRow = false;
                        }
                    }
                    break;
                case 'date-us':
                    if (v.bSearchable) {
                        var $thisRange = $($('.cmClientTable tfoot th')[i]).find('.clFilterDateRange');
                        var dateFrom = $thisRange.find('.clFilterDateFrom').val();
                        var dateTo = $thisRange.find('.clFilterDateTo').val();

                        if ((dateFrom == '' && dateTo == '') || (dateFrom == undefined || dateTo == undefined)) {
                            showRow = showRow && true;
                        } else if (dateFrom == '') {
                            showRow = showRow && (new Date(data[i]) <= new Date(dateTo));
                        } else if (dateTo == '') {
                            showRow = showRow && (new Date(data[i]) >= new Date(dateFrom));
                        } else {
                            showRow = showRow && (new Date(data[i]) >= new Date(dateFrom) && new Date(data[i]) <= new Date(dateTo));
                        }
                    }
                    break;
                case 'string':
                    if (v.bSearchable) {
                        var thisVal = $($('.cmClientTable tfoot th')[i]).find('input').val();
                        if (thisVal != '') {
                            if (data[i].indexOf(thisVal) < 0) {
                                showRow = false;
                            }
                        }
                    }
                    break;
                default:
                    break;
            }
        });

        return showRow;
    }
);

I was confused because if I set the range to Aug-04-2017 to Aug-04-2017, everything was fine. However, if I changed the "to" value to Aug-10-2017, I was only getting values for that date. After debugging the code, I realized that my logic isn't the problem, I am only being provided with rows for the last date entered, not for the entire table. So in the above example, if I change the to date to Aug-10-2017, I am only getting rows where the date is exactly Aug-10-2017, and my logic is correctly saying those should be shown.

Why am I not seeing all rows hitting the above logic?

Evan

rf1234

I am using this too and it is working fine. Reading this "After debugging the code, I realized that my logic isn't the problem, I am only being provided with rows for the last date entered, not for the entire table." it sounds like your code above isn't the issue anyway. It might be possible that you implemented this kind of or a similar search functionality in your datatable: https://datatables.net/examples/api/multi_filter.html

If that is the case the search for the value entered into the date field is probably being executed BEFORE $.fn.dataTable.ext.search.push. That would explain that you only get provided with data for that one date.

If you want to use those date fields for $.fn.dataTable.ext.search.push they shouldn't be defined as search fields.

Evan S. Dictor

I have no filtering in my database .. I always return all records and allow the DataTable to do all the filtering itself.

However, I'm not sure what you mean about "shouldn't be defined as search fields" ?? This sounds like it could be my problem in a nutshell.

Can you elaborate?

rf1234

"I have no filtering in my database .. I always return all records and allow the DataTable to do all the filtering itself." Right and this is what I suspect: Once you enter something into that date field the table is being filtered immediately: Only records for that date are left and those records are passed into your $.fn.dataTable.ext.search.push routine!
So you need to make sure that the fields that you use to enter your criteria aren't regarded as search fields in the sense of the example mentioned above. In the example you see a field in the footer called "Search Start date". If you enter a date the table gets filtered immediately! And as you can see the table in the example does not have a $.fn.dataTable.ext.search.push routine! If there was such a routine in the example it would only get records for exactly the "Search Start date" entered which is precisely what seems to happen in your case.

Since I don't have your source code other than the $.fn.dataTable.ext.search.push routine which doesn't seem to be the problem anyway I have no idea whether you really have a similar situation. But I think it is quite likely: Since you are not filtering the data in your database some data tables routine other than $.fn.dataTable.ext.search.push must be the cause of this.

Evan S. Dictor

It's working now. I wouldn't have found the problem if not for the posts here by rf1234. I had some old code that did text filtering, which was always firing prior to my custom date filter code. The end result was that my date filter was working on data that was already processed based on the text filter.

The code in question was:

$('input', this.footer()).on('keyup change', function () {
    if (that.search() !== this.value) {
        that
            .search(this.value)
            .draw();
    }
});

The change that got it to work was in that first line, which is now:

$('input.clFilterText', this.footer()).on('keyup change', function () {

Once I got that fixed I found a few other problems, but that was the big one.

Thanks for your help rf1234, not sure I would have found it otherwise - at least not in a reasonable timeframe.