Queries

Filters

Conditions, groups, the thirteen operators, and filters on relation paths.

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

OperatorSQLValue
is= valueone value
isNotNOT IN (values)one value or an array; every value excluded; on a to-many path: no related row holds one
isAnyOfIN (values)an array
containsILIKE '%value%'text, case-insensitive
gt> valueone value
gte>= valueone value
lt< valueone value
lte<= valueone value
betweenBETWEEN a AND b{ from, to }, both included; either end may be left out
before< valuesame as lt, for dates
after> valuesame as gt, for dates
isEmptyIS NULL / NOT EXISTSnull; see Empty values
isNotEmptynegation of isEmptynull

Empty values

isEmpty matches rows where the field holds no value:

  • a column: IS NULL, plus = '' on text columns and cardinality(...) = 0 on 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.

Not cancelled, and large or from a matching customer real engine · 10,000 sample orders in your browser
requestplayground →
{
  "filters": [
    {
      "type": "condition",
      "key": "status",
      "operator": "isNot",
      "value": "cancelled"
    },
    {
      "type": "group",
      "combinator": "or",
      "children": [
        {
          "type": "condition",
          "key": "totalAmount",
          "operator": "gte",
          "value": 5000
        },
        {
          "type": "condition",
          "key": "customer.name",
          "operator": "contains",
          "value": "north"
        }
      ]
    }
  ],
  "sorting": [
    {
      "key": "totalAmount",
      "dir": "desc"
    }
  ]
}
Runs when scrolled into view.

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.

Copyright © 2026