Laravel Eloquent query for a column that is X or NULL
orWhere returns rows that ignore my other where conditionsShort answer
Wrap the pair in a closure: where(function ($q) { $q->where('col', $x)->orWhereNull('col'); }). The closure adds parentheses in SQL, so other filters still apply to both branches. Do not compare with = NULL, because that never matches any row in MySQL.
Fix it fast
1Find out which cause you have
Print the SQL Laravel builds. The place where 'or' sits relative to the other conditions shows the problem at once.
dd(Post::where('user_id', 5)->where('status', 'draft')->orWhereNull('status')->toRawSql());2Match what you see, then jump to its fix
- 1
The SQL reads a and b or c is null, without parentheses, and returns other users' rowsorWhereNull is not grouped, so it escapes the other filtersFix - 2
A variable that is sometimes null is passed into where, and rows with NULL never come backThe value is compared with = NULLFix - 3
Soft-deleted or other tenants' rows appear only when the orWhereNull line is presentA global scope or relation constraint is bypassed by the loose ORFix
- 1
What does the error look like?
orWhere returns rows that ignore my other where conditionswhere('column', null) returns no rows
Cause 1 of 3
orWhereNull is not grouped, so it escapes the other filters
1Check
SQL runs AND before OR. In the first-step query, the OR branch matches every row with a NULL status, whatever the user_id.
echo Post::where('user_id', 5)->where('status', 'draft')->orWhereNull('status')->toRawSql();2Fix
Put the X-or-NULL pair in a closure. Laravel documents grouping orWhere calls in parentheses as the safe habit.
$posts = Post::where('user_id', 5)
->where(function ($query) {
$query->where('status', 'draft')
->orWhereNull('status');
})
->get();3Verify
The SQL now has parentheses around the pair.
php artisan tinker --execute="echo App\Models\Post::where('user_id',5)->where(fn(\$q)=>\$q->where('status','draft')->orWhereNull('status'))->toRawSql();"Confirmed by laravel.com
Cause 2 of 3
The value is compared with = NULL
1Check
In MySQL, any comparison with =, < or <> against NULL gives NULL, never true. Only IS NULL and IS NOT NULL work.
mysql -e "SELECT 1 = NULL, 1 IS NULL;"2Fix
Choose the method by the value, not one where call.
$query = Post::query();
$status = request('status');
$status === null
? $query->whereNull('status')
: $query->where(fn ($q) => $q->where('status', $status)->orWhereNull('status'));3Verify
Run both branches and check the printed SQL for IS NULL.
dd($query->toRawSql());Confirmed by dev.mysql.com
Cause 3 of 3
A global scope or relation constraint is bypassed by the loose OR
1Check
Global scopes and relation constraints are added as AND conditions. An ungrouped orWhere can let rows through them.
dd(Post::where('status', 'draft')->orWhereNull('status')->toRawSql());2Fix
Group the pair in a closure, as above, so the scope condition applies to both sides.
Post::where(fn ($q) => $q->where('status', 'draft')->orWhereNull('status'))->get();3Verify
The SQL puts the scope condition outside the parentheses.
dd(Post::where(fn ($q) => $q->where('status','draft')->orWhereNull('status'))->toRawSql());Confirmed by laravel.com
How do I stop it coming back?
- Wrap every orWhere in a closure, even when you have no other filter yet.
- Print toRawSql() for any query that mixes where and orWhere before you ship it.
- Never compare a column to a variable that can be null without handling the null case.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- Spotless LaundryAn on-demand laundry app: Flutter on a Laravel back end, with Firebase for live updates.
- EZ EstimaterConstruction quoting and job tracking for contractors, on a Laravel and Firebase stack.
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
Related fixes
Back end
Laravel insert fails with 'Field user_id doesn't have a default value'
The insert works when the column is removed or made nullable
Error 1364 means Laravel sent an INSERT without a value for a NOT NULL column that has no default, and MySQL strict mode refuses it.
3 causes, each with a fixBack end
Laravel migration cannot drop a column that has a foreign key
Error on rename of '%s' to '%s' (errno: %d - %s)
MySQL refuses to drop a column while a foreign key constraint still uses it.
2 causes, each with a fixBack end
Laravel: distinct() and count() give the total instead of the unique count
select('city')->distinct()->count() ignores the select
Laravel builds the count from the column you pass to count(), not from select().
2 causes, each with a fixBack end
Laravel insert fails with Invalid datetime format 1292 Incorrect datetime value
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value
MySQL ran in strict mode and refused a date or time value that it cannot store.
3 causes, each with a fixBack end
php artisan migrate stops with could not find driver
could not find driver (Connection: mysql, SQL: select * from information_schema.tables ...)
Laravel talks to the database through a PDO driver, and this error means the PHP that runs artisan has none for your DB_CONNECTION.
3 causes, each with a fixBack end
php artisan migrate stops because the table already exists
SQLSTATE[42S01]: ... 1050 Table 'users' already exists
migrate tried to create a table the database already has.
2 causes, each with a fix