Postgresql ServerSide Global search Bug

Gerald.Rager

Hi i think i found a big Bug in the global search for Postgresql

Editor.php
Line 1044: Fehler If you have Fields with different type such as text,integer,datetime,timestamp. and so on
you became i failure that´s no matching operator is found

Original:

  private function _ssp_filter ( $query, $http )
{
    $that = $this;

    // Global filter
    $fields = $this->_fields;

    // Global search, add a ( ... or ... ) set of filters for each column
    // in the table (not the fields, just the columns submitted)
    if ( $http['search']['value'] ) {
        $query->where( function ($q) use (&$that, &$fields, $http) {
            for ( $i=0 ; $i<count($http['columns']) ; $i++ ) {
                if ( $http['columns'][$i]['searchable'] == 'true' ) {
                    $field = $that->_ssp_field( $http, $i );

                    if ( $field ) {
                        $q->or_where( $field, '%'.$http['search']['value'].'%', 'like' );
                    }
                }
            }
        } );
    }

    // Column filters
    for ( $i=0, $ien=count($http['columns']) ; $i<$ien ; $i++ ) {
        $column = $http['columns'][$i];
        $search = $column['search']['value'];

        if ( $search && $column['searchable'] == 'true' ) {
            $query->where( $this->_ssp_field( $http, $i ), '%'.$search.'%', 'like' );
        }
    }
}

My Solution (not the best i think but it works much better)

    private function _ssp_filter ( $query, $http )
{
    $that = $this;

    // Global filter
    $fields = $this->_fields;

    // Global search, add a ( ... or ... ) set of filters for each column
    // in the table (not the fields, just the columns submitted)
    if ( $http['search']['value'] ) {
        $query->where( function ($q) use (&$that, &$fields, $http) {
            for ( $i=0 ; $i<count($http['columns']) ; $i++ ) {
                if ( $http['columns'][$i]['searchable'] == 'true' ) {
                    $field = $that->_ssp_field( $http, $i );

                                            //Replace WhiteSpace with '%' So a 'word' search is possible
                                            $search_value = str_replace(' ','%',$http['search']['value']);

                                            //$field has to cast as text so you can search over the like operator
                    if ( $field ) {
                        $q->or_where( $field.'::text', '%'.$search_value.'%', 'like' );
                    }
                }
            }
        } );
    }

    // Column filters
    for ( $i=0, $ien=count($http['columns']) ; $i<$ien ; $i++ ) {
        $column = $http['columns'][$i];
        $search = $column['search']['value'];

        if ( $search && $column['searchable'] == 'true' ) {
            $query->where( $this->_ssp_field( $http, $i ), '%'.$search.'%', 'like' );
        }
    }
}

allan

Hi,

Thanks for posting this. Very good point! Postgres isn't quite as liberal as some of the other database engines for how like can be applied. I will certainly look into this - thanks for your suggested fix.

Allan