Creating a data grid with server-side dropdown filtering functionality

Backend
1

I want to modify my existing data grid to include a dropdown filter positioned above the table (not within the column headers). The filtering logic needs to be processed on the server side.

My date column uses dd/mm/yyyy format, but the year periods don’t follow standard calendar years. Instead, they run from 01/11/N to 31/10/N+1. This custom date range logic must be applied when generating the filter options.

For example, if my table contains these records:

 -------------------------------------------------------
|   date_col     |  column_a  |  column_b  |  column_c  |
|-------------------------------------------------------|
|   01/05/2013   |     def    |     rty    |     abc    |
|-------------------------------------------------------|
|   05/11/2018   |     ghi    |     uio    |     mno    |
 -------------------------------------------------------

The dropdown should display these options based on my custom year logic:

<2012/2013>
and
<2018/2019>

I need the dropdown to analyze all date values in the column and generate appropriate filter ranges. When a user selects a range, the table should refresh with filtered results.

Here’s my table setup code:

$tableElement.DataTable({
    "ajax": {
        "url": this._dataEndpoint,
        "dataSrc": ""
    },
    "sDom": 'ltipr',
    "bLengthChange": false,
    "paging": this._defaultRowsPerPage > 0,
    "pagingType": "full_numbers",
    "iDisplayLength": this._defaultRowsPerPage,
    "order": [[ this._defaultSortColumn, this._defaultSortOrder ]],
    "columns": this._tableColumns,
    "columnDefs" : this._columnSettings,
    "fnRowCallback": function(row, data, displayIndex, fullIndex) {
        if ($(row).hasClass('even')) {
            $(row).addClass("alternate");
        } else {
            if ($(row).hasClass('alternate')) {
                $(row).removeClass("alternate");
            }
        }
    },
    "fnDrawCallback": function() {
        var totalPages = Math.ceil((this.fnSettings().fnRecordsDisplay()) / this.fnSettings()._iDisplayLength);

        if (totalPages > 1)  {
            $('.dataTables_paginate').show(); 
        } else {
            $('.dataTables_paginate').hide(); 
        }
    },
    "language": {       
        "sProcessing":    "Loading data...",
        "sLengthMenu":    "Show _MENU_ entries",
        "sZeroRecords":   "No matching records found",
        "sEmptyTable":    "No data available in table",
        "sInfo":          "Showing _START_ to _END_ of _TOTAL_ entries",
        "sInfoEmpty":     "Showing 0 to 0 of 0 entries",
        "sInfoFiltered":  "(filtered from _MAX_ total entries)",
        "sInfoPostFix":   "",
        "sSearch":        "Search:",
        "sUrl":           "",
        "sInfoThousands":  ",",
        "sLoadingRecords": "Loading...",
        "oPaginate": {
            "sFirst":    "First",
            "sLast":    "Last",
            "sNext":    "Next",
            "sPrevious": "Previous"
        }
    },
    "initComplete": function() {
        if ($(this).attr('id') == "myDataTable") {
            var dateValues = $('#myDataTable tr td:first-child').toArray();

            buildDropdownOptions(dateValues);

            setupEventHandlers();
        }
    }
});

My dropdown population function:

function buildDropdownOptions(dateValues) {
    var periods = [];

    for (var i = 0; i < dateValues.length; i++) {
        var yearValue = Number($(dateValues[i]).html().split(' ')[0].split('/')[2]);
        var monthValue = Number($(dateValues[i]).html().split(' ')[0].split('/')[1] - 1);
        var dayValue = Number($(dateValues[i]).html().split(' ')[0].split('/')[0]);
        var currentDate = new Date(yearValue, monthValue, dayValue);

        var periodStart = new Date(yearValue, 10, 1);

        var period;
        if (currentDate < periodStart) {
            period = Number(yearValue-1) + "/" + yearValue;
        } else {
            period = yearValue + "/" + Number(yearValue+1);
        }

        if ($.inArray(period, periods) == -1) {
            periods.push(period);
        }
    }

    periods.sort();

    var optionsList = "";

    $.each(periods, function(index, value) {
        optionsList += "<option> Period " + value + "</option>";
    }); 

    $('#period_filter').append(optionsList);
}

Event handler setup:

function setupEventHandlers() {
    $('#period_filter').on('change', function() {
        var selectedPeriod = $("#period_filter").val().split(' ')[1];
        var startDate = new Date(selectedPeriod.split('/')[0],10,01);
        var endDate = new Date(startDate.getFullYear() + 1, startDate.getMonth(), startDate.getDate());

        $('#myDataTable').dataTable().fnFilter("15/05/2018 10:30:00").draw();
    });
}

HTML structure:

<select name="period_filter" id="period_filter">
</select>

I’m getting errors with the fnFilter method. It returns undefined when I try to chain the draw() function. The search method also throws “not a function” errors. How can I properly implement this filtering functionality?

2

You’re doing the date logic client-side when you need server-side filtering. Right now you’re loading all the data first, then building filter options from DOM elements - that kills the whole point of server-side processing. Send an initial request to your backend to get available periods, then use DataTables’ ajax.data parameter to pass the selected filter to your server. Try something like “ajax”: { “url”: this._dataEndpoint, “data”: function(d) { d.selectedPeriod = $(‘#period_filter’).val(); } } - this’ll send the filter parameter with each request. Let your server handle both generating period options and the actual filtering.

3

You’re using outdated API syntax - fnFilter was deprecated ages ago. Try $('#myDataTable').DataTable().search(filterValue).draw() instead. Notice the capital T in DataTable() for the new API. Also, you’re hardcoding a date in the change handler instead of using actual filter logic.

4

Nice approach with the custom year periods! Are you handling server-side filtering correctly though? Your change handler doesn’t seem to be sending the selected period to the backend endpoint. Shouldn’t you modify the ajax parameters to include the filter value?