Level 70
@trukken I have a suggestion to use when() which will be a clean approach to me!
Check details: https://laravel-school.com/posts/laravel-code-refactoring-using-when-65/
4 likes
Nov 20, 2024
6
Level 1
Filter query result based in get parameters.
Hey guys,
I am working on a filtering functionality, the filter is based on the query parameters coming from the URI. I have a load of parameters (17 to be precise), some of them match the exact column names on the Model I want to filter on.
However I have data that I have to access through intermediate tables and data that don't match the exact column names as well as date ranges.
How would you proceed?
My code looks like this as of right now.
foreach ($params as $field => $value) {
//Check if the field is a date range
if (str_ends_with($field, '_start')) {
$model->where(explode('_start', $field)[0], '>=', $value);
continue;
}
if (str_ends_with($field, '_end')) {
$model->where(explode('_end', $field)[0], '<=', $value);
continue;
}
//Check if the field is a relation
if (str_contains($field, '_id')) {
match ($field) {
'company_id' =>
$model->whereHas('companyHistory', function ($query) use ($value) {
$query->where('invoices.company_history_id', $value);
}),
'project_id' =>
$model->whereHas('projects', function ($query) use ($value) {
$query->whereIn('id', $value);
}),
'division_id' => $model->whereHas('division', function ($query) use ($value) {
$query->whereIn('id', $value);
}),
'user_id' => $model->whereHas('user', function ($query) use ($value) {
$query->whereIn('id', $value);
}),
'label_id' => $model->whereHas('labels', function ($query) use ($value) {
$query->whereIn('label_id', $value);
})
};
continue;
}
//If the value is an array, use whereIn
if (is_array($value)) {
$model->whereIn($field, $value);
continue;
}
$model->where($field, $value);
}
Level 73
I did it like this in one of my projects, and as @tisuchi says, use the when method
class BooksIndexController extends Controller
{
public function __invoke(Request $request)
{
return view('books.index')
->with([
'books' => BookIndexView::query()
->when($request['authors'], function ($query, $authors) {
$query->whereIn('author_id',
$this->numericStringToArray($authors));
})
->when($request['published'], function ($query, $published) {
$query->where('published_year', $published);
})
->when($request['genre'], function ($query, $genre) {
$query->where('genre', $genre);
})
->when($request['format'], function ($query, $format) {
$query->where('format', $format);
})
->when($request['search'], function ($query, $search) {
$query->where('title', 'LIKE', "%$search%")
->orWhere('author_name', 'LIKE', "%$search%")
->orWhere('series', 'LIKE', "%$search%");
})
->orderBy('author_name')
->orderBy('series')
->orderBy('part')
->orderBy('published_year')
->get(),
]);
}
2 likes
Level 75
when is just an if statement in disguise.
1 like
Level 80
@trukken You can use the when method on the query builder like @tisuchi and @tray2 suggest, to conditionally add clauses to your query based on the presence of query string parameters:
$posts = Post::query()
->when($request->query('author'), function (Builder $query, int $author) {
$query->where('author_id', '=', $author);
})
->paginate();
However, if I have more than one or two filters I wish to support, I usually then reach for Spatie’s Laravel Query Builder package: https://github.com/spatie/laravel-query-builder
1 like
Level 2
i cannot understand what are you talking about here "However I have data that I have to access through intermediate tables and data that don't match the exact column names as well as date ranges." can you explain and specifically showing the part of the code involved.
Level 4
Check out here on Laracast Jeremy McPeaks MasterClass API series, he has some info on that specifically on passing an includes flag when you want to use API-Resources that reference other tables/models.
Please or to participate in this conversation.