---
title: "Expressions and conditions"
description: "Comparisons, arithmetic, optional predicates and aggregates."
---

> Documentation Index
> Fetch the complete documentation index at: https://effect-orm.maple.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Expressions and conditions

Two brands flow through the DSL. An **`Expr<T>`** is anything that evaluates to a value — a
column, a literal, a function call. A **`Condition`** is a boolean predicate, which is what
`where` collects. Comparison methods turn an `Expr` into a `Condition`.

## How a value becomes a literal

Comparing a column against a plain value encodes that value through the column's own type, so
the literal is whatever ClickHouse expects for _that_ column rather than whatever the JavaScript
value looks like:

```ts
$.Attributes.eq({ "http.method": "GET" }) // Attributes = map('http.method', 'GET')
$.Tags.eq(["a", "b"])                     // Tags = ['a', 'b']
$.Live.eq(true)                           // Live = 1
$.Timestamp.gte(new Date(...))            // Timestamp >= '2026-01-01 00:00:00'
```

### Testing for NULL

`= NULL` is never true in SQL, so a comparison does not take `null`: `.eq(null)`,
`.in_(null)` and the like are type errors, and a `null` that arrives at runtime fails
compilation with a `QueryBuilderError`. Use `.isNull()` (or `.isNotNull()` for present
values), which write `IS NULL` and work on every dialect:

```ts title="null-filter.ts"
import * as CH from "@maple-dev/effect-orm/clickhouse"

const Notes = CH.table("notes", {
	columns: { Note: CH.nullable(CH.string) },
	engine: CH.engine.mergeTree(),
	orderBy: [],
})
export const compiled = CH.compileUnsafe(CH.from(Notes).select("Note").where(($) => [$.Note.isNull()]))
console.log(compiled.sql) // SELECT Note AS Note FROM notes WHERE Note IS NULL
```

### Invalid literals

A value the column cannot hold fails while the SQL is being built:

```ts
$.Count.eq("lots")
// QueryBuilderError { code: "InvalidLiteral" }: column Count: string "lots" is not a valid value
```

Expressions with no type to read — `untypedExpr`, an untyped `dynamicColumn`, a function
declared with `defineFn` — fall back to guessing from the JavaScript value, which handles
strings, numbers, bigints, booleans, dates and `null`; a string is written as an escaped
literal. Anything structured (an array, an object), a symbol or a function fails compilation
with a `QueryBuilderError` (`InvalidLiteral`) rather than being written as `String(value)`,
so an unsupported value that arrives untyped is never written as raw SQL. `undefined` fails
too: as a function argument with `InvalidLiteral`, and compared against an expression
(`.gt(undefined)`) with `InvalidArguments`, like any comparison with a missing value.

_(Backed by `src/ch/literal.test.ts` and `src/ch/failure.test.ts`.)_

## Comparisons

Every `Expr<T>` carries:

| Method                          | SQL                     |
| ------------------------------- | ----------------------- |
| `.eq(x)` / `.neq(x)`            | `= x` / `!= x`          |
| `.gt(x)` / `.gte(x)`            | `> x` / `>= x`          |
| `.lt(x)` / `.lte(x)`            | `< x` / `<= x`          |
| `.in_(...xs)` / `.notIn(...xs)` | `IN (…)` / `NOT IN (…)` |
| `.between(a, b)` / `.notBetween(a, b)` | `BETWEEN a AND b` / `NOT BETWEEN a AND b` |
| `.isNull()` / `.isNotNull()`    | `IS NULL` / `IS NOT NULL` |

Each accepts a raw value or another `Expr<T>`. String literals are escaped; booleans emit as
`1` / `0`.

`in_` carries a trailing underscore because `in` is a reserved word in JavaScript. An empty
list is written as the constant it means, `1 = 0` for `in_()` and `1 = 1` for `notIn()`, since
`IN ()` is not SQL.

### String-only

`.like(pattern)`, `.notLike(pattern)`, `.ilike(pattern)` are constrained by `this` to
`Expr<string>`, so calling them on a numeric column is a type error.

### Text search

`.contains(value)`, `.startsWith(value)` and `.endsWith(value)` match text, not a pattern. `%`,
`_` and `\` in the value match themselves: the helper escapes them with a backslash and adds the
wildcards. Use them for a search box; use `.like()` only for a pattern you wrote.

```ts
.where(($) => [$.OrgId.eq("org_123"), $.Name.contains("50%_off", { insensitive: true })])
// ClickHouse: … AND events.Name ILIKE '%50\\%\\_off%'
// Postgres:   … AND "events"."Name" ILIKE $1                              -- $1 = '%50\%\_off%'
// SQLite, D1: … AND lower("events"."Name") LIKE lower(?) ESCAPE '\'       -- ? = '%50\%\_off%'
```

- The value is a string, or a param or expression of one. A string is escaped before it is bound.
  A param or expression is escaped in SQL (`replace(…)` three times), because its value is known
  only when the query runs.
- The backslash is the escape character on every dialect: ClickHouse and Postgres use it by
  default, and SQLite gets `ESCAPE '\'`, as `.like()` does there.
- Case-sensitive by default, also on SQLite and D1, whose `LIKE` ignores ASCII case. There the
  helpers write `GLOB` instead (`"Name" GLOB ?` with `? = '*50%*'`), escaping GLOB's `*`, `?` and `[` the same
  way.
- `{ insensitive: true }` writes `ILIKE`, or on SQLite and D1 (which have no `ILIKE`) a `LIKE`
  of `lower()` on both sides. Which characters fold is the database's choice. ClickHouse folds
  UTF-8 (`'ÉCOLE' ILIKE 'é%'` is true). Postgres folds by the database's `LC_CTYPE`: a `C` locale
  (PGlite's default) folds ASCII only. SQLite's `lower()` folds ASCII only (without the ICU
  extension).
- An empty value matches every non-NULL row. A NULL column never matches.
- `escapeLike(text)` is the escape step on its own, for a pattern you build for `.like()`.

_(Backed by `docs/expressions.md > Text search escapes LIKE wildcards`.)_

## Combining conditions

```ts
.where(($) => [
	$.OrgId.eq("org_123"),
	$.Name.eq("checkout").or($.Name.eq("cart")),
])
// … WHERE OrgId = 'org_123' AND (Name = 'checkout' OR Name = 'cart')
```

`.and()` / `.or()` parenthesise their result, so precedence is explicit. `CH.not(condition)` wraps
in `NOT (…)` and is available from both dialect entries and the `/expr` subpath.

`CH.and(...)` and `CH.or(...)` take any number of conditions, skip `undefined` ones, and write
one flat group. With none left they return `undefined`, which `where` skips, so optional
filters combine without special cases:

```ts
.where(($) => [
	$.OrgId.eq("org_123"),
	CH.or(CH.when(name, (n) => $.Name.eq(n)), CH.when(minMs, (ms) => $.Ms.gte(ms))),
])
// both given -> … AND (Name = 'checkout' OR Ms >= 100); neither -> only the OrgId test
```

The `where` array is AND-joined. [Tenant scoping](https://effect-orm.maple.dev/clickhouse/database/tenant-scoping/) preserves evidence
through both separate entries, `.and()` and `CH.and()`; `.or()` and `CH.or()` discard it.

_(Backed by `docs/expressions.md > Combining conditions with and/or`.)_

## Optional predicates

`when` and `whenTrue` return `Condition | undefined`, and `where` drops `undefined` entries.
This is how you build filters from optional inputs without string-concatenating SQL:

```ts
const build = (nameFilter?: string) =>
	CH.from(Events)
		.select(($) => ({ name: $.Name }))
		.where(($) => [$.OrgId.eq("org_123"), CH.when(nameFilter, (n) => $.Name.eq(n))])

// build("checkout") -> … WHERE OrgId = 'org_123' AND Name = 'checkout'
// build()           -> … WHERE OrgId = 'org_123'
```

`when` skips `undefined`, `null`, and `false`, and narrows the value for the callback.
`whenTrue(flag, () => cond)` is the variant for a plain boolean gate.

_(Backed by `docs/expressions.md > Optional predicates with when`.)_

### Empty lists and wildcard searches

Decide what an empty filter list means before building the query: omit it for “no filter”, or
return no rows for “nothing selected”. Do not accidentally turn an empty selection into an
unbounded read. The [optional-filter recipe](https://effect-orm.maple.dev/clickhouse/start/recipes/#optional-filters-and-empty-lists) shows
an explicit choice.

Escaping SQL strings does not escape LIKE wildcards. `%` and `_` in a `.like()` argument are
patterns; use `.eq()` for exact text and [`.contains()`](#text-search) to search for it. Keep table names, aliases, and raw SQL under application
control: value escaping is not an allowlist for identifiers.

## Arithmetic

`Expr<number>` carries `.add()`, `.sub()`, `.mul()`, `.div()`. Addition, subtraction, and
multiplication stay as nullable as their operands. Finite floating-point inputs can still
overflow, and ClickHouse encodes the non-finite result as JSON `null`; a non-Nullable result
decodes that `null` as `NaN`. `sum`, `sumIf`, and `toFloat64OrZero` behave the same way. Use
`ifNotFinite` when a finite fallback is required.

> **These do not parenthesise.** Chaining follows SQL operator precedence, not call order:
>
> ```ts
> $.DurationMs.sub(1).div(2)
> // DurationMs - 1 / 2   →  DurationMs - (1 / 2)
> // NOT (DurationMs - 1) / 2
> ```
>
> Order the calls so precedence works in your favour, or bind an intermediate alias in a
> subquery. This is a deliberate trade — the emitted SQL stays readable — but it is the most
> common source of quietly wrong numbers.

_(Backed by `docs/expressions.md > Arithmetic does not parenthesise`.)_

An expression passed as an *argument* keeps its boundary, so that is how to group:
`$.DurationMs.sub($.Offset.add(1))` is `DurationMs - (Offset + 1)`, and
`CH.lit(0.5).mul($.DurationMs.sub(1))` is `0.5 * (DurationMs - 1)`.

_(Backed by `docs/expressions.md > An arithmetic argument keeps its parentheses`.)_

### Division can produce a NULL

`.div()` and `.mod()` decode nullably, whatever their operands are. ClickHouse renders `1 / 0` as
`inf` and `0 / 0` as `nan`, and both come back as JSON `null` — so a division that meets a zero
denominator returns a null the column type has to accept, or the row fails to decode.

Both operators return `Expr<number | null>` — unless the divisor is a numeric literal of
magnitude 1 or more. `$.Duration.div(1_000_000)` cannot manufacture a null from a finite
dividend, so it stays as nullable as `$.Duration` (and is exactly rounded, where
`.mul(0.000001)` drifts by an ulp on a third of integer inputs). A zero, a literal below 1
(`1 / 5e-324` overflows to `inf`), a plain `number`, or another expression as the divisor
makes the result nullable. Modulo by zero can also raise a ClickHouse error; nullable
decoding does not suppress server errors.

When the output must be numeric, guard both non-finite numbers and SQL NULL:

```ts
.select(($) => ({
	errorRate: CH.ifNull(CH.ifNotFinite(CH.sum($.Errors).div(CH.sum($.Total)), 0), CH.lit(0)),
}))
```

`CH.ifNotFinite(expr, fallback)` replaces `nan`/`inf`, but SQL NULL passes through unchanged.
`CH.ifNull` supplies the remaining fallback. `CH.nullIf(expr, value)` returns `Expr<T | null>`;
for example, `CH.sum(x).div(CH.nullIf(CH.sum(y), 0))` keeps a null for an absent denominator.

Addition, subtraction, and multiplication preserve nullable operands in their types and codecs.

_(Backed by `docs/expressions.md > division decodes nullably and ifNotFinite guards it`.)_

## Literals and raw escape hatches

- `lit(value)` — an explicit `Expr` from a `string` or `number`. You rarely need it, since
  comparison methods accept raw values directly.
- `rawExpr(sql, type)` — an `Expr` from a SQL string, with the column type it produces.
- `untypedExpr<T>(sql)` — the same with no type declared; selecting one costs the query its
  row schema, so it is a separate name rather than an omitted argument.
- `rawCond(sql)` — a `Condition` from a SQL string.

Raw helpers interpolate nothing and escape nothing. Never build one from user input. See
[Extending the DSL](https://effect-orm.maple.dev/clickhouse/queries/extending/).

## Aggregates

`count()`, `sum()`, `avg()`, `min()`, `max()`, `uniq()`, `groupUniqArray()`, `argMaxMerge()`,
and the conditional forms `countIf()`, `sumIf()`, `avgIf()`, `minIf()`, `maxIf()`, `anyIf()`.

The `*If` family takes a `Condition` as its last argument:

```ts
.select(($) => ({
	total: CH.count(),
	slow: CH.countIf($.DurationMs.gt(1000)),
}))
// count() AS total, countIf(DurationMs > 1000) AS slow
```

`quantile` is curried, taking the quantile first:

```ts
CH.quantile(0.95)($.DurationMs) // quantile(0.95)(DurationMs)
```

So are the parametric funnel aggregates — the window / pattern is a parameter,
the timestamp and step conditions are the arguments:

```ts
CH.windowFunnel(3600)($.Timestamp, $.Name.eq("view"), $.Name.eq("signup"))
// windowFunnel(3600)(Timestamp, Name = 'view', Name = 'signup')
CH.windowFunnel(3600, "strict_order")($.Timestamp, …)
CH.sequenceMatch("(?1)(?t<3600)(?2)")($.Timestamp, $.Name.eq("view"), $.Name.eq("signup"))
```

`windowFunnel` takes `Date`, `DateTime` or an unsigned integer for the timestamp
(not `DateTime64`) and the window is in that column's unit.

_(Backed by `docs/expressions.md > Conditional aggregation`.)_

`avg`, `avgIf`, and `quantile` return `Expr<number | null>` because empty input produces NaN,
which ClickHouse serializes as JSON null. Use the guards above when the empty result should be zero.

## Conditionals

- `if_(cond, then, else)` — note the underscore; `if` is a reserved word.
- `multiIf([[cond, value], …], fallback)`
- `coalesce(...exprs)`
- `nullIf(expr, value)`

## Everything else

String, numeric, date/time, array, map, JSON, and window functions are catalogued in the
[API reference](https://effect-orm.maple.dev/reference/reference/#clickhouse-functions). Anything not wrapped can be declared in
one line with [`defineFn`](https://effect-orm.maple.dev/clickhouse/queries/extending/).

Source: https://effect-orm.maple.dev/clickhouse/queries/expressions/index.mdx