Compare a Column Against Another Column Range with Laravel whereBetweenColumns()

Sometimes the value you want to check isn't compared against fixed numbers.

Instead, the valid range is stored directly in the database.

For example, a product may have:

minimum_price
maximum_price
current_price

You may want to find products where the current price falls inside that range.

The Traditional Approach

You could write this using raw SQL:

DB::table('products')
    ->whereRaw('current_price BETWEEN minimum_price AND maximum_price')
    ->get();

But Laravel already provides a cleaner method for this.

Use whereBetweenColumns()

$products = Product::whereBetweenColumns(
    'current_price',
    ['minimum_price', 'maximum_price']
)->get();

Laravel will compare current_price against the two columns from the same row.

In simple terms:

minimum_price <= current_price <= maximum_price

Real-World Example

Imagine a products table containing:

minimum_price
maximum_price
current_price

You can find products currently within their allowed price range:

$products = Product::query()
    ->whereBetweenColumns('current_price', [
        'minimum_price',
        'maximum_price',
    ])
    ->get();

You can also find products outside their allowed range:

$products = Product::query()
    ->whereNotBetweenColumns('current_price', [
        'minimum_price',
        'maximum_price',
    ])
    ->get();