7.10.8. SQL-08 — _where Predicates: the Full Surface

Inside _sql(...), _where(predicate) accumulates a WHERE clause via a recursive AST walk. Multiple _where calls compose with AND. This tutorial walks every predicate shape the analyzer recognizes; anything outside this surface raises a compile-time macro_error pointing at the offending node.

7.10.8.1. Recognized predicate shapes

Source shape

Lowered SQL

_.FieldName

column reference (quoted identifier)

someVar, 42, "alice"

bind parameter (?)

==, !=

=, <>

<, <=, >, >=

same operators

&&, ||, !

AND, OR, NOT

+ - * / % (numeric)

same operators

a + b (strings)

(a) || (b) (SQL string concatenation)

& | << >>, unary - ~

same operators

s |> starts_with(p)

s LIKE ? ESCAPE '\' (bind p + %)

s |> ends_with(p)

s LIKE ? ESCAPE '\' (bind % + p)

s |> contains(p)

s LIKE ? ESCAPE '\' (bind % + p + %)

s |> to_lower(), s |> to_upper()

LOWER(s), UPPER(s)

length(s)

LENGTH(s)

x |> abs()

ABS(x)

x |> is_some, x |> is_none

x IS NOT NULL, x IS NULL

x |> unwrap_or(d)

COALESCE(x, ?) — the bound default

x |> _in(subq), x |> _not_in(subq)

IN (subquery), NOT IN (subquery)

x |> _in(arr) (captured array)

IN (SELECT value FROM json_each(?))

int64(x), double(x), string(x)

CAST(x AS INTEGER) / REAL / TEXT

_.Col |> text_match(q)

MATCH ? (FTS5 — see SQL-40 — FTS5 full-text search)

For the LIKE shapes, the % wildcard padding and the escaping of literal % / _ / \ are applied to the bound value (not concatenated into the SQL), so user input can never inject a wildcard.

7.10.8.2. Captured-variable equality

The simplest pattern — a free variable on one side, a column ref on the other:

let target = 3
let one <- _sql(db |> select_from(type<Car>)
                  |> _where(_.Id == target))
// emits:  ... WHERE "Id" = ?     binds: [3]

7.10.8.3. Composition with multiple _where

Each _where adds an AND-clause; chain freely:

let cheap_F <- _sql(db |> select_from(type<Car>)
                      |> _where(_.Price < 1000)
                      |> _where(_.Name |> starts_with("F")))
// emits:  ... WHERE ("Price" < ?) AND ("Name" LIKE ? ESCAPE '\')
// binds:  [1000, "F%"]

7.10.8.4. Boolean operators

&&, ||, ! lower to SQL AND / OR / NOT. Use parentheses to disambiguate precedence:

let cheap_or_F <- _sql(db |> select_from(type<Car>)
                         |> _where(_.Price < 100
                                   || (_.Name |> starts_with("F"))))

7.10.8.5. Arithmetic, bitwise, and string concatenation

Numeric + - * / % and bitwise & | << >> (plus unary - / ~) lower to the matching SQLite operators. daslang + on strings is concatenation, so it lowers to SQLite’s || (not +, which is numeric in SQLite). The same expressions are allowed as computed _select columns and _order_by keys (see SQL-09 — _select Projections, SQL-10 — _order_by and _order_by_descending).

let pricey <- _sql(db |> select_from(type<Car>)
                     |> _where(_.Price * 2 > 400))
// ... WHERE ("Price") * (?) > ?

An expression over a column that has no SQL translation — bitwise ^ (no SQLite operator), or an unhandled function/cast on a column — is rejected at compile time with a clear message, rather than silently mistranslating. Compute it in daslang after the query, or drop to raw SQL.

7.10.8.6. String-tests via LIKE

starts_with / ends_with / contains lower to a LIKE pattern:

let ends_da   <- _sql(db |> select_from(type<Car>)
                        |> _where(_.Name |> ends_with("da")))
let contains_o <- _sql(db |> select_from(type<Car>)
                         |> _where(_.Name |> contains("o")))

7.10.8.7. Case folding

to_lower / to_upper lower to LOWER / UPPER:

let case_match <- _sql(db |> select_from(type<Car>)
                         |> _where(_.Name |> to_lower() == "ford"))

7.10.8.8. String length and numeric scalar

length(s) and x |> abs() lower to LENGTH(...) and ABS(...):

let long_names <- _sql(db |> select_from(type<Car>)
                         |> _where(length(_.Name) > 5))

let dear <- _sql(db |> select_from(type<Car>)
                   |> _where((_.Price |> abs()) > 5000))

7.10.8.9. Inspecting the emitted SQL

_sql_text returns the SQL the macro would emit instead of running it — useful when the chain grows complex:

let sql = _sql_text(db |> select_from(type<Car>)
                      |> _where((_.Price |> abs()) > 5000))
// sql == 'SELECT "Id", "Name", "Price" FROM "Cars" WHERE ABS("Price") > ?'

7.10.8.10. Untranslatable predicates

If you write a shape the analyzer doesn’t recognize (a user-defined function, a math op without a builtin lowering, a regex), the macro raises macro_error at compile time pointing at the offending node. For those cases, drop down to the raw-SQL escape hatch (query_one, query_scalar, exec) covered in SQL-05 — Parameter Binding.

See also

Full source: tutorials/sql/08-where.das

Previous tutorial: SQL-07 — Anatomy of _sql

Next tutorial: SQL-09 — _select Projections