Add number range filter to multiple DataTable columns

orangeman51

I have a DataTable and I'm trying to add filters to multiple columns in it. Some columns are strings and require text inputs while others are numbers and require range inputs. I add filters to each column as described here. For the range inputs, I'm trying to add my own custom search plugin, as described here. Essentially, I'm trying to combine strategies from both parts of the documentation: I want to loop through each column applying filters, and for those columns that are numerical, I want to employ range filters. The example table they provide in their multi-column filtering documentation includes numerical columns, but the filters they use for those columns are text inputs, which, frankly, doesn't seem like the way most would go about it in real-world implementations. The documentation they provide for setting up numerical range filters includes an example that only has one column to be filtered by the input, which allows them to hard-code the appropriate column index into their custom function.

My problem is that I don't know how to get the variables that I need into the custom range filter function. Specifically, I need to get the column index and the user inputs into the function. I'm using this bootstrap slider for the range inputs, so in order to get the user input values, I call .slider on my input and pass in 'getValue'.

How do I get my variables, specifically the column index and the user inputs, into my custom range filter function? My code is below.

function populateEntryTable() {
    $('#entryTableContainer').empty();
    /* put demo data in array of objects that is used to populate DataTable */
    var entries = [{name: John, age: 20, salary: 40000},
     {name: Bill, age: 40, salary: 200000},
     {name: Amy, age: 31, salary: 150000}];

    /*build my table*/
    $('#entryTableContainer').append('<table id="entryTable"><thead><tr></tr></thead><tbody></tbody></table>');
    for (var key in entries[0]) {
        $('#entryTableContainer thead tr').append('<th>' + key + '</th>');
    }
    for (var i = entries.length - 1; i >= 0; i--) {
        for (var key in entries[i]) {
            $('#entryTableContainer tbody tr:last-child').append('<td>' + entries[i][key] + '</td>');
        }
    }

    /* add column filters below each column, as described in DataTables documentation */
    $('#entryTable thead tr').clone(true).appendTo('#entryTable thead');
    var numberInputs = ['age','salary'];
    $('#entryTable thead tr:eq(1) th').each(function(i) {
        var title = $(this).text();
            /* if the col requires a text input filter, do text input filter stuff, which works fine. Else if it requires a number range filter, do number filter stuff, which doesn't work fine. */
            if (numberInputs.indexOf(title) == -1) {
              $(this).html('<input type="text" placeholder="Search">');
              $('input',this).on('keyup change',function() {
                if (entryTable.column(i).search() !== this.value) {
                    entryTable.column(i).search(this.value).draw();
                }
            });
        } else if (numberInputs.indexOf(title) > -1) {
        /* get min and max values in each column to set appropriate bootstrap-slider attributes */
            var min;
            var max;
            $('#entryTable tbody tr').each(function(j) {
                var item = parseFloat($('#entryTable tbody tr:eq(' + j + ') td:eq(' + i + ')').text());
                if (min == undefined || item < min) {
                    min = Math.floor(item);
                }
                if (max == undefined || item > max) {
                    max = Math.ceil(item);
                }
            });

        /* create bootstrap-slider with double inputs */
            $(this).html('<input id="' + title + '" data-slider-min="' + min + '" data-slider-max="' + max + '" data-slider-step="1" data-slider-value="[' + min + ',' + max + ']">');
            $('#' + title).slider({});

        /* add listener for bootstrap-slider change */
            $('input',this).on('change',function() {
            /* returns an array with the min and max user inputs*/
               var userInputs = $(this).slider('getValue');
               var userMin = userInputs[0];
               var userMax = userInputs[1];
               entryTable.draw();
            });
        }
    });

    /* call DataTable on my table and include my option settings*/
    var entryTable = $('#entryTable').DataTable({
        orderCellsTop: true,
        paging: false,
        bInfo: false,
        scrollY: 400,
        scrollCollapse: true,
        order: [ 1, 'desc' ],
        searching: true
    });

    /* searching must be set to true for my column searches to work, but I don't want the whole table search bar to display, so I remove it here */
    $('#entryTable_filter').addClass('d-none');
}

// custom DataTables function for filtering number ranges
$.fn.dataTable.ext.search.push(
    function( settings, data, dataIndex ) {
        /* how do I get i (the col index of the filter that the user is engaging with), the userMin and the userMax into here??? */
        var colVal = parseFloat(data[i].replace('$','')) || 0;

        if ( ( isNaN( userMin ) && isNaN( userMax ) ) ||
             ( isNaN( userMin ) && colVal <= userMax ) ||
             ( userMin <= colVal   && isNaN( userMax ) ) ||
             ( userMin <= colVal   && colVal <= userMax ) )
        {
            return true;
        }
        return false;
    }
);

kthorngren

How do I get my variables, specifically the column index and the user inputs, into my custom range filter function?

The Search Plugin is called for each row.

specifically the column index

The data parameter is an array of the row data. If you want to search on column 0 then you would access data[0], column 1 is data[1], etc.

the user inputs

Use Javascript or jQuery methods (as you prefer) to get the values of the inputs. Just like the example uses var min = parseInt( $('#min').val(), 10 );.

According to the Bootstrap Slider docs and you mentioned this use something like var value = mySlider.slider('getValue'); to get the slider value.

Not sure this is answering your questions. Maybe you are looking for a way to define the column.type. Take a look at the example provided by bindrid in this thread. Note the use of $.each(settings.aoColumns, and if (col.type == "date") { to only apply the filter to date columns.

If this doesn't help please build an example showing what you have so we can help build the solution.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

orangeman51

Thanks for the info. For the bootstrap slider values, I run into issues calling their .slider method in the custom function, but I can find a workaround for that.

My main issue is with the column index. I understand that each column's data is in data and I can reference it by index, but my issue is that the column number I'm looking for is going to be variable. For example, if the filter for the first column is being used, the col index would be 0. If the filter for the second column is being used, the col index would be 1, and so on.

kthorngren

If you are using search inputs like the example you linked to you can just use the column().search() API as in the example. You don't need to check the values in the search plugin. The search plugins run after the column().search() has been applied. For example, if you use column().search() and, after filtering, there are 10 rows remaining the search plugin will only be called for those 10 rows. Hope that makes sense.

Kevin

orangeman51

I made this JSFiddle to show what I'm trying to do. The text input search bars work if you comment out the custom DataTables function. I'm trying to make it so the sliders work, too.

kthorngren

Uncaught ReferenceError: thisColIndex is not defined

The problem is with this statement:
var colVal = parseFloat(data[thisColIndex]) || 0;

thisColIndex isn't defined anywhere. I'm not sure how you are trying to use it.

I changed the selector to run the search plugin to be specific to only the sliders:

        // add listener for bootstrap-slider change
        $('input[tpe="range"]', this).on('change', function() {

I commented out your code and added a simple example to hide rows that are greater than the slider value. Doesn't look like you added the BS slider() code but a similar concept (I have commented code to show this).

Here is your updated example:
https://jsfiddle.net/pmu3c2ft/

Hope it gets you started with what you want to do. Please update the test case if you still need help.

Kevin

orangeman51

I knew that thisColIndex and thisRangeInput were both undefined. I was just using those as placeholders to establish what I was trying to solve.

Your solution still hard codes the column index into the custom function. I was able to solve my problem by moving the custom function inside the .each loop, bringing the variables I need into the proper scope. I put my solution on JSFiddle

kthorngren

Your solution still hard codes the column index into the custom function

Yes, I was just showing a simple example. You could use HTML5 data attributes to indicate the column index. In the plugin you could iterate through the desired inputs and get the column index from the data attribute. There are probably other that will allow for not hard coding the column index.

I was able to solve my problem by moving the custom function inside the .each loop,

Keep in mind that each plugin will be called for each row that is not filtered. With 2 inputs that is doubling the number of calls to the search plugin. Might be a performance issue with large tables.

Kevin