Conditional Eloquent Where Clauses

Hi there,

I didn’t wrote for a while, but today I find a very good Laravel Eloquent feature to work with – Conditional Clauses.

Think of each Eloquent model as a powerful query builder allowing you to fluently query the database table associated with the model.

So, basically you can use every Query Builder methods in your Eloquent model.

You can read about conditional clauses here: https://laravel.com/docs/5.3/queries#conditional-clauses

But why it is important and why you need it?

How often you have a situation when you want to filter your Eloquent results by the URL query parameters. Example:

http://myawesomewebsite.com/products?color=black&size=xl&orderBy=price&sort=desc

After that many of us (not these geeky guys who write some services and etc. to manage this case) just do something like this in our controllers:

$query = Product::newInstance();

if ($request->color) {
    $query->whereColor($request->color);
}

if ($request->size) {
    $query->whereSize($request->size);
}

if ($request->orderBy && $request->sort) {
    $query->orderby($request->orderBy, $request->sort);
}

$products = $query->get();

This way it’s pretty common. One more thing, often you want to have default ordering, so you need to add “else” statement:

$query = Product::newInstance();

if ($request->color) {
    $query->whereColor($request->color);
}

if ($request->size) {
    $query->whereSize($request->size);
}

if ($request->orderBy && $request->sort) {
    $query->orderby($request->orderBy, $request->sort);
} else {
    $query->orderby('price', 'desc');
    // or how I like $query->latest('price'); it's the same, just easier to remember
}

$products = $query->get();

Pretty familiar for you yeah?

But when you start to use Conditional Clauses:

$products = Product::when($request->color, function ($query) use ($request) {
    return $query->whereColor($request->color);
})
->when($request->size, function ($query) use ($request) {
   return $query->whereSize($request->size);
})
->when($request->orderBy && $request->sort, function ($query) use ($request) {
   return $query->orderBy($request->orderBy, $request->sort);
})
->get();
        

Much easier, yeah? Wait, but what about default ordering? You can pass it as third parameter in closure:

$products = Product::when($request->color, function ($query) use ($request) {
    return $query->whereColor($request->color);
})
->when($request->size, function ($query) use ($request) {
   return $query->whereSize($request->size);
})
->when($request->orderBy && $request->sort, function ($query) use ($request) {
   return $query->orderBy($request->orderBy, $request->sort);
}, function ($query) {
   return $query->latest('price');
})
->get();
        

Hope it helps you become a better version of yourself. Have a nice day 😉

Share on FacebookShare on RedditShare on Google+Tweet about this on TwitterShare on LinkedIn
don't waste your time using copy/paste, just use share buttons above
Conditional Eloquent Where Clauses
  • Ernestas Kvedaras

    Well, I don’t know if it is much easier, looks like more code also :)) However, seems like a nice approach in a functional way, that might be very useful in some cases 🙂 Thanks for the tip 🙂

  • hshhhhh


    $request->size && $query->whereSize($request->size);
    ($request->orderBy && $request->sort) && $query->orderby($request->orderBy, $request->sort);
    ($request->orderBy && $request->sort) || $query->orderby('price', 'desc');