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
You can also chain the when method to make it even better looking:
$authors = Author::query()
->when(request(‘filter_by’) == ‘likes’, function ($q) {
return $q->where(‘likes’, ‘>’, request(‘likes_amount’, 0));
})
->when(request(‘filter_by’) == ‘date’, function ($q) {
return $q->orderBy(‘created_at’, request(‘ordering_rule’, ‘desc’));
})->get();
nice! just what i needed
Thanks it help me a lot
[…] – Eloquent “When” method: https://laraveldaily.com/less-know-way-conditional-queries/ – Code Styling and PSR-12: […]
I don’t find when nicer and prettier than IF. It’s the same thing said in a different way and there is even more typing. IF statement is far more easy to get and nicer to the eyes.
I agree with you. I don’t see any advantage of using “when”. To me the block with if/else statement is much more readable and cleaner
Thanks for this,
Do you happen to know if when also groups the where conditions? For example if I am doing:
$result = Model::where(‘active’, 1);
if (!empty($something)) {
$result = $result->where(function($query){
$query->where(‘age’, ’30’);
$query->orWhere(‘age’, ’40’);
});
}
$result = $result->get();
Is this identical to doing:
$result = Model::where(‘active’, 1)
->when(!empty($something), function ($query) {
$query->where(‘age’, ’30’);
$query->orWhere(‘age’, ’40’);
})->get();
or do I need another where group inside?
I have tested and answered my own question, I must do the following:
$result = Model::where(‘active’, 1)
->when(!empty($something), function ($query) {
$query->where(function ($query) {
$query->where(‘age’, ’30’);
$query->orWhere(‘age’, ’40’);
});
})->get();