Flexible Where Between Laravel Package

Getting Started

This is a micro-package: one trait (plus a service provider) that gives Eloquent models a whereBetween-style query scope which tolerates awkward real-world input — a single value, a two-element array, a comma-separated string, or an empty/null value that should be skipped entirely. It exists to stop you writing the same if ($request->filled('start') && $request->filled('end')) dance in every controller.

Install

composer require primitive/flexible-where-between

Laravel's auto-discovery (5.5+) will pick up the service provider. There is no config file to publish and no migrations — if you're expecting config/flexible-where-between.php, you'll be waiting a while.

Where to look first

1. src/ — read the trait and the macro registration. It is small enough to read end-to-end in five minutes, and you should, because the README is thin and the exact argument signature matters.
2. Confirm the supported Laravel/PHP range in composer.json before you commit. This package's last release was November 2020 (Laravel 7/8 era, PHP 7.x), so treat compatibility as something you verify, not assume.
3. Check whether it registers a global query-builder macro or a model trait — that determines whether you opt in per-model or get it everywhere.

First use case

A date-range filter on an index page. The user may submit ?from=2024-01-01&to=2024-01-31, may submit only one of the two, or may submit nothing at all. Instead of branching, you hand the whole thing to the scope:

use Primitive\FlexibleWhereBetween\FlexibleWhereBetween; // verify namespace in src/

class Order extends Model
{
    use FlexibleWhereBetween;
}

// Controller
$orders = Order::query()
    ->whereBetweenFlexible('created_at', $request->input('range'))
    ->paginate();

range as ['2024-01-01', '2024-01-31'], as '2024-01-01,2024-01-31', or as null should all produce sane SQL. That last case — null producing no where clause rather than BETWEEN null AND null — is the entire value proposition.

Implementation Patterns

Normalize at the edge, filter in the scope. Let the package handle shape, but keep your own validation in a FormRequest. Dates going into a BETWEEN against a DATETIME column are the most common real bug (see Gotchas).

class OrderFilterRequest extends FormRequest
{
    public function rules(): array
    {
        return [
            'range'   => ['nullable', 'array', 'size:2'],
            'range.*' => ['nullable', 'date'],
        ];
    }
}

Chain it as one filter among many. The scope composes cleanly with your existing query builder chain, so treat it as a replacement for a hand-rolled when() block rather than a new layer:

$orders = Order::query()
    ->when($request->status, fn ($q, $s) => $q->where('status', $s))
    ->whereBetweenFlexible('created_at', $request->input('range'))
    ->when($request->customer_id, fn ($q, $id) => $q->where('customer_id', $id))
    ->latest()
    ->paginate();

Single-value input as an open-ended bound. If the package accepts one value, decide whether you want that to mean "on that day" or "from that day onward." If it only does the former, wrap it:

if ($from = $request->date('from')) {
    $query->where('created_at', '>=', $from->startOfDay());
}
if ($to = $request->date('to')) {
    $query->where('created_at', '<=', $to->endOfDay());
}

This explicit form is often clearer than relying on a package's interpretation of a one-element array — worth knowing which you're getting.

Reusable filter objects. For anything with more than two filters, hide the scope behind a dedicated filter class so the controller stays readable and the range logic is testable in isolation:

class OrderFilters
{
    public function __construct(private array $input) {}

    public function apply(Builder $query): Builder
    {
        return $query->whereBetweenFlexible('created_at', $this->input['range'] ?? null);
    }
}

Test the shape permutations, not the happy path. The package's whole reason for existing is edge cases, so your test suite should be:

it('skips the clause when the range is null', function () {
    $sql = Order::whereBetweenFlexible('created_at', null)->toSql();
    expect($sql)->not->toContain('between');
});

it('accepts a two-element array', function () {
    $sql = Order::whereBetweenFlexible('created_at', ['2024-01-01', '2024-01-31'])->toSql();
    expect($sql)->toContain('between');
});

Consistency across the team. If you adopt it, adopt it everywhere in the codebase — a half-migrated codebase where some endpoints use when() blocks and others use the scope is worse than either alone, because reviewers can no longer grep for one pattern.

Gotchas and Tips

• Maintenance risk is the headline. Last release November 2020, 15 stars, no dependents. It likely hasn't been touched since Laravel 8. Test it against your actual Laravel version early; if it breaks, the trait is short enough to copy into app/Support/ and own it. That is a perfectly reasonable outcome for a package this size.
• BETWEEN on DATETIME columns is half-open in practice. whereBetween('created_at', ['2024-01-01', '2024-01-31']) becomes >= 2024-01-01 00:00:00 AND <= 2024-01-31 00:00:00 — the entire last day is silently excluded. Either pass now()->endOfDay() / '2024-01-31 23:59:59', or use whereDate / a half-open range >= from AND < to+1day. This bites everyone once; make it a code-review checklist item.
• Timezone drift. If your app stores UTC but users filter in local time, convert before the query. The package does no timezone handling whatsoever.
• Empty strings are not null. A form submitting range[]= and range[]= may arrive as ['', '']. Depending on the package's internals that can become BETWEEN '' AND '' or a driver error. Normalize with array_filter() or a prepareForValidation() hook before it reaches the query.
• The column name is interpolated into SQL. Most of these scopes accept the column as a string and pass it through to the grammar. If the column name is ever user-controllable, whitelist it — this is a genuine injection surface that's easy to miss because the values are bound correctly and the code "looks" safe.
• Debugging is just SQL. No magic to trace: ->dd() or ->toSql() with ->getBindings() will tell you exactly what the scope produced. If a filter "isn't working," check whether the scope silently dropped the clause because it judged the input empty.
• Extension points. Because it's macro/trait-based, you can override the scope in a base model, or register your own global macro that wraps it and adds null/empty normalization plus timezone conversion in one place. Given the package's age, that wrapper is also your insurance policy: if you later drop the dependency, you change the wrapper body and nothing else.
• Don't reach for it on numeric ranges with sparse input. "Price between X and Y" with only one bound supplied is a different problem (open-ended comparison, not BETWEEN); the scope's flexibility won't save you there, and you'll want explicit >=/<= clauses instead.