Jquery Datatables custom column searching

Question

I've been researching into this for a while and useful information is drying up without a solution.

I'm using Datatables, and in certain columns I am displaying a Chosen multi-select box to allow the user to edit their data after it is added by continuing to make selections.

I've got per-column search boxes working using initComplete, and they do filter the data in the column, but not in an acceptable way; they are matching on the options' text, but also on the select element's classes, and even on all the unselected options, such that it is useless.

...,
initComplete: function () {
            // Apply the search
            this.api().columns().every(function () {
                var that = this;

                $('input', this.footer()).on('keyup change clear', function () {
                    if (that.search() !== this.value) {
                        that
                            .search(this.value)
                            .draw();
                    }
                });
            });
        }

I would like this to only match on the text of the selected options of the select boxes. Writing the function to get the array of text strings of a select box in a given row is easily done by me, but I'm struggling to find out where I can hook in such a custom function to the Datatables flow.

The Datatables documentation mentions that you can define a column filter function for orthogonal data, but gives no example of this and I'm struggling to find any online either. My attempt at this was adding the following to the DataTable initialisation, but it's not getting called:

...,
"columns": [          
            {
                filter: function (a,b,c) {
                    console.log('called from column filter');
                    console.log(arguments)
                    return false;
                }
            },
            {
                filter: function (a,b,c) {
                    console.log('called to column filter');
                    console.log(arguments)                 
                    return false;
                }
            },
            null  
        ]

Thank you for your time.

Update

So I've found that I can push a function onto $.fn.dataTable.ext.search.push(...) which seems like it can do what I need. Following down this path, I've found that the column html that is passed in the parameters does not include the Chosen generated elements, which I need, just the pre-chosen .... I've tried using table.rows.invalidate.draw but this just removes the Chosen elements from the DOM instead of fixing this problem by making Datatables pass in the expected html data.

Example of the actual row html that I would expect to be passed in:

<td>
<select class="myclasses form-control" multiple="" style="display: none;">                    
    <option value="...">Bahamas</option>
    ...
</select>
<div class="chosen-container chosen-container-multi" title="" style="width: 437px;">
    <ul class="chosen-choices">
        <li class="search-choice">
            <span>Greece</span>
            <a class="search-choice-close" data-option-array-index="4"></a>
        </li>
        <li class="search-choice">
            <span>Belgium</span>
            <a class="search-choice-close" data-option-array-index="6"></a>
        </li>
        <li class="search-choice">
            <span>France</span>
            <a class="search-choice-close" data-option-array-index="7"></a></li><li class="search-field">
            <input class="chosen-search-input" type="text" autocomplete="off" value="Select some options" style="width: 25px;">
        </li>
    </ul>
    <div class="chosen-drop">
      <ul class="chosen-results"></ul>
    </div>
</div>
</td>

The actual parameter data in the search call, which appears to be in the pre-chosen state. This is why I expected .invalidate() to fix this...

<select class="myclasses form-control" multiple>                    
    <option value="...">Bahamas</option>
</select>

I'm now looking into generating the chosen html before I add the select html to the table. This may still have problems if a user makes changes to the select box options after they have been added to the table, but I'll see when I get there.

andrewJames · Accepted Answer · 2021-04-14 17:35:13Z

I think your approach of using $.fn.dataTable.ext.search is the right way to go, because unlike the search() API function, this gives you the ability to access the DOM, where your user selections can be found (i.e. outside of the DataTables object).

However, because $.fn.dataTable.ext.search is not restricted to only one column, there is some extra work needed to ensure your combined searching across different columns still works correctly.

This is a somewhat basic example, but it shows one approach.

The only thing each input event does is to trigger a table re-draw. This re-draw is what, in turn, triggers the global search function:

    initComplete: function () {
      var api = this.api();
      api.columns().each(function () {
        $('input', this.footer()).on('keyup change clear', function () {
          api.draw();
        });
      });
    }

The search function is as follows. The code could be streamlined - but it shows the basic approach:

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

      var fromFilter = $( '#in_0' ).val();
      var toFilter = $( '#in_1' ).val();

      var trNode = table.row( [index] ).node();

      var fromTdNode = $( "td:nth-child(1)", trNode );
      var fromSelectedValNodes = $( "select option:selected", fromTdNode );   
      var fromSelections = '';
      fromSelectedValNodes.each(function() {
        fromSelections += ( $( this ).val() + ', ' ); 
      });

      var toTdNode = $( "td:nth-child(2)", trNode );
      var toSelectedValNodes = $( "select option:selected", toTdNode );   
      var toSelections = '';
      toSelectedValNodes.each(function() {
        toSelections += ( $( this ).val() + ', ' ); 
      });
      
      if ( fromSelections.trim().toLowerCase().includes( fromFilter.trim().toLowerCase() ) &&
              toSelections.trim().toLowerCase().includes( toFilter.trim().toLowerCase() ) ) {
        return true;
      } else {
          return false;
      }
    }
  );

We retrieve both of the user-provided search values, and save them in fromFilter and toFilter.

Then for each of our two columns containing multi-selects, we retrieve the text of the selected values. We use the DataTables API to get the related node() objects from each DataTables row.

Then we use jQuery selectors to collect the selected multi-select values into a string.

Finally we see if the search term is contained in the string we built. And we do this for each of the two columns separately, so that the overall table is filtered consistently.

This implementation assumes there are only 2 columns containing these Chosen multi-selects - and also that they are the first 2 columns.

I also remove the default global search input box from the DataTable using the dom option, since this would also cause $.fn.dataTable.ext.search to be invoked.

Final note: the way I am loading my data for my small demo appears to be different from your data loading - so, in case that may need changing, here is my full stand-alone demo:

<!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">

  <!-- chosen select library -->
  <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/chosen/1.8.7/chosen.min.css">
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/chosen/1.8.7/chosen.jquery.min.js"></script>


</head>

<body>

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

    <table id="example" class="display dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>From Country</th><th>To Country</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td></td><td></td>
            </tr>
            <tr>
                <td></td><td></td>
            </tr>
            <tr>
                <td></td><td></td>
            </tr>
            <tr>
                <td></td><td></td>
            </tr>
            <tr>
                <td></td><td></td>
            </tr>
        </tbody>
        <tfoot>
            <tr>
                <th></th><th></th>
            </tr>
        </tfoot>
    </table>

</div>

<script>

$(document).ready(function() {

  $('#example tfoot th').each( function ( idx ) {
    var title = $(this).text();
    $(this).html( '<input id="in_' + idx + '" type="text" placeholder="Search ' + title + '"/>' );
  } );

  var table = $('#example').DataTable( {

    dom: 'lrtip', // removed "f" for "filter".

    columnDefs: [
      {
        targets: [ 0, 1 ],
        render: function ( data, type, row ) {
          var select = $('<select multiple class="chosen-select"><option value=""></option></select>');
          select.append( '<option value="Argentina">Argentina</option>' );
          select.append( '<option value="Brazil">Brazil</option>' );
          select.append( '<option value="Cuba">Cuba</option>' );
          select.append( '<option value="Denmark">Denmark</option>' );
          select.append( '<option value="Egypt">Egypt</option>' );
          return select[0].outerHTML;
        }        
      }
    ],

    initComplete: function () {
      var api = this.api();
      api.columns().each(function () {
        $('input', this.footer()).on('keyup change clear', function () {
          api.draw();
        });
      });
    }
  } );

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

      var fromFilter = $( '#in_0' ).val();
      var toFilter = $( '#in_1' ).val();

      var trNode = table.row( [index] ).node();

      var fromTdNode = $( "td:nth-child(1)", trNode );
      var fromSelectedValNodes = $( "select option:selected", fromTdNode );   
      var fromSelections = '';
      fromSelectedValNodes.each(function() {
        fromSelections += ( $( this ).val() + ', ' ); 
      });

      var toTdNode = $( "td:nth-child(2)", trNode );
      var toSelectedValNodes = $( "select option:selected", toTdNode );   
      var toSelections = '';
      toSelectedValNodes.each(function() {
        toSelections += ( $( this ).val() + ', ' ); 
      });
      
      if ( fromSelections.trim().toLowerCase().includes( fromFilter.trim().toLowerCase() ) &&
              toSelections.trim().toLowerCase().includes( toFilter.trim().toLowerCase() ) ) {
        return true;
      } else {
          return false;
      }
    }
  );

  $(".chosen-select").chosen({
    width: "75%"
  });


} );

</script>

</body>
</html>

Collectives™ on Stack Overflow

Jquery Datatables custom column searching

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related