Skip to content

Writing Queries

Jump to bottom
John James Jacoby edited this page Jul 9, 2026 · 8 revisions

Writing Queries

Query is the class application code uses most often. It is similar in spirit to WP_Query: pass arguments in, receive shaped results out.

Basic Reads

$query = new Acme\Database\WidgetQuery();

$widgets = $query->query(
	array(
		'status'  => 'active',
		'orderby' => 'created',
		'order'   => 'DESC',
		'number'  => 20,
	)
);

Get One Item

$widget = $query->get_item( 123 );

Use get_item_by() for cache-key columns or other supported single-column lookups:

$widget = $query->get_item_by( 'uuid', $uuid );

Create

$id = $query->add_item(
	array(
		'name'   => 'Example Widget',
		'status' => 'active',
	)
);

Columns marked created, modified, or uuid receive special handling.

Update

$updated = $query->update_item(
	$id,
	array(
		'status' => 'archived',
	)
);

Only known schema columns are saved. Column validation and formatting come from the Column definition.

Copy

$copy_id = $query->copy_item(
	$id,
	array(
		'name' => 'Copied Widget',
	)
);

UUID columns are regenerated rather than copied.

Delete

$deleted = $query->delete_item( $id );

Common Query Vars

$query->query(
	array(
		'fields'        => 'ids',
		'number'        => 50,
		'offset'        => 0,
		'orderby'       => 'id',
		'order'         => 'ASC',
		'no_found_rows' => false,
	)
);

fields => 'ids' returns item IDs. An empty fields value returns shaped row objects.

count => true returns a count rather than rows.

__in and __not_in

Columns marked with in or not_in support list queries:

$query->query(
	array(
		'id__in' => array( 5, 9, 13 ),
	)
);
$query->query(
	array(
		'status__not_in' => array( 'deleted', 'spam' ),
	)
);

The by Container

by is a shorthand that groups several {column}__in filters under one key. Each column => value(s) entry folds to the canonical {column}__in var - a scalar becomes a single-element list, an array is used as-is:

$query->query(
	array(
		'by' => array(
			'status' => 3,             // -> status__in => array( 3 )
			'type'   => array( 1, 2 ), // -> type__in   => array( 1, 2 )
		),
	)
);

Only columns declared in => true can be folded; a non-in-filterable column is logged and ignored. An empty entry value ('', array(), or null) is skipped, and an explicit top-level {column}__in always wins over the same column's by entry. A non-array by is malformed (logged and ignored); the container itself is always consumed, never sent to the database.

Search

Columns marked searchable can participate in search queries:

$query->query(
	array(
		'search' => 'blue widget',
	)
);

Search columns can be filtered by parser hooks in custom query classes.

Date Queries

Columns marked date_query support date-style clauses:

$query->query(
	array(
		'created_query' => array(
			array(
				'after'     => '2026-01-01',
				'before'    => '2026-12-31',
				'inclusive' => true,
			),
		),
	)
);

Meta Queries

BerlinDB includes a meta parser for applications that use a meta-table pattern. Configure the query class and schema so the parser knows the primary table and related meta table.

Comparison Queries

compare_query builds a single WHERE comparison beyond the direct-column and __in shortcuts:

$query->query(
	array(
		'compare_query' => array(
			'key'     => 'priority',
			'compare' => '>=',
			'value'   => 40,
		),
	)
);
// ... WHERE priority >= 40

Operators include =, !=, <, <=, >, >=, IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE, REGEXP, IS NULL, IS NOT NULL, and their negations. Whole parser buckets combine with OR / NOT through the criteria boolean tree.

A group of clauses joins with a relation of AND (all match), OR (any match), or XOR (chained exclusive-or — matches when an odd number are true; for a two-clause group, exactly one):

'compare_query' => array(
	'relation' => 'XOR',
	array( 'key' => 'status', 'value' => 'active' ),
	array( 'key' => 'priority', 'compare' => '>=', 'value' => 20 ),
),
// ... WHERE ( status = 'active' XOR priority >= 20 )

An unrecognized relation falls back to AND.

Expression Operands

Either side of a compare_query may be a structured operand instead of a bare value — a column reference, an allow-listed function, a list, a range, or a row-constructor tuple. Each is an array carrying an operand key, resolved against the query's own schema, and fails closed (matches no rows) if it can't resolve. There is no raw-SQL passthrough.

Compare two columns:

'compare_query' => array(
	'key'     => 'updated',
	'compare' => '>',
	'value'   => array( 'operand' => 'column', 'name' => 'created' ),
),
// ... WHERE updated > created

Wrap a column in a function (LOWER, UPPER, LENGTH, ABS, DATE, YEAR, MONTH, DAYOFMONTH, DAYOFYEAR, DAYOFWEEK, WEEKDAY, WEEK, HOUR, MINUTE, SECOND, DATE_FORMAT, COALESCE, GREATEST, LEAST, CONCAT, CONCAT_WS, NOW, DATE_SUB, DATE_ADD). Position (key vs value) picks the side:

'compare_query' => array(
	'key'     => array(
		'operand' => 'func',
		'name'    => 'LOWER',
		'args'    => array( array( 'operand' => 'column', 'name' => 'name' ) ),
	),
	'compare' => '=',
	'value'   => 'acme',
),
// ... WHERE LOWER(name) = 'acme'

COALESCE is variadic (two or more arguments) and returns the first non-NULL one. Having no type of its own, the placeholder a bare scalar compares against is derived from its arguments — the common type when they agree, a string placeholder when they mix:

'compare_query' => array(
	'key'     => array(
		'operand' => 'func',
		'name'    => 'COALESCE',
		'args'    => array(
			array( 'operand' => 'column', 'name' => 'display_name' ),
			array( 'operand' => 'column', 'name' => 'user_login' ),
			'guest',
		),
	),
	'compare' => '=',
	'value'   => 'acme',
),
// ... WHERE COALESCE(display_name, user_login, 'guest') = 'acme'

A list (IN), a range (BETWEEN), or a tuple whose members are themselves operands — so a list can mix columns, functions, and values, and a tuple compares multiple columns at once:

// status IN ( default_status, 'active' )
'value' => array( 'operand' => 'list', 'items' => array(
	array( 'operand' => 'column', 'name' => 'default_status' ),
	'active',
) ),

// ( a, b ) IN ( ( 1, 2 ), ( 3, 4 ) )
'key'     => array( 'operand' => 'tuple', 'items' => array(
	array( 'operand' => 'column', 'name' => 'a' ),
	array( 'operand' => 'column', 'name' => 'b' ),
) ),
'compare' => 'IN',
'value'   => array( 'operand' => 'list', 'items' => array(
	array( 'operand' => 'tuple', 'items' => array( 1, 2 ) ),
	array( 'operand' => 'tuple', 'items' => array( 3, 4 ) ),
) ),

The plain 'value' => array( 1, 2, 3 ) bare list still works and is simpler for scalars. Operands pair by shape and width, so a width or shape mismatch (say a tuple against a scalar) fails closed rather than emitting invalid SQL.

Any scalar operand (column, value, function, or a nested cast) can carry a cast key that wraps it in CAST( ... AS <type> ) — so you can cast an arbitrary expression, not just a column:

'compare_query' => array(
	'key'     => array(
		'operand' => 'func',
		'name'    => 'LOWER',
		'args'    => array( array( 'operand' => 'column', 'name' => 'name' ) ),
		'cast'    => 'CHAR',
	),
	'compare' => '=',
	'value'   => 'acme',
),
// ... WHERE CAST(LOWER(name) AS CHAR) = 'acme'

The target is validated against a safe subset (BINARY, CHAR(n), DATE, DATETIME, TIME, SIGNED, UNSIGNED, DECIMAL(p,s)). A cast composes as a function argument and is checked against the function's accepted categories (a DATE cast is fine for YEAR()). A cast on a list/range/tuple, an invalid target, or cast => true on a non-column fails closed. The compared scalar derives its placeholder from the target: SIGNED → %d, everything else → %s.

A math operand does infix arithmetic (+ - * /) over scalar operands, parenthesized and nestable — the plain operator/value model can't express this:

'compare_query' => array(
	'key'     => array(
		'operand'  => 'math',
		'operator' => '*',
		'operands' => array(
			array( 'operand' => 'column', 'name' => 'price' ),
			array( 'operand' => 'column', 'name' => 'quantity' ),
		),
	),
	'compare' => '>',
	'value'   => 100,
),
// ... WHERE ( price * quantity ) > 100

The result is numeric (%d, or %f for division / a float member). Fewer than two members or an unknown operator fails closed.

Relative dates: NOW(), DATE_SUB / DATE_ADD, and an interval operand compose "the last 30 days" with no PHP-side timestamp math:

'compare_query' => array(
	'key'     => 'date_created',
	'compare' => '>',
	'value'   => array(
		'operand' => 'func',
		'name'    => 'DATE_SUB',
		'args'    => array(
			array( 'operand' => 'func', 'name' => 'NOW' ),
			array( 'operand' => 'interval', 'value' => 30, 'unit' => 'DAY' ),
		),
	),
),
// ... WHERE date_created > DATE_SUB(NOW(), INTERVAL 30 DAY)

The interval amount is integer-cast and the unit is allow-listed (SECOND … YEAR); an interval is only valid inside DATE_SUB / DATE_ADD (anywhere else it fails closed).

Aggregates

The aggregate container computes SQL aggregates over the matched (and filtered) rows in one cached query, returning values instead of item rows:

$totals = $query->query(
	array(
		'aggregate' => array(
			'revenue' => array( 'sum', 'amount' ),   // alias => array( function, column )
			'orders'  => array( 'count', '*' ),       // COUNT(*)
			'peak'    => array( 'max', 'created' ),
		),
		'status'    => 'complete',                     // aggregates honor your filters
	)
);
// array( 'revenue' => '1234.50', 'orders' => 42, 'peak' => '2026-06-...' )

Functions are sum, avg, max, min, and count (array( 'count', '*' ) for COUNT(*), or the named form array( 'function' => 'count', 'column' => 'col', 'distinct' => true ) for COUNT(DISTINCT col)). An empty set is null per alias, except count, which is 0.

Add a groupby column for one row per group, order by an alias or group column, and filter groups by their results with having:

$rows = $query->query(
	array(
		'aggregate' => array( 'revenue' => array( 'sum', 'amount' ) ),
		'groupby'   => 'status',
		'having'    => array( 'revenue' => array( '>', 1000 ) ),
		'orderby'   => 'revenue',
		'order'     => 'DESC',
	)
);
// one row per status whose revenue exceeds 1000, highest first

The get_sum() / get_avg() / get_max() / get_min() methods are the shortcut for a single ungrouped aggregate.

Index Hints

index_hints passes MySQL optimizer hints (USE / FORCE / IGNORE INDEX) through to the table reference. A hint only changes the query plan, never which rows come back, so it is a RESULTS_INVARIANT var (it does not participate in the cache key) and it fails open - anything malformed is dropped and logged rather than failing the query. Pass a single spec or a list of them:

$query->query(
	array(
		'index_hints' => array(
			'type'    => 'use',                // 'use' | 'force' | 'ignore'
			'indexes' => array( 'status_type' ), // declared index names, or 'primary'
			'for'     => 'order by',           // '' | 'join' | 'order by' | 'group by'
		),
	)
);

Index names are validated against the schema's declared indexes plus PRIMARY (which also closes off injection); an unknown name is dropped, and a spec left with no valid index is dropped entirely. type must be one of use / force / ignore. The for scope is optional and accepts join, order by, or group by (the aliases orderby / groupby are folded in); an unknown scope coerces to none. MySQL forbids mixing USE and FORCE on one table reference, so the first of the two seen wins and a later conflicting spec is dropped - IGNORE always coexists.

Extending Query

Most projects extend Query only by setting protected properties. Override methods when you need custom parser registration, custom cache behavior, or application-specific helpers.

Keep application convenience functions outside the query class when possible:

function acme_get_widget( int $id ) {
	$query = new Acme\Database\WidgetQuery();
	return $query->get_item( $id );
}

Clone this wiki locally