Where Clause Query in Laravel 11 & Laravel 12

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();
?>