Laravel is an open-source PHP framework designed specifically for web development. Included in the Laravel framework is Laravel Eloquent‚ an object-relational mapper (ORM) which makes interacting‚ managing and querying databases much more straightforward.
Database queries are made much more fluent across a range of database systems through Laravel’s Database Query Builder. Using Query Builder‚ Laravel developers can perform and access many typical database actions much more quickly – without writing complicated SQL queries.
Query Builder can also help keep systems secure against common database threats. For example‚ Laravel uses PDO parameter binding to help avert SQL injection attacks against your application.
When using Query Builder‚ developers have several commands and clauses at their disposal to help achieve their desired results. One of those clauses is the Where clause.
In this blog‚ we’ll explore the following:
- What the Laravel Where clause is‚ and how to use it
- Examples of basic Where clauses and their uses
- Examples of more advanced and specific Where clause use cases
- How to use many Where clauses together to achieve granular results.
Laravel ‘Where’ clause – what does it do and how to use it
One of the reasons that we love Laravel is that it gives developers several powerful possibilities to query their databases. One of those is the Where clause.
The clue is in the name: Where clauses add a condition to query statements that specify certain conditions. The basic where call requires the developer to include three arguments:
- The name of the database table column that you wish to query.
- An operator function‚ such as equals‚ greater‚ or less than.
- A value that the query will measure against the database column’s contents.
As with most code tutorials‚ it’s often helpful to give an example. Let’s say we want to query the database table to find movies with a runtime of exactly 120 minutes.
$movies= DB::table(‘movies’)
->where(‘runtime’‚ ‘=’‚ 120)
->get();
When using an equals operator function‚ you may also skip the second argument‚ and Laravel will assume the rest:
$movies= DB::table(‘movies’)
->where(‘runtime’‚ 120)
->get();
If you wanted to find movies with a runtime of more than 120 minutes‚ you would simply change the operator function appropriately:
$movies= DB::table(‘movies’)
->where(‘runtime’‚ ‘>’‚ 120)
->get();
If you had a second specification‚ for example‚ movies longer than 120 minutes and made in the year 2021‚ you can add a second where clause:
$movies= DB::table(‘movies’)
->where(‘runtime’‚ ‘>’‚ 120)
->where(‘year’‚ ‘2021’)
->get();
While these are the basic uses of the where clause‚ they are still very powerful ways to fetch specified information from your database tables. But there are many other possible where clauses to help you find the exact information that you need.
Other ‘where’ queries in Laravel
The following are examples of when‚ why and how you might use other where queries in Laravel.
Array of clauses
While the above example of stacking multiple where clauses is functional‚ it could become untidy with more than a couple. One way around this problem – of creating AND clauses – is to pass an array of clauses:
$movies= DB::table(‘movies’)
->where([
[‘runtime’‚ ‘>’‚ 120]‚
[‘year’‚ ‘2021’]‚
])->get();
Or where
The above example is fine when you want the matching records to match every single clause. But what about when they can match just one or more? That’s where the query builder’s orWhere function helps.
The orWhere function accepts the same arguments as our above examples – but will return any movie that is either more than 120 minutes long‚ or was made in 2021:
$movies= DB::table(‘movies’)
->where(‘runtime’‚ ‘>’‚ 120)
->orWhere(‘year’‚ ‘2021’)
->get();
Where in
If you want to test that your table column values match one of many values stored in an array‚ you can use the whereIn method. Say we wanted to return any film with a runtime of 119‚ 120 or 121 minutes:
$movies= DB::table(‘movies’)
->whereIn(‘runtime’‚ [119‚ 120‚ 121])
->get();
What about if you wanted to ignore any movies that had those runtimes? Then you would use whereNotIn:
$movies= DB::table(‘movies’)
->whereNotIn(‘runtime’‚ [119‚ 120‚ 121])
->get();
By storing and managing your arrays‚ it’s possible to create very powerful functions for fetching ultra-specific results.
Where not
So‚ we’ve seen how to use the where clause to see the information that matches. But what about if we want to see everything that does not match? For example‚ you may want to fetch your list of movies‚ ignoring every film made in the year 2020. Then‚ you could use whereNot:
$movies= DB::table(‘movies’)
->whereNot (‘year’‚ ‘2020’)
->get();
You may combine whereNot clauses to create multiple criteria for your results. It’s also possible to achieve this result by passing a standard where clause with the not equal to operator ‘!=’.
JSON where
With Laravel‚ you can query JSON column types on specific database types‚ including versions of MySQL‚ SQL Server and SQLite. You would then pass the whereJsonContains clause to specify the results you need.
Where between
Looking for a range of results? Then whereBetween can help. Let’s say we wanted to query our movie database table for movies between 115 and 125 minutes long‚ we would use whereBetween to verify results between those two values:
$movies= DB::table(‘movies’)
->whereBetween(‘runtime’‚ [115‚ 125])
->get();
Likewise‚ if you wanted movies shorter than 115 minutes or longer than 125 minutes‚ you could reverse the above query using whereNotBetween:
$movies = DB::table(‘movies’)
->whereNotBetween(‘runtime’‚ [115‚ 125])
->get();
Grouping ‘where’ queries in Laravel
Often in database query methods‚ you’ll need to check multiple criteria. This might be simple‚ such as a multiple AND query where you can simply stack your required clauses. However‚ things can get pretty complicated.
Sometimes‚ you’ll need to group several where clauses – often with not‚ or‚ and between clauses included. You’ll need to use parentheses to group these correctly and avoid errors.
Let’s say you’re looking for movies made in 2021‚ that are longer than 120 minutes or have an IMDB rating above 7. Pass the multiple criteria in correct parentheses‚ using a function to specify the orWhere clause:
$movies= DB::table(‘movies’)
->where(‘year’‚ ‘=’‚ ‘2021’)
->where(function ($query) {
$query->where(‘runtime’‚ ‘>’‚ 120)
->orWhere(‘imdb_rating’‚ ‘>’‚ 7);
})
->get();
While this where clause might look pretty complicated‚ it is merely scratching the surface of Eloquent’s powers and possibilities.
Summary
Laravel Eloquent is a robust PHP framework for web application development. Using Eloquent ORM’s many tools‚ such as database query builder‚ allows developers to find the exact information their users need. Using clauses like Where‚ your applications can serve users quickly‚ efficiently and correctly. You can read more web application development insights on our blog.
