Level 104
$query = Model::where(function ($query) use ($active_filters) {
collect($active_filters)
->filter(fn ($filterValues) => count($filterValues))
->each(fn ($values, $key) => $query->orWhereIn($key, $values));
})->get();
Apr 19, 2022
9
Level 1
Generate a dynamic where/orWhere query
Hi, I'm trying to implement a system where the records can be filtered by multiple columns and values
Array example
[
"brand" => [
0 => "scania"
1 => "volvo"
2 => "man"
]
"model" => [
]
"color" => [
1 => "red"
2 => "blue"
]
]
I was expecting to achieve it with something likes this
foreach($active_filters as $k => $v)
{
$where = [];
foreach($v as $z)
{
$where[] = [$k, $z];
}
$products = $products->where($where);
}
But it doesn't work, because this is doing an AND search right? Any idea how can I get this working?
Level 1
@tykus thanks for the answer but I'm afraid it's not working
It seems when there are multiple filters your code is doing an OR search between them.
Given the array I posted before it should do:
WHERE brand = 'scania' OR brand = 'volvo' OR brand = 'man' AND color = 'red' OR color = 'blue'''
Oh nvm, I think I just need to replace orWhereIn for whereIn.
Level 104
@EB942 ah, understood; a simple whereIn will work in that case:
$query = Model::query();
collect($active_filters)
->filter()
->each(fn ($values, $column) => $query->whereIn($column, $values));
$query->get();
Level 1
@tykus yes I jiust figured it out thanks!
Level 1
@tykus sorry, would it be possible to exclude a filter by name? I don't really understand closures :(
Level 104
@EB942 sure, what filter do you want to exclude?
Level 1
@tykus anyone by an arbitrary name, for instance "year".
Level 104
@EB942 ok, so we have a filter operation already - this removes from $active_filters any key/value pairs where the value is falsey (i.e. empty array). So the next operation is to filter/reject the specified keys - if the current key is in the array of excluded keys, we reject that key/value pair
$excludedFilters = ['year'];
$query = Model::query();
collect($active_filters)
->filter()
->reject(fn ($values, $key) => in_array($key, $excludedFilters))
->each(fn ($values, $column) => $query->whereIn($column, $values));
$query->get();
1 like
Level 1
@tykus excellent, tyvm.
Please or to participate in this conversation.