Where Clauses

Where Clauses

where('price', '>=', 30) takes a column, an operator and a value (two arguments mean =), and consecutive calls are joined with and. Named variants cover Filtering with WHERE and Comparisons: whereIn, whereBetween, whereNull, whereLike, whereColumn (column against column) and the date helpers whereDate, whereMonth and whereYear. A -> in a column name reads a JSON path.

Filtering the catalog with operators, lists, ranges, LIKE, and a JSON pathPHP

$q = DB::table('products')
    ->where('price', '>=', 30)
    ->whereIn('category_id', [2, 3])
    ->whereBetween('stock', [1, 20])
    ->where('attributes->format', 'paperback')
    ->whereLike('title', '%s%');
echo wordwrap($q->toRawSql(), 95), "\n", $q->pluck('sku'), "\n";

Output

select * from `products` where `price` >= 30 and `category_id` in (2, 3) and `stock` between 1
and 20 and json_unquote(json_extract(`attributes`, '$."format"')) = 'paperback' and `title`
like '%s%'
["BK-LNX-01","BK-SQL-01"]

The JSON path became the ->> of Reading and Writing JSON. whereLike follows the collation, here case-insensitive; caseSensitive: true writes like binary. Only whereIn expands an array: where('id', [1, 2]) quietly became id = 1. And whereMonth('ordered_at', 5) compiles to month(ordered_at) = '05', which is not sargable (Sargable Predicates), so write a range instead.

MENU