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);
CodePudding user response:
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.