An Overview of Laravel's Where Clause Variations

Database Eloquent

Committed to the LaraBrain by: wjgilmore (@wjgilmore) at November 3, 2015 6:20 pm

Laravel's Eloquent ORM offers plenty of options when it comes to retrieving database data

Laravel's Eloquent object-relational mapper offers incredible power and flexibility when querying the application database. These capabilities are particularly apparent when retrieving filtered data using the where clause. For instance you're probably familiar with the following where clause use case:

$tips = Tip::where('published', '=', true)->get();

This produces the following MySQL query:

SELECT * FROM tips WHERE published = 1;

Did you know where's default operator is =, meaning you can actually write the same statement like so:

$tips = Tip::where('published', true)->get();

The equals operator is just one of many supported by where. For instance, what if you wanted to retrieve all tips having a star count greater than three? You can use the greater than operator:

$tips = Tip::where('star_count', '>', 3)->get();

Likewise, you can use < (less than), <= (less than or equals), and >= (greater than or equals), to name a few.

But still other variations exist. What if you wanted to retrieve all tips having between 5 and 10 stars? You could chain multiple where methods, or use the whereBetween shortcut:

$tips = Tip::whereBetween('star_count', [5,10])->get();

More exotic approaches are also at your disposal. What if you wanted all tips having less than 5 stars or more than 10 stars? One solution involves using the orWhere method:

$tips = Tip::where('star_count', '<' 5)
         ->orWhere('star_count', '>', 10)->get();

Or save yourself a few keystrokes using the whereNotBetween method:

$tips = Tip::whereNotBetween('star_count', [5,10])->get();

Be sure to check out the Laravel documentation on this topic, as plenty of additional examples are available!