In Laravel 11, the `where` clause is used to filter query results based on specific conditions. You can use the `where` method in both the Query Builder and Eloquent ORM. Below are examples of how to use the `where` clause in various scenarios.
1. Basic `where` Clause
The simplest form of the `where` clause checks for equality.
Example:
<?php
use App\Models\User;
// Get users where the 'status' is 'active'
$users = User::where('status', 'active')->get();
?>
Explanation:
`where(‘status’, ‘active’)`: Filters records where the `status` column equals `’active’`.
`get()`: Executes the query and retrieves the results.
2. `where` with Operators
You can use comparison operators like `>`, `<`, `>=`, `<=`, `!=`, etc.
Example:
<?php
use App\Models\Product;
// Get products where the price is greater than 100
$products = Product::where('price', '>', 100)->get();
?>
Explanation:
`where(‘price’, ‘>’, 100)`: Filters records where the `price` column is greater than `100`.
3. Multiple `where` Conditions
You can chain multiple `where` conditions to apply multiple filters.
Example:
<?php
use App\Models\Order;
// Get orders where status is 'completed' and total_amount is greater than 500
$orders = Order::where('status', 'completed')
->where('total_amount', '>', 500)
->get();
?>
Explanation:
Multiple `where` conditions are combined using `AND` logic.
4. `orWhere` Clause
Use `orWhere` to apply `OR` logic between conditions.
Example:
<?php
use App\Models\User;
// Get users where status is 'active' OR role is 'admin'
$users = User::where('status', 'active')
->orWhere('role', 'admin')
->get();
?>
Explanation:
– `orWhere(‘role’, ‘admin’)`: Adds an `OR` condition to the query.
5. `where` with Arrays
You can pass an array of conditions to the `where` method.
Example:
<?php use App\Models\User; // Get users where status is 'active' and role is 'user' $users = User::where([ ['status', '=', 'active'], ['role', '=', 'user'], ])->get(); ?>
Explanation:
The array syntax is useful for applying multiple `AND` conditions in a single `where` call.
6. `where` with `NULL` and `NOT NULL`
You can check for `NULL` or `NOT NULL` values.
Example:
<?php
use App\Models\Post;
// Get posts where the 'deleted_at' column is NULL
$posts = Post::whereNull('deleted_at')->get();
// Get posts where the 'deleted_at' column is NOT NULL
$posts = Post::whereNotNull('deleted_at')->get();
?>
7. `where` with `IN` and `NOT IN`
You can filter records where a column’s value is in a list of values.
Example:
<?php
use App\Models\User;
// Get users where the role is either 'admin' or 'editor'
$users = User::whereIn('role', ['admin', 'editor'])->get();
// Get users where the role is neither 'admin' nor 'editor'
$users = User::whereNotIn('role', ['admin', 'editor'])->get();
?>
8. `where` with `BETWEEN`
You can filter records where a column’s value is between two values.
Example:
<?php
use App\Models\Product;
// Get products where the price is between 100 and 500
$products = Product::whereBetween('price', [100, 500])->get();
?>
9. `where` with Dates
You can filter records based on date columns.
Example:
<?php
use App\Models\Order;
// Get orders created on a specific date
$orders = Order::whereDate('created_at', '2023-10-01')->get();
// Get orders created in the last 7 days
$orders = Order::where('created_at', '>=', now()->subDays(7))->get();
?>
10. Dynamic `where` Clauses
You can use dynamic `where` methods for cleaner syntax.
Example:
<?php
use App\Models\User;
// Get users where the 'age' column is greater than 30
$users = User::whereAge(30)->get();
// Get users where the 'status' column is 'active'
$users = User::whereStatus('active')->get();
?>
Explanation:
– Laravel dynamically resolves methods like `whereAge` or `whereStatus` based on the column name.
11. `where` with Subqueries
You can use subqueries in `where` clauses.
Example:
<?php
use App\Models\User;
use App\Models\Order;
// Get users who have placed at least one order
$users = User::whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereColumn('orders.user_id', 'users.id');
})->get();
?>
12. `where` with JSON Columns
If you’re using JSON columns, you can query nested data.
Example:
<?php
use App\Models\User;
// Get users where the 'options->language' is 'en'
$users = User::where('options->language', 'en')->get();
?>
13. `where` with Raw Expressions
For complex conditions, you can use raw SQL expressions.
Example:
<?php
use App\Models\User;
// Get users where the 'email' column matches a pattern
$users = User::whereRaw('email LIKE ?', ['%@example.com'])->get();
?>
14. `where` with Eloquent Relationships
You can use `where` with relationships.
Example:
<?php
use App\Models\User;
// Get users who have at least one post
$users = User::whereHas('posts')->get();
// Get users who have at least one post with the status 'published'
$users = User::whereHas('posts', function ($query) {
$query->where('status', 'published');
})->get();
?>