Laravel Query Builder: Find Results Between a Range of Numbers

Introducing Laravel’s Query Builder

Laravel’s Query Builder provides a convenient, fluent interface for creating and running database queries. It can be used to perform most database operations in your application and works perfectly with all of Laravel’s supported database systems.

One of the common tasks in processing database records is searching for results within a specific range. This tutorial will take an in-depth look at how we can leverage Laravel’s Query Builder to retrieve a set of results between two numbers.

Basic Usage of whereBetween

The whereBetween method allows you to define a range for a given column. The following example demonstrates a simple usage:

$results = DB::table('products')
           ->whereBetween('price', [100, 500])
           ->get();

In this example, $results will contain all the products in the price range of 100 to 500.

Search With Exclusive Boundaries

Sometimes, you might want to exclude the boundaries from the search results. For that, you have an whereNotBetween method.

$results = DB::table('products')
           ->whereNotBetween('price', [100, 500])
           ->get();

Here, $results will contain products that do not fall into the price range of 100 to 500.

Complex Range Queries

If you have multiple conditions for a range, such as dates or multiple columns, the Query Builder can handle that gracefully as well:

$results = DB::table('orders')
           ->whereBetween('finalized_at', [
               now()->subMonth(), 
               now()
           ])
           ->whereBetween('total', [500, 1000])
           ->get();

This snippet will retrieve orders finalized in the last month with a total between 500 to 1000.

Combining whereBetween with Other where Clauses

You can combine whereBetween with other where clauses for more complex queries. The following example adds a check for the ‘status’ column:

$results = DB::table('orders')
           ->whereBetween('amount', [50, 150])
           ->where('status', 'shipped')
           ->get();

This query will give us orders with an amount between 50 and 150 that have already been shipped.

Ordering and Limiting Results

To add order and limit to our results:

$results = DB::table('users')
           ->whereBetween('age', [18, 65])
           ->orderBy('name', 'asc')
           ->limit(10)
           ->get();

This will return the first ten users aged between 18 and 65, sorted by name in ascending order.

Using Advanced Sub-Queries

Sometimes, you might need to use more advanced sub-queries within your whereBetween statement, for which you can use Query Builder’s closures:

$results = DB::table('orders')
           ->whereBetween('amount', function ($query) {
               $query->selectRaw('AVG(amount) * 0.5, AVG(amount) * 1.5')
                     ->from('orders')
                     ->where('status', 'paid');
           })
           ->get();

This query will find all orders where the amount is between half and one-and-half times the average order amount of paid orders.

Conclusion

In this tutorial, we explored the flexibilities of Laravel’s Query Builder in retrieving a range of data from our database. From simple single-column ranges to advanced sub-queries and combining with other conditions, we’ve seen how Laravel’s eloquent syntax streamlines these common query tasks. Mastering these methods can have a significant impact on the efficiency of your data handling and manipulation within your Laravel applications.