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

user_8550926 0
Viewed 781

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).

enter image description here

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.

datatables datatable jquery javascript
1 Answers

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.

user_125673650
Related