jqueryfilterdatatables

Table with multiple range filters doesn't filter properly

I have an HTML table that I'm using JQuery and DataTables to provide sorting and filtering. I'm trying to get range filtering for numeric columns working. The problem is that the second numeric column's range input seems to filter on the values of the first one under some circumstances and the second range gets ignored in others.

These work (using the filter inputs at the bottom of the columns):

This does not work:

Entering an Age filter value then a Salary filter value, the Salary value filters the Age column. For example, entering 40 for Minimum Age (5 results) then 100000 for salary leaves no results. But changing the Salary to 65 leaves one result (Ashton Cox, age 66).

Press the Reset Filters button. Enter 100000 for Minimum Salary (6 results). Now enter 40 for Minimum Age (5 results). The Age column is properly filtered, but the Salary column now shows salaries below the value entered in the Minimum Salary field.

I've written this intending it to be flexible, avoiding hard-coding the number of numeric columns or their positions

I'm sure that I have the range filtering with an incorrect "conjunction", but I'm unable to determine what's wrong.

function filterColumnRange(table, col) {
  mininp = $("#min" + col.index()).val()
  maxinp = $("#max" + col.index()).val()
  table
    .column(col)
    .search(
      (d) =>
      (d >= mininp * 1 && d <= maxinp * 1) ||
      (mininp === "" && d <= maxinp * 1) ||
      (d >= mininp * 1 && maxinp === ""),
    )
    .draw()
}

$(document).ready(function() {

  table = $("#example").DataTable({
    layout: {
      bottomEnd: {
        buttons: [{
          text: 'Reset Filters',
          action: function() {
            table.search('').columns().search('').draw();
            $('tfoot input').val('');
          }
        }],
      }
    }
  })
  table.columns(".dt-type-numeric").every(function() {
    var column = this
    var title = column.footer().textContent
    $(
        '<input type="text" id="min' +
        column.index() +
        '" placeholder="Minimum ' +
        title +
        '" />',
      )
      .appendTo($(column.footer()))
      .on("keyup change clear", function() {
        filterColumnRange(table, column)
      })
    $(
        '<input type="text" id="max' +
        column.index() +
        '" placeholder="Maximum ' +
        title +
        '" />',
      )
      .appendTo($(column.footer()))
      .on("keyup change clear", function() {
        filterColumnRange(table, column)
      })
  })
  table.columns(":not(.dt-type-numeric)").every(function() {
    var column = this
    var title = column.footer().textContent
    $('<input type="text" placeholder="Filter ' + title + '" />')
      .appendTo($(column.footer()))
      .on("keyup change clear", function() {
        if (column.search() !== this.value) {
          column.search(this.value).draw()
        }
      })
  })
})
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/2.2.2/js/dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/3.2.2/js/buttons.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/3.2.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/3.2.2/js/buttons.html5.min.js"></script>
<link href="https:/cdn.datatables.net/2.2.2/css/dataTables.dataTables.min.css" rel="stylesheet" />
<link href="https://cdn.datatables.net/buttons/3.2.2/css/buttons.dataTables.min.css" rel="stylesheet" />
<table id="example" class="display" style="width:100%">
  <thead>
    <tr>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Age</th>
      <th>Start date</th>
      <th>Salary</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Tiger Nixon</td>
      <td>System Architect</td>
      <td>Edinburgh</td>
      <td>61</td>
      <td>2011-04-25</td>
      <td>320800</td>
    </tr>
    <tr>
      <td>Garrett Winters</td>
      <td>Accountant</td>
      <td>Tokyo</td>
      <td>63</td>
      <td>2011-07-25</td>
      <td>170750</td>
    </tr>
    <tr>
      <td>Ashton Cox</td>
      <td>Junior Technical Author</td>
      <td>San Francisco</td>
      <td>66</td>
      <td>2009-01-12</td>
      <td>86000</td>
    </tr>
    <tr>
      <td>Cedric Kelly</td>
      <td>Senior Javascript Developer</td>
      <td>Edinburgh</td>
      <td>22</td>
      <td>2012-03-29</td>
      <td>433060</td>
    </tr>
    <tr>
      <td>Airi Satou</td>
      <td>Accountant</td>
      <td>Tokyo</td>
      <td>33</td>
      <td>2008-11-28</td>
      <td>162700</td>
    </tr>
    <tr>
      <td>Brielle Williamson</td>
      <td>Integration Specialist</td>
      <td>New York</td>
      <td>61</td>
      <td>2012-12-02</td>
      <td>372000</td>
    </tr>
    <tr>
      <td>Herrod Chandler</td>
      <td>Sales Assistant</td>
      <td>San Francisco</td>
      <td>59</td>
      <td>2012-08-06</td>
      <td>137500</td>
    </tr>
  </tbody>
  <div id="myDiv">
  </div>
  <tfoot>
    <tr>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Age</th>
      <th>Start date</th>
      <th>Salary</th>
    </tr>
  </tfoot>
</table>

Solution

  • The Problem

    I don't know enough about DataTables to explain precisely why you are getting the problem you are seeing - but I can point to the values of d in your filterColumnRange function. If your very first filter is on "minimum age", and if you then filter by "minimum salary", you will see that the filter first processes all of the values in the "age" column again, before processing the values in the "salary" column. By then it's too late - your data has been incorrectly filtered.

    I believe DataTables uses an array of filters, behind the scenes, to allow you to define various custom filters which are all processed in turn for each new draw event. For some reason (which I cannot explain) your filterColumnRange function is therefore applying your "salary" filter to the "age" data - as you are seeing.

    I don't have a way to fix this approach. There may well be a simple fix that someone else can share for us all.

    One Possible Solution

    However, here is a way to build the equivalent set of filters, but using a different approach - using columns().search.fixed(), which is a new feature in DataTables 2.0.

    Maybe this is acceptable.

    Here is a demo:

    <!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>DataTables</title>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
  <script src="https://cdn.datatables.net/2.2.2/js/dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/3.2.2/js/buttons.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/3.2.2/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/3.2.2/js/buttons.html5.min.js"></script>
  <link href="https:/cdn.datatables.net/2.2.2/css/dataTables.dataTables.min.css" rel="stylesheet" />
  <link href="https://cdn.datatables.net/buttons/3.2.2/css/buttons.dataTables.min.css" rel="stylesheet" />
</head>

<body>
  <table id="example" class="display" style="width:100%">
    <thead>
      <tr>
        <th>Name</th>
        <th>Position</th>
        <th>Office</th>
        <th>Age</th>
        <th>Start date</th>
        <th>Salary</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td>Tiger Nixon</td>
        <td>System Architect</td>
        <td>Edinburgh</td>
        <td>61</td>
        <td>2011-04-25</td>
        <td>320800</td>
      </tr>
      <tr>
        <td>Garrett Winters</td>
        <td>Accountant</td>
        <td>Tokyo</td>
        <td>63</td>
        <td>2011-07-25</td>
        <td>170750</td>
      </tr>
      <tr>
        <td>Ashton Cox</td>
        <td>Junior Technical Author</td>
        <td>San Francisco</td>
        <td>66</td>
        <td>2009-01-12</td>
        <td>86000</td>
      </tr>
      <tr>
        <td>Cedric Kelly</td>
        <td>Senior Javascript Developer</td>
        <td>Edinburgh</td>
        <td>22</td>
        <td>2012-03-29</td>
        <td>433060</td>
      </tr>
      <tr>
        <td>Airi Satou</td>
        <td>Accountant</td>
        <td>Tokyo</td>
        <td>33</td>
        <td>2008-11-28</td>
        <td>162700</td>
      </tr>
      <tr>
        <td>Brielle Williamson</td>
        <td>Integration Specialist</td>
        <td>New York</td>
        <td>61</td>
        <td>2012-12-02</td>
        <td>372000</td>
      </tr>
      <tr>
        <td>Herrod Chandler</td>
        <td>Sales Assistant</td>
        <td>San Francisco</td>
        <td>59</td>
        <td>2012-08-06</td>
        <td>137500</td>
      </tr>
    </tbody>
    <div id="myDiv">
    </div>


    <tfoot>
      <tr>
        <th>Name</th>
        <th>Position</th>
        <th>Office</th>
        <th>Age</th>
        <th>Start date</th>
        <th>Salary</th>
      </tr>
    </tfoot>
  </table>

  <script>
    const table = new DataTable('#example', {

      layout: {
        bottomEnd: {
          buttons: [{
            text: 'Reset Filters',
            action: function() {
              $('tfoot input').val('');
              clearFixedFilters();
              table.search('').columns().search('').draw();

            }
          }],
        }
      },

      initComplete: function() {

        // non-numeric fields:
        this.api()
          .columns(":not(.dt-type-numeric)")
          .every(function() {
            let column = this;
            let title = column.footer().textContent;

            // Create input element
            let input = document.createElement('input');
            input.placeholder = title;
            column.footer().replaceChildren(input);

            // Event listener for user input
            input.addEventListener('keyup', () => {
              if (column.search() !== this.value) {
                column.search(input.value).draw();
              }
            });
          });

        // numeric fields using min/max ranges:
        this.api()
          .columns(".dt-type-numeric")
          .every(function() {
            let column = this;
            let title = column.footer().textContent;

            // Create input elements
            let inputMin = document.createElement('input');
            inputMin.placeholder = "min " + title;
            let inputMax = document.createElement('input');
            inputMax.placeholder = "max " + title;
            column.footer().replaceChildren(inputMin, inputMax);

            // Event listeners for user inputs
            inputMin.addEventListener('input', function() {
              table.draw();
            });
            inputMax.addEventListener('input', function() {
              table.draw();
            });
          });

      }
    });

    function setUpFixedFilter() {
      table.columns(".dt-type-numeric")
        .every(function() {
          let column = this;
          column.search.fixed('my-range', function(cellData) {

            var min = parseInt(column.footer().childNodes[0].value, 10);
            var max = parseInt(column.footer().childNodes[1].value, 10);
            var data = parseFloat(cellData) || 0;

            if (
              (isNaN(min) && isNaN(max)) ||
              (isNaN(min) && data <= max) ||
              (min <= data && isNaN(max)) ||
              (min <= data && data <= max)
            ) {
              return true;
            }

            return false;

          });
        });
    }

    function clearFixedFilters() {
      table.columns().search.fixed('my-range', null);
      setUpFixedFilter();
    }

    setUpFixedFilter();
  </script>

</body>

    This approach is basically a combination of two official demos: range searching and individual column searching.

    The fixed search approach seems to be a good fit for what you need to do here:

    The fixed search (which might also be termed sticky or named searches) provides a way to easily add cumulative search terms to the table. You can add multiple independent search terms, and DataTables will combine them (AND logic) displaying the result of all search terms that have been applied.

    This handles the "conjunction" issue you referred to in your question.

    One caveat:

    To reset all filter data (using my "Reset Filters" custom button), my demo actually completely removes all fixed searches from the DataTable and then re-applies the initial search state:

    function clearFixedFilters() {
  table.columns().search.fixed('my-range', null);
  setUpFixedFilter();
}

    This feels a bit heavy-handed and there may be a better way to do this.