Sorting

Definition

During index requests, usually we have to sort by specific attributes. This requires the $sort configuration:

class PostRepository extends Repository
{
    public static array $sort = ['id'];

Performing request requires the sort query param:

Descending sorting

Sorting DESC requires a minus (-) sign before the attribute name:

GET: /api/restify/posts?sort=-id

Sorting ASC:

GET: /api/restify/posts?sort=id

or with plus sign before the field:

GET: /api/restify/posts?sort=+id

Sort using relation

Sometimes you may need to sort by a belongsTo or hasOne relationship.

This become a breeze with Restify. Firstly you have to instruct your sort to use a relationship:

HasOne sorting

Using a related relationship, it becomes very easy to define a sortable by has one related.

You simply add the ->sortable() method to the relationship:

public function related(): array
{
    return [
        'post' => HasOne::make('post', PostRepository::class)->sortable('title'),
    ];
}

The API request will always have to use the full path to the attributes:

GET: /api/restify/posts?sort=post.attributes.title

The structure of the sort query param value consist always from 3 parts:

• post - the name of the relation defined in the related method
• attributes - a generic json:api term
• title - the column name from the database of the related model

BelongsTo sorting

The belongsTo sorting works in a similar way.

You simply add the ->sortable() method to the relationship:

public function related(): array
{
    return [
        'user' => BelongsTo::make('user', UserRepository::class)->sortable('name'),
    ];
}

Using custom sortable filter

You can override the sorts method, and return an instance of SortableFilter that might be instructed to use a relationship:

// PostRepository
use Binaryk\LaravelRestify\Fields\BelongsTo;
use Binaryk\LaravelRestify\Filters\SortableFilter;

public static function sorts(): array
{
    return [
        'users.name' => SortableFilter::make()
            ->setColumn('users.name')
            ->usingRelation(
                BelongsTo::make('user', 'user', UserRepository::class),
        )
    ];
}

Make sure that the column is fully qualified (include the table name).

The request could look like:

GET: /api/restify/posts?sort=-users.name

This will return all posts, sorted descending by users name.

JOIN strategy for sort-by-relation

By default, sorting by a BelongsTo or HasOne relation column emits a correlated subquery in the ORDER BY clause:

ORDER BY (SELECT vendors.code FROM vendors WHERE vendors.id = invoices.vendor_id LIMIT 1) ASC

This evaluates per row and prevents the database optimizer from using indexes on the related column. Restify can emit a LEFT JOIN instead, which is index-friendly:

LEFT JOIN vendors ON invoices.vendor_id = vendors.id
ORDER BY vendors.code ASC

Enable globally

Flip the config flag (default false) to switch every sort-by-relation to LEFT JOIN:

'sort' => [
    'use_joins_for_belongs_to' => env('RESTIFY_SORT_USE_JOINS_FOR_BELONGS_TO', false),
],

Override per filter

Force LEFT JOIN (or the legacy subquery) for a single sortable, regardless of the config flag:

use Binaryk\LaravelRestify\Fields\BelongsTo;
use Binaryk\LaravelRestify\Filters\SortableFilter;

public static function sorts(): array
{
    return [
        'users.name' => SortableFilter::make()
            ->setColumn('users.name')
            ->usingRelation(BelongsTo::make('user', UserRepository::class))
            ->useJoin(),       // force LEFT JOIN
            // ->useSubquery() // force legacy subquery
    ];
}

Resolution order: per-filter (useJoin / useSubquery) wins over the config flag, which wins over the legacy subquery default.

Sort using closure

If you have a quick sort method, you can use a closure to sort your data:

// PostRepository
use Binaryk\LaravelRestify\Http\Requests\RestifyRequest;

public static function sorts(): array
{
    return [
        'users.name' => function(RestifyRequest $request, $query, $direction) {
            // custom sort
        }
    ];
}

Invokable Custom Sort Classes

Alongside the already provided sorting mechanisms, you can also use an invokable class. This provides you with a flexible way to create your own custom sort logic using classes.

Basic Usage

Such classes should implement the __invoke method, which will be called during sorting. Here's an example:

class NaturalSort {
    public function __invoke(RestifyRequest $request, Builder $query, string $order, string $column): void
    {
        $query->orderBy($column, $order);
    }
};

You can then use it in your Repository's sorts method like this:

public static function sorts(): array
{
    return [
        'name' => app(NaturalSort::class),
    ];
}

However, for even more convenience, you can also directly specify the invokable class name as a string:

PostRepository::$sort = [
    'name' => NaturalSort::class,
];

Built-in Natural Sort Filter

For those not looking to write their own sort filters, binaryk/laravel-restify already provides a built-in natural sort filter. This allows you to quickly sort fields in a natural order without additional implementations:

use Binaryk\LaravelRestify\Filters\Sorts\NaturalSortFilter;

PostRepository::$sort = [
    'name' => NaturalSortFilter::class,
];

Using the NaturalSortFilter class, you can effortlessly apply natural sorting to your repository fields.

Get available sorts

You can use the following request to get sortable attributes for a repository:

/api/restify/posts/filters?only=sortables