Sorts and selects

A search reads each field through its declaration: sortable() and sortUsing() decide how a field is sorted, and selectable(), writeOnly(), virtual() and resolveUsing() decide what a row can return.

use Illuminate\Contracts\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Lomkit\Rest\Fields\ID;
use Lomkit\Rest\Fields\Json;
use Lomkit\Rest\Fields\Text;

public function fields(): array
{
    return [
        ID::make('id'),
        Text::make('first_name'),
        Text::make('last_name'),
        Text::make('full_name')
            ->virtual()
            ->requires(['first_name', 'last_name'])
            ->resolveUsing(fn (Model $author): string => $author->first_name.' '.$author->last_name)
            ->sortUsing(fn (Builder $query, string $direction) => $query
                ->orderBy('last_name', $direction)
                ->orderBy('first_name', $direction)),
        Text::make('api_token')->writeOnly(),
        Json::make('settings')->nullable(),
    ];
}

Sorts

{
  "search": {
    "sorts": [
      {"field": "price", "direction": "desc"},
      {"field": "name"}
    ]
  }
}

• Sorts apply in their order. direction is asc or desc, asc when omitted. Another value gives The selected search.sorts.0.direction is invalid.
• A field is ordered by its column, qualified with the table of the model: products.price.
• A field with sortUsing() is ordered by its callback, which receives the Eloquent query, also on an include, and the direction. A virtual() or writeOnly() field with sortUsing() is sortable.
• Without sorts, the rows follow defaultOrderBy(), the key of the model in descending order by default.
• Every SQL order ends with the key of the model, in descending order, unless it already holds it, so that rows with equal values keep one order across pages and within the limit of an include. To order ties by another column, add it to sorts or to defaultOrderBy().

public function defaultOrderBy(): array
{
    return ['published_on' => 'desc', 'title' => 'asc'];
}

Fields that sorts refuse

These fields give The selected search.sorts.0.field is invalid.:

• an undeclared field;
• a Json field;
• a field declared sortable(false);
• a virtual() or writeOnly() field without sortUsing();
• a field declared selectable(false) without sortable().

Sort on a related field

A sort can name a field of a to-one relation, <relation>.<field>: a BelongsTo, HasOne, HasOneThrough, HasOneOfMany, MorphOne or MorphOneOfMany relation.

{
  "search": {
    "sorts": [{"field": "author.last_name"}]
  }
}

• The query orders by a subquery that reads the greatest value of the field among the related rows. That subquery applies the perimeterQuery() of the related resource and the global scopes of its model.
• A missing related row, a soft-deleted one, or one outside the perimeter sorts as a null value. The database places null values as it does for a column: first in ascending order and last in descending order on SQLite and MySQL, the other way round on PostgreSQL.
• The field must be one that max accepts in an aggregate: sortable by its own column, neither virtual() nor writeOnly(), and not a Boolean.
• A MorphTo relation, a relation with several rows, a deeper path such as author.posts.title and any other field give The selected search.sorts.0.field is invalid.
• The related resource needs viewAny, checked before validation: a denial answers 403 under search.sorts.0.

Sort on an aggregate

A sort can name the key of an aggregate of the same search, its alias or <relation>_<type>_<field>:

{
  "search": {
    "aggregates": [{"relation": "posts", "type": "count"}],
    "sorts": [{"field": "posts_count", "direction": "desc"}]
  }
}

• The query orders by the subquery of the aggregate, which runs a second time for the order.
• A min, max, avg or sum without a related row is a null value.
• The key of an aggregate that the same level does not request, such as an aggregate of an include, gives The selected search.sorts.0.field is invalid.

Only an aggregate over a relation to many sorts, with no field or on a field that is not a key:

A key is the key of the related model, an ID field, or a column that relates rows, such as the foreign key of the relation or of a relation of the related resource. The key of another aggregate, its alias included, gives the same 422: author_count on a BelongsTo relation, comments_max_id or comments_sum_show_id. The aggregate itself is still accepted in aggregates, and its value is returned in each row.

These sorts work at every level that accepts sorts, and each one adds a subquery: index the columns that relate the rows.

Relevance

With a text search whose strategy orders by relevance, @relevance places the relevance among the other sorts, desc when direction is omitted:

{"sorts": [{"field": "featured", "direction": "desc"}, {"field": "@relevance"}]}

See Text search.

Selects

{
  "search": {
    "selects": [{"field": "name"}, {"field": "price"}]
  }
}

{
  "current_page": 1,
  "data": [
    {"name": "Floor lamp", "price": 129},
    {"name": "Desk lamp", "price": 39}
  ],
  "from": 1,
  "last_page": 1,
  "per_page": 50,
  "to": 2,
  "total": 2,
  "meta": {}
}

• selects lists the fields that each row returns, in this order.
• Without selects, a row holds every selectable field of its resource, in the order of fields(), at every level of the includes.
• The selects of an include are checked against the fields of the related resource.
• selects accepts the selectable fields only. A field declared writeOnly() or selectable(false), a virtual() field without resolveUsing() and an undeclared field give The selected search.selects.0.field is invalid.
• A writeOnly() field is never returned, by any endpoint.

Computed values

resolveUsing() gives a field its value in a row. The callback receives the model of the row, and the response sends what it returns.

Text::make('display_name')
    ->virtual()
    ->requires(['first_name', 'last_name'])
    ->resolveUsing(fn (Model $author): string => $author->first_name.' '.$author->last_name),

• A virtual() field with resolveUsing() is selectable: selects accepts it, and a row without selects returns it.
• A field that is not virtual can declare resolveUsing() too: the callback then gives its value instead of the column.
• In a row, the values of the callbacks come after the columns and the aggregates.
• The callback runs once per returned row, so a query inside it runs once per row.
• requires() lists the columns that the callback reads: they load when the field is returned.
• A virtual field is still sorted and filtered only through sortUsing() and filterUsing().
• The appended attributes of a model ($appends) are not evaluated on the rows of a response: return an appended value through a field whose resolveUsing() reads it.

Loaded columns

A search loads only the columns that its rows need, at every level:

• the columns of the returned fields and the key of the model;
• the columns that a returned field lists in requires();
• the columns that the resource lists in alwaysSelect();
• the keys that its includes need, on the parent row and on the related rows.

public function alwaysSelect(): array
{
    return ['status'];
}

• A policy of the gates, a resolveUsing(), map(), searched() or searchMeta() that reads a column that the search did not load throws MissingColumnException, which names the resource, the column and the model. The search answers 500. List the column in alwaysSelect() or in the requires() of the field that needs it.
• alwaysSelect() returns [] by default. Its columns load without being returned.
• A field that is not a column of the table, such as an accessor, must be declared virtual(), with resolveUsing() to return its value. A field that is not virtual is loaded as a column.
• A relation that the model loads by itself, through $with or a with() in a scope, a global scope, an instruction or perimeterQuery(), still loads: the search adds the keys that it needs.
• Set rest.search.select_columns to false in config/rest.php to load every column again. The fields of a row then come in the order of the columns of the table.

Errors

{
  "message": "The selected search.sorts.0.field is invalid.",
  "errors": {
    "search.sorts.0.field": ["The selected search.sorts.0.field is invalid."]
  }
}