I am looking to implement a search function, that optionally includes the various fields that I have for my users to search on.
My code thusfar:
$categories = $request->input('category'); // array(int)
$textSearch = $request->input('text'); // string
$query = Product::query();
if(!is_null($categories)){
//$query->selectSub();
foreach($categories as $catId){
$category = ShopCategory::find($catId);
$query = $query->whereBelongsTo($category);//TODO
}
}
if(!is_null($textSearch)){
$query = $query->whereAny([
'name',
'description',
'shortDescription',
],
'like', '%'.$textSearch.'%');
}
$searchResults = $query->paginate(36);
My goal is to reach an SQL statement equivalent to:
SELECT * FROM products WHERE (shop_category_id = ? OR shop_category_id = ?) AND (/* text search LIKE */)
But thusfar, I end up with more of a :
SELECT * FROM products WHERE shop_category_id = ? AND shop_category_id = ? AND (/* text search LIKE */)
Where each of the category id's are AND
ed as part of the overall query instead of OR
ed in a group.
I am aware of or*()
query methods, but it is unclear how to properly group them for the desired effect instead of just OR
ing them along with the text search.
Edit: Based on the accepted answer, I landed on this:
$mainQuery->where(function($query) use ($categories) {
foreach ($categories as $catId) {
$category = ShopCategory::find($catId);
$query->orWhereBelongsTo($category);
}
});
You can wrap some where condition into a group by using the where
function with a callback. Any condition applied on the builder, inside the callback, will be wrap in parathesis. For example:
Model::query()->where('condition1', 'value')->where(function($query) {
// those where will be wrapped into ()
$query->where('condition2', 'value')->orWhere('condition3', 'value');
})
Will give the following query:
SELECT *
FROM models
WHERE conditon1 = 'value'
AND (condition2 = 'value'
OR condition3 = 'value')
And since it doesn't matter if the first where is a OR
or AND
, you can put that into a loop without worring about the first OR
:
$columns = [
'column1',
'column2',
'column3'
]
Model::query()->where('condition1', 'value')->query(function($query) use ($columns) {
foreach($columns as $column) {
$query->orWhere($column, 'value');
}
});
This query is dynamically generated and will return the follow SQL:
SELECT *
FROM models
WHERE condition1 = 'value'
AND (column1 = 'value'
OR column2 = 'value'
OR column3 = 'value')
And since the "column conditions" are wrapped into a group, they won't interfere with the first condition.