Eloquent::when(): No More if-elses for Conditional Queries

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

Like our articles?
Check out our Laravel online courses!

RELATED ARTICLESMORE FROM AUTHOR

9 COMMENTS

    • 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….
      });

  4. $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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Or, you can subscribe without commenting.