Search across columns

AllowedFilter::search(string $name, array $columns, array $asText = []) gives an endpoint one search box that looks through several columns at once: a row matches when any of them contains the phrase. It lists its columns instead of taking an internal name:

use RoundlyConsulting\QueryBuilder\AllowedFilter;

->allowedFilters(
    AllowedFilter::search('search', ['name', 'email']),                              // bare columns: index-friendly
    AllowedFilter::search('q', ['name', 'key', 'kind', 'tags'], asText: ['tags']),   // jsonb compared as text
)

// After a join, qualify the columns:
QueryBuilder::for(User::query()->join('teams', 'teams.id', '=', 'users.team_id')->select('users.*'))
    ->allowedFilters(AllowedFilter::search('search', ['users.name', 'teams.name']));

A request compiles to one grouped condition — ILIKE on PostgreSQL, LIKE elsewhere — with the pattern and the escape character bound for every column:

-- GET /users?filter[search]=ann  (PostgreSQL; like on the other engines)
("name" ilike ? escape ? or "email" ilike ? escape ?)
-- bound once per column: '%ann%' and '\'

One phrase

// ?filter[search]=Smith, John         → one phrase: "Smith, John"
// ?filter[search][]=a&filter[search][]=b → rejoined: "a,b"
// ?filter[search]=,                    → no constraint
// ?filter[search]=not:draft            → searched as the text "not:draft"
// ?filter[search]=ann&filter[status]=published → (name or email contains "ann") and status = 'published'

• A search box holds one piece of text, so a comma is part of it: Smith, John is searched as typed, never as Smith or John. That is deliberately unlike a partial() list, which is an OR of values.
• Array syntax is rejoined with commas. The phrase is capped at limits.max_value_length after the rejoin, then trimmed with mb_trim (spaces, tabs, non-breaking spaces).
• An empty phrase — empty, whitespace only, or nothing but commas — adds no constraint.
• No operator prefix is read (not:draft is searched as text), and true / false stay text.

Literal, NULL-safe and grouped

• %, _ and \ in the phrase are escaped and matched with an explicit escape '\' on every engine, so they are literal characters.
• A NULL column simply does not match; the row is still found through another column.
• The columns form one parenthesised OR, ANDed with every other filter: filter[search]=ann&filter[status]=published never returns a draft that mentions “ann”.
• Case folding is the engine’s, as for partial() (see Filters).

Non-text columns — asText

A bare column is compared as it is — never lowercased, never cast. A column listed in asText (it must also be in $columns) is cast to text:

PostgreSQL has no ILIKE for json / jsonb, uuid, integer, inet or native enum columns: an uncast one fails with SQLSTATE 42883 (operator does not exist). Declare every non-text column in asText. On MySQL an uncast JSON column compares as binary — case-sensitive, so foo does not find ["Foo"] — which asText fixes too.

Indexes

Because a column is never lowercased, a gin_trgm_ops (pg_trgm) index on it is used, and a multi-column search uses one index per column (a BitmapOr) when each column has one. lower(col) like ? — what hand-rolled search callbacks usually write — cannot use the column’s index at all. List only the non-text columns in asText; a varchar listed by mistake costs nothing on PostgreSQL (measured on PostgreSQL 16, "title"::text ilike ? and "title" ilike ? produce the same plan, both on the index). pg_trgm needs a term of at least 3 characters to narrow anything.

Relations and declaration errors

• No relation search — a dot is a table qualifier (posts.title), as everywhere in the package. To search a related table, join it and qualify the columns, or use a callback with whereHas.
• A mistaken declaration throws InvalidFilterDeclaration (a LogicException) when the filter is built: no columns, a column that is not a bare or table-qualified name (tags::text, lower(name), name as n — use asText instead of a cast), or an asText column the filter does not search.
• Duplicate columns are dropped, keeping their order.

Using SearchFilter directly

RoundlyConsulting\QueryBuilder\Filters\SearchFilter — new SearchFilter(array $columns, array $asText = []) — is what AllowedFilter::search() wraps. Register it under another wire name with AllowedFilter::custom(), or apply it outside filter[] for a top-level ?search= parameter:

use RoundlyConsulting\QueryBuilder\AllowedFilter;
use RoundlyConsulting\QueryBuilder\Filters\SearchFilter;

// The same filter under another wire name:
->allowedFilters(AllowedFilter::custom('q', new SearchFilter(['users.name', 'users.email'])))

// Outside filter[], for a top-level ?search= contract:
(new SearchFilter(['name', 'email']))->apply($query, $request->string('search')->toString(), 'search');