Filter a table looking into multiple columns

Pyrokwak

Greetings,

I'm building a table using Datatables, and I face an issue I've been unable to fix for now : filter the table using 1 filter field, and looking into 2 data fields with a "OR" condition.
I've searched the forum about this issue, and I've found an example that should have been perfect for me : http://live.datatables.net/jiwosaju/154/edit

But despite having exactly the same code, it just won't work for me.

When I run the code, I can see (thanks to a little "Processing..." window displayed when filter is running) that the filter is trying to apply, but the table remains exactly the same.

I've tried to create single filter (ie, search in only 1 column) through this method and once again, the filter runs, but the data is not filtered.

It is just like if my $.fn.dataTable.ext.search.push is ignored.

For now, I have 6 filtering fields, 5 filtering only on 1 data column and working well with the below code, the last one having to filter on 2 data columns. My code is like following (this is my latest attempt to fix the issue) :

function createFilter(table, columns, id) {
    var len = columns.length;
    var input = $('<input id="' + id + '" class="form-control" type="text"/>');
    if (len === 1) {
        input.on(
            "keyup",
            function () {
                table.columns(columns).search(this.value).draw();
            }
        );
    } else {
        input.on(
            "keyup",
            function () {
                $.fn.dataTable.ext.search.push(
                    function(settings, searchData, index, rowData, counter) {
                        var val = this.value;
                        for (var i = 0; i++ < len;) {
                            if (searchData[columns[i]].indexOf(val) !== -1) {
                                return true;
                            }
                        }
                        return false;
                    }
                );
                table.draw();
            }
        );
    }
    return input;
}

Can anyone help me please ?

Thanks in advance

kthorngren

I can see (thanks to a little "Processing..." window displayed when filter is running)

Sounds like you might have server side processing enabled. if so your server script will need to handle the OR search and return the desired data.

Is this the case?

If not can you provide a link to you page or an example showing the issue?
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Pyrokwak

Sounds like you might have server side processing enabled. if so your server script will need to handle the OR search and return the desired data.

Yes that's the case. If I've understood correctly, that means I need to adapt either the "simple" or "filter" method (I put my bet on the 2nd one), am I right ?

What about my filterCreation function ? Do I need to change it or I can leave it as it is ?

Thanks again for you help

kthorngren

Since you are using server side processing the filterCreation function won't help as it is meant for client side data. Essentially you will need to move that functionality to your server side code. Basically your server code will need to structure your DB query to handle the OR condition.

Kevin

Pyrokwak

Ok

Thanks for all Kevin and have a nice evening

Pyrokwak

Hello all,

Just so you know I've been able to get the result I was looking for. I've binded my filter field with only 1 column ID, then updated the 'filter' function of SSP.php with the following code :

/*
        // Original individual column filtering
        if ( isset( $request['columns'] ) ) {
            for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
                $requestColumn = $request['columns'][$i];
                $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                $column = $columns[$columnIdx];

                $str = $requestColumn['search']['value'];

                if ($requestColumn['searchable'] == 'true' && $str != '') {
                    $binding = self::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
                    $columnSearch[] = '(' . $column['db'] . ' LIKE ' . $binding . ')';
                }
            }
        }
*/

        // Customized individual column filtering
        if ( isset( $request['columns'] ) ) {
            for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
                $requestColumn = $request['columns'][$i];
                $columnIdx = array_search( $requestColumn['data'], $dtColumns );

                $str = $requestColumn['search']['value'];

                if ($requestColumn['searchable'] == 'true' && $str != '') {
                    $binding = self::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
                    $query = '';
                    if ($columnIdx === 4) {
                        $binding2 = self::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
                        $query = '( ';
                        $query = $query . $columns[4]['db'] . ' LIKE ' . $binding;
                        $query = $query . ' OR ';
                        $query = $query . $columns[5]['db'] . ' LIKE ' . $binding2;
                        $query = $query . ' )';
                    } else {
                        $query = $query . $columns[$columnIdx]['db'] . ' LIKE ' . $binding;
                    }
                    $columnSearch[] = $query;
                }
            }
        }

In my case, I only wanted to look into columns #4 and #5 using only 1 filter field, so I binded the field to column #4. I know I have done this code for specific use only and it could be cleaner but it works

The point I was missing is that for every column you search in, it is mandatory that you use a different binding, otherwise, you'll get an Ajax error.

Have a nice day everyone

allan

Nice one - thanks for posting back with your solution.

Allan