SSP Server-side Word by Word solution

SSP Server-side Word by Word solution

oracastoracast Posts: 1Questions: 0Answers: 0

I have a quick solution that someone may be able to improve as we needed a simple way to have word by word filtering on the server side. I know this is not perfect but perhaps someone can make a few improvements. Essentially we create a multi-dimensional array to support different words and then match against all fields using an OR and then use an AND to join the two. Can anyone see improvements that can be made here to improve performance or deal with potential issues?

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

    if ( isset($request['search']) && $request['search']['value'] != '' ) {
        $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[$x][] = "`".$column['db']."` LIKE ".$binding;
                    }
                }
            }
        }
    }

    // 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;
            }
        }
    }

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

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

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

    if ( $where !== '' ) {
        //remove the first AND
        $where  = preg_replace('/AND/', '', $where, 1);
        $where = 'WHERE '.$where;
    }
    return $where;
}

Replies

  • rclippirclippi Posts: 9Questions: 1Answers: 0

    It works great! Thank you very much!

  • rclippirclippi Posts: 9Questions: 1Answers: 0

    I only changed this:

       if ( $where !== '' ) {
            //remove the first AND
            $where  = preg_replace('/AND/', '', $where, 1);
            $where = 'WHERE '.$where;
        }
        return $where;
    }
    
    

    to this

     if ( $where !== '' ) {
            //remove the first AND
     if ( count( $globalSearch ) ) {
    
          $where  = preg_replace('/AND/', '', $where, 1);
      }
          $where = 'WHERE '.$where;
        }
        return $where;
    }
    
This discussion has been closed.