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!

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

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

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

  3. 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.

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here