jQuery DataTables filter column with comparison operators

Question

I've been using the DataTables jQuery plugin with the filter plug in, and it is awesome. However, I was wondering if it is possible to filter table columns by row using a comparison operator (e.g. '<' '>' or '<>') before a value in the filter input at the bottom of the table.

http://www.datatables.net/plug-ins/filtering#functions

There is way to filter by range using input fields that accept a max and min value. However, I'd like to forgo adding two additional input fields and somehow parse the input of this column.

The row i want to filter is populated with only integers (age) values.

some examples of desire behaviour:

input      results
< 20       less than than 20
> 20       greater than 20
20 - 80    between 20 and 80
<> 20      not 20

Anyone have experience modifying the behavior of the filter plugin to achieve this behavior? Thanks.

edit:

I'd like to be able to directly type in the comparison operator into these input boxes. If an operator is detected it will filter based on the operator. If no filter operator is detected, I'd like it to filter normally.

the html for the filter input looks like this:

<tfoot>
    <tr>
        ...
        <th class=" ui-state-default">
            <input type="text" class="search_init" value="Age" name="search_age">
        </th>
        <th class=" ui-state-default">
            <input type="text" class="search_init" value="PD Status" name="search_age_of_onset">
        </th>
        ...
    </tr>
</tfoot>

Thanks!

Answer 1

The 3 steps needed should be:

• create the UI
• write a filtering function
• setup events to redraw the DataTable when the UI changes

First create the UI. For me, the easiest way to capture the user's intent is to use a select box where the user can pick which comparison operator he wants to use:

<select id="filter_comparator">
  <option value="eq">=</option>
  <option value="gt">&gt;=</option>
  <option value="lt">&lt;=</option>
  <option value="ne">!=</option>
</select>
<input type="text" id="filter_value">

Now, you need to push a function into the set of filters. The function simply grabs the specified comparison operator and uses it to compare the row data with the value entered. It should return true if a row should stay visible and return false if it should go away based on the filter. If the user doesn't enter a valid number it returns true. Change the column_index to the appropriate value:

$.fn.dataTableExt.afnFiltering.push(
  function( oSettings, aData, iDataIndex ) {
    var column_index = 2; //3rd column
    var comparator = $('#filter_comparator').val();
    var value = $('#filter_value').val();

    if (value.length > 0 && !isNaN(parseInt(value, 10))) {

      value = parseInt(value, 10);
      var row_data = parseInt(aData[column_index], 10);

      switch (comparator) {
        case 'eq':
          return row_data == value ? true : false;
          break;
        case 'gt':
          return row_data >= value ? true : false;
          break;
        case 'lt':
          return row_data <= value ? true : false;
          break;
        case 'ne':
          return row_data != value ? true : false;
          break;
      }

    }

    return true;
  }
);

Finally, at the point where you create your DataTable, setup events on your UI elements to redraw the table when the user makes changes:

$(document).ready(function() {
    var oTable = $('#example').dataTable();
    /* Add event listeners to the filtering inputs */
    $('#filter_comparator').change( function() { oTable.fnDraw(); } );
    $('#filter_value').keyup( function() { oTable.fnDraw(); } );
});

---

ON THE OTHER HAND, if you would like the user to type the comparison operator instead of selecting it then you will need to parse their input. If you have a simple text box:

<input type="text" id="filter">

Then you can parse the input in a filter function like this:

$.fn.dataTableExt.afnFiltering.push(
    function( oSettings, aData, iDataIndex ) {
        var filter = $('#filter').val().replace(/\s*/g, '');
        var row_data = aData[3] == "-" ? 0 : aData[3]*1;

        if (filter.match(/^<\d+$/)) {
            var num = filter.match(/\d+/);
            return row_data < num ? true : false;
        }
        else if (filter.match(/^>\d+$/)) {
            var num = filter.match(/\d+/);
            return row_data > num ? true : false;
        }
        else if (filter.match(/^<>\d+$/)) {
            var num = filter.match(/\d+/);
            return row_data != num ? true : false;
        }
        else if (filter.match(/^\d+$/)) {
            var num = filter.match(/\d+/);
            return row_data == num ? true : false;
        }
        else if (filter.match(/^\d+-\d+$/)) {
            var num1 = filter.match(/^\d+/);
            var num2 = filter.match(/\d+$/);
            return (row_data >= num1 && row_data <= num2) ? true : false;
        }
        return true;
    }
);

and a document ready:

$(document).ready(function() {
    var oTable = $('#example').dataTable();
    /* Add event listeners to the filtering inputs */
    $('#filter').keyup( function() { oTable.fnDraw(); } );
});

This filter only works on positive integers. Decimals and negative number support would require more work. You could also extend the function to add >= and <= support, or just make those the default behavior for > and < depending on your user expectations.

I've also once again attached the event listener to a free floating input text box. I've tried this with a basic DataTable and it works. You would need to attach the behavior to those text boxes at the bottom of your columns, but I'm not sure how you got them there - I've never done that with a DataTable.