Laravel's whereValueBetween(): Clean Column Range Queries

Ever found yourself writing messy whereRaw() queries or chaining multiple where() clauses just to check if a timestamp falls between two database columns? Laravel's new whereValueBetween() method eliminates this pain point with elegant simplicity.

The whereValueBetween() method allows you to check if a given value falls between two columns directly through Laravel's query builder. This complements the existing whereBetween() and whereColumnsBetween() methods by handling the specific case where you need to compare a single value against a range defined by two database columns.

The Problem It Solves

Before this method, developers had to resort to less elegant solutions. You might have used raw SQL queries or chained multiple where conditions, both of which made code harder to read and maintain:

Post::whereRaw('? between "visible_from" and "visible_to"', now())->get();

Post::where('visible_from', '<=', now())
    ->where('visible_to', '>=', now())
    ->get();

While these approaches work, they're verbose and don't follow Laravel's expressive query builder patterns.

Real-World Example

Consider a content management system where posts have visibility windows defined by visible_from and visible_to columns. You want to fetch all posts that should be visible right now:

use App\Models\Post;

$visiblePosts = Post::whereValueBetween(now(), ['visible_from', 'visible_to'])
    ->get();

$userContent = Post::where('user_id', auth()->id())
    ->whereValueBetween(now(), ['visible_from', 'visible_to'])
    ->where('status', 'published')
    ->get();

This method shines in scenarios involving time-based visibility, price ranges, or any situation where you need to check if a value falls within bounds defined by two database columns.

Method Variations

Laravel provides four related methods to handle different scenarios:

• whereValueBetween() - Basic inclusive range check
• orWhereValueBetween() - OR condition for range checks
• whereValueNotBetween() - Exclude values within range
• orWhereValueNotBetween() - OR condition for exclusion

Post::whereValueBetween(now(), ['visible_from', 'visible_to'])
    ->orWhereValueBetween(now()->addDays(7), ['visible_from', 'visible_to'])
    ->get();

The whereValueBetween() method transforms what used to be awkward raw queries into clean, expressive Laravel code that clearly communicates intent while maintaining the framework's elegant syntax patterns.

Stay Updated with More Laravel Tips

Enjoyed this article? There's plenty more where that came from! Subscribe to our channels to stay updated with the latest Laravel tips, tricks, and best practices:

• Follow us on Twitter @harrisrafto
• Join us on Bluesky @harrisrafto.eu
• Subscribe to our YouTube channel @harrisrafto