Search multiple columns using or

Search multiple columns using or

imacoolguyfulimacoolguyful Posts: 17Questions: 5Answers: 0

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!

Answers

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

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

    Kevin

  • imacoolguyfulimacoolguyful Posts: 17Questions: 5Answers: 0

    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

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • imacoolguyfulimacoolguyful Posts: 17Questions: 5Answers: 0

    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?

  • imacoolguyfulimacoolguyful Posts: 17Questions: 5Answers: 0

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

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited July 2021

    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

  • imacoolguyfulimacoolguyful Posts: 17Questions: 5Answers: 0
    'ajax': '/topTaskData',
                    'serverSide': true,
    

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

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

Sign In or Register to comment.