Column filtering by date older or date newer

TomBajzek

I've built a column filter that filters a column to find rows dated "yesterday", "today", or "tomorrow", based on a select element. Having done that, I now see that it would also be useful to be able to filter for dates older than yesterday and newer than tomorrow. However, I don't see any simple way to extend what I've built to accommodate an open-ended date range as that would require.

I have the following code to define the select options:

            // Set up comparison dates for filtering the dueDate column
            var today = new Date();
            var yesterday = moment(today).add(-1,'days');
            yesterday = yesterday._d.toISOString().substring(0,10);
            var tomorrow = moment(today).add(1,'days');
            tomorrow = tomorrow._d.toISOString().substring(0,10);
            today = today.toISOString().substring(0,10);

And the following code implements the column filter (for columns with className="dFilter"):

            // Populate the dueDate column filter
            this.api().columns('.dFilter').every( function () {
                var column = this;
                var that = this;
                var select = $('<select><option value="">All</option>
                                           <option value="'+yesterday+'">Yesterday</option>
                                           <option value="'+today+'">Today</option>
                                           <option value="'+tomorrow+'">Tomorrow</option></select>')
                    .appendTo( $(column.header()) )
                    .on( 'change', function() {
                        that
                            .search ($(this).val() )
                            .draw();
                    } );
                    column
                        .cache( 'search' )
                        .sort()
                        .unique()
                        .each( function() {
                        } );
            } );

This works great for what it does, and is quite similar to other column filters I use, but it looks like a dead-end with regard to being extensible in the way I want. Is there a way to extend this scheme, or do I need to abandon this and take a completely different approach?

Thanks,
Tom

kthorngren

I would consider using a search plugin for this. Take a look at this example from this thread. Its a date range search but could be adapted to what you want.

Kevin

TomBajzek

Kevin,

So, all I need to do is write a filter function and push it onto $.fn.dataTable.ext.search, and my filter function will be used for the column specified as the index to searchData. I assume from this that $.fn.dataTable.ext.search is used for user-defined searches, and will not interfere with the internal searches inherent in DataTables which would be applied to other columns in my table.

I'm just trying to confirm that I understand this correctly before I try to do this.

Thanks,
Tom

kthorngren

The search plugin augments the normal Datatables search. It might be easier to see with this example:
http://live.datatables.net/yisaqore/1/edit

If there is a search value it will remove all rows with London in the Office column. Angelica Ramos is in London and is System Architect. Type sy and you will see Angela is filtered out of the table.

Change the return false to return true in this code:

      if (searchData[2] === 'London') {
        return false;
      }

You will see that Angela is now displayed when typing sy. Note also that others in London that don't have sy in the Position are not displayed. Based on what I've seen it seems that the Datatables search takes place first then the search plugins are executed against the remaining displayed rows.

HTH,
Kevin

TomBajzek

Kevin,

Your example at **http://live.datatables.net/yisaqore/1/edit** does not illustrate what you describe in you response. I think you may have included the URL for a similar but different case. Your example seems to pass rows whose office is 'Edinburg' or if they contain the string in the Search box.

However, I think I see how to write a filter that would do what I need, provided the Search function inherent in DataTables does not interfere. If disabling the DataTables filter on the 'dataTables_filter' class doesn't break the other column filters I use, then I suspect what I have in mind can be made to work.

I think what will do is try that and report back here if I learn anything of use to others.

Tom

kthorngren

Yes, you are right, I intended to modify that example to this one:
http://live.datatables.net/yisaqore/10/edit

You don't need to disable the Datatables search functionality. Note that I removed the default search disabling from this example.

Kevin

TomBajzek

Kevin,

Thanks for clarifying and updating this example. That removes some of the uncertainty on my part.

Thanks,
Tom

TomBajzek

I tried the search plugin technique, with mixed results. I started with filters for today and tomorrow, which worked, but I noticed that with the plugin, response times when editing the field to be filtered were much greater than previously, presumably because of the amount of time needed to recompute the table after editing the filterable field.

So, I decided to try a different approach, which is to compute the filter term on the server and deliver it as orthogonal data with the object data for that column containing the actual display date and the filterable word (overdue, today, tomorrow, and beyond). Now I need to figure out how to to return the displayDate and filterDate from the PHP code on the server.

Right now I'm getting the warning for tn/4: DataTables warning: table id=tasks - Requested unknown parameter 'duedate' for row 0, column 5.

The Javascript code I'm trying to use for this field is:

            {
                "data": "duedate",
                "className": "dFilter",
                "render": {
                    _: "displayDate",
                    "filter": "filterDate"
                }
            },

And the PHP that is giving the error is:

        Field::inst( 'duedate' )
            ->getFormatter( function( $val, $data, $opts) {
                $displayDate = date("Y-m-d", strtotime( $val) );
                if ( strtotime($val) < date(strtotime($val)) ) {
                    $filterDate = 'overdue';
                } elseif (strtotime($val) === strtotime('today')) {
                    $filterDate = 'today';
                } elseif (strtotime($val) === strtotime('tomorrow')) {
                    $filterDate = 'tomorrow';
                } else {
                    $filterDate = 'beyond';
                }
                $dueDateObj = (object) [
                    'filterDate' => $filterDate,
                    'displayDate' => $displayDate
                ];
                $dueDateJSON = json_encode($dueDateObj);
                return $dueDateJSON;
            } ),

The JSON data for the dueDate field that is returned is of the form below, which I believe is the problem, but I haven't been able to return that correctly:

"duedate":"{\"filterDate\":\"beyond\",\"displayDate\":\"2018-03-01\"}"

I realize that the computations of the filter dates are not correct, but I can fix that later. Right now I'm just trying to get the orthogonal data for the filter passed back correctly. Can you provide a suggestion?

Thanks,
Tom

colin

This thread should help, it's asking the same thing.

Cheers,

Colin