Filters
filters is a list of nodes combined with AND. A node is either a condition on one field or a group that combines other nodes with and or or.
Condition
import type { QueryFilterCondition } from "drizzle-resource";
{
type: "condition",
key: "status", // a field path
operator: "is",
value: "active",
};
Group
import type { QueryFilterGroup } from "drizzle-resource";
{
type: "group",
combinator: "and", // "and" | "or"
children: [
// conditions or other groups
],
};
Operators
| Operator | SQL | Value |
|---|---|---|
is | = value | one value |
isNot | NOT IN (values) | one value or an array; every value excluded; on a to-many path: no related row holds one |
isAnyOf | IN (values) | an array |
contains | ILIKE '%value%' | text, case-insensitive |
gt | > value | one value |
gte | >= value | one value |
lt | < value | one value |
lte | <= value | one value |
between | BETWEEN a AND b | { from, to }, both included; either end may be left out |
before | < value | same as lt, for dates |
after | > value | same as gt, for dates |
isEmpty | IS NULL / NOT EXISTS | null; see Empty values |
isNotEmpty | negation of isEmpty | null |
Empty values
isEmpty matches rows where the field holds no value:
- a column:
IS NULL, plus= ''on text columns andcardinality(...) = 0on array columns; - a relation path, to-one or to-many: no related row holds a value (
NOT EXISTS), so a missing relation counts as empty.
When a filter tests a relation for emptiness, every condition on that relation goes through EXISTS. So f.or([f.isAnyOf("group.name", ["Paris"]), f.isEmpty("group.name")]) keeps rows without a group.
Facets can count those rows too: request missing: true and the result carries missing.
A group inside the list
Orders that are not cancelled, and either worth at least 1,000 or from a customer whose name contains "acme":
filters: [
{
type: "condition",
key: "status",
operator: "isNot",
value: "cancelled",
},
{
type: "group",
combinator: "or",
children: [
{
type: "condition",
key: "totalAmount",
operator: "gte",
value: 1000,
},
{
type: "condition",
key: "customer.name",
operator: "contains",
value: "acme",
},
],
},
],
};
The same shape runs live on the sample data below. Edit the values; open sql to see the WHERE clause it compiles to.
Filters on relation paths
Any path reachable through the resource's relations can be filtered on:
import type { QueryFilterCondition } from "drizzle-resource";
{
type: "condition",
key: "orderLines.product.category",
operator: "is",
value: "electronics",
};
A path through a to-many relation compiles to an EXISTS subquery, so an order matches when at least one of its lines matches, and it is returned once.
Building filters on the server
In the scope and in custom stages, use the typed filter builder instead of writing objects by hand:
import { createQueryFilterBuilder } from "drizzle-resource";
const f = createQueryFilterBuilder<MyFieldPaths>();
// The same filter as the group example above
f.and([
f.isNot("status", "cancelled"),
f.or([f.gte("totalAmount", 1000), f.contains("customer.name", "acme")]),
]);
It has one method per operator, plus f.and and f.or. See the filter builder reference.
Client filters and the scope
The effective condition is always (scope) AND (client filters). An empty filters list leaves only the scope. See Scope.