Advanced Where

Advanced Where

Logical Grouping and Advanced Where Clauses

orWhere() joins with or, and and binds tighter, so mixed chains mislead. A closure passed to where() parenthesizes its conditions; one passed to whereExists() becomes a subquery, and a builder works as a value, as in whereIn('id', $builder).

An ungrouped orWhere, the grouped fix, and NOT EXISTSPHP

$wrong = DB::table('products')->where('stock', '<', 100)
    ->where('category_id', 3)->orWhere('price', '<', 15);
$right = DB::table('products')->where('stock', '<', 100)
    ->where(fn ($q) => $q->where('category_id', 3)->orWhere('price', '<', 15));
echo $wrong->toRawSql(), "\n", $wrong->pluck('sku'), "\n";
echo $right->toRawSql(), "\n", $right->pluck('sku'), "\n";
echo DB::table('customers as c')->whereNotExists(fn ($q) => $q->select(DB::raw(1))
    ->from('orders as o')->whereColumn('o.customer_id', 'c.id'))->pluck('name'), "\n";

Output

select * from `products` where `stock` < 100 and `category_id` = 3 or `price` < 15
["BK-SQL-01","BK-SQL-02","AC-MUG-01","AC-STK-01"]
select * from `products` where `stock` < 100 and (`category_id` = 3 or `price` < 15)
["BK-SQL-01","BK-SQL-02","AC-MUG-01"]
["Gus Lindqvist","Hana Sato"]

The first query leaks the sticker pack (150 in stock): it means (stock < 100 and category_id = 3) or price < 15. whereColumn() compares columns unbound, correlating the subquery (Correlated Subqueries), and the anti-join finds LEFT and RIGHT OUTER JOIN's two customers. whereAny(['name', 'email'], 'like', 'ben%') writes a parenthesized or across columns (whereAll() an and), and when($term, fn ($q, $t) => ...) adds a clause only when $term is truthy.

MENU