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.
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();