How to Simplify Database Queries in Laravel Using Spatie Query Builder

Laravel’s Eloquent ORM (Object-Relational Mapping) system offers an elegant approach to database interaction. However, as your project grows, handling complex queries efficiently can become a huge challenge. Here’s where Spatie Query Builder steps in. This package is a simple solution that makes your database interactions more efficient.

At its core, Spatie Query Builder integrates with Laravel Eloquent ORM where it extends its capabilities and provides a smooth approach to handling a spectrum of query challenges. This significantly simplifies the process of navigating through filterable requests, dynamic filtering, sorting, or relationship filtering.

After implementing this package in some of our projects, we’re here to share insights from our experience. So, throughout this guide, we’ll talk about the key features of the Spatie query builder and provide real-life examples to illustrate essential concepts along the way.

Spatie Query Builder: Key Features

Filtering

In one of our projects, we had a growing list of apartments and needed a straightforward way to filter and retrieve specific data based on user preferences. Spatie Laravel Query Builder made this process extremely easy for us.

As we worked on the “Apartment” model, our goal was to filter apartments by their name, price, and location. The initial option was to handle it like this in the traditional way:

$apartments = Apartment::when(request('name'), function ($query, $name) {
        return $query->where('name', $name);
    })
    ->when(request('price'), function ($query, $price) {
        return $query->where('price', $price);
    })
    ->when(request('location'), function ($query, $location) {
        return $query->where('location', $location);
    })
    ->get();

However, by using the Spatie Query Builder package, the code became way more concise and readable:

// apartments?filter[name]=myapartment
$filteredApartments = QueryBuilder::for(Apartment::class)
    ->allowedFilters([AllowedFilter::exact('name')])
    ->get();
// apartments with the exact name “myapartment”

Pretty straightforward, isn’t it?

Other than exact filters, we defined filters for non-exact matches. To filter apartments that contain the given value in their name (“myapartment” in this case), we simply defined filters like this:

$filteredApartments = QueryBuilder::for(Apartment::class)
    ->allowedFilters(['name'])
    ->get();

In addition to exact filters, Spatie Laravel Query Builder allowed us to create filters for partial matches, which gave us more flexibility in filtering apartments based on their contained values.

Scope Filters

Spatie Laravel Query Builder includes another great feature – scope filters. This feature allowed us to define custom query scopes on our models, extending the filtering capabilities beyond the default functionality. 

Let’s explore how we defined a scope that allowed us to filter apartments where the price was greater than or equal to the specified value.

// Apartment.php
public function scopePriceFrom(Builder $query, $price): Builder
{
    return $query->where(price, '>=', $price);
}

Then we executed the query and got the desired results – /apartments?filter[price_from]=5000

QueryBuilder::for(Apartment::class)
    ->allowedFilters([
        AllowedFilter::scope('price_from'),
    ])
    ->get();
// apartments that cost more than or exactly 5000

Callback Filters

Callback Filter was another great feature of the package that allowed us to define custom filters.
This is how we handled the scenario when we needed to extract only those apartments that had at least one balcony:

QueryBuilder::for(Apartment::class)
    ->allowedFilters([
        AllowedFilter::callback('has_balcony', function (Builder $query, $value) {
            $query->whereHas('balconies');
        }),
    ]);

But there’s more beyond Callback Filters. We took it a step further by creating invokable custom filter classes, where we had the flexibility to design our filters with precision. We simply implemented the \\Spatie\\QueryBuilder\\Filters\\Filter interface, and the __invoke method received the current query builder instance along with the filter name/value.

class FiltersApartmentRooms implements Filter
{
    public function __invoke(Builder $query, $value, string $property)
    {
        $query->whereHas(rooms, function (Builder $query) use ($value) {
            $query->where('type', $value);
        });
    }
}

Sorting

We also refined our sorting process and used the Spatie Query Builder package, which allowed us to achieve the result in a more straightforward and practical way. Let’s take a closer look at what we did:

// GET /apartments?sort=area,-price
$apartments = QueryBuilder::for(Apartment::class)
        ->defaultSort('id')
    ->allowedSorts(['area', 'price'])
    ->get();

Here, we’re demonstrating how the package handled different sorting options for our model. Let’s break it down: by default, the results are sorted in descending order based on ‘id’, and users can choose to further sort by both the ‘area’ and ‘price’ columns, either together or separately. This specific example retrieves the apartments sorted ascendingly by ‘area’ with a secondary sort on price in descending order (note the ‘-‘ symbol before the ‘price’ parameter).

Just like filtering, Laravel Query Builder Spatie allowed us to craft custom sorting classes to tailor the sorting logic according to our specific needs. Here’s how we used it:

->allowedSorts([
        AllowedSort::custom('price-per-square', new PricePerSquareSort(), 'price','area'),
    ])

Keep in mind that while using query builder, you can fully customize it to match your preferences. This includes adjusting how you send query parameters, such as using the syntax filter[my_filter]=value. To do this, you can modify the configuration file of Spatie/laravel-query-builder, which can be published as necessary. This gives you access to a wide range of configuration options, allowing you to personalize the query builder to meet your exact needs.

php artisan vendor:publish --provider="Spatie\\QueryBuilder\\QueryBuilderServiceProvider" --tag="query-builder-config"

This is the default configuration of the query builder parameters:

'parameters' => [
        'include' => 'include',
        'filter' => 'filter',
        'sort' => 'sort',
        'fields' => 'fields',
        'append' => 'append',
    ],

Including Relationships
When working on our project, there was a case where fetching related records became a pivotal requirement. Instead of manually eager-loading relationships in Laravel, Spatie Query Builder offered a more expressive and flexible alternative. To be more specific, we needed to fetch apartments along with their rooms:

// apartments?include=rooms
$apartmentsWithRooms = QueryBuilder::for(Apartment::class)
    ->allowedIncludes(['rooms'])  // allows including `rooms` or `roomsCount` or `roomsExists`
    ->get();

Using this approach, we included ‘rooms’ in the data and automatically requested the existence and count of its related model using the ‘Exists’ and ‘Count’ suffixes, respectively. This functionality was provided by the package, which uses Laravel’s ‘withExists’ and ‘withCount’ methods under the hood.

Spatie Query Builder’s flexibility also applies to including relationships, inheriting all the features available for filtering and sorting. This means that aliases, default values, and other functionalities mentioned earlier also work when including relationships.

Selecting Fields

Another essential feature of the Spatie/laravel-query-builder package is selecting fields. This functionality allowed us to tailor our queries to fetch only the necessary data.

With Spatie Laravel Query Builder, selecting specific fields was pretty simple. Take a look at the example below:

// apartments?fields[apartments]=name,price
$filteredApartments = QueryBuilder::for(Apartment::class)
    ->allowedFields(['id', 'name', 'price'])
    ->get();

It translates to the following SQL query:

// apartments?fields[apartments]=name,price
SELECT "name","price" FROM "apartments"

Similarly, we chose fields for included models when only specific columns were needed from a related relationship.

apartments?include=rooms&fields[rooms]=id,name
QueryBuilder::for(Apartment::class)
    ->allowedFields('rooms.id', 'rooms.name')
    ->allowedIncludes('rooms');

Remember that it’s important to call allowedFields before allowedIncludes. Otherwise, the query builder wouldn’t know which fields to include for the requested includes, resulting in an exception being thrown, as mentioned in the documentation.