Filters | Atoll Book

Write WHERE predicates, ordering, paging, and runtime-conditional clauses that all compile to one fixed statement.

Inside a query, a bare name that matches a model field is a column and any other in-scope name is a bound parameter. Everything on this page follows from that one rule.

schema shop

model Product {
    @id
    id: int
    name: string
    price: float
    stock: int
    discontinued: bool
}

fn cheap(ceiling: float, n: int): []Product ! QueryError {
    return FROM Product
        WHERE price <= ceiling
        ORDER BY price ASC, id ASC
        LIMIT n?
}

fn main(): void {
    match cheap(9.99, 20) {
        Ok(rows) => println("${rows.len()} cheap products")
        Err(e) => println("read failed")
    }
}

price and id are columns; ceiling and n become ?1 and ?2. The compiler checks the column names, the operator types, the parameter encodings, and the selected engine’s support for each clause.

Predicates

Comparisons use the ordinary Atoll operators: ==, !=, <, <=, >, >=, &&, ||, !. A bool column stands alone as a predicate.

schema shop

model Product {
    @id
    id: int
    price: float
    stock: int
    discontinued: bool
}

fn dead(): []Product ! QueryError {
    return FROM Product WHERE discontinued?
}

fn live(): []Product ! QueryError {
    return FROM Product WHERE !discontinued?
}

fn live_explicit(): []Product ! QueryError {
    return FROM Product WHERE discontinued == false?
}

fn band(lo: float, hi: float): []Product ! QueryError {
    return FROM Product WHERE price > lo && price < hi?
}

fn either(floor: float, level: int): []Product ! QueryError {
    return FROM Product WHERE price >= floor || stock == level?
}

fn main(): void {
    match dead() { Ok(r) => println("${r.len()} dead") Err(e) => println("failed") }
    match live() { Ok(r) => println("${r.len()} live") Err(e) => println("failed") }
    match live_explicit() { Ok(r) => println("${r.len()} live") Err(e) => println("failed") }
    match band(1.0, 5.0) { Ok(r) => println("${r.len()} in band") Err(e) => println("failed") }
    match either(5.0, 0) { Ok(r) => println("${r.len()} either") Err(e) => println("failed") }
}

Repeating WHERE is an implicit AND. It reads better than one long conjunction when the conditions are independent, and each line can carry its own comment:

schema shop

model Product {
    @id
    id: int
    price: float
    stock: int
    discontinued: bool
}

fn sellable(floor: float, minimum: int): []Product ! QueryError {
    return FROM Product
        WHERE discontinued == false
        WHERE price >= floor
        WHERE stock >= minimum?
}

fn main(): void {
    match sellable(1.0, 3) {
        Ok(rows) => println("${rows.len()} sellable")
        Err(e) => println("failed")
    }
}

Arithmetic on columns and parameters is allowed where the types line up:

schema shop

model Product {
    @id
    id: int
    price: float
    stock: int
}

fn within_budget(budget: float): []Product ! QueryError {
    return FROM Product WHERE price * stock <= budget?
}

fn main(): void {
    match within_budget(1000.0) {
        Ok(rows) => println("${rows.len()} within budget")
        Err(e) => println("failed")
    }
}

Null tests

Comparing an optional column against None becomes SQL IS NULL / IS NOT NULL. The contextual spellings null and nil mean the same thing, and a null test consumes no parameter slot.

schema shop

model Product {
    @id
    id: int
    note: string?
}

fn unnoted(): []Product ! QueryError {
    return FROM Product WHERE note == None?
}

fn noted(): []Product ! QueryError {
    return FROM Product WHERE note != None?
}

fn unnoted_again(): []Product ! QueryError {
    return FROM Product WHERE note == null?
}

fn main(): void {
    match unnoted() { Ok(r) => println("${r.len()} unnoted") Err(e) => println("failed") }
    match noted() { Ok(r) => println("${r.len()} noted") Err(e) => println("failed") }
    match unnoted_again() { Ok(r) => println("${r.len()} unnoted") Err(e) => println("failed") }
}

Write these explicitly rather than assuming a comparison against a missing value evaluates to false — SQL’s three-valued logic does not work that way, and dialects differ at the edges.

Text matching

String methods on a column lower to LIKE with the wildcards the method implies. The argument is still a bound parameter, so the pattern cannot inject SQL.

Atoll	SQL
`col.contains(v)`	`col LIKE '%' \|\| v \|\| '%'`
`col.starts_with(v)`	`col LIKE v \|\| '%'`
`col.ends_with(v)`	`col LIKE '%' \|\| v`

schema shop

model Product {
    @id
    id: int
    name: string
    sku: string
}

fn anywhere(text: string): []Product ! QueryError {
    return FROM Product WHERE name.contains(text)?
}

fn by_prefix(prefix: string): []Product ! QueryError {
    return FROM Product WHERE sku.starts_with(prefix)?
}

fn by_suffix(suffix: string): []Product ! QueryError {
    return FROM Product WHERE sku.ends_with(suffix)?
}

fn main(): void {
    match anywhere("widget") { Ok(r) => println("${r.len()}") Err(e) => println("failed") }
    match by_prefix("A-") { Ok(r) => println("${r.len()}") Err(e) => println("failed") }
    match by_suffix("-XL") { Ok(r) => println("${r.len()}") Err(e) => println("failed") }
}

Set membership

in accepts a literal list — each element becomes its own placeholder — or a subquery projecting exactly one column.

schema shop

model Product {
    @id
    id: int
    stock: int
}

// Renders as: ... WHERE stock IN (?1, ?2, ?3)
fn low(): []Product ! QueryError {
    return FROM Product WHERE stock in [0, 1, 2]?
}

fn main(): void {
    match low() {
        Ok(rows) => println("${rows.len()} nearly out")
        Err(e) => println("failed")
    }
}

schema shop

model Category {
    @id
    id: int
    active: bool
}

model Product {
    @id
    id: int
    category_id: int

    BELONGS TO category: Category VIA category_id
}

fn in_active_categories(): []Product ! QueryError {
    return FROM Product
        WHERE category_id in (FROM Category WHERE active SELECT { id })?
}

fn any_active_category(): []Product ! QueryError {
    return FROM Product WHERE exists(FROM Category WHERE active SELECT { id })?
}

fn main(): void {
    match in_active_categories() { Ok(r) => println("${r.len()}") Err(e) => println("failed") }
    match any_active_category() { Ok(r) => println("${r.len()}") Err(e) => println("failed") }
}

A runtime list cannot be expanded into a fixed placeholder set, so this is rejected rather than silently mis-rendered:

schema shop

model Product {
    @id
    id: int
    stock: int
}

fn f(levels: []int): []Product ! QueryError {
    return FROM Product WHERE stock in levels?
}

That reports ATOLL3244. The rule is exact: in takes a list literal whose arity is known while the statement is rendered, or a subquery. A []int parameter, a local bound to a list, and a list built in a loop are all the same unsupported shape.

When the candidate set genuinely varies in size, put it in a table and use a subquery, or read a superset and filter in Atoll:

schema shop

model Product {
    @id
    id: int
    stock: int
}

// Read the superset the engine can render, then narrow in guest code.
fn with_levels(levels: []int, ceiling: int): []Product ! QueryError {
    candidates := FROM Product WHERE stock <= ceiling?
    mut out: []Product = []
    for p in candidates {
        if levels.contains(p.stock) { out.add(p) }
    }
    return out
}

fn main(): void {
    match with_levels([0, 3, 7], 10) {
        Ok(rows) => println("${rows.len()} matched")
        Err(e) => println("failed")
    }
}

That trades network and decode cost for flexibility, so keep the SQL predicate as selective as you can before falling back to it.

Ordering

Each key may carry ASC or DESC; keys are applied left to right.

schema shop

model Product {
    @id
    id: int
    name: string
    price: float
}

fn shelf(): []Product ! QueryError {
    return FROM Product ORDER BY price DESC, name ASC, id ASC?
}

fn main(): void {
    match shelf() {
        Ok(rows) => println("${rows.len()} on the shelf")
        Err(e) => println("failed")
    }
}

A key’s direction can also come from a runtime value: an expression of type SortDir (Asc/Desc), or a bool where true means descending. The compiler pre-renders one statement per direction combination and the guest picks — no SQL keyword is ever bound as data.

schema shop

model Product {
    @id
    id: int
    price: float
}

fn by_price(descending: bool): []Product ! QueryError {
    direction: SortDir = if descending { Desc } else { Asc }
    return FROM Product ORDER BY price direction, id ASC?
}

fn by_price_bool(descending: bool): []Product ! QueryError {
    return FROM Product ORDER BY price descending, id ASC?
}

fn main(): void {
    match by_price(true) { Ok(r) => println("${r.len()} desc") Err(e) => println("failed") }
    match by_price_bool(false) { Ok(r) => println("${r.len()} asc") Err(e) => println("failed") }
}

Because each runtime direction doubles the number of rendered statements, at most six such keys are allowed in one query:

schema shop

model Reading {
    @id
    a: int
    b: int
    c: int
    d: int
    e: int
    f: int
    g: int
}

fn q(d1: bool, d2: bool, d3: bool, d4: bool, d5: bool, d6: bool, d7: bool): []Reading ! QueryError {
    return FROM Reading ORDER BY a d1, b d2, c d3, d d4, e d5, f d6, g d7?
}

That reports ATOLL3242: too many runtime ORDER BY directions in one query.

Paging

LIMIT and OFFSET accept exactly two shapes: an integer literal, which is inlined into the rendered statement, or a bare local or parameter of integer type, which becomes a bound placeholder. Compute anything else into a local first:

schema shop

model Product {
    @id
    id: int
}

fn page(size: int, page_index: int): []Product ! QueryError {
    skip := page_index * size
    return FROM Product ORDER BY id LIMIT size OFFSET skip?
}

fn first_ten(): []Product ! QueryError {
    return FROM Product ORDER BY id LIMIT 10?
}

fn main(): void {
    match page(20, 3) { Ok(rows) => println("${rows.len()} on page 3") Err(e) => println("failed") }
    match first_ten() { Ok(rows) => println("${rows.len()}") Err(e) => println("failed") }
}

An arithmetic expression in the clause itself is neither of those two shapes, and the whole query fails to lower:

schema shop

model Product {
    @id
    id: int
}

fn page(size: int, page_index: int): []Product ! QueryError {
    return FROM Product ORDER BY id LIMIT size OFFSET page_index * size?
}

The types are checked too — a non-integer limit is a plain type error:

schema shop

model Product {
    @id
    id: int
}

fn f(): []Product ! QueryError {
    return FROM Product LIMIT "lots"?
}

Paging without a stable order can repeat or skip rows between executions, and validating an untrusted page size or offset is still your job: the compiler checks types and dialect support, not application range policy. For deep paging prefer a keyset predicate over an ever-growing OFFSET.

Guards

An if guard makes a clause conditional at run time while keeping one fixed statement. A guarded WHERE or HAVING compiles to a boolean parameter gate; a guarded static ORDER BY key compiles to a CASE gate.

schema shop

model Product {
    @id
    id: int
    category: string
    price: float
}

fn browse(wanted: string, filter_category: bool, dearest_first: bool): []Product ! QueryError {
    return FROM Product
        WHERE category == wanted if filter_category
        ORDER BY price DESC if dearest_first?
}

fn main(): void {
    // One rendered statement; the two calls differ only in bound gate values.
    match browse("tools", true, true) { Ok(r) => println("${r.len()} filtered") Err(e) => println("failed") }
    match browse("tools", false, false) { Ok(r) => println("${r.len()} unfiltered") Err(e) => println("failed") }
}

The guard is an ordinary Atoll bool expression, resolved strictly. It is not a preprocessor: the guarded clause is fully type-checked whether or not a particular call passes true.

schema shop

model Product {
    @id
    id: int
    price: float
}

fn f(n: int): []Product ! QueryError {
    return FROM Product WHERE price >= 1.0 if n?
}

schema shop

model Product {
    @id
    id: int
    price: float
}

fn f(): []Product ! QueryError {
    return FROM Product WHERE price >= 1.0 if enabled?
}

Guards that would change the shape of the statement are rejected, including conditional joins, conditional SET assignments, guarded set-operation ordering, and a runtime direction plus an if guard on the same key:

schema shop

model Product {
    @id
    id: int
    price: float
}

fn f(descending: bool, enabled: bool): []Product ! QueryError {
    return FROM Product ORDER BY price descending if enabled?
}

That reports ATOLL3242. When a condition changes row cardinality or statement shape, write two complete queries and choose between them with an ordinary if:

schema shop

model Category {
    @id
    id: int
    active: bool
}

model Product {
    @id
    id: int
    category_id: int
    price: float

    BELONGS TO category: Category VIA category_id
}

// Two whole queries, one `if`. Each renders its own statement.
fn listing(only_active: bool): []Product ! QueryError {
    if only_active {
        return FROM Product
            WHERE category_id in (FROM Category WHERE active SELECT { id })
            ORDER BY price?
    }
    return FROM Product ORDER BY price?
}

fn main(): void {
    match listing(true) { Ok(r) => println("${r.len()} active") Err(e) => println("failed") }
    match listing(false) { Ok(r) => println("${r.len()} all") Err(e) => println("failed") }
}

What can vary at run time

Compile time	Run time
table and column names	predicate values
operators and clause order	`LIMIT` / `OFFSET` values
SQL keywords	guard booleans
the number of `IN` placeholders	a bounded `ORDER BY` direction selector

Everything in the left column is baked into the registered statement, which is what keeps row decoding fixed and makes injection structurally impossible.

A composed example

A product search with an optional stock filter, a runtime sort direction, a subquery membership test, and a projection:

schema shop

model Category {
    @id
    id: int
    name: string
    active: bool
}

model Product {
    @id
    id: int
    category_id: int
    name: string
    price: float
    stock: int
    discontinued: bool

    BELONGS TO category: Category VIA category_id

    INDEX (category_id, price)
}

type Hit = { id: int, name: string, price: float }

fn search(
    text: string,
    ceiling: float,
    in_stock_only: bool,
    dearest_first: bool,
    size: int,
): []Hit ! QueryError {
    direction: SortDir = if dearest_first { Desc } else { Asc }
    return FROM Product
        WHERE discontinued == false
        WHERE name.contains(text)
        WHERE price <= ceiling
        WHERE stock > 0 if in_stock_only
        WHERE category_id in (FROM Category WHERE active SELECT { id })
        ORDER BY price direction, id ASC
        LIMIT size
        SELECT { id, name, price }?
}

fn main(): void {
    match search("widget", 50.0, true, false, 25) {
        Ok(hits) => {
            for hit in hits { println("${hit.name} at ${hit.price}") }
            println("${hits.len()} hits")
        }
        Err(e) => println("search failed")
    }
}

Two statements are rendered for that function — one per sort direction — and every other varying part rides a parameter.