where(), orWhere(), whereIn(), whereBetween(), whereNull()
Concept
WHERE clauses are the primary filtering mechanism. The Query Builder provides a comprehensive set of methods that cover nearly all SQL WHERE expressions.
Basic where(): Three forms:
->where('column', 'value'): Shorthand for=.->where('column', '=', 'value'): Explicit operator.->where('column', '>=', 100): Any comparison operator (=,!=,<>,<,>,<=,>=,LIKE,NOT LIKE,ILIKE).
Multiple conditions: Chained where() adds AND. Pass an array of pairs for multiple ANDs: ->where([['status', 'active'], ['role', 'admin']]).
orWhere(): Adds an OR condition. Warning: orWhere applies at the top level — wrapping is needed for complex logic: ->where(fn($q) => $q->where('a', 1)->orWhere('b', 2)).
whereIn() / whereNotIn(): ->whereIn('id', [1, 2, 3]). Pass a Closure for a subquery.
whereBetween() / whereNotBetween(): ->whereBetween('age', [18, 65]).
whereNull() / whereNotNull(): ->whereNull('deleted_at').
whereDate() / whereMonth() / whereDay() / whereYear() / whereTime(): Compare against date parts.
whereColumn(): Compare two columns: ->whereColumn('updated_at', '>', 'created_at').
when(bool $condition, Closure $callback): Conditionally add where clauses without breaking the chain.
Code Example
<?php
use Illuminate\Support\Facades\DB;
// Basic where
DB::table('users')->where('active', true)->get();
DB::table('orders')->where('total', '>=', 100)->get();
DB::table('users')->where('name', 'like', 'Ali%')->get();
// Multiple conditions (AND)
DB::table('users')
->where('active', true)
->where('role', 'admin')
->get();
// Array syntax — shorthand for multiple ANDs
DB::table('users')->where([
['active', true],
['role', 'admin'],
['age', '>=', 18],
])->get();
// orWhere — be careful with grouping!
// WRONG: returns ALL admins OR ALL active users (not intended)
DB::table('users')
->where('active', true)
->orWhere('role', 'admin')
->get();
// CORRECT: group the OR in parentheses
DB::table('users')
->where('status', 'verified')
->where(function($q) {
$q->where('role', 'admin')
->orWhere('role', 'superuser');
})
->get();
// SQL: WHERE status = 'verified' AND (role = 'admin' OR role = 'superuser')
// whereIn
DB::table('orders')->whereIn('status', ['pending', 'processing'])->get();
DB::table('orders')->whereNotIn('status', ['cancelled', 'refunded'])->get();
// whereBetween
DB::table('orders')->whereBetween('total', [50, 500])->get();
DB::table('events')->whereBetween('starts_at', [now(), now()->addWeek()])->get();
// whereNull / whereNotNull
DB::table('posts')->whereNull('published_at')->get(); // drafts
DB::table('posts')->whereNotNull('published_at')->get(); // published
// Date filtering
DB::table('orders')->whereDate('created_at', today())->get();
DB::table('orders')->whereMonth('created_at', 12)->get(); // December
DB::table('orders')->whereYear('created_at', 2024)->get();
// when() — conditional clauses
$query = DB::table('users')
->when($request->role, fn($q, $role) => $q->where('role', $role))
->when($request->search, fn($q, $s) => $q->where('name', 'like', "%$s%"))
->when(!$showInactive, fn($q) => $q->where('active', true));
$users = $query->get();