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...