For a while many of us used to write conditional queries with “if-else”, something like this:
if (request('filter_by') == 'likes') {
$query->where('likes', '>', request('likes_amount', 0));
}
if (request('filter_by') == 'date') {
$query->orderBy('created_at', request('ordering_rule', 'desc'));
}
What if I told you there’s a (much) better way? Easily readable and more Laravel-ish. Meet when() method.
$query = Author::query();
$query->when(request('filter_by') == 'likes', function ($q) {
return $q->where('likes', '>', request('likes_amount', 0));
});
$query->when(request('filter_by') == 'date', function ($q) {
return $q->orderBy('created_at', request('ordering_rule', 'desc'));
});
$authors = $query->get();
Looks nicer, doesn’t it?
Notice: click here to watch video version of this lesson for free, in my 4-hour online-course Eloquent: Expert Level
Wait, there’s more
In the example below we will filter users only if the role was passed as a request option.
This means – as long as there is no parameter or it’s false/null – you will see all of the users but if it becomes a value, lets say “1” for administrator – you will only see administrators.
$query = User::query();
// From Laravel 5.4 you can pass the same condition value as a parameter
$query->when(request('role', false), function ($q, $role) {
return $q->where('role_id', $role);
});
$authors = $query->get();
It’s not just a prettier way to write the same “IF” but is also a great way to organize conditional queries.
More information about conditional queries: https://laravel.com/docs/5.5/queries#conditional-clauses
Here’s a pull request for the parameter acceptance (not mentioned in official docs): https://github.com/laravel/framework/pull/18419
excellent, thnks!
how about giving current field as parameter on when() method?
example :
$query->when( is_student_active == 1, function ($q) {
return….
});
You don’t have to use parameters. You can use external value inside of a closure that way:
$query->when( is_student_active == 1, function() ($param1, $parma2) {
return….
});
Thank you Thank you Thank you Thank you
$package = Tbl_service::select(array(‘tbl_service.*’,
DB::raw(“IFNULL((select IFNULL(avg(rate),0) from tbl_rating where service_id = tbl_service.service_id group by service_id),0) as avg_rating”),
DB::raw(“IFNULL((select COALESCE(count(rate),0) from tbl_rating where service_id = tbl_service.service_id group by service_id),0) as total_rating”),
DB::raw(“IFNULL((select COALESCE(count(like_to),0) from tbl_service_favorite where like_to = tbl_service.service_id group by service_id),0) as total_like”),
DB::raw(“IFNULL((select CASE WHEN id THEN 1 END from tbl_service_favorite where like_to = tbl_service.service_id and like_by = $userId group by tbl_service.service_id),0) as is_like_by_me”)
))
->Join(‘tbl_seller_level’, function($join) {
$join->on(‘tbl_service.seller_id’, ‘=’, ‘tbl_seller_level.seller_id’);
})
->with(‘Images’, ‘Packages’, ‘User_data’)
->where(‘tbl_service.seller_id’, ‘!=’, $userId)
->orderBy(‘tbl_service.service_id’, ‘DESC’)
->get();
How to use when in this Query