Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

Laravel Eloquent query for a column that is X or NULL

ErrororWhere returns rows that ignore my other where conditions

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

  • Applies to Laravel 12, PHP 8.3, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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());
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2A variable that is sometimes null is passed into where, and rows with NULL never come backThe value is compared with = NULLFix
    3. 3Soft-deleted or other tenants' rows appear only when the orWhereNull line is presentA global scope or relation constraint is bypassed by the loose ORFix

What does the error look like?

  • orWhere returns rows that ignore my other where conditions
  • where('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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Back 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 fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.