Nullable, relation & JSON filters

Three constructors answer the questions a bare value cannot: “has no value”, “is related to” and “carries this tag”. Each supports is and a not negation.

Nullable columns — nullable()

A nullable column needs nullable(), not operators(). “Rows with no project” and “rows that have one” cannot be said in a bare value — an empty filter[project]= is indistinguishable from no filter — so nullable() reserves a sentinel (none by default) and reads its negation:

use RoundlyConsulting\QueryBuilder\Enums\FilterValueShape;

->allowedFilters(
    AllowedFilter::nullable('project', 'project_id', FilterValueShape::Uuid),
)

// ?filter[project]=none            → project_id IS NULL
// ?filter[project]=not:none        → project_id IS NOT NULL
// ?filter[project]=<uuid>          → project_id = <uuid>
// ?filter[project]=not:<uuid>      → project_id != <uuid>, or project_id IS NULL
// ?filter[project]=none,<uuid>     → project_id IS NULL, or project_id = <uuid>
// ?filter[project]=not:none,<uuid> → project_id IS NOT NULL and != <uuid>

Signature: nullable(string $name, ?string $internalName = null, FilterValueShape $shape = FilterValueShape::Text, array $operators = [RequestedOperator::Not], string $sentinel = FilterSentinel::NONE). A negation of a real value also matches the unset rows — “not this project” covers rows with no project.

The sentinel is a default, not a reservation. If a column legitimately stores the string none, pass your own:

AllowedFilter::nullable('assignee', 'assignee_id', FilterValueShape::Id, sentinel: 'unassigned'),

// ?filter[assignee]=unassigned → assignee_id IS NULL

Relations — relation()

relation() matches through a relation: whereHas for a match, whereDoesntHave for a negation — never a negated whereHas, which keeps exactly the rows it should exclude (a row related to two labels still satisfies the subquery through the other one). The second argument is the relation, the third the qualified column inside it:

use RoundlyConsulting\QueryBuilder\Enums\FilterValueShape;
use RoundlyConsulting\QueryBuilder\Support\FilterSentinel;

->allowedFilters(
    AllowedFilter::relation('label', 'labels', 'labels.id', FilterValueShape::Uuid),
    AllowedFilter::relation('department', 'departments', 'departments.id', FilterValueShape::Id, sentinel: FilterSentinel::NONE),
)

// ?filter[label]=<id>               → whereHas('labels', labels.id IN (<id>))
// ?filter[label]=not:<id>           → whereDoesntHave('labels', labels.id IN (<id>))
// ?filter[department]=none          → no related departments at all
// ?filter[department]=not:none      → at least one related department
// ?filter[department]=none,<id>     → no department, or related to <id>
// ?filter[department]=not:none,<id> → has a department, and not <id>

Signature: relation(string $name, string $relation, string $column, FilterValueShape $shape = FilterValueShape::Text, array $operators = [RequestedOperator::Not], ?string $sentinel = null). The sentinel is off by default — pass one where “related to nothing” is a question the list asks.

JSON arrays — jsonContains()

jsonContains() matches membership in a JSON array column such as tags, through the driver’s own JSON support (whereJsonContains). exact() would compare the whole document, and partial() would match release inside pre-release:

->allowedFilters(
    AllowedFilter::jsonContains('tag', 'tags'),
)

// ?filter[tag]=release          → tags contains "release"
// ?filter[tag]=release,docs     → tagged release OR docs
// ?filter[tag]=not:release      → not tagged release (rows with NULL tags included)
// ?filter[tag]=not:release,docs → tagged neither

Signature: jsonContains(string $name, ?string $internalName = null, array $operators = [RequestedOperator::Not]). Several values are ORed (“any of these”); their negation is ANDed (“none of these”).

Supported operators

These three filters answer exactly two questions — “is one of these” and “is none of these” — so they accept only RequestedOperator::Is and RequestedOperator::Not, and is is always nameable. Declaring anything else (gte, contains, …) throws UnsupportedOperator when the filter is built: a developer mistake caught at declaration, not a request error.