Laravel Eloquent Conditional Queries: where, whereIn, whereNotIn, and whereNull

Beyond a basic where('column', 'value'), Eloquent's query builder has a full set of conditional clause methods — whereIn, whereNotIn, whereNull, multiple stacked conditions, OR conditions, and a genuinely useful when() method for conditionally applying a clause at all.

Multiple where conditions (implicit AND)

$products = Product::where('category_id', 3)
    ->where('is_active', true)
    ->where('price', '>', 100)
    ->get();

OR conditions

$products = Product::where('category_id', 3)
    ->orWhere('featured', true)
    ->get();

Mixing where() and orWhere() without grouping can produce unexpected results due to operator precedence — grouping related OR conditions inside a closure is the safer pattern for anything beyond a single simple OR:

$products = Product::where('is_active', true)
    ->where(function ($query) {
        $query->where('category_id', 3)
            ->orWhere('featured', true);
    })
    ->get();

whereIn and whereNotIn

$products = Product::whereIn('category_id', [1, 3, 5])->get();
$products = Product::whereNotIn('status', ['draft', 'archived'])->get();

whereNull and whereNotNull

$products = Product::whereNull('deleted_at')->get();
$products = Product::whereNotNull('published_at')->get();

Conditionally applying a clause with when()

$products = Product::query()
    ->when($request->filled('category'), function ($query) use ($request) {
        $query->where('category_id', $request->category);
    })
    ->when($request->filled('search'), function ($query) use ($request) {
        $query->where('name', 'like', '%'.$request->search.'%');
    })
    ->get();

when() only applies its closure if the first argument evaluates truthy — this eliminates the repetitive if ($request->filled(...)) { $query->where(...); } pattern that a typical filterable search endpoint would otherwise need, letting the whole filter chain read as one fluent statement.

when() with an else callback

$products = Product::query()
    ->when(
        $request->filled('sort'),
        fn ($query) => $query->orderBy($request->sort),
        fn ($query) => $query->orderBy('created_at', 'desc')
    )
    ->get();

The optional third argument to when() runs if the condition is falsy — genuinely useful for applying a sensible default (sorting by newest here) when the expected condition isn't met, rather than leaving the query unsorted.

Ordering results

$products = Product::orderBy('price', 'asc')->get();

// Multiple order-by clauses
$products = Product::orderBy('category_id')->orderBy('price', 'desc')->get();

Combining everything in a realistic filtered search

$products = Product::query()
    ->when($request->filled('category'), fn ($q) => $q->where('category_id', $request->category))
    ->whereNotIn('status', ['draft', 'archived'])
    ->whereNull('deleted_at')
    ->when($request->filled('sort'), fn ($q) => $q->orderBy($request->sort), fn ($q) => $q->orderBy('created_at', 'desc'))
    ->paginate(20);