Search - OR vs AND

ricktig65

I've got a simple jQuery DataTable that fetches a JSON object via an AJAX call and builds the table. When I perform a simple text search by entering data in the search box, I seem to get an OR search instead of an AND search. The examples on the DataTables website indicate that an AND search is the default functionality. I suspect that the way the columns are being defined per the DT documentation for building an AJAX-based interface could be causing the issue but I'm unclear as how to use the aoColumns syntax with the AJAX call.

Here is my JavaScript:

 $(document).ready(function()
 {

 var mydatatable = $('#mydatatable').dataTable( 
 {

    "ajax": "php/fetchopentasksbyuseridjson.php",
    "columns": [
        { "data": "pkid" },
        { "data": "shortdescription" },
        { "data": "opendate" },
        { "data": "duedate" },
        { "data": "type" },
        { "data": "priority" },
        { "data": "editclose" },
        { "data": "overdue" }
    ],

    "columnDefs":[
    {
         "targets":[6],
         "bSortable":false
    },
    {
        "targets":[7],
        "visible":false,
        "searchable":true
    }
    ]

});//end dataTable
});//end QJ DOM load

ignignokt

If the query used AND instead of OR, then that means the search term would have to match every single column instead of just one of them. I doubt that is what you'd want. Is there a certain problem you are experiencing or just curious what the difference is?

ricktig65

Hello - I was hoping to search on multiple 'and' values (words) in a single column. For example, I've got two rows with an id, description, and priority:

pkId Description Priority
1 Task 1 High
2 Task 2 Medium

If I enter 'task 1' into the search box, I get both rows 1 and 2. However, if I enter 'High' I get row 1 as expected. I'm making an assumption that the search can filter each 'word' in the column, but that doesn't appear to be true. It looks as if the filtering looks at the cell as a whole and compares it to other cells as a whole to do the 'AND'.

ignignokt

Do both rows have the term "Task 1" in them? Do you mean you want to search for a row where two different results must both exist? Them being both "Task 1" and "High"?

It would help visualize if you also had an example of your data.

ricktig65

Yes. I'd like to be able to enter multiple words in the search box and have DT find AND matches across all searchable columns.

ignignokt

What would be the separator for terms? Would you want it to be a space? So "Task 1 High" would search for "Task" AND "1" AND "High"? I'm assuming you are using the ssp.class.php file?

ricktig65

Yes.

allan

I'm assuming you are using the ssp.class.php file?

It doesn't look like server-side processing is being used as there is no mention of the serverSide parameter. @ricktig65 can you confirm this. It would be very helpful if you could link to the page in question so we can debug what is going on. The client-side filtering is certainly AND based. You would need to use regex or a custom filter to have OR based filtering.

Allan

ricktig65

I added a filter to each column which allows for the AND functionality I'm looking for. Given that my dataset is quite small (<500 records), I'm not sure if the serverSide parameter would be appropriate?

allan

I would suggest probably not. Typically you should only need it for 50'000+ rows (you can start wanting it around 20'000 depending on various factors).

Allan