Conditional Query Logic in Laravel: Mastering when() in the Query Builder

In Laravel’s Query Builder, when() is a fluent helper for conditionally modifying a query. It lets you apply additional clauses only if a condition is met, without breaking your chain with if statements.

What when() Does

// Signature (conceptual)
Builder::when(mixed $value, callable $callback, ?callable $default = null)

• $value: The condition. If it evaluates truthy, Laravel runs $callback.
• $callback($query, $value): Receives the current builder instance and the raw $value.
• $default($query, $value) (optional): Runs when $value is falsy.

Key idea: Write one linear chain and “branch” inside it where needed.

Why Use when()

• Readability: Keeps complex, conditional logic in-chain, avoiding nested ifs.
• Safety: Encourages proper grouping (parentheses) through closures.
• Expressiveness: Provides an else branch via the third parameter.
• Composability: Multiple conditional blocks can be stacked clearly.

Practical Patterns (Query Builder Only)

All examples use DB::table('...') and Query Builder methods.

1) Conditional where (explicit null-check)

$query = DB::table('orders')
    ->select('id', 'status', 'created_at')
    ->when($status !== null, function ($q) use ($status) {
        $q->where('status', $status);
    });

Why explicit !== null? Because 0 or '0' may be valid but falsy in PHP. Be explicit if those values matter.

2) Conditional where with an else branch

$query = DB::table('orders')
    ->select('id', 'status', 'created_at')
    ->when($status,
        function ($q, $status) {
            $q->where('status', $status);
        },
        function ($q) {
            $q->whereIn('status', ['new', 'processing']); // sensible default
        }
    );

Tip: The third argument is your “else”.

3) Passing the value directly through when()

$query = DB::table('products')
    ->select('id', 'title', 'price')
    ->when($search, function ($q, $search) {
        $q->where(function ($sub) use ($search) {
            $sub->where('title', 'like', "%{$search}%")
                ->orWhere('sku', 'like', "%{$search}%");
        });
    });

Benefit: No need to use ($search) if you rely on the second parameter.

4) Grouping with closures to preserve SQL precedence

$query = DB::table('invoices')
    ->select('id', 'customer_id', 'due_date')
    ->when($customerId !== null, function ($q) use ($customerId) {
        $q->where(function ($sub) use ($customerId) {
            $sub->where('customer_id', $customerId)
                ->orWhereNull('customer_id');
        });
    });

Why the inner closure? It becomes (customer_id = ? OR customer_id IS NULL), ensuring the OR doesn’t “leak” across the whole query.

5) Conditional date range

$query = DB::table('logs')
    ->select('id', 'level', 'created_at')
    ->when($dateRange, function ($q, $dateRange) {
        $q->whereBetween('created_at', [$dateRange['from'], $dateRange['to']]);
    });

Advice: Normalize inputs beforehand (e.g., convert empty strings to null) so your conditions remain predictable.

6) Conditional joins and selects

$query = DB::table('tickets as t')
    ->select('t.id', 't.subject')
    ->when($withStats, function ($q) {
        $q->leftJoin('ticket_stats as s', 's.ticket_id', '=', 't.id')
          ->addSelect('s.response_time', 's.resolution_time');
    });

Consideration: Extra joins increase cost—only add them when needed.

7) Stacking multiple when() blocks

$query = DB::table('users')
    ->select('id', 'email', 'role', 'created_at')
    ->when($role, fn ($q, $role) => $q->where('role', $role))
    ->when($active !== null, fn ($q) => $q->where('is_active', (int) $active))
    ->when($createdAfter, fn ($q, $d) => $q->where('created_at', '>=', $d));

Guideline: Keep each block focused on a single responsibility.

Common Pitfalls (and How to Avoid Them)

• Truthiness traps:
  0, '0', '', [], false, null are falsy in PHP.
  Use !== null when 0 is a valid filter.
• Ungrouped orWhere:
  ->orWhere(...) on the root builder can change meaning of the whole query.
  Always group related ORs:
  ->where(fn($sub) => $sub->where(...)->orWhere(...)).
• Heavy defaults in the third parameter:
  If your else branch is complex, consider pre-normalizing inputs or splitting the logic before building the query.
• Over-selecting columns:
  Large selects slow down hydration and transport. Prefer narrow selects (only the fields you need).

Performance Considerations

• Indexes matter:
  • Add indexes for frequently filtered columns (e.g., status, customer_id, dates).
  • For patterns like OR column IS NULL, ensure the main column is indexed; consider schema design that avoids frequent NULL if possible.
• Avoid unnecessary joins:
  Join only when the condition demands it (use conditional joins with when()).
• Pagination with conditions:
  Apply all when() filters before calling paginate()/simplePaginate() to ensure consistent page counts.

Testing & Debugging

• Test both branches: Ensure you cover truthy and falsy paths for every when().
• Inspect SQL in dev:
  • Use DB::enableQueryLog() and DB::getQueryLog() (for small tests).
• Input normalization: Convert empty strings to null, trim whitespace, and coerce types before building the query.

Or hook into the query listener:

DB::listen(function ($query) {
    logger()->debug($query->sql, $query->bindings);
});

Additional Considerations You Might Be Missing

• Chaining when() with value passing: Prefer ->when($value, fn($q, $value) => ...) to reduce use(...) captures.
• Collections also have when(): Laravel Collections support when() too, but it’s separate from the Query Builder version. Don’t mix their semantics.
• Consistency across endpoints: Define a common filtering policy (e.g., how to treat empty strings vs. null) to avoid subtle bugs between controllers.
• Security: When building dynamic filters, keep using Query Builder bindings (never concatenate raw user input into SQL).

Finally

• when() enables clean, conditional query construction in the Query Builder.
• Use explicit null checks when values like 0 are valid.
• Group OR conditions with closures to preserve SQL precedence.
• Keep performance in mind: indexes, narrow selects, and conditional joins.
• Test both branches and normalize inputs before building queries.

Applied well, when() keeps your query code readable, safe, and extensible—exactly what you need for evolving filter-heavy endpoints.

Support Us