Different ways to use where() in Laravel

#webdev #laravel

When we need to filter data in database we use where, Simply in Laravel we use where() also,

But there is different ways to use where..

- It supports:

Various comparison operators (=, >, <, etc.).
Chaining multiple where() calls.
Dynamic where methods (e.g., whereStatus('active')).

Single where:

User::where('email', '!=' ,'example@example.com')->get();

Dynamic where methods and their magic:

  User::whereEmail('example@example.com')->get();

But what if we need more strength conditions

-The following statement will get all user with null value of emails and registered previous 7 days

User::where('email', null)->where('created_at', '<=' ,now()->subDays(7))->get();

Multiple conditions inside one where

User::where([['email', null], ['gender' , 'male']])->get();

The new whereAll and whereAny methods in Laravel's Query Builder (introduced in Laravel 10.47) are powerful tools for constructing complex queries that involve searching multiple columns with AND or OR logic. These methods, along with orWhereAll and orWhereAny, streamline search functionality across multiple fields.

Dynamic Search with whereAny
Imagine a search form where users can input a query string to search across multiple fields.

use Illuminate\Http\Request;

public function search(Request $request)
{
    $query = $request->input('query'); // Get search query from the request

    $users = User::whereAny(
        ['first_name', 'last_name', 'email'],
        'LIKE',
        "%$query%"
    )->get();

    return response()->json($users);
}

whereAll Example
Suppose you are looking for users who have a specific first name, last name, and email domain.


    $filters = $request->only(['first_name', 'last_name', 'email']); // Get filters from request

    $users = User::whereAll(
        ['first_name', 'last_name', 'email'],
        '=',
        [$filters['first_name'], $filters['last_name'], $filters['email']]
    )->get();

Summary:

Laravel's where simplifies database filtering with single or multiple conditions. The new whereAny and whereAll methods streamline queries across multiple columns using OR or AND logic, while orWhereAny and orWhereAll expand existing conditions flexibly. These methods reduce repetitive code, improve readability, and make queries dynamic by integrating user input. They are ideal for advanced filters and multi-field searches, showcasing Laravel's commitment to developer-friendly features.