How to do a non-text based search on a datatable when a select changes-CodePudding

I am trying to filter the datatable column status via select when the content of the column is not a text.

What I've tried so far:

Basically I tried using the search engine this way:

$('#search2').on('change', () =>
{
    DT1.search($('#search2').val()).draw();
});

But as is obvious, if we compare a String with a 'colored square' there is no result.

How can I do so that if the user selects Active, only the green squares are shown and if the user selects inactive, only the red squares are shown?

Any help will be highly appreciated

CodePudding user response：

Not the most optimal way to do it though thought I'd provide a working solution in case no one else does.

Show code snippet

$(document).ready(function() {
  var DT1 = $('#example').DataTable({
    columnDefs: [{
      orderable: false,
      className: 'select-checkbox',
      targets: 0,
    }],
    select: {
      style: 'os',
      selector: 'td:first-child'
    },
    order: [
      [1, 'asc']
    ],
    dom: 'lrt'
  });
  $(".selectAll").on("click", function(e) {
    if ($(this).is(":checked")) {
      DT1.rows().select();
    } else {
      DT1.rows().deselect();
    }
  });

  $('#search').on('input', () => {
    DT1.search($('#search').val()).draw();
  });
  $('#search2').on('change', () => {
    const state = $("#search2").val();
    if (state === "none") {
      $(".status-active").parent().parent().attr("hidden", false);
      $(".status-inactive").parent().parent().attr("hidden", false);
      return;
    }

    $(".status-"   ((state === "active") ? 'inactive' : 'active')).parent().parent().attr("hidden", true);
    $(".status-"   state).parent().parent().attr("hidden", false);

  });
});

body {
  font: 90%/1.45em "Helvetica Neue", HelveticaNeue, Verdana, Arial, Helvetica, sans-serif;
  margin: 0;
  padding: 0;
  color: #333;
  background-color: #fff;
}

.status-active {
  height: 25px;
  width: 25px;
  background-color: #385C0B;
  margin: 0 auto;
}

.status-inactive {
  height: 25px;
  width: 25px;
  background-color: #CC000C;
  margin: 0 auto;
}

<!DOCTYPE html>
<html>

<head>
  <meta name="description" content="stackoverflow" />
  <script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>

  <link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
  <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-KyZXEAg3QhqLMpG8r 8fhAXLRk2vvoC2f3B09zVXn8CA5QIVfZOJ3BCsw2P0p/We" crossorigin="anonymous">
  <script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>
  <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/select/1.3.3/js/dataTables.select.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-U1DAWAznBHeqEIlVSCgzq c9gqGAJn5c/t99JyeKa9xxaYpSvHU5awsuZVVFIhvj" crossorigin="anonymous"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/1.3.3/css/select.dataTables.min.css">

  <meta charset=utf-8 />
  <title>DataTables - JS Bin</title>
</head>

<body>


  <div class="container">
    <div class="row">
      <div class="form-inline">
        <input id="search" title="Search" placeholder="Search" class="filter-input form-control form-control-sm m-2" type="text" name="filter-project" value="">
        <select id="search2" name="expense_category" class="form-control form-control-sm m-2">
          <option value="none">Select a Status</option>
          <option value="active">Active</option>
          <option value="inactive">Inactive</option>
        </select>
      </div>
      <table id="example" class="display nowrap" width="100%">
        <thead>
          <tr>
            <th class="text-center"><input type="checkbox" class="selectAll" name="selectAll" value="all"></th>
            <th>Name</th>
            <th>Status</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
          </tr>
        </thead>

        <tbody>
          <tr>
            <td></td>
            <td>Tiger Nixon</td>
            <td>
              <div class="status-active" title="Active"></div>
            </td>
            <td>Edinburgh</td>
            <td>61</td>
            <td>2011/04/25</td>
            <td>$3,120</td>
          </tr>
          <tr>
            <td></td>
            <td>Garrett Winters</td>
            <td>
              <div class="status-active" title="Active"></div>
            </td>
            <td>Edinburgh</td>
            <td>63</td>
            <td>2011/07/25</td>
            <td>$5,300</td>
          </tr>
          <tr>
            <td></td>
            <td>Donna Snider</td>
            <td>
              <div class="status-inactive"></div>
            </td>
            <td>New York</td>
            <td>27</td>
            <td>2011/01/25</td>
            <td>$3,120</td>
          </tr>
        </tbody>
      </table>
    </div>
</body>

</html>

Explanation

We are focusing primarily on the event handler for when the status dropdown is changed.

$('#search2').on('change', () =>
{
    const state = $("#search2").val();
    if (state === "none") {
      $(".status-active").parent().parent().attr("hidden", false);
      $(".status-inactive").parent().parent().attr("hidden", false);
      return;
    }

    $(".status-"   ((state === "active") ? 'inactive' : 'active')).parent().parent().attr("hidden", true);
    $(".status-"   state).parent().parent().attr("hidden", false);
    
});

First, we fetch the state of the dropdown menu and what is selected by fetching the value of the selection with .val(), this will give us either active, inactive, or none. (I made the necessary adjustment to your HTML dropdown to add these values.)

With this information, we know what type of filtering we need to do.

Updating States

If no status is selected (none) then we unhide all rows by fetching every element with the classes .status-active and .status-inactive, this is done in the following manner:

$(".status-active").parent().parent().attr("hidden", false);

The $(".status-active") fetches all rows with the active class, and gets the parent of the parent which gives us the row element itself, and sets the hidden attribute to false, making it visible.

Filtering Active/Inactive

Here is the code that actually does the filtering:

$(".status-"   ((state === "active") ? 'inactive' : 'active')).parent().parent().attr("hidden", true);
$(".status-"   state).parent().parent().attr("hidden", false);

Examining the first line more closely, there is a ternary operator which checks to see if the state is equal to active, in which case if it is, it will output inactive, or vise versa, the purpose of this is to select the opposite class attribute so we can hide it.

The second line simply fetches all rows with the state that was selected, and makes them visible.

CodePudding user response：

You can use DataTables support for orthogonal data. This feature allows you to save more than one value for each cell in your table, including:

the display value
the value to use when sorting
the value to use when filtering

(Most of the time, you do not explicitly use this feature, and therefore all these values are the same as the display value.)

So, in your case, you can use the colored square as the display value, and the words "Active" and "Inactive" as the filter values.

Because you have a pre-built HTML table, you can use DataTables support for HTML 5 orthogonal values to provide the filter terms:

<td data-filter="Inactive"><div class="status-inactive"></div></td>

In the above fragment, I added the data-filter="Inactive" attribute to the <td> tag.

The following extra steps are also needed:

Because you want to combine the drop-down filter with the global text filter, you cannot provide these as two separate DT1.search() functions. Currently, these two functions will not work in combination correctly.

To handle this you can create a custom search function for the drop-down filter:

$.fn.dataTable.ext.search.push(
  function( settings, searchData, index, rowData, counter ) {
    console.log( searchData );
    var statusFilter = $('#search2').val();
    var statusCell =  searchData[2] || ''; // using 'status' data from the 3rd column
    return (statusFilter === '' || statusFilter === statusCell) ;
  }
);

This custom search function is added to an array containing the existing search function provided out-of-the box by DataTables - this is why we use $.fn.dataTable.ext.search.push in the above code fragment.

The existing search function is combined with our custom search function for the drop-down.

We also change the related event to simply re-draw the table - which will automatically cause the array of search functions to be executed:

$('#search2').on('change', () => {
  DT1.draw();
});

Here is a demo:

    $(document).ready(function() {
    
    $.fn.dataTable.ext.search.push(
      function( settings, searchData, index, rowData, counter ) {
        //console.log( searchData );
        var statusFilter = $('#search2').val();
        var statusCell =  searchData[2] || ''; // using 'status' data from the 3rd column
        return (statusFilter === '' || statusFilter === statusCell) ;
      }
    );
    
        var  DT1 = $('#example').DataTable(
        {
            columnDefs: [ 
            {
                orderable: false,
                className: 'select-checkbox',
                targets:   0,
            } ],
            select: {
                style:    'os',
                selector: 'td:first-child'
            },
            order: [[ 1, 'asc' ]],
            dom: 'lrt'
        });

        $(".selectAll").on( "click", function(e) {
            if ($(this).is( ":checked" )) {
                DT1.rows(  ).select();        
            } else {
                DT1.rows(  ).deselect(); 
            }
        });
      
        $('#search').on('input', () => {
            DT1.search($('#search').val()).draw();
        });
        
        $('#search2').on('change', () => {
            DT1.draw();
        });
        
    });

<!DOCTYPE html>
<html>
  <head>
<meta name="description" content="stackoverflow" />
    <script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>

    <link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-KyZXEAg3QhqLMpG8r 8fhAXLRk2vvoC2f3B09zVXn8CA5QIVfZOJ3BCsw2P0p/We" crossorigin="anonymous">
    <script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/select/1.3.3/js/dataTables.select.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-U1DAWAznBHeqEIlVSCgzq c9gqGAJn5c/t99JyeKa9xxaYpSvHU5awsuZVVFIhvj" crossorigin="anonymous"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/1.3.3/css/select.dataTables.min.css">
    
    <meta charset=utf-8 />
    <title>demo</title>

<style>
body {
  font: 90%/1.45em "Helvetica Neue", HelveticaNeue, Verdana, Arial, Helvetica, sans-serif;
  margin: 0;
  padding: 0;
  color: #333;
  background-color: #fff;
}

.status-active{
    height: 25px;
    width: 25px;
    background-color: #385C0B;
    margin: 0 auto;
}

.status-inactive{
    height: 25px;
    width: 25px;
    background-color: #CC000C;
</style>
  </head>
  <body>
    
    
    <div class="container">
            <div class="row">
        <div class="form-inline">
            <input id="search" title="Search" placeholder="Search" class="filter-input form-control form-control-sm m-2"
                type="text" name="filter-project" value="">
            <select id="search2" name="expense_category" class="form-control form-control-sm m-2">
                <option value="">Select a Status</option>
                <option value="Active">Active</option>
                <option value="Inactive">Inactive</option>
            </select>
        </div>
      <table id="example" class="display nowrap" width="100%">
        <thead>
          <tr>
            <th class="text-center"><input type="checkbox" class="selectAll" name="selectAll" value="all"></th>
            <th>Name</th>
            <th>Status</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
          </tr>
        </thead>

        <tbody>
          <tr>
            <td></td>
            <td>Tiger Nixon</td>
            <td data-filter="Active"><div class="status-active" title="Active"></div></td>
            <td>Edinburgh</td>
            <td>61</td>
            <td>2011/04/25</td>
            <td>$3,120</td>
          </tr>
          <tr>
            <td></td>
            <td>Garrett Winters</td>
            <td data-filter="Active"><div class="status-active" title="Active"></div></td>
            <td>Edinburgh</td>
            <td>63</td>
            <td>2011/07/25</td>
            <td>$5,300</td>
          </tr>
          <tr>
            <td></td>
            <td>Donna Snider</td>
            <td data-filter="Inactive"><div class="status-inactive"></div></td>
            <td>New York</td>
            <td>27</td>
            <td>2011/01/25</td>
            <td>$3,120</td>
          </tr>
        </tbody>
      </table>
    </div>



  </body>
</html>

Final point:

If you are populating the HTML table dynamically, then you can use the DataTables columns.render function to achieve the same effect as the HTML5 data-filter attribute.

In your case you don't need this, because your HTML table is pre-built, before you create the DataTable.