Brackets Between "and" / "or" Conditions

In Eloquent, there's a pretty dangerous thing that you may encounter if you try to build a more complex query with and and or clauses.

Imagine the scenario where you want to filter the users with email_verified_at and some other condition with or. For example, we're filtering users with email_verified_at not null and where the day of created_at is equal to 4 or 5.

$users = User::whereNotNull('email_verified_at')
    ->whereDay('created_at', 4)
    ->orWhereDay('created_at', 5)
    ->get();
 
foreach ($users as $user) {
    dump($user->id . ': ' . $user->name);
}

In the database, I have three users.

Two of them are with verified email, and all are created on the fourth or fifth day. What should this query return?

Probably two users, because we're querying the email_verified_at, which should be true for two out of three records. But the result is all three records:

Let's check the SQL query.

select * from "users"
    where "email_verified_at" is not null
        and strftime('%d', "created_at") = cast('04' as text)
        or strftime('%d', "created_at") = cast('05' as text)

NOTE: The SQL query syntax is for SQLite.

If you know the theory of SQL, then the order of that sequence would be exactly this: email, and day, and then or day.

Which means the filter query is either "email_verified_at" is not null and strftime('%d', "created_at") = cast('04' as text) or strftime('%d', "created_at") = cast('05' as text).

In this example, even if the first filter for verified email and the fourth day is false, the second filter for the fifth day is true. So, you must add the dates filter in the brackets.

To add date filters in the brackets they must go into another where clause. Then, this additional where will make the date filter one sub statement.

use Illuminate\Database\Eloquent\Builder;
 
$users = User::whereNotNull('email_verified_at')
    ->where(function (Builder $query) { 
        $query->whereDay('created_at', 4)
            ->orWhereDay('created_at', 5);
    }) 
    ->get();

If we check the SQL query now, the date conditions are in the brackets.

select * from "users"
    where "email_verified_at" is not null
        and (strftime('%d', "created_at") = cast('04' as text)
        or strftime('%d', "created_at") = cast('05' as text))

Now the result is correct with only two users because the query is correct:

The point here is that if you have and and or conditions, be careful in which order they execute and whether they return the correct results.

Quote from the official documentation:

You should always group orWhere calls in order to avoid unexpected behavior when global scopes are applied.

Additionally, since Laravel 10.47, if you want to search multiple fields for the same keyword instead of using where() and providing where columns in a closure:

$search = $request->input('search');
 
$users = User::whereNotNull('email_verified_at')
    ->where(function (Builder $query) use ($search) {
        $query->where('name', 'LIKE', "%{$search}%")
            ->orWhere('email', 'LIKE', "%{$search}%");
    })
    ->get();

You can use a whereAny() method and provide columns as an array.

$search = $request->input('search');
 
$users = User::whereNotNull('email_verified_at')
    ->whereAny([
        'name',
        'email'
    ], 'LIKE', "%{$search}%")
    ->get();

Or if you need every column to have the keyword, then the whereAll() method could be used.

$search = $request->input('search');
 
$users = User::whereNotNull('email_verified_at')
    ->whereAll([
        'name',
        'email'
    ], 'LIKE', "%{$search}%")
    ->get();

The whereAll() will make a query with a AND operator, and whereAny() will use the OR operator.

Code for this lesson can be found on GitHub.