Home > OS >  DataTable date range filter shows incorrect results
DataTable date range filter shows incorrect results

Time:12-14

I have implemented version 1.13.1 of the DataTable. Which I have the filter by date range, and I have created two buttons, one to proceed with the filter, and the other to reset it (In the guide of the datatable web page, there are not these buttons).

Date Range Filter Datatable

The problem is that when filtering and resetting several times (trying various combinations in the date picker), there are some results that are displayed erroneously.

I don't know if the algorithm that I implemented is the correct one for the functionalities (the two buttons) that I want.

Thanks in advance.

var minDate, maxDate;

$(function() {
    // Create date inputs
    minDate = new DateTime($('#min'), {
        format: 'DD / MM / YYYY'
    });
      maxDate = new DateTime($('#max'), {
        format: 'DD / MM / YYYY'
    });
 
    // DataTables initialisation
    var table = $('#example').DataTable();

    $("#filter_date").click(function() {

       if ( $("#min").val() !== "" || $("#max").val() !== "") {
            // Custom filtering function which will search data in column four between two values
            $.fn.dataTable.ext.search.push(
                function( settings, data, dataIndex ) {
                    var min = minDate.val();
                    var max = maxDate.val();
                    var date = new Date( data[4] );
             
                    if (
                        ( min === null && max === null ) ||
                        ( min === null && date <= max ) ||
                        ( min <= date   && max === null ) ||
                        ( min <= date   && date <= max )
                    ) {
                        return true;
                    }
                    return false;
                }
            );

            table.draw();
        }
    });

    $("#reset_filter_date").click(function() {

        if ( $("#min").val() !== "" || $("#max").val() !== "" ) {
            $("#min, #max").val("");
            $.fn.dataTable.ext.search = [];
            table.draw();
        }
    });
});
<!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.13.1/js/jquery.dataTables.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.2/moment.min.js"></script>
  <script src="https://cdn.datatables.net/datetime/1.2.0/js/dataTables.dateTime.min.js"></script>


  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.1/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/datetime/1.2.0/css/dataTables.dateTime.min.css">


</head>

<body>

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

    <table border="0" cellspacing="5" cellpadding="5">
      <tbody>
        <tr>
          <td>From:</td>
          <td>
            <input type="text" id="min" name="min">
          </td>
        </tr>
        <tr>
          <td>Until:</td>
          <td>
            <input type="text" id="max" name="max">
          </td>
        </tr>
        <tr>
          <td>
            <button id="filter_date">Filter</button>
            <button id="reset_filter_date">Reset</button>
          </td>
        </tr>
      </tbody>
    </table>

    <br><br>


    <table id="example"  style="width:100%">
      <thead>
        <tr>
          <th>Name</th>
          <th>Position</th>
          <th>Office in Country</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>$320,800</td>
        </tr>
        <tr>
          <td>Garrett Winters</td>
          <td>Accountant</td>
          <td>Tokyo</td>
          <td>63</td>
          <td>2011/07/25</td>
          <td>$170,750</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>$86,000</td>
        </tr>
        <tr>
          <td>Sonya Frost</td>
          <td>Software Engineer</td>
          <td>Edinburgh</td>
          <td>23</td>
          <td>2008/12/13</td>
          <td>$103,600</td>
        </tr>
        <tr>
          <td>Donna Snider</td>
          <td>Customer Support</td>
          <td>New York</td>
          <td>27</td>
          <td>2011/01/25</td>
          <td>$112,000</td>
        </tr>
      </tbody>
    </table>

  </div>


</body>

</html>

CodePudding user response:

Datatables search plug-in allows to push some functions to an array of function that will execute on search.

You need to push your custom function only once... In the click hadler, just trigger the search and the draw.

Also... Try to always use const and let instead of var. You can read about it here

$(function() {
  // Create date inputs
  const minDate = new DateTime($('#min'), {
    format: 'DD / MM / YYYY'
  });
  const maxDate = new DateTime($('#max'), {
    format: 'DD / MM / YYYY'
  });

  // DataTables initialisation
  const table = $('#example').DataTable();

  // Add your custom filter function only once (not at every click)
  $.fn.dataTable.ext.search.push(
    function(settings, data, dataIndex) {
      const min = minDate.val();
      const max = maxDate.val();
      const date = new Date(data[4]);
      console.log(min, max)

      if (
        (min === null && max === null) ||
        (min === null && date <= max) ||
        (min <= date && max === null) ||
        (min <= date && date <= max)
      ) {
        return true;
      }
      return false;
    }
  );

  $("#filter_date").click(function() {
    if ($("#min").val() !== "" || $("#max").val() !== "") {
      
      // search and draw
      table.search()
      table.draw();
    }
  });

  $("#reset_filter_date").click(function() {
  
    // Clear the input values
    $("#min, #max").val("");
    
    // set those null aswell
    minDate.val(null)
    maxDate.val(null)
    
    // search and draw
    table.search()
    table.draw();
  });
});
<!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.13.1/js/jquery.dataTables.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.2/moment.min.js"></script>
  <script src="https://cdn.datatables.net/datetime/1.2.0/js/dataTables.dateTime.min.js"></script>


  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.1/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/datetime/1.2.0/css/dataTables.dateTime.min.css">


</head>

<body>

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

    <table border="0" cellspacing="5" cellpadding="5">
      <tbody>
        <tr>
          <td>From:</td>
          <td>
            <input type="text" id="min" name="min">
          </td>
        </tr>
        <tr>
          <td>Until:</td>
          <td>
            <input type="text" id="max" name="max">
          </td>
        </tr>
        <tr>
          <td>
            <button id="filter_date">Filter</button>
            <button id="reset_filter_date">Reset</button>
          </td>
        </tr>
      </tbody>
    </table>

    <br><br>


    <table id="example"  style="width:100%">
      <thead>
        <tr>
          <th>Name</th>
          <th>Position</th>
          <th>Office in Country</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>$320,800</td>
        </tr>
        <tr>
          <td>Garrett Winters</td>
          <td>Accountant</td>
          <td>Tokyo</td>
          <td>63</td>
          <td>2011/07/25</td>
          <td>$170,750</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>$86,000</td>
        </tr>
        <tr>
          <td>Sonya Frost</td>
          <td>Software Engineer</td>
          <td>Edinburgh</td>
          <td>23</td>
          <td>2008/12/13</td>
          <td>$103,600</td>
        </tr>
        <tr>
          <td>Donna Snider</td>
          <td>Customer Support</td>
          <td>New York</td>
          <td>27</td>
          <td>2011/01/25</td>
          <td>$112,000</td>
        </tr>
      </tbody>
    </table>

  </div>


</body>

</html>

  • Related