Optional filters: WHERE { }

type Colony {
	name:   Str
	apiary: Str
	frames: Int
}

query findColonies(apiary: Str, minFrames: Int, small: Bool, huge: Bool): Colony[dyn] {
	SELECT name, apiary, frames FROM colonies
	WHERE {
		if apiary != ""  { apiary = {apiary} }
		if minFrames > 0 { frames >= {minFrames} }
		or {
			if small { frames < 4 }
			if huge  { frames > 10 }
		}
	}
	ORDER BY name
}

proc main(): void {
	opened := hive.sql.pool(hive.sql.DatabaseDriver.SQLite(), "file::memory:?cache=shared", 1, 1)
	if opened is Result.Ok(db) {
		using db run raw "CREATE TABLE colonies (name TEXT, apiary TEXT, frames INTEGER)"
		using db run raw "INSERT INTO colonies VALUES ('north', 'orchard', 12)"
		using db run raw "INSERT INTO colonies VALUES ('south', 'orchard', 2)"
		using db run raw "INSERT INTO colonies VALUES ('east', 'meadow', 7)"

		// Everything off: no predicate holds, so there is no WHERE at all.
		if using db run findColonies("", 0, false, false) is Result.Ok(all) {
			echo len(all)
		}
		// apiary AND frames >= 5 AND (frames > 10).
		if using db run findColonies("orchard", 5, false, true) is Result.Ok(some) {
			echo some
		}
		hive.sql.close(db)
	}
}

A search screen with four boxes on it is four optional predicates, and assembling that as text is where SQL injection and "WHERE 1 = 1" both come from. A WHERE block is the declaration that replaces it: it ANDs together the predicates whose conditions hold, and a nested or { } or and { } flips the connective for the group inside it.A group that contributes nothing disappears rather than leaving a dangling connective, a group contributing more than one predicate is parenthesised, and when no predicate holds at all there is no WHERE clause in the statement — so there is no 1 = 1 to write and none to read back in a log. Every branch's text is fixed at compile time; the only thing decided while the program runs is which branches are taken. The values still travel as bound parameters, exactly as they do in a plain query.Two things a where block deliberately cannot do. A column name or a sort direction can never be a parameter — ORDER BY {col} would order every row by one constant string — so make that choice a variant type and dispatch to one query per ordering. And SQL you genuinely do assemble yourself goes through run raw instead, which is untyped by construction and greppable by design.

← Prev Next →