# Filtering

> Column operators, AND/OR/NOT, and filters through relations.

Source: https://bettersupabase.com/docs/repository/filtering

## Columns [#columns]

A value means equality; `null` means `is null`:

```ts
where: { status: 'active', kvk: null }
```

Operators depend on the column type:

| Type                 | Operators                                                                          |
| -------------------- | ---------------------------------------------------------------------------------- |
| All                  | `eq`, `neq`, `in`, `notIn`, `isNull`, `not`                                        |
| Text, numbers, dates | `gt`, `gte`, `lt`, `lte`                                                           |
| Text                 | `like`, `ilike`, `match`, `imatch`, `contains`, `startsWith`, `endsWith`, `search` |
| Arrays               | `has`, `hasSome`, `hasEvery`, `containedBy`                                        |
| jsonb                | `contains` (`@>`), `path` with the operators below                                 |

`contains`, `startsWith` and `endsWith` are case-insensitive and escape `%`,
`_` and `*` in your input. Use `like` or `ilike` when you want wildcards (`%`
and `_`; a `*` in the pattern is a literal character, as in Postgres), and
`match` (`~`) or `imatch` (`~*`, case-insensitive) for a POSIX regular
expression. SQLite (PowerSync) returns `unsupported` for `match` and `imatch`.

On a jsonb column, `contains` sends its value as a JSON document, arrays
included, so an array matches a JSON array that holds every given element:

```ts
where: {
  attachments: {
    contains: [{ kind: "image" }];
  }
}
```

`path` compares the text at a key inside a jsonb column, as `->>` does.
It takes `eq`, `neq`, `in`, `notIn`, `isNull`, `gt`, `gte`, `lt`, `lte`,
`like` and `ilike`:

```ts
where: {
  metadata: { path: ['owner', 'id'], eq: userId },
  AND: [{ metadata: { path: ['replacedBy'], isNull: true } }],
}
```

This sends `metadata->owner->>id=eq.<id>`. Numbers and booleans compare as
their text, so `gt` and `lt` order strings; `isNull: true` matches a missing
key and a JSON `null`. Combine several paths on one column with `AND` or `OR`.
SQLite (PowerSync) returns `unsupported` for path filters.

A `null` in an `in` list matches rows where the column is null, and a `null`
in a `notIn` list leaves them out, so the list behaves like the values it
holds rather than like SQL's `in (..., null)`. A `Date` is sent as ISO 8601
text.

Enum and CHECK columns only accept their allowed values:

```ts
where: { status: { in: ['lead', 'active'] } }
// @ts-expect-error: 'deleted' is not a CustomersStatus
where: { status: 'deleted' }
```

## Long `in` lists [#long-in-lists]

`in` lists send uuids, numbers, dates and other plain values without quotes,
as supabase-js does. A read whose query string is still longer than
`urlLengthLimit` (6000 characters by default, below the 8 KB request line the
Supabase API gateway accepts, or the client's own `urlLengthLimit` when that is
lower) is split along its longest top-level `in` list
into reads that fit; they run in parallel, and the rows come back merged:

```ts
await db.employees.findMany({ where: { id: { in: managerIds } } });
```

Splitting needs a read without `limit`, `offset`, `count` or a page, since
the parts can't share those, and an `orderBy` on number, uuid, date or time
columns, which are sorted again after the merge (an order column you don't
select is fetched for the sort and left out of the rows). Other reads that are
too long return `invalid_request`, and so do `updateMany`, `deleteMany` and
the other writes, which can't be split: page through a shorter list, or pass
the list to a function. Set the limit with `defineSupabase(schema, { urlLengthLimit })`.

## Building a filter step by step [#building-a-filter-step-by-step]

`WhereInput` is read-only, like every argument type. The generated module
exports `WhereOf<"table">`, the same shape with writable keys, for a filter
you assemble one condition at a time:

```ts
import type { WhereOf } from "@/lib/supabase/generated";

const where: WhereOf<"customers"> = {};
if (status) where.status = status;
if (search) where.name = { ilike: `%${search}%` };
await db.customers.findMany({ where });
```

`MutableWhere<Models, T>` from `better-supabase` is the generic form.

`OrderByOf<"table">` types a sort you build outside the call, one term or a
list:

```ts
import type { OrderByOf } from "@/lib/supabase/generated";

const orderBy: OrderByOf<"customers"> =
  sort === "newest" ? { createdAt: "desc" } : [{ name: "asc" }, { id: "asc" }];
await db.customers.findMany({ where, orderBy });
```

`OrderTermOf<"table">` is one term of that sort, for a tiebreak or a
list you assemble from parts:

```ts
import type { OrderByOf, OrderTermOf } from "@/lib/supabase/generated";

const tiebreak: OrderTermOf<"customers"> = { id: "asc" };
const orderBy: OrderByOf<"customers"> = [{ name: "asc" }, tiebreak];
```

`OrderByArg<Models, T>` and `OrderByInput<Models, T>` from `better-supabase`
are the generic forms.

## Combining [#combining]

```ts
where: {
  OR: [{ name: { contains: 'acme' } }, { kvk: '1001' }],
  NOT: { status: 'archived' },
}
```

Keys in one object are combined with AND. `AND` also accepts an array.

## Indexes [#indexes]

Equality, ranges and `startsWith` on a plain btree index work as you would
expect. The other operators need a different kind of index, or every query
reads the whole table:

| Filter                                      | Index                                                                                               |
| ------------------------------------------- | --------------------------------------------------------------------------------------------------- |
| `contains`, `ilike`, `endsWith` on text     | trigram: `create extension pg_trgm`, then `create index on customers using gin (name gin_trgm_ops)` |
| `search` on a text column                   | a generated `tsvector` column with a GIN index (below)                                              |
| `contains` on jsonb                         | `create index on notes using gin (attachments jsonb_path_ops)`                                      |
| `has`, `hasSome`, `hasEvery`, `containedBy` | `create index on posts using gin (tags)`                                                            |

`search` on a text column compiles to `to_tsvector(column) @@
websearch_to_tsquery(...)`, which an index can't serve, because the
configuration is a parameter. Store the vector in a generated column instead,
index it, and search that column:

```sql
alter table customers add column search_vector tsvector
  generated always as (to_tsvector('simple', coalesce(name, '') || ' ' || coalesce(kvk, ''))) stored;
create index on customers using gin (search_vector);
```

```ts
where: {
  searchVector: {
    search: "acme";
  }
}
```

`jsonb_path_ops` makes the index smaller and faster for `@>`, which is all
`contains` uses; leave it out if SQL elsewhere uses `?`. Doctor's
[BS219](/docs/cli/doctor#bs219) flags containment filters on columns without
a GIN index.

## Relations [#relations]

To-many relations take `some`, `none` and `every`:

```ts
where: {
  notes: { some: { kind: 'call' } },      // at least one call note
  locations: { none: {} },                // no locations at all
  customerTags: { every: { tag: { color: 'green' } } },
}
```

To-one relations take a filter directly, `null` when the relation is
nullable, or `is` and `isNot`:

```ts
where: { organization: { slug: 'acme' }, primaryContact: null }
```

Relation filters nest to any depth and work inside `OR` and `NOT`. They compile
to filter-only embeds (`!inner` for `some`, anti-joins for `none` and
`every`), so they run as a single request under the caller's RLS.

> **every and NULL**
>
> `every` is "no related row fails the condition", following SQL's three-valued
> logic: a related row whose column is `NULL` does not fail `{ isPrimary: true }`.
> Add `isPrimary: { not: null }` when nulls should count as failures.

## Limits of the PostgREST path [#limits-of-the-postgrest-path]

`updateMany` and `deleteMany` cannot filter through relations over PostgREST,
and return an `invalid_request` error. Use the direct Postgres executor for
those, or select the ids first.