How to search whether a value falls into a range defined by two columns?

TrilceAC

Hi,

Suppose that a table has two columns that represent the lower and the upper bound of a range or an interval. What I'd like to achieve is a way to make the search to look for not any of the limits, which is what the columns have, but rather to look for those columns that the given value falls into the interval defined. This would have to take into account not just the cell, but the row.

For example, suppose that I have a table showing min and max of prices. When typing a value, what I want to see are the rows which min_prize <= search_value <= max_prize.

I have found some answers to the opposite problem, which is to search for the rows which one of its columns satisfy a given range of values. I'm looking for a way to do the opposite problem: the range is defined by two table columns, and using the search input, I want to look whether the given value falls into the range.

Thank you for your help,
Carlos

kthorngren

I would try creating a search plugin. In the plugin you can do the comparison of one or more columns.

Kevin

TrilceAC

Hi kthorngren,

Thanks for the help. I have written the function, but I only get datatables calling it only when I delete the whole text of the search. This is my function:

$.fn.dataTable.ext.search.push(
  function(settings, data, dataIndex, dataSource, loopIndex) {
    const min = parseInt(dataSource['min_value'], 10);
    const max = parseInt(dataSource['max_value'], 10);
    const val = parseInt($('.dataTables_filter input').val(), 10)
    if (  (isNaN(min) && isNaN(max)) ||
          (isNaN(min) && val <= max) ||
          (min <= val && isNaN(max)) ||
          (min <= val && val <= max) ||
          (isNaN(val))
        )
    {
      return true;
    }
    return false;
  }
);

The table uses ajax to retrieve the data:

$(function() {
  const table = $('table#mc-dsids-with-evgen-dups').DataTable({
    ajax: {
      url: Flask.url_for('panda.evgen_dups'),
      dataSrc: ''
    },
    columns: [
      {
        data: 'min_value',
      },
      {
        data: 'max_value'
      },
    ]
  });

  /* I have tried without this, or with any of the two lines below: */
  /* But none of the three combinations makes the search to be called. */
  $('.dataTables_filter input').keyup(function() {
    table.search(this.value, false, false).draw();
    table.draw();
  });
});

How can I get the function be called when something is written on the input search?

kthorngren

Good try but I would recommend not using the global search for the range search. I would create a separate input for the range search. I put together a quick example for you:
http://live.datatables.net/sixujequ/1/edit

A good test is to type 27 into the Range search input. Hopefully the comments explain everything.

Kevin

TrilceAC

Since in my real example the table just contains ranges and not anything else, not even a column with names for the rows, I was wondering whether it is possible to just use the global input search and not pollute the page with a new inputs. No other search has sense in my context.

It seems complicated or problematic to do what I wanted. Your solution just works. I will try to just replace the global input search with the custom one, and if this doesn't work, I'll just add a new field.

Thank you!
Carlos

TrilceAC

Kevin,

dom saved my day. What I have done is use dom to replace the default search with a div on its place, and later, by means of JQuery, to add a custom input field. The rest is just what you do with some minor changes.

// A variable to save whatever is written on the filter. Improve its name!
let val = '';

/* Custom range filtering */
/* See https://datatables.net/manual/plug-ins/search */
$.fn.dataTableExt.afnFiltering.push(
  function(settings, data, dataIndex, dataSource, loopIndex) {
    if (val === '') return true;
    const min = parseInt(dataSource['min_value'], 10);
    const max = parseInt(dataSource['max_value'], 10);
    if (  (isNaN(min) && isNaN(max)) ||
          (isNaN(min) && val <= max) ||
          (min <= val && isNaN(max)) ||
          (min <= val && val <= max)
        )
    {
      return true;
    }
    return false;
  }
);

$(function() {
  const table = $('table#mc-dsids-with-evgen-dups').DataTable({
    ajax: {
      url: Flask.url_for('panda.evgen_dups'),
      dataSrc: ''
    },
    columns: [
      {
        data: 'min_value',
      },
      {
        data: 'max_value'
      },
    ],
    // Customize the layout to be able to append our custom range filter.
    // Here I'm using Foundation. Change this to whatever style you use.
    dom: "<'row'<'small-6 columns'l><'#in-range-search.small-6 columns'>r>"+
          "t"+
          "<'row'<'small-6 columns'i><'small-6 columns'p>>",
  });

  // Append the custom input where the default one was.
  $(`<div id="mc-dsids-with-evgen-dups_filter" class="dataTables_filter">
  <label>Filter range
  <input id="input-range-filter" type="search" aria-controls="mc-dsids-with-evgen-dups">
  </label>
  </div>`).appendTo('#in-range-search');

  // Attach the event handled to the input.
  $('#input-range-filter').keyup(function(event) {
    val = $(this).val();
    table.draw();
  })
});

Thanks for your help.

kthorngren

I was wondering whether it is possible to just use the global input search and not pollute the page with a new inputs.

Yes, you can hijack the default search and use it the way you want. Here is the updated example:
http://live.datatables.net/xuwequgu/1/edit

Note the use of .off() to turn off the event Datatables created.

Glad you got it working.

Kevin

TrilceAC

Just what I was looking for. Thanks!

Carlos