In Laravel's query builder, why does chaining where('shop_id', 3)->where('status', 'paid')->orWhere('status', 'refunded') return other shops' orders, and how do you fix it?
answer
- and binds tighter than or
- chain compiles flat, no parentheses
- closure passed to where()
- whereNested wraps in parentheses
- whereIn for one column
basics
~20 sThe chain compiles to shop_id = 3 and status = 'paid' or status = 'refunded'; SQL evaluates and before or, so every refunded order from any shop matches. Group the alternatives in a closure passed to where(), or use whereIn().
solid answer
~40 sThe builder emits each clause in order with its own `and` or `or` and adds no parentheses, so the SQL is `where shop_id = ? and status = ? or status = ?`. Because `and` binds tighter than `or`, that means "paid orders of shop 3, **or** any refunded order anywhere". The fix is a **grouping closure**: `->where(function (Builder $query) { $query->where('status', 'paid')->orWhere('status', 'refunded'); })`, which Laravel compiles into a parenthesised group via `whereNested()`. When both alternatives test the same column, `whereIn('status', ['paid', 'refunded'])` is simpler. The docs advise always grouping `orWhere` calls, and warn about unexpected behaviour once global scopes are applied; any `where()` chained after a bare `or` lands on only one side of it.
code
php · 19 lines<?php
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;
// Wrong: where shop_id = ? and status = ? or status = ?
DB::table('orders')
->where('shop_id', 3)
->where('status', 'paid')
->orWhere('status', 'refunded')
->get();
// Right: where shop_id = ? and (status = ? or status = ?)
DB::table('orders')
->where('shop_id', 3)
->where(function (Builder $query) {
$query->where('status', 'paid')->orWhere('status', 'refunded');
})
->get();go deeper
Recall that and binds tighter than or, and that a closure passed to where() produces a parenthesised group.
Explain how the builder stores each clause with its own boolean and compiles them flat, and when whereIn or whereAny replaces a group.
Show why ungrouped orWhere calls leak rows across tenants once scopes or later filters are added, and how you catch it in review.
Argue for a team rule that every orWhere lives in a group, and for fixtures with several tenants so leaks fail tests.
## What the chain actually compiles to Laravel's query builder stores every `where` call as an entry in a list, together with the **boolean** that joins it to the previous one: `and` for `where()`, `or` for `orWhere()`. When the query compiles, the grammar writes those entries out in order and adds no parentheses of its own. So this chain: ```php DB::table('orders') ->where('shop_id', 3) ->where('status', 'paid') ->orWhere('status', 'refunded') ->get(); ``` becomes `select * from orders where shop_id = ? and status = ? or status = ?`. SQL gives `and` **higher precedence** than `or`, so the database reads it as `(shop_id = 3 and status = 'paid') or (status = 'refunded')`. The last branch has no shop filter at all, which is why refunded orders from every shop appear in shop 3's report. ## The fix: a grouping closure Passing a **closure** as the first argument of `where()` tells the builder to open a nested group. Internally `where()` sees a `Closure` with no operator and hands it to `whereNested()`, which gives the closure a fresh builder for the same table and wraps whatever it collects in parentheses: ```php use Illuminate\Database\Query\Builder; DB::table('orders') ->where('shop_id', 3) ->where(function (Builder $query) { $query->where('status', 'paid') ->orWhere('status', 'refunded'); }) ->get(); // where shop_id = ? and (status = ? or status = ?) ``` The group itself is joined with `and` because it was added through `where()`. Adding it through `orWhere(function ...)` joins it with `or` instead. ## Simpler forms when they fit - **Same column, several values:** `whereIn('status', ['paid', 'refunded'])` compiles to `status in (?, ?)` and needs no grouping. - **Same value across several columns:** `whereAny(['name', 'email'], 'like', $term)` builds a parenthesised `or` group for you, and `whereAll` builds the `and` version. - **Array form:** `where([['status', '=', 'paid'], ['total', '>', 0]])` adds each pair as an `and` condition inside one nested group. ## Why the docs say always group `orWhere` The query builder documentation recommends grouping every `orWhere` call, and repeats it as a warning about unexpected behaviour when **global scopes** are applied to Eloquent models. The underlying reason is general: anything appended after a bare `or` joins only the last branch. `->where('a', 1)->orWhere('b', 2)->where('shop_id', 3)` compiles to `a = ? or b = ? and shop_id = ?`, which the database reads as `a = 1 or (b = 2 and shop_id = 3)`. Grouping your alternatives means any condition added later, by a filter further down the chain or by a colleague, is ANDed onto a single self-contained expression. Every `or...` method behaves the same way: `orWhereIn`, `orWhereNull`, `orWhereColumn`, `orWhereBetween` and `orWhereRaw` each contribute one clause joined by `or`, written flat into the SQL. None of them opens a group on its own. A practical checklist for a code review: 1. Find every `orWhere` or `orWhereX` call in the chain. 2. Check that it sits inside a closure together with the alternative it is an alternative *to*. 3. Check that nothing outside that closure is meant to apply to only one side. 4. When unsure, print `->toRawSql()` and read where the parentheses fall. ## Where it tends to go wrong | Situation | Symptom | Fix | |---|---|---| | Filter plus two acceptable statuses | rows from outside the filter | closure group or `whereIn` | | Search box over two columns | search ignores the tenant filter | `whereAny` or a closure group | | Optional filter added after an `orWhere` | filter applies to one branch only | move the `or` into a group first | The bug is easy to miss in tests, because a fixture with a single shop returns exactly the rows the test expects. It shows up as a data leak between customers in production, which is why interviewers treat it as a screening question.
- How would you add a search box that matches name or email without breaking the shop filter?Group the alternatives: `->where(fn (Builder $q) => $q->where('name', 'like', $term)->orWhere('email', 'like', $term))`, or use `whereAny(['name', 'email'], 'like', $term)`, which builds the same parenthesised `or` group. Either way the shop condition stays ANDed onto the whole search.
- What does passing a closure as the value, rather than the column, of where() do?A closure in the value position is treated as a subquery: `where('total', '>', function ($q) { $q->selectRaw('avg(total)')->from('orders'); })` compiles to `total > (select avg(total) from orders)`. A closure in the column position with no operator starts a nested group instead.
saying these in an interview costs you the question
- Believes the builder adds parentheses around orWhere automatically
- Thinks orWhere applies only to the preceding where clause
- Assumes SQL evaluates and/or strictly left to right
- Fixes it by reordering the chain instead of grouping
- Says a test with one shop proves the query is correct