Building APIs with Laravel Query Builder

Introduction

If you've ever worked on any medium sized API I'm sure you quickly discovered a lot of frequently occurring patterns. Parsing the query string, retrieving necessary models, filtering, sorting, adding pagination and finally encoding the data as JSON. Creating a new endpoint quickly becomes an exhausting chore of making the same decisions repeatedly.
Over at Spatie we don't like doing the same thing more than once (except for releasing packages). So to make some of these decisions for us, we built our latest package: spatie/laravel-query-builder.

Laravel Query Builder

This package allows you to filter, sort and load relationships on an Eloquent query. All the above is achieved based on an incoming API request and only a few lines of code:

$users = QueryBuilder::for(User::class)
    ->allowedFilters('name', 'address')
    ->get();

// Will allow filtering users by `name` and `address` by request URL
// GET api/users?filter[name]=john

In addition, the package integrates perfectly with everything Laravel already has to offer. The QueryBuilder class in this package extends Laravel's default Eloquent builder. This means all of your favorite methods, scopes, and macros are still available.

$users = QueryBuilder::for(User::where('admin', true)) // start from a base query
	->allowedFilters('name', 'address')
	->where('score', '>', 100)
	->popular() // add an existing scope
	->get();

Finally, to make life even easier, the URL parameters in the package adhere to JSON API specification. (and so should you, your API's consumers will be forever grateful to you). JSON API specification describes some conventions and recommendations for JSON APIs. It will help you decide on what a request URL should look like and how a response might be formatted. This way you can focus on your business logic instead of what a filter query should look like.

Filters for days

One of the things you'll end up doing a lot when building APIs is filtering down results. You might want to retrieve posts published before a certain date, get all users with a certain status or search for log entries containing a given keyword.

Out of the box, our query builder comes with two filters: one for partial matches (LIKE '%something%') and one for exact matches (id = 123). These two filters are sufficient to do most basic filter requests. We've provided only those two types of filters as their exact formatting is defined in the JSON API specification. If you need anything more specific you can easily add custom filters as well.

The allowedFilters method can whitelist columns that can be used for filtering. It takes an array of filters as its only argument. These filters are created using the static partial($columnName) and exact($columnName) methods on
Spatie\QueryBuilder\Filter.

Consider the following example:

use Spatie\QueryBuilder\QueryBuilder;
use Spatie\QueryBuilder\Filter;

$query = QueryBuilder::for(Event::class)
	->allowedFilters(
		Filter::partial('name'),
		Filter::exact('venue_type'),
		Filter::exact('organiser_id')
	);

GET /api/events?filter[name]=summer&filter[venue_type]=park

This will create a query to get all Events that are organized in a park and contain the string summer in their name. It won't match any events organized on a parking because that's not an exact match with park.

Dumping the raw SQL query before executing it using $query->toSql() would look something like this:

select * from `events` where `name` LIKE '%summer%' and `venue_type`='park'

As a shorthand you can also pass regular strings to the allowedFilters method and those will be parsed as partial filters:

QueryBuilder::for(Event::class)
	->allowedFilters('name', 'address', 'country');

Custom filters

When whitelisting filters the Filter::partial and Filter::exact methods will create a Spatie\QueryBuilder\Filter object. This Filter object has a $filterClass property which will be set to an invokable class containing the filter logic.

For example the Filter::exact method will set $filterClass to Spatie\QueryBuilder\Filters\FiltersExact:

namespace Spatie\QueryBuilder\Filters;

use Illuminate\Database\Eloquent\Builder;

class FiltersExact implements Filter
{
    public function __invoke(Builder $query, $value, string $property) : Builder
    {
        if (is_array($value)) {
            return $query->whereIn($property, $value);
        }

        return $query->where($property, $value);
    }
}

To add your own custom filters simply create an invokable class that implements the Spatie\QueryBuilder\Filters\Filter interface. This class will contain all filtering logic.

interface Filter
{
    public function __invoke(Builder $query, $value, string $property) : Builder;
}

The following example will return only events having a minimum_age >= the given filter value.

class AgeLimitFilter implements \Spatie\QueryBuilder\Filters\Filter {
    public function __invoke(Builder $query, $value, string $property) : Builder
    {
        return $query->where('minimum_age', '>=', $value);
    }
}

As you can see this is a fairly simple invokable class. It receives the $query being built, the query parameter $value from the URL, and the $property specified in Filter::custom. The expected return value is the $query with the added filtering logic.

When specifying allowed filters you can add any custom filter using the Filter::custom(string $queryParameter, string $filterClass) method. The $queryParameter and its value parsed from the request URL will be passed to the $filterClass.

QueryBuilder::for(Event::class)
    ->allowedFilters(
        Filter::custom('minimum_age', AgeLimitFilter::class)
    );

A possible request might look like: GET /api/events?filter[minimum_age]=21 and create the following query:

SELECT * FROM `events` WHERE `minimum_age` >= 21;

Filters behind the scenes

Behind the scenes Filter::custom(), Filter::exact() and Filter::partial() will create a new Spatie\QueryBuilder\Filter instance. The Filter's $filterClass property will be set to one of the default invokable filters or the custom one you've specified.

Finally, there's just a tiny bit of extra magic going on. Notice the missing type for $value in the Filter interface. This is because $value will be cast automatically to the most appropriate type based on its value.
For example when given a comma-separated list of strings like ?filter[names]=brent,freek,seb,alex it will be cast to an array containing ['brent', 'freek', 'seb', 'alex']. When given anything that looks like a boolean (0, 1, true or false) it will be cast to (you guessed it) a boolean. This conversion happens in the Request::filters macro, more on that later.

Including models

Another regularly occurring feature for our APIs is the possibility to include related models on a response. Think of the classic example of retrieving a couple of posts and including their authors in the JSON response.

Just like filters you can specify allowed includes like this:

QueryBuilder::for(...)->allowedInludes('author')

Including authors on posts using the QueryBuilder then becomes as easy as GET /api/posts?include=author.

Internally the query builder will try to load the authors() relationship on all Post models. If this relationship doesn't exist or the include isn't one of the allowed includes, an exception will be thrown.

The query builder will only eager load the relationship. Conditionally adding it to the right place in the JSON response is up to you.

Luckily, if you're using Laravel this is pretty easy to deal with using Eloquent resources. These resource classes allow you to wrap, transform, paginate and conditionally include related models on the response. The latter is done using the whenLoaded method:

class Event extends Resource
{
    public function toArray($request)
    {
        return [
            'id' => $this->id,
            'name' => $this->name,
            'venue' => Venue::make($this->whenLoaded('venue')),
            'speakers' => User::colletion($this->whenLoaded('speakers')),
        ];
    }
}

Alternatively, you can use the tried and tested Fractal package by The League.

Sorting

Finally, the query builder also offers a convenient way to sort queries by any of the model's columns. Add the sort parameter to your URL (e.g. ?sort=age) and we'll add an ORDER BY clause to your query.

By default, the ORDER BY clause will sort results in an ascending fashion. However, you can reverse this to be descending by prepending a dash to the column name: ?sort=-age. Neat.

Contrary to filters and includes, sorting is allowed on all of the available columns by default. No whitelisting required.
However, you can still specifically whitelist only some columns to be used for sorting using the allowedSorts method.
You might, for example, want to exclude the password column from the list of available user filters.

Pagination

Not part of this package but pretty important for any API is pagination. If you're returning large data sets it might be nice to offer a way of paginating the results to your API consumers.

Using Laravel this is already easily achievable using the paginate method on a QueryBuilder instance. However, you might notice that the response format and URL parameters diverge a lot from the JSON API spec. That's why we've released the spatie/laravel-json-api-paginate package a while back. Using that package you can use the jsonPaginate method instead of paginate() on the QueryBuilder. This will add all necessary metadata to the JSON response and paginate the response using the correct URL parameters. All available parameters can be found in the specification.

Alternative options

When publishing this package, @gnumoksha on Twitter tweeted about the l5-repository package. It's an extensive implementation of the repository pattern for Laravel and a great alternative for this package. If you're shopping for ways to build up your API it's definitely worth considering. However, for us at Spatie this solution usually feels a bit too heavy.

In closing

Our query builder provides a lot of convenient features you'll need in almost any API. It's a great starting point which can easily be extended upon for any application specific requirement.