Search multiple columns using or

imacoolguyful

Hi, I have a data table with 5 columns. The datatable is pulling entries from my DB through the backend. 3 of the 5 columns are write in fields. I want to be able to make a drop down filter and when it is selected. I search in all 3 of those columns and if there is a write in response in any of those 3 columns, then it should filter those entries. Let's say for example that the columns names are "what's your fav colour", "what's your fav food", "what's your name". If that specific table row has a response in any of those 3 columns, then the table should return those results once filtered with the dropdown. I have managed to code the dropdown and the search , but I am having trouble figuring out how to do an or search and can only find ways to do an and search which doesn't work because I want to return results if they have a write in response in any of those 3 columns and not all of those 3 columns.

I tried this but it does an "AND" search

$('#dataSet', this).on('keyup change', function() {
                if (this.value != "") {
                    table.columns([10, 15, 17]).search('[aeouiAEOUI]+', true, false).draw();
                }
            });

I also tried calling table.column 3 times on the 3 columns followed by a draw but it still does an and search. Any help is appreciated!

kthorngren

Take a look at this thread from yesterday asking a similar question. Let us know if it helps.

Kevin

imacoolguyful

This doesn't really help because it is using multiple drop-downs. I want one dropdown that when selected checks for non empty responses in all 3 columns and if any column has non empty data then it will display that row

kthorngren

The idea is the same. You will need a search plugin to implement OR search logic across multiple columns. If that example doesn't help then please build a test case showing what you have so we can give a more specific answer.

Kevin

imacoolguyful

http://live.datatables.net/cucuciya/1/edit @kthorngren I made a version of what I need in the js fiddle, however. When I try to replicate it in my code, it does not work.
I think it may have to do with this block

initComplete: function() {
                    this.api().columns([10]).every(function() {
                        var select = $('<select id="dataSet" class="form-control"><option value="">choose</option></select>')
                            .appendTo("label[for='dataSet']")
                            .on('change', function() {
                                $("#topTaskTable").DataTable().draw();
                            });
                        select.append('<option value="">Show non empty entries</option>');
                    });
                }

I am just using one filter in my case to clear out all empty results in 3 columns. So I think I need an alternative to api().columns(). Because the API is not being used the same way as in the js fiddle. Do you have any recommendations?

imacoolguyful

Could it be an issue because I am loading the entries through backend instead of front end?

kthorngren

Could it be an issue because I am loading the entries through backend instead of front end?

Are you enabling server side processing (serverSide: true)? If so then the search plugin won't work and you will need to perform the searching with queries in your server side script. If you are just using ajax to load the data it shouldn't behave differently as it si still client side processing.

If you aren't using server side processing its hard to say what the problem might be. You can use ajax loaded data for your test case.

Kevin

imacoolguyful

'ajax': '/topTaskData',
                'serverSide': true,

I am using both those flags, so this means it can only be done from the serverside?

kthorngren

Yes. By definition server side processing means all sorting, searching and paging functions are performed by the server script. Do you have many thousands of rows that require server side processing?

Kevin