Defining queries

The definitions API describes in one class what a request may ask of a resource: its fields, includes, filters, sorts and pagination. It adds typed filters with operators, relation sorts, virtual fields, nested definitions, and reports every problem of the request in one 422 response.

A definition

use App\Models\Post;
use App\Models\User;
use MyProgs\QueryBuilder\Definitions\Field;
use MyProgs\QueryBuilder\Definitions\Filter;
use MyProgs\QueryBuilder\Definitions\Pagination;
use MyProgs\QueryBuilder\Definitions\QueryDefinition;
use MyProgs\QueryBuilder\Definitions\Relationship;
use MyProgs\QueryBuilder\Definitions\Sort;

final class UserQuery extends QueryDefinition
{
    public function model(): string
    {
        return User::class;
    }

    public function type(): string
    {
        return 'users';
    }

    public function fields(): array
    {
        return ['id', 'name', 'email', Field::virtual('display_name', dependsOn: ['name', 'nickname'])];
    }

    public function includes(): array
    {
        return [
            Relationship::to('posts', PostQuery::class)->withFilters(),
            'profile',
            Relationship::count('posts'),
        ];
    }

    public function filters(): array
    {
        return [
            Filter::partial('name'),
            Filter::bool('active'),
            Filter::datetime('created_at')->operators(),
            Filter::int('posts.views')->operators(),
        ];
    }

    public function sorts(): array
    {
        return ['name', 'created_at', Sort::relation('profile.country.name')->as('country'), Sort::count('posts')];
    }

    public function defaultSorts(): array
    {
        return ['-created_at'];
    }

    public function pagination(): Pagination
    {
        return Pagination::make(default: 20, max: 100);
    }
}

final class PostQuery extends QueryDefinition
{
    public function model(): string
    {
        return Post::class;
    }

    public function type(): string
    {
        return 'posts';
    }

    public function fields(): array
    {
        return ['id', 'title', 'views', 'published_at'];
    }

    public function filters(): array
    {
        return [Filter::exact('status')];
    }

    public function sorts(): array
    {
        return ['views'];
    }
}

model() and type() are required, the other methods are optional: what a definition does not declare, the request may not ask for.

The controller only runs it:

public function index(Request $request)
{
    return UserQuery::for($request)->paginate();
}

GET /users?fields[users]=name&fields[posts]=title&include=posts,postsCount&filter[posts.views][gte]=20&sort=-name

select "users"."name", "users"."id",
  (select count(*) from "posts" where "users"."id" = "posts"."user_id") as "posts_count"
from "users"
where exists (select * from "posts" where "users"."id" = "posts"."user_id" and "posts"."views" >= ?)
order by "users"."name" desc

The same filter also narrows the included posts, and the posts query selects title together with the keys it needs.

Definitions are resolved from the container, so they may take constructor dependencies. Override query() to start from a constrained builder instead of Model::query():

public function query(): Builder
{
    return User::query()->where('team_id', $this->team->id);
}

A query without a class

A one-off query does not need a definition class:

use MyProgs\QueryBuilder\Definitions\Query;

Query::for(User::class)            // or a builder, or a relation
    ->fields('id', 'name')
    ->includes('posts')
    ->filters(Filter::exact('name'))
    ->sorts('name')
    ->defaultSorts('name')
    ->pagination(default: 20, max: 100)
    ->get();

The request is taken from the container; pass another one as the second argument of for(). Without a definition the resource type is the table name.

The same methods may be chained after UserQuery::for($request): fields, includes, filters and sorts add to what the definition declares, while type(), defaultSorts() and pagination() replace it.

When the query runs

Nothing touches the query until the first call that reads it: get, paginate, first, toSql, getEloquentBuilder and so on. Then the request is validated as a whole, and filters, sorts, fields and includes are applied.

• validate() checks the request without running anything.
• getSubject() returns the builder as it was given.
• Other builder methods (where, with, scopes, ...) go straight to the underlying builder.

Writes run on the filtered rows: UserQuery::for($request)->update([...]), delete(), increment() and the like validate the request and apply its filters, but not its sorts, fields or includes.

A macro registered with Builder::macro() (on the Eloquent builder, the base query builder or a relation) counts as a call that reads the query, so declarations chained after one throw a LogicException. The macros a global scope adds in its extend() (withTrashed(), onlyTrashed(), your own withArchived()) only constrain the query and keep the chain open; restore(), restoreOrCreate() and createOrRestore() read or write it.

A select() on the query is replaced by the requested fields, as the select the subject was given with; addSelect(), withCount() and other columns added later are kept.