Laravel: Refining a database query from URL parameters

So, I made a package to solve a problem that many of us have

Photo by Panos Sakalakis on Unsplash

Most of the time, when I develop an application that returns a listing of a given resource, I also allow the URL parameters to modify the underlying SQL query. For example, include some relations, order the results by a given column and direction, or even limit the number of items returned.

https://app.com/articles/?with[]=user&order_by=published_at&order=desc&limit=10

This plumbing is something that Laravel doesn’t help with, and the framework doesn’t expect this kind of (useful) way to refine a database query from the URL parameters, at least in a safe way.

Because of that, your controllers are often populated with a ton of logic to validate and accommodate the URL parameters to refine the query.

use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Route;

Route::get('users', function (Request $request) {
    $request->validate([
        // ...
    ]);

    $query = User::query();

    $query->when($request->has('order_by'))...;
    $query->when($request->has('order'))...;
    $query->when($request->has('with'))...;

    return $query->paginate();
});

Well, I created a package for just that, called Laragear Refine.

Refining a database query like a pro

The Laragear Refine package works by adding the refineBy() method to the Query Builder, which allows to pass a class that will be used to match the URL parameters to methods to refine the query.

use App\Article;
use App\Refiners\ArticleRefiner;
use Illuminate\Support\Facades\Route;

Route::get('articles', function () {
    return Article::query()->refineBy(ArticleRefiner::class)->paginate();
});

The URL parameters map automatically as camelCase to the methods of the refiner class you pass on.

For example, let’s say we want to use the order_by URL parameter to set the column to order the list. We only need a class that extends the Refiner class and add the orderBy() method, like the ArticleRefiner.

namespace App\Refiners;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Laragear\Refine\Refiner;

class ArticleRefiner extends Refiner
{
    public function orderBy(Builder $query, string $column, Request $request)
    {
        $query->orderBy($column, $request->query('order') ?? 'asc')
    }
}

Each method receives the current instance of the Query Builder, the values that are being passed as URL parameters, and (optionally) the current Request in case you need something for it.

You said “securely”

First and foremost, only the public methods that match the camelCase name of the key are executed. Secondly, you can validate the keys, and only use a given set of keys from the URL.

You read that right. What’s great about the Refiners is that you can move the validation of the request outside the controller or a FormRequest, into the refiner class itself, and limit the scope of the keys that will be actually executed by the class. We only need to add the ValidatesRefiner interface and that’s it.

For example, let’s limit the accepted relations through a validation rule, “users” and “comments”, and only use the with key from the URL.

namespace App\Refiners;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Laragear\Refine\Refiner;
use Laragear\Refine\Contracts\ValidatesRefiner;

class ArticleRefiner extends Refiner implements ValidatesRefiner
{
    public function validationRules(): array
    {
        return [
            'with' => 'sometimes|array',
            'with.*' => 'required_with:with|in:users,comments',
            'order_by' => 'sometimes|string|in:published_at,created_at',
            'order' => 'sometimes|string|in:asc,desc',
        ];
    }

    public function getKeys(): array
    {
        return ['with', 'order_by', 'order'];
    }

    // ...
}

The getKeys() method is especial. By default, all keys of the request are used to execute class methods, so if there is a matching method it will run, validated or not, but with this we can set only some and ignore the rest. If you want to execute a logic even if the key is null or not present, use getObligatoryKeys().

public function getObligatoryKeys(): array
{
    return ['order_by'];
}

/**
 * This will run even if the parameter from the request does not exist.
 */
public function orderBy(Builder $query, ?string $value, Request $request)
{
    $query->orderBy(
        $value ?? 'published_at',
        $request->query('order') ?? 'desc'
    );
}

And that’s pretty much it. You can download it for free at GitHub. While you’re at it, be a nice person and spare a couple of bucks to pay the coffee I use to stay awake at 3:00 AM developing packages like this.

GitHub - Laragear/Refine: Filter a database query using the request query keys and matching…