---
title: "Search across columns — Query Builder for Laravel | Roundly"
description: "One search box across several columns with search() — a literal phrase, one grouped OR, NULL-safe, and kind to PostgreSQL trigram indexes."
url: https://roundly-consulting.com/open-source/docs/query-builder-for-laravel/search
language: en
---

[All packages](https://roundly-consulting.com/open-source.md)

[Query Builder for Laravel](https://roundly-consulting.com/open-source/docs/query-builder-for-laravel.md)

# 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:

```php
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:

```sql
-- 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

```php
// ?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:

| Driver | An asText column | Case folding |
| --- | --- | --- |
| PostgreSQL | `"tags"::text ilike ?` | The database’s ctype locale, as for partial(). |
| MySQL / MariaDB | ``cast(`tags` as char) like ?`` | The connection collation (Laravel’s default utf8mb4\_unicode\_ci folds case and accents), so JSON folds case like text. |
| SQLite, others | unchanged | ASCII letters only, as for partial(). |

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:

```php
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');
```

## Show your open-source love

This package is free and MIT-licensed. If it saves you time, a one-off donation or a Patreon membership keeps it maintained, tested and documented.

[More ways to support, including crypto](https://roundly-consulting.com/support-us.md)

By donating, you agree to our [donation terms](https://roundly-consulting.com/donation-terms.md).

[Support our open source work (opens in a new tab)](https://donate.stripe.com/dRmeVe8FX5PF1Qd9pXcEw00) [Join us on Patreon (opens in a new tab)](https://www.patreon.com/cw/roundly)

## Want this built into your product?

We integrate our packages into custom Laravel and AI builds. Tell us what you're working on and we'll reply within 48 hours.

[Get a quote in 48 hours](https://roundly-consulting.com/contact.md) [Browse all packages](https://roundly-consulting.com/open-source/docs/query-builder-for-laravel.md)