search in multiple columns

smo_user

Hi,
It seems than I cannot find how to use the table.columns([0, 1, 2, 3]).search().draw() method properly...
When I tried it, it doesn't gave me the result I expected...

My simplified JS code look like this:

var table = $('#list').DataTable();
$("#list_filter input").on('keyup click', function() {
    table.columns([0, 1, 2, 3, 4, 5, 6, 7, 8, 9]).search($(this).val()).draw();
});

$("#number_search").on('keyup click', function() {
    table.column(10).search($(this).val()).draw();
});

All the searchable are true and columns 9 and 10 are .hidden (display: none !important;).
My purpose here is to use the "usual" search field to search in the 10 1st column and the last one with an extra text field...

Does this search method searches on column 0 AND column 1 AND column 2 AND ... in spite of what I am expecting with OR ??

Thanks for any help!

Armec

You probably need to unbind original searching event handler:

var table = $('#list').DataTable();
$('#list_filter input').off('keyup.DT input.DT');

allan

table.columns([0, 1, 2, 3]).search().draw()

That is an AND search over all four columns. The term you are searching for would have to be present in a row in all four columns to be shown. It does not do an OR search like the global filter does.

You would have to use a custom plug-in filter if you wanted to do such an OR search.

Being able to build group filters like that is something I hope to add in a future version of DataTables.

Allan

smo_user

Ok I came with a partial solution...

Thanks to the advice of Allan I am using this piece of code:

var table = $('#list').DataTable();

function searchBis(str_search) {
  var ret = $($.parseHTML(str_search)).text().toLowerCase();
  return ret.indexOf($('#list_wafers_filter input').val().toLowerCase()) != -1;
}

$.fn.dataTable.ext.search.push(
  function( settings, data, dataIndex ) {
    
    if($('#list_wafers_filter input').val().trim() != '') {
      var ret = data.slice(0,10).find(searchBis);
           // get the columns I want to search in and then search in...
      if(typeof(ret) != 'undefined') {
        return true;
      } else {
        return false;
      }
    } else {
      return true;
    }
    
  }
);

$("#list_filter input").on('keyup click', function() {
    table.draw();
});
 
$("#number_search").on('keyup click', function() {
    table.column(10).search($(this).val()).draw();
});

smo_user

Actually here is a better one... This one takes into account the space in the request, split it and used it to look into all columns and return those with all the requested piece of requests.

This takes into account only more than 1 character ensemble and it is case insensitive.

var table = $('#list').DataTable();
 
var search_for = [''];
var index_search_for = 0;

$.fn.dataTable.ext.search.push(
  function( settings, data, dataIndex ) {
    // only more than 1 character
    if($('#list_wafers_filter input').val().trim().length > 1) {
      // makes it case insensitive and split
      search_for = $('#list_wafers_filter input').val().toLowerCase().trim().split(' ');
      // only more than 1 character
      search_for = search_for.filter(function(filt) {return (filt.length > 1 && typeof(filt) == 'string')});
      
      var ret = true;
      for(index_search_for = 0; index_search_for < search_for.length; index_search_for++) {
        // slice to your convenience here 
        var tmp = data.slice(0,10).find(function(str_search) {
          // makes it case insensitive
          var search_in = $($.parseHTML(str_search)).text().trim().toLowerCase();
          return search_in.indexOf(search_for[index_search_for]) != -1;
        });
        ret = (ret && typeof(tmp) != 'undefined');
        // if ret is false then the output will be false anyway so save time and break the loop by returning false
        if(!ret) {
          return false;
        }
      }
      
      return true;  // if it arrives there that mean all search_for strings have been found in the row
    } else {
      return true;
    }
    
  }
);
 
$("#list_filter input").on('keyup click', function() {
    table.draw();
});
  
$("#number_search").on('keyup click', function() {
    table.column(10).search($(this).val()).draw();
});

allan

Awesome. Thanks for sharing your solution with us!

Allan

okp

Hi,
Thanks for this solution.
But any way to make it more generic by not referencing the input hardcoded in the plugin?
Is it interfering with standard DT search?

I mean I have a DT with generic global search, as well as individual columns search.
I'd like to also have a set of filters (dropdown selects), which should search if the value is present in any of specified columns of the table. (as an OR, not an AND)

$('#filt_A').on('change',function(){
    statusTable.search('');
    statusTable.columns([1,4,7]).search(this.value).draw();
})
$('#filt_B').on('change',function(){
    statusTable.search('');
    statusTable.columns([2,5,8]).search(this.value).draw();
})
$('#filt_C').on('change',function(){
    statusTable.search('');
    statusTable.columns([3,6,9]).search(this.value).draw();
})

Do I have to create different custom search functions for each of my filters?

allan

There currently isn't really a good why of doing what you are looking for since the search extensions apply to all tables (which is going to be addressed in the next major update to DataTables).

To make it generic, what you have to do at the moment is write a wrapper which will give you the input element and add a search filter to the search array based on that input only, ignoring all others. Its a pain - although it is possible.

Allan

okp

Hi allan, thanks for the answer.
However, i'm not sure how you'd do such a thing, though...

allan

Yeah - its a bit of a pig. You'd need a function that would both create the input element and also the function for the filter - effectively binding the two together as a closure.

I'll try to make some time to make an example, but no promises - I've got a bit of a backlog of support issues atm

Allan

okp

Thanks, hoping you'll get some time to make a quick example to understand the logic

okp

Hi Allan, i guess you've been quite busy since then.
Any chance you find a little time to point me into the right direction with a quick example to achieve this _OR _filtering with several select elements?
Thanks!

allan

Here is a little example: http://live.datatables.net/jiwosaju/1/edit .

The first filtering box will search in the first two columns and the second will search in the third column.

There are things that can be done to improve the performance and obviously the layout, but that's the basic idea.

Allan

okp

Thanks Allan, life savior
Worked perfectly for my case.

maddevs

Hi, i want to implement mulitple search option using laravel, but didn't work it properly. Can anyone help me to solve this problem? Search filter is custom design where 2 selection, date ranger and one textfield.

kthorngren

implement mulitple search option using laravel, but didn't work it properly.

What happens? Do you get errors?

What did you try? Can you post a link to your page or a test case replicating the issue? This way we can see what is happening so we can help.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

maddevs

Datatable is not draw.there is no error show.but draw is not working.
There is need to search field in table section?

kthorngren

This part of the last example that Allan posted creates the event:

function createFilter(table, columns) {
  var input = $('<input type="text"/>').on("keyup", function() {
    table.draw();
  });

Sounds like you may have different events. The place to start is put a console.log statement in the event to see if the event is invoked when expected.

Are you using server side processing (serverSide: true)? If so then your server script is responsible for searching. The example Allan provided is only for client side processing.

Again without seeing what you are doing it is impossible for us to help.

Kevin

maddevs

table data comes from database.
i tried some of methods, but didn't get result.
hope you help me in this matter.
Thanks

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

maddevs

I understood this matter. But i work on file with database and it is offline.
How to i provide test case with database connection?

kthorngren

Instead of using Ajax to fetch the data you can use an example of your data as a Javascript variable and use the data option to provide the data to Datatables. Like this example:
https://datatables.net/examples/data_sources/js_array.html

Kevin

maddymathan

maddevs, Please share your table source code I hope it will help me, bro.