How to add minimum and maximum range filter with input field in datatable?

Question

I have a table with multiple columns, each column has an input field to search through a particular column.

But for 4 columns, which are number-based, I wanted a range filter. That means, those 4 columns will have two input fields, the first is to put the minimum range number and the second is to put the maximum range number. Take a look at the reference image(it has only 2 columns out of 4 number-based columns, the other 2 are not visible in the screenshot).

Now I am trying to implement this functionality by adding my own search function to the datatable search function array, as given in the docs. https://datatables.net/examples/plug-ins/range_filtering.html

But the search is not working, or i say it is not making any changes in UI at all.

this is the code -

// Call the dataTables jQuery plugin
$(document).ready(function() {

  // Setup - add a text input to each footer cell
  $('#dataTable thead tr').clone(true).appendTo('#dataTable thead');
  $('#dataTable thead tr:eq(1) th').each( function (i) {
    var title = $(this).text();
    if(title==='clicks' || title==='impressions' || title==='ctr' || title==='position') {
      $(this).html(`
        <div class='d-flex'>
          <input name=${title}_min id=${title}_min class='' type='number' min='0' placeholder='Min' style='width: 80px;'/>
          <input name=${title}_max id=${title}_max class='ml-1' type='number' min='0' placeholder='Max' style='width: 80px;'/>
        </div>
      `);

      var minInputValue = parseFloat($(`input[id=${title}_min]`, this).val()) || 0;
      var maxInputValue = parseFloat($(`input[id=${title}_max]`, this).val()) || 0;

      $.fn.dataTable.ext.search.push(function (settings, data, dataIndex) {
        // logic to filter min and max values
        var colVal = parseFloat(data[i]) ||  0;
        if (colVal >= minInputValue || colVal <= maxInputValue || minInputValue === 0 || maxInputValue === 0) {
          return true;
        }
        return false;
      });

      $(`#${title}_min`, this).on('keyup change', function () {
        minInputValue = parseFloat($(this).val()) || 0;
        console.log('min', minInputValue);
        dataTable.draw();
      });

      $(`#${title}_max`, this).on('keyup change', function () {
        maxInputValue = parseFloat($(this).val()) || 0;
        console.log('max', maxInputValue);
        dataTable.draw();
      });

    } else {
      $(this).html('<input type="text" placeholder="Search '+title+'" />');
      $('input', this).on('keyup change', function () {
        if (dataTable.column(i).search() !== this.value) {
          dataTable.column(i).search(this.value).draw();
        }
      });
    }
  });

  var dataTable = $('#dataTable').DataTable({
    orderCellsTop: true,
    paging: true,
    scrollX: 400,
    searching: true,
    // lengthMenu: true,
    dom: 'Blfrtip',
    buttons: [
      { extend: 'csv', className: 'mb-2 btn btn-sm btn-info'}, 
      { extend: 'excel', className: 'mb-2 btn btn-sm btn-info' },
      { extend: 'pdf', className: 'mb-2 btn btn-sm btn-info' },
      { extend: 'print', className: 'mb-2 btn btn-sm btn-info' },
    ]
  });

  $('#dataTable_wrapper .dataTables_length').css({ display: 'inline-flex', 'margin-left': '20px' })
});

I really appreciate your help.

andrewJames · Answer 1 · 2021-08-04T00:30:25.140

Your approach is close, but you need to expand the logic in your filter function:

$.fn.dataTable.ext.search.push(function (settings, data, dataIndex) {
  
  // gather all the inputs we will need:
  var clicks_val = parseFloat(data[2]) ||  0.0;
  var clicks_min = parseFloat($('#clicks_min').val()) || 0;
  var clicks_max = parseFloat($('#clicks_max').val()) || Number.MAX_VALUE;
  var impressions_val = parseFloat(data[3]) ||  0.0;
  var impressions_min = parseFloat($('#impressions_min').val()) || 0;
  var impressions_max = parseFloat($('#impressions_max').val()) || Number.MAX_VALUE;

  // evaluate to true to filter in a row, or false to filter it out:
  var clicks = (clicks_val >= clicks_min && clicks_val <= clicks_max);
  var impressions = (impressions_val >= impressions_min && impressions_val <= impressions_max);

  // combine the above evaluations for overall row filtering:
  return clicks && impressions;
});

Here I explicitly gather all the inputs needed to decide if a row should be visible or filtered out.

A demo:

$(document).ready(function() {

  $.fn.dataTable.ext.search.push(function (settings, data, dataIndex) {
    // gather all the inputs we will need:
    var clicks_val = parseFloat(data[2]) ||  0.0;
    var clicks_min = parseFloat($('#clicks_min').val()) || 0;
    var clicks_max = parseFloat($('#clicks_max').val()) || Number.MAX_VALUE;
    var impressions_val = parseFloat(data[3]) ||  0.0;
    var impressions_min = parseFloat($('#impressions_min').val()) || 0;
    var impressions_max = parseFloat($('#impressions_max').val()) || Number.MAX_VALUE;

    // evaluate to true to filter in a row, or false to filter it out:
    var clicks = (clicks_val >= clicks_min && clicks_val <= clicks_max);
    var impressions = (impressions_val >= impressions_min && impressions_val <= impressions_max);

    // combine the above evaluations for overall row filtering:
    return clicks && impressions;
  });

  // Setup - add a text input to each footer cell
  $('#dataTable thead tr').clone(true).appendTo('#dataTable thead');
  $('#dataTable thead tr:eq(1) th').each( function (i) {
    var title = $(this).text();
    if(title==='clicks' || title==='impressions' ) {
      $(this).html(`
        <div class='d-flex'>
          <input name=${title}_min id=${title}_min class='' type='number' min='0' placeholder='Min' style='width: 80px;'/>
          <input name=${title}_max id=${title}_max class='ml-1' type='number' min='0' placeholder='Max' style='width: 80px;'/>
        </div>
      `);

      $(`#${title}_min`, this).on('keyup change', function () {
        minInputValue = parseFloat($(this).val()) || 0;
        dataTable.draw();
      });

      $(`#${title}_max`, this).on('keyup change', function () {
        maxInputValue = parseFloat($(this).val()) || 0;
        dataTable.draw();
      });

    } else {
      $(this).html('<input type="text" placeholder="Search '+title+'" />');
      $('input', this).on('keyup change', function () {
        if (dataTable.column(i).search() !== this.value) {
          dataTable.column(i).search(this.value).draw();
        }
      });
    }
  });

  var dataTable = $('#dataTable').DataTable({
    orderCellsTop: true,
    paging: true,
    scrollX: 400,
    searching: true,
    // lengthMenu: true,
    dom: 'Blfrtip',
    buttons: [
      { extend: 'csv', className: 'mb-2 btn btn-sm btn-info'}, 
      { extend: 'excel', className: 'mb-2 btn btn-sm btn-info' },
      { extend: 'pdf', className: 'mb-2 btn btn-sm btn-info' },
      { extend: 'print', className: 'mb-2 btn btn-sm btn-info' },
    ]
  });

  $('#dataTable_wrapper .dataTables_length').css({ display: 'inline-flex', 'margin-left': '20px' })
});

<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
  <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

</head>

<body>

<div style="margin: 20px;">

    <table id="dataTable" class="display dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>page</th>
                <th>query</th>
                <th>clicks</th>
                <th>impressions</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>https://google.comn</td>
                <td>foo bar</td>
                <td>1.2</td>
                <td>3.4</td>
            </tr>
            <tr>
                <td>https://google.comn</td>
                <td>foo bar</td>
                <td>2.3</td>
                <td>4.5</td>
            </tr>
            <tr>
                <td>https://google.comn</td>
                <td>foo bar</td>
                <td>3.4</td>
                <td>5.6</td>
            </tr>
        </tbody>
    </table>

</div>

</body>
</html>

Note also that we no longer need these:

var minInputValue = parseFloat($(`input[id=${title}_min]`, this).val()) || 0;
var maxInputValue = parseFloat($(`input[id=${title}_max]`, this).val()) || 0;

Update

Here is one approach to make your code more "generic" - by which I mean you do not have to explicitly capture separate variables for each cell value and each related min/max value:

$.fn.dataTable.ext.search.push(function ( settings, searchData, index, rowData, counter ) {

  // assume the row is going to be displayed:
  var result = true; 

  // loop through the cells we want to check:
  $('#dataTable thead tr:eq(0) th').each( function (colIdx) {
    var title = $(this).text();
    if ( $(`#${title}_min`).length ) {
    
      // gather all the inputs we will need to check one cell in the current row:
      var val = parseFloat(searchData[colIdx]) ||  0.0;
      var min = parseFloat($(`#${title}_min`).val()) || 0;
      var max = parseFloat($(`#${title}_max`).val()) || Number.MAX_VALUE;

      //console.log( min, val, max ); // just for testing

      if (val < min || val > max) {
        result = false; // any one failure means the row is filtered out
      }
    }
  
  } );
  return result;

});

Only the $.fn.dataTable.ext.search.push function has changed, here. The rest of the code stays the same. I have tried to annotate the code to clarify what it is doing. I'm sure you could improve this even more, but this should give you some ideas.

Hey @andrewjames it is working like charm, Thanks. but what if we have 20 columns or even 50 columns having same min,max filter? in that case we are not supposed to hard code for each column, can you suggest anything like I was already doing in my question, something dynamic? — Gaurav Sharma, Aug 03 '21 at 03:26
Have you tried to make it more generalized for yourself? If so, what did you try, and what problems did you encounter? (By the way, I would suggest that any table with "_20 columns or even 50 columns having same min,max filter_" would be more-or-less unusable from an end-user's point of view.) — andrewJames, Aug 03 '21 at 13:13
I just noticed something - my apologies, I did not catch it the first time around: The `$.fn.dataTable.ext.search` function should be moved to be a top-level function. It should not be inside the `each` loop. Reason: I only need to push that function onto the function array once. — andrewJames, Aug 03 '21 at 16:40

How to add minimum and maximum range filter with input field in datatable?

1 Answers1