cake/where

Builds WHERE and HAVING conditions for SELECT, UPDATE, and DELETE

Where clauses are used to filter rows in a table.

Also used to build HAVING clauses for SQL queries, because they work the same way as WHERE clauses, but are used to filter rows after GROUP BY has been applied.

Aliases

import cake/where as w

The Where type is identical for both WHERE and HAVING contexts. Use the same cake/where functions in s.having(...) calls.

Concept

A Where is a tree of conditions. Leaf nodes are comparisons; branch nodes are logical combinators (AND, OR, XOR). You compose them freely and pass the result to query builder functions like s.where(...), u.where(...), etc.

flowchart TD
    ROOT[AndWhere] --> A[eq: active = TRUE]
    ROOT --> B[OrWhere]
    B --> C[eq: role = admin]
    B --> D[eq: role = mod]

Corresponding code:

w.and([
  w.eq(w.col("active"), w.bool(True)),
  w.or([
    w.eq(w.col("role"), w.string("admin")),
    w.eq(w.col("role"), w.string("mod")),
  ]),
])

WhereValue constructors

A WhereValue is one operand in a comparison. It can be a column reference, a literal parameter, a sub-query, or a fragment.

Function	Produces
`col("table.column")`	Column reference
`bool(value)`	Boolean param
`true()`	`TRUE` param
`false()`	`FALSE` param
`float(value)`	Float param
`int(value)`	Integer param
`string(value)`	String param
`null()`	`NULL` param
`date(value)`	Date param
`sub_query(query)`	Sub-query (must return single column)
`fragment_value(fragment)`	Raw SQL fragment

Logical combinators

`and(wheres) -> Where`

All conditions must be true (AND).

w.and([
  w.eq(w.col("active"), w.bool(True)),
  w.gt(w.col("age"), w.int(18)),
])
// active = $1 AND age > $2

`or(wheres) -> Where`

At least one condition must be true (OR).

w.or([
  w.eq(w.col("status"), w.string("pending")),
  w.eq(w.col("status"), w.string("processing")),
])
// status = $1 OR status = $2

`not(where) -> Where`

Negates a condition.

w.not(w.is_null(w.col("deleted_at")))
// NOT (deleted_at IS NULL)

`xor(wheres) -> Where`

Exactly one condition must be true.

w.xor([w.eq(w.col("a"), w.int(1)), w.eq(w.col("b"), w.int(2))])

True conditions	Result
0	FALSE
1	TRUE
2+	FALSE

`xor_parity(wheres) -> Where`

An odd number of conditions must be true (matches MySQL’s native XOR).

w.xor_parity([cond_a, cond_b, cond_c])

True conditions	Result
0	FALSE
1	TRUE
2	FALSE
3	TRUE

On 🦭 MariaDB / 🐬 MySQL: uses native XOR. On 🐘 PostgreSQL / 🪶 SQLite: emulated via integer arithmetic.

`none() -> Where`

An empty / no-op condition. Useful as a default when building conditions conditionally.

let age_filter = case min_age {
  Some(age) -> w.gte(w.col("age"), w.int(age))
  None -> w.none()
}

Comparison operators

All comparisons take two WhereValues.

Function	SQL
`eq(a, b)`	`a = b`
`neq(a, b)`	`a <> b`
`lt(a, b)`	`a < b`
`lte(a, b)`	`a <= b`
`gt(a, b)`	`a > b`
`gte(a, b)`	`a >= b`

w.eq(w.col("status"), w.string("active"))
w.gt(w.col("score"), w.int(100))
w.lte(w.col("created_at"), w.date(cutoff))

NULL checks

w.is_null(w.col("deleted_at"))        // deleted_at IS NULL
w.is_not_null(w.col("email"))         // email IS NOT NULL

Boolean checks

w.is_true(w.col("active"))            // active IS TRUE
w.is_false(w.col("banned"))           // banned IS FALSE
w.is_bool(w.col("verified"), True)    // verified IS TRUE
w.is_not_bool(w.col("locked"), True)  // locked IS NOT TRUE

IN and BETWEEN

`in(value, values) -> Where`

w.in(w.col("status"), [w.string("active"), w.string("pending")])
// status IN ($1, $2)

`in_query(value, sub_query) -> Where`

import cake/select as s

let active_ids =
  s.new()
  |> s.from_table("subscriptions")
  |> s.col("user_id")
  |> s.where(w.eq(w.col("active"), w.bool(True)))
  |> s.to_query

w.in_query(w.col("users.id"), active_ids)
// users.id IN (SELECT user_id FROM subscriptions WHERE active = $1)

`exists_in_query(sub_query) -> Where`

w.exists_in_query(active_ids)
// EXISTS (SELECT user_id FROM subscriptions WHERE active = $1)

`between(a, b, c) -> Where`

w.between(w.col("age"), w.int(18), w.int(65))
// age BETWEEN $1 AND $2

Pattern matching

`like(value, pattern) -> Where`

Case-sensitive pattern match. Use % for any sequence and _ for any single character.

w.like(w.col("name"), "Alice%")
// name LIKE $1

`ilike(value, pattern) -> Where`

Case-insensitive variant of LIKE.

`similar_to(value, pattern, escape_with) -> Where`

SQL regex pattern matching.

Not supported by 🪶 SQLite.

w.similar_to(w.col("phone"), "([0-9]{3}-)?[0-9]{3}-[0-9]{4}", "\\")

Sub-query comparisons (ANY / ALL)

Not supported by 🪶 SQLite.

ANY — true if at least one row in the sub-query satisfies the comparison

Function	SQL
`eq_any_query(v, q)`	`v = ANY(q)`
`neq_any_query(v, q)`	`v <> ANY(q)`
`lt_any_query(v, q)`	`v < ANY(q)`
`lte_any_query(v, q)`	`v <= ANY(q)`
`gt_any_query(v, q)`	`v > ANY(q)`
`gte_any_query(v, q)`	`v >= ANY(q)`

ALL — true only if every row in the sub-query satisfies the comparison

Function	SQL
`eq_all_query(v, q)`	`v = ALL(q)`
`neq_all_query(v, q)`	`v <> ALL(q)`
`lt_all_query(v, q)`	`v < ALL(q)`
`lte_all_query(v, q)`	`v <= ALL(q)`
`gt_all_query(v, q)`	`v > ALL(q)`
`gte_all_query(v, q)`	`v >= ALL(q)`

w.gt_all_query(w.col("salary"), avg_salary_query)
// salary > ALL(SELECT AVG(salary) FROM ...)

Raw fragment condition

import cake/fragment as f

w.fragment(f.prepared("? @> ?::jsonb", [
  f.string("tags"),
  f.string("[\"gleam\"]"),
]))

Full Example

import cake/select as s
import cake/where as w

let query =
  s.new()
  |> s.from_table("users")
  |> s.select_cols(["id", "name", "email"])
  |> s.where(w.and([
    w.is_not_null(w.col("email")),
    w.or([
      w.eq(w.col("role"), w.string("admin")),
      w.and([
        w.eq(w.col("role"), w.string("user")),
        w.is_true(w.col("active")),
      ]),
    ]),
    w.gte(w.col("created_at"), w.date(start_date)),
  ]))
  |> s.order_by_asc("name")
  |> s.to_query

Types

Fragment

</>

pub type Fragment =
  @internal Fragment

ReadQuery

</>

pub type ReadQuery =
  @internal ReadQuery

Where

</>

pub type Where =
  @internal Where

WhereValue

</>

pub type WhereValue =
  @internal WhereValue

Values

and

</>

pub fn and(wheres wheres: List(Where)) -> Where

Logical AND of multiple Wheres.

between

</>

pub fn between(
  value_a value_a: WhereValue,
  value_b value_b: WhereValue,
  value_c value_c: WhereValue,
) -> Where

Creates a WHERE clause that checks if a WhereValue A is between two WhereValues B and C.

bool

</>

pub fn bool(value value: Bool) -> WhereValue

Creates a WhereValue from a Bool.

col

</>

pub fn col(name name: String) -> WhereValue

Creates a WhereValue from a column name String.

date

</>

pub fn date(value value: calendar.Date) -> WhereValue

Creates a WhereValue from a calendar.Date.

eq

</>

pub fn eq(
  value_a value_a: WhereValue,
  value_b value_b: WhereValue,
) -> Where

Creates a WHERE clause that checks if a WhereValue equals another WhereValue.

eq_all_query

</>

pub fn eq_all_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue matches all in a sub-query.

NOTICE: Not supported by 🪶SQLite.

eq_any_query

</>

pub fn eq_any_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue matches any in a sub-query.

NOTICE: Not supported by 🪶SQLite.

exists_in_query

</>

pub fn exists_in_query(sub_query query: ReadQuery) -> Where

Creates a WHERE clause that checks if it exists in a sub-query.

false

</>

pub fn false() -> WhereValue

Creates a FALSE WhereValue.

Notice: You probably want to use where.is_false() instead.

float

</>

pub fn float(value value: Float) -> WhereValue

Creates a WhereValue from a Float.

fragment

</>

pub fn fragment(fragment fragment: Fragment) -> Where

Creates a WhereFragment from a Fragment.

fragment_value

</>

pub fn fragment_value(fragment fragment: Fragment) -> WhereValue

Creates a WhereValue from a Fragment.

gt

</>

pub fn gt(
  value_a value_a: WhereValue,
  value_b value_b: WhereValue,
) -> Where

Creates a WHERE clause that checks if a WhereValue is greater than another WhereValue.

gt_all_query

</>

pub fn gt_all_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue is greater than all in a sub-query.

NOTICE: Not supported by 🪶SQLite.

gt_any_query

</>

pub fn gt_any_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue is greater than any in a sub-query.

NOTICE: Not supported by 🪶SQLite.

gte

</>

pub fn gte(
  value_a value_a: WhereValue,
  value_b value_b: WhereValue,
) -> Where

Creates a WHERE clause that checks if a WhereValue is greater or equal to another WhereValue.

gte_all_query

</>

pub fn gte_all_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue is greater or equal to all in a sub-query.

NOTICE: Not supported by 🪶SQLite.

gte_any_query

</>

pub fn gte_any_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue is greater or equal to any in a sub-query.

NOTICE: Not supported by 🪶SQLite.

ilike

</>

pub fn ilike(
  value value: WhereValue,
  pattern pattern: String,
) -> Where

Creates a WHERE clause that checks if a WhereValue matches a pattern.

ilike is the same as like but case-insensitive.

in

</>

pub fn in(
  value value: WhereValue,
  values values: List(WhereValue),
) -> Where

Creates a WHERE clause that checks if a WhereValue is in a list of WhereValues.

in_query

</>

pub fn in_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue is IN a sub-query.

NOTICE: Usually the sub-query must return a single column.

int

</>

pub fn int(value value: Int) -> WhereValue

Creates a WhereValue from an Int.

is_bool

</>

pub fn is_bool(value value: WhereValue, bool bool: Bool) -> Where

Creates a WHERE clause that checks if a WhereValue matches a Bool.

is_false

</>

pub fn is_false(value value: WhereValue) -> Where

Creates a WHERE clause that checks if a WhereValue is False.

is_not_bool

</>

pub fn is_not_bool(
  value value: WhereValue,
  bool bool: Bool,
) -> Where

Creates a WHERE clause that checks if a WhereValue does not match a Bool.

is_not_null

</>

pub fn is_not_null(value value: WhereValue) -> Where

Creates a WHERE clause that checks if a WhereValue is not SQL NULL.

is_null

</>

pub fn is_null(value value: WhereValue) -> Where

Creates a WHERE clause that checks if a WhereValue is SQL NULL.

is_true

</>

pub fn is_true(value value: WhereValue) -> Where

Creates a WHERE clause that checks if a WhereValue is True.

like

</>

pub fn like(
  value value: WhereValue,
  pattern pattern: String,
) -> Where

Creates a WHERE clause that checks if a WhereValue matches a pattern. The pattern can contain for example the following wildcards:

% matches any sequence of characters.
_ matches any single character.

lt

</>

pub fn lt(
  value_a value_a: WhereValue,
  value_b value_b: WhereValue,
) -> Where

Creates a WHERE clause that checks if a WhereValue lower than another WhereValue.

lt_all_query

</>

pub fn lt_all_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue is lower than all in a sub-query.

NOTICE: Not supported by 🪶SQLite.

lt_any_query

</>

pub fn lt_any_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue is lower than an any in a sub-query.

NOTICE: Not supported by 🪶SQLite.

lte

</>

pub fn lte(
  value_a value_a: WhereValue,
  value_b value_b: WhereValue,
) -> Where

Creates a WHERE clause that checks if a WhereValue lower or equal to another WhereValue.

lte_all_query

</>

pub fn lte_all_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue is lower or equal to all in a sub-query.

NOTICE: Not supported by 🪶SQLite.

lte_any_query

</>

pub fn lte_any_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue is lower or equal to any in a sub-query.

NOTICE: Not supported by 🪶SQLite.

neq

</>

pub fn neq(
  value_a value_a: WhereValue,
  value_b value_b: WhereValue,
) -> Where

Creates a WHERE clause that checks if a WhereValue is not equal to another WhereValue.

neq_all_query

</>

pub fn neq_all_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue is not equal to all in a sub-query.

NOTICE: Not supported by 🪶SQLite.

neq_any_query

</>

pub fn neq_any_query(
  value value: WhereValue,
  sub_query query: ReadQuery,
) -> Where

Creates a WHERE clause that checks if a WhereValue is not equal to any in a sub-query.

NOTICE: Not supported by 🪶SQLite.

none

</>

pub fn none() -> Where

No where condition.

not

</>

pub fn not(where where: Where) -> Where

Negates a Where.

null

</>

pub fn null() -> WhereValue

Creates a NULL WhereValue.

or

</>

pub fn or(wheres wheres: List(Where)) -> Where

Logical OR of multiple Wheres.

similar_to

</>

pub fn similar_to(
  value value: WhereValue,
  to pattern: String,
  escape_with escape_char: String,
) -> Where

Creates a WHERE clause that checks if a WhereValue is similar to a pattern.

NOTICE: Not supported by 🪶SQLite.

string

</>

pub fn string(value value: String) -> WhereValue

Creates a WhereValue from a String.

sub_query

</>

pub fn sub_query(query query: ReadQuery) -> WhereValue

Creates a WhereValue off a ReadQuery.

NOTICE: Usually the sub-query must return a single column.

true

</>

pub fn true() -> WhereValue

Creates a TRUE WhereValue.

Notice: You probably want to use where.is_true() instead.

xor

</>

pub fn xor(wheres wheres: List(Where)) -> Where

Logical XOR of multiple Wheres.

Returns TRUE when exactly one condition is true.

Unlike xor_parity, which returns TRUE for any odd number of true conditions, xor is stricter: two or more true conditions yield FALSE.

Number of conditions true	Result
0	FALSE
1	TRUE
2	FALSE
3	FALSE
4	FALSE

xor_parity

</>

pub fn xor_parity(wheres wheres: List(Where)) -> Where

Logical XOR of multiple Wheres using left-associative binary XOR.

Unlike xor, which returns TRUE when exactly one condition is true, xor_parity returns TRUE when an odd number of conditions are true — matching the behaviour of MySQL’s and MariaDB’s native XOR operator.

Number of conditions true	Result
0	FALSE
1	TRUE
2	FALSE
3	TRUE
4	FALSE

NULL handling: if any predicate evaluates to NULL, the entire expression evaluates to NULL, which a WHERE clause treats as no match (NULL-poisoning). This is consistent across all adapters:

🐘PostgreSQL / 🪶SQLite emulate parity via integer arithmetic; a NULL predicate propagates as NULL through the sum and modulo.
🦭MariaDB / 🐬MySQL use native XOR, where NULL XOR anything = NULL.

For adapters 🦭MariaDB or 🐬MySQL the native XOR syntax will be utilized under the hood.