WhereBetweenSpecification

A built-in specification for BETWEEN range conditions, checking if a field value falls within a specified range (inclusive).

Namespace

DangerWayne\Specification\Specifications\Common\WhereBetweenSpecification

Constructor

public function __construct(
    string $field,
    mixed $min,
    mixed $max
)

Parameters

• $field (string) - The database column or field name to check
• $min (mixed) - The minimum value (inclusive)
• $max (mixed) - The maximum value (inclusive)

Usage Examples

Basic Range Filtering

use DangerWayne\Specification\Specifications\Common\WhereBetweenSpecification;

// Age range: 18 to 65
$ageSpec = new WhereBetweenSpecification('age', 18, 65);

// Apply to query
$workingAgeUsers = User::whereSpecification($ageSpec)->get();

// Use with collection
$users = User::all();
$workingAgeUsers = $users->filter(function($user) use ($ageSpec) {
    return $ageSpec->isSatisfiedBy($user);
});

Common Scenarios

Price Ranges

// Products in specific price range
$priceSpec = new WhereBetweenSpecification('price', 50.00, 200.00);
$affordableProducts = Product::whereSpecification($priceSpec)->get();

// Sale price range
$saleSpec = new WhereBetweenSpecification('sale_price', 10.00, 99.99);
$saleProducts = Product::whereSpecification($saleSpec)->get();

Date Ranges

// Orders from last month
$startDate = now()->subMonth()->startOfMonth();
$endDate = now()->subMonth()->endOfMonth();
$dateSpec = new WhereBetweenSpecification('created_at', $startDate, $endDate);
$lastMonthOrders = Order::whereSpecification($dateSpec)->get();

// This year's records
$yearSpec = new WhereBetweenSpecification(
    'created_at', 
    now()->startOfYear(), 
    now()->endOfYear()
);
$thisYearData = Model::whereSpecification($yearSpec)->get();

Numeric Ranges

// Score ranges
$passingSpec = new WhereBetweenSpecification('score', 60, 100);
$passingStudents = Student::whereSpecification($passingSpec)->get();

// Quantity ranges
$bulkSpec = new WhereBetweenSpecification('quantity', 100, 1000);
$bulkOrders = Order::whereSpecification($bulkSpec)->get();

Combining with Other Specifications

AND Combinations

$ageSpec = new WhereBetweenSpecification('age', 25, 45);
$salarySpec = new WhereBetweenSpecification('salary', 50000, 100000);
$activeSpec = new WhereSpecification('status', 'active');

// Target demographic with salary range
$targetSpec = $ageSpec->and($salarySpec)->and($activeSpec);
$targetUsers = User::whereSpecification($targetSpec)->get();

OR Combinations

$juniorAgeSpec = new WhereBetweenSpecification('age', 18, 25);
$seniorAgeSpec = new WhereBetweenSpecification('age', 55, 70);

// Either young adults or seniors
$specialOffersSpec = $juniorAgeSpec->or($seniorAgeSpec);
$eligibleUsers = User::whereSpecification($specialOffersSpec)->get();

Complex Combinations

$priceSpec = new WhereBetweenSpecification('price', 100, 500);
$discountSpec = new WhereBetweenSpecification('discount_percentage', 10, 30);
$stockSpec = new WhereSpecification('stock_quantity', '>', 0);
$ratingSpec = new WhereBetweenSpecification('average_rating', 4.0, 5.0);

// Premium products: good price range, decent discount, in stock, highly rated
$premiumSpec = $priceSpec
    ->and($discountSpec)
    ->and($stockSpec)
    ->and($ratingSpec);

$premiumProducts = Product::whereSpecification($premiumSpec)->get();

Real-World Examples

E-commerce Filtering

// Multi-range product filter
$priceRange = new WhereBetweenSpecification('price', $minPrice, $maxPrice);
$ratingRange = new WhereBetweenSpecification('average_rating', 3.5, 5.0);
$weightRange = new WhereBetweenSpecification('weight', 0.1, 10.0);

$filterSpec = $priceRange
    ->and($ratingRange)
    ->and($weightRange);

$filteredProducts = Product::whereSpecification($filterSpec)
    ->with(['reviews', 'images'])
    ->get();

Analytics and Reporting

// Performance metrics filtering
$performanceRange = new WhereBetweenSpecification('performance_score', 70, 100);
$experienceRange = new WhereBetweenSpecification('years_experience', 2, 10);
$salaryRange = new WhereBetweenSpecification('salary', 40000, 80000);

$analyticsSpec = $performanceRange
    ->and($experienceRange)
    ->and($salaryRange);

$reportData = Employee::whereSpecification($analyticsSpec)
    ->selectRaw('department, AVG(performance_score) as avg_performance')
    ->groupBy('department')
    ->get();

Time-based Filtering

// Business hours specification
$businessHoursSpec = new WhereBetweenSpecification(
    'created_at', 
    today()->setTime(9, 0), 
    today()->setTime(17, 0)
);

// Peak season specification
$peakSeasonSpec = new WhereBetweenSpecification(
    'created_at',
    now()->setMonth(11)->startOfMonth(), // November
    now()->setMonth(12)->endOfMonth()    // December
);

$businessHoursOrders = Order::whereSpecification($businessHoursSpec)->get();
$holidayOrders = Order::whereSpecification($peakSeasonSpec)->get();

Implementation Details

isSatisfiedBy() Method

public function isSatisfiedBy(mixed $candidate): bool
{
    $value = data_get($candidate, $this->field);
    
    return $value >= $this->min && $value <= $this->max;
}

toQuery() Method

public function toQuery(Builder $query): Builder
{
    return $query->whereBetween($this->field, [$this->min, $this->max]);
}

Performance Considerations

1. Index Strategy: Ensure the field has an appropriate index
2. Range Size: Very large ranges may impact performance
3. Data Types: Ensure consistent data types between boundaries and field values
4. Query Optimization: Consider composite indexes for frequently combined fields

Optimizing Date Ranges

// Good: Use database indexes effectively
$recentSpec = new WhereBetweenSpecification(
    'created_at', 
    now()->subDays(30), 
    now()
);

// Consider partitioning for large date ranges
$yearSpec = new WhereBetweenSpecification(
    'created_at',
    now()->startOfYear(),
    now()->endOfYear()
);

Negation (NOT BETWEEN)

To create a NOT BETWEEN condition, use the not() method:

$excludeAgeSpec = new WhereBetweenSpecification('age', 13, 17);
$notTeenagerSpec = $excludeAgeSpec->not();

// Get all users NOT between 13 and 17 years old
$nonTeenagers = User::whereSpecification($notTeenagerSpec)->get();

Dynamic Range Building

class DynamicRangeSpecification
{
    public static function fromRequest(Request $request, string $field): ?WhereBetweenSpecification
    {
        $min = $request->input($field . '_min');
        $max = $request->input($field . '_max');
        
        if ($min === null || $max === null) {
            return null;
        }
        
        // Ensure min <= max
        if ($min > $max) {
            [$min, $max] = [$max, $min];
        }
        
        return new WhereBetweenSpecification($field, $min, $max);
    }
    
    public static function percentileRange(string $field, float $lowerPercentile, float $upperPercentile): WhereBetweenSpecification
    {
        // Calculate percentile values from your data
        $min = self::calculatePercentile($field, $lowerPercentile);
        $max = self::calculatePercentile($field, $upperPercentile);
        
        return new WhereBetweenSpecification($field, $min, $max);
    }
}

Testing

use Tests\TestCase;
use DangerWayne\Specification\Specifications\Common\WhereBetweenSpecification;

class WhereBetweenSpecificationTest extends TestCase
{
    public function test_it_filters_by_range()
    {
        User::factory()->create(['age' => 17]); // Below range
        User::factory()->create(['age' => 25]); // In range
        User::factory()->create(['age' => 45]); // In range
        User::factory()->create(['age' => 70]); // Above range
        
        $spec = new WhereBetweenSpecification('age', 18, 65);
        
        $users = User::whereSpecification($spec)->get();
        
        $this->assertCount(2, $users);
        $this->assertTrue($users->pluck('age')->every(fn($age) => $age >= 18 && $age <= 65));
    }
    
    public function test_it_handles_inclusive_boundaries()
    {
        User::factory()->create(['score' => 60]); // Exactly min
        User::factory()->create(['score' => 100]); // Exactly max
        User::factory()->create(['score' => 80]); // In between
        
        $spec = new WhereBetweenSpecification('score', 60, 100);
        
        $users = User::whereSpecification($spec)->get();
        
        $this->assertCount(3, $users);
    }
}

Common Patterns

Configuration-Based Ranges

class ConfigurableRangeSpecification extends WhereBetweenSpecification
{
    public function __construct(string $configKey)
    {
        $range = config("ranges.{$configKey}");
        parent::__construct($configKey, $range['min'], $range['max']);
    }
}

// Usage with config/ranges.php
$priceSpec = new ConfigurableRangeSpecification('product_price');

Sliding Window Ranges

class SlidingWindowSpecification extends WhereBetweenSpecification
{
    public function __construct(string $field, int $days)
    {
        parent::__construct(
            $field,
            now()->subDays($days),
            now()
        );
    }
}

// Usage
$recentSpec = new SlidingWindowSpecification('created_at', 30);
$lastWeekSpec = new SlidingWindowSpecification('updated_at', 7);

See Also

• WhereSpecification - Basic WHERE conditions
• WhereInSpecification - IN conditions
• WhereNullSpecification - NULL checks
• SpecificationBuilder - Fluent builder with whereBetween()