Laravel – query part of a integer and boolean value

In my database I have id column which can contain integer, string, integer + string value. like, check this image:

As you can see on the id column I have something like this value:

product id
PPAETRABN018283

So, On the input box (my search form) –

If I enter product I can get the result as there is product id value as id.
If I enter PPAE I can get the result as there is PPAETRABN018283 value as id

BUT

If I enter 182 I can’t get any result, although I use LIKE. Can you tell me why?

And

I have another column called availability which contain true and false as boolean. If I do search with true I can get result but I do search with false I can’t get the result. Can you tell me why?

Here is my code:

$trimmed_array = $request->all();
$whereLike = $whereIn = $where = [];

foreach( $trimmed_array as $k => $v ) {
    if( $k !== 'page' && $k !== 'projectToken' && $k !== 'userId' ) {
        if( !empty( $v ) )  {
            if( is_numeric( $v) ) {
                $whereIn[] = [$k, (int) $v];
            } elseif( trim( strtolower($v)) == 'false' || trim(strtolower($v)) == 'true') {
                $where[] = [$k, $v ];
            } else {
                $whereLike[] = [$k, 'LIKE', '%'. $v .'%' ];
            }
        }
    }            
}

$projectToken = $request->input('projectToken');
$userId = $request->input('userId');

$this->set_connection( $projectToken, $userId );

$get_project_id =  DB::connection('mysql')->table('projects')->where('token', $projectToken )->get(['id_project'])->first();
$collection = 'products_' . $get_project_id->id_project;
$collection = 'products_105';

$search = DB::connection('mongodb')->collection( $collection );

if( count( $whereIn) ) {
    foreach( $whereIn as $array ) {
        $key = $array[0];
        $value = (int) $array[1];
        $search->whereIn($key, [$value]);
    }
} 

if( count( $whereLike) ) {
    $search->where($whereLike);
}

if( count( $where ) ) {
    foreach( $where as $arr ) {
        $key = $arr[0];
        $value = (bool) $arr[1];
        $search->where($key, $value);
    }
}

$search = $search->paginate(100);
return response()->json( [ 'product_value' => $search ], 200);

Answer

In such cases we use LIKE with wrapping %, for example:

$needle = ...; // What to find (User's input).
$query = ...;

$query->where('myColumn', 'like', '%' . $needle . '%');

And if you search in multiple columns, try:

$needle = ...;
$query = ...;

// List search target.
$columns = ['myFirstColumn', 'myOtherColumn'];
$pattern = '%' . $needle . '%';

foreach ($columns as $name) {
    $query = $query->orWhere($name, 'like', $pattern);
}

Note that if where(...) method’s third-parameter is not set, Laravel automatically uses “=” condition-operator in the query by default, while above uses “LIKE” instead.

Advertisement

Answer