Global search not functioning correctly

jagsweb

I am using datatables with serverside processing.

I have individual column filters and a global filter. The global filter will not accept a string of any kind. As soon as you press the space bar to add a second word it returns 'no matching records'. for example, entering '1999' will return results, but '1999 ford' returns no matching records and it shouldnt.

See this example http://jsbin.com/tocafidi/1/edit

see js below:

 function filterGlobal () {
$('#example').DataTable().search(
    $('#global_filter').val()
).draw();
 }

 function filterColumn ( i ) {
$('#example').DataTable().column( i ).search(
    $('#col'+i+'_filter').val()
).draw();
 }

 $(document).ready(function() {
var table = $('#example').DataTable( {
    "processing": true,
    "serverSide": true,
    "ajax": {
      "url":  "http://www.performanceconverters.com/catscript3.php",
    "dataType": "jsonp"
    },
    "columns": [
        { "data": "0"},
        { "data": "1"},
        { "data": "2"},
        { "data": "3"},
        { "data": "5"},
        { "data": "4"},
        { "data": "6"},
        { "data": "7"}
    ],
    "order": [[0,'asc'], [1,'asc'], [2,'asc']],
    "bSort": true,
    "bPaginate": true,
    "bLengthChange": true,
    "bInfo": false,
    "bAutoWidth": false,
    "iCookieDuration": 60*60*24, // 1 day 
 } );

    $('input.global_filter').on( 'keyup click', function () {
    filterGlobal();
 } );

$('input.column_filter').on( 'keyup click', function () {
    filterColumn( $(this).parents('tr').attr('data-column') );
 } );

 } );

Any assistance is much appreciated

Daimian

This is actually not a DataTables problem, since you are using serverSide then the global filter is getting sent to you AJAX page, it is your PHP page that must split the space and search by multiple values.

jagsweb

I am using the default ssp.class.php file from github and I noticed it has entries in it for global search etc...... Am I doing something wrong in my js above that is conflicting with the php??

Daimian

No I believe it is an issue with the php. If you set your serverSide to false then the smart search functions correctly.

jagsweb

Hmmm, thanks for your help Daimian,

My search code from the php is below. I am assuming ther problem is there somewhere but I am not sure.

 static function filter ( $request, $columns, &$bindings )
{
    $globalSearch = array();
    $columnSearch = array();
    $dtColumns = SSP::pluck( $columns, 'dt' );

    if ( isset($request['search']) && $request['search']['value'] != '' ) {
        $str = $request['search']['value'];

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

            if ( $requestColumn['searchable'] == 'true' ) {
                $binding = SSP::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
            }
        }
    }

    // Individual column filtering
    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 = SSP::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
            $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
        }
    }

    // Combine the filters into a single string
    $where = '';

    if ( count( $globalSearch ) ) {
        $where = '('.implode(' OR ', $globalSearch).')';
    }

    if ( count( $columnSearch ) ) {
        $where = $where === '' ?
            implode(' AND ', $columnSearch) :
            $where .' AND '. implode(' AND ', $columnSearch);
    }

    if ( $where !== '' ) {
        $where = 'WHERE '.$where;
    }

    return $where;
 }

Daimian

I checked the ssp.class.php file from github and it doesn't support multi-word filtering.

     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here performance on large
     * databases would be very poor

jagsweb

I didn't realize that is what that statement meant. thanks

Daimian

Well, to be fare I read that and checked the code. The global search string is simply checked as a whole for each column and isn't split by spaces and checked for each column multiple times.

Daimian

BTW it wouldn't be that complicated to add - but like the comment said... it would cause poor performance.

Here you go... this is untested code however.
Replace this...

        $str = $request['search']['value'];
 
        for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
            $requestColumn = $request['columns'][$i];
            $columnIdx = array_search( $requestColumn['data'], $dtColumns );
            $column = $columns[ $columnIdx ];
 
            if ( $requestColumn['searchable'] == 'true' ) {
                $binding = SSP::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
            }
        }

With this...

        $str = explode(' ', $request['search']['value']);

        for ( $x=0, $l=count($str) ; $x<$l ; $x++ ) {
            for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
                $requestColumn = $request['columns'][$i];
                $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                $column = $columns[ $columnIdx ];
     
                if ( $requestColumn['searchable'] == 'true' ) {
                    $binding = SSP::bind( $bindings, '%'.$str[$x].'%', PDO::PARAM_STR );
                    $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
                }
            }
        }

jagsweb

Hmmm, that made the box function but the search returned funky results

You can see here http://jsbin.com/tocafidi/3/edit

2005 starts to display 2005 items
2005 ford switches to display fords of all years
then adding a space to type f-150 and the table resets after the space but before adding the f-150.

Is this what the comment meant by poor performance?

Daimian

Sorry, that was a case of "untested code". I have added a check to help fix the issue.

$str = explode(' ', $request['search']['value']);
 
for ( $x=0, $l=count($str) ; $x<$l ; $x++ ) {
    if ( $str[$x] != '' ) {
        for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
            $requestColumn = $request['columns'][$i];
            $columnIdx = array_search( $requestColumn['data'], $dtColumns );
            $column = $columns[ $columnIdx ];
     
            if ( $requestColumn['searchable'] == 'true' ) {
                $binding = SSP::bind( $bindings, '%'.$str[$x].'%', PDO::PARAM_STR );
                $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
            }
        }
    }
}

jagsweb

I appreciate your help Daimian. It is still producing the same result as above :(

Daimian

Have you updated you online example so I can check it?

jagsweb

Yes i have

Daimian

It appears to be working to me.

spacing is not altering the actual results

jagsweb

It is working but not returning correct results

Entering 2004, it displays all parts that fit 2004

add a space and then add bmw, it should now be showing all parts that fit a 2004 bmw however it now shows all parts that fit bmw regardless of the year

add a space and type 330xi and it still shows all parts for bmw regardless of the year and the 330xi

Daimian

I think your misunderstanding how the global search is supposed to function. Each "word" is matched. In other words if you search "2005 Ford 300zx" it will match any column that has any of those 3 words - not all of them combined.

In order to do that you will need to change more code...

        if ( count( $globalSearch ) ) {
            $where = '('.implode(' OR ', $globalSearch).')';
        }

Needs to be...

        if ( count( $globalSearch ) ) {
            $where = '('.implode(' AND ', $globalSearch).')';
        }

jagsweb

Well I'll be the first to acknowledge I dont understand a lot of the drilled down specifics of this.

I added that code and now global search returns no matching records regardless of what is typed in.

Daimian

You are correct. It is now trying to match every column. Sorry but making this global search work how you desire will take some significant code. I might have time this weekend to code it but wouldn't count on it.

Sorry I am not able to help you more.

jagsweb

No issues, I really appreciate you assistance Daimian.