DBI Query Actions

About 3 min

DBI Query Actions

Overview

Hazaar DBI supports a criteria action syntax for building WHERE and HAVING clauses using structured arrays. Actions are operator keys prefixed with $ (for example, $or, $in, $gte).

These actions are interpreted by the SQL query builder in Hazaar\DBI\QueryBuilder\SQL.

Basic Criteria

Simple key/value criteria use equality by default:

$criteria = [
	'id' => 10,
	'status' => 'active',
];

This maps to SQL like:

id = :id0 AND status = :status0

Action Syntax

Use actions inside the criteria array:

$criteria = [
	'$or' => [
		['status' => 'active'],
		['status' => ['$ne' => 'archived']],
	],
	'age' => ['$gte' => 18],
	'deleted_at' => null,
];

Supported Actions

Logical

$and: combine sub-criteria with AND.
$or: combine sub-criteria with OR.
$not: negate a nested criteria block.

[
	'$and' => [
		['status' => 'active'],
		['age' => ['$gte' => 18]],
	],
]

[
	'$or' => [
		['role' => 'admin'],
		['role' => 'editor'],
	],
]

[
	'$not' => [
		'status' => 'archived',
	],
]

Comparison

$ne: not equal (!=) for most values, and IS NOT for boolean values.
$gt: greater than (>).
$gte: greater than or equal (>=).
$lt: less than (<).
$lte: less than or equal (<=).
$bt: between two values (BETWEEN ... AND ...). Requires exactly 2 values.

[
	'score' => ['$gt' => 100],
	'rank' => ['$lte' => 5],
	'created_at' => ['$bt' => ['2026-01-01', '2026-12-31']],
]

Membership

$in: value is in a list.
$nin: value is not in a list.

[
	'status' => ['$in' => ['active', 'pending']],
	'id' => ['$nin' => [1, 2, 3]],
]

$in and $nin require a non-empty array when array input is used.

Pattern Matching

$like: SQL LIKE.
$ilike: SQL ILIKE (case-insensitive in PostgreSQL).

[
	'email' => ['$ilike' => '%@example.com'],
	'name' => ['$like' => 'A%'],
]

Regular Expressions

These actions map directly to PostgreSQL regex operators:

$~: regex match (case-sensitive)
$~*: regex match (case-insensitive)
$!~: regex non-match (case-sensitive)
$!~*: regex non-match (case-insensitive)

[
	'username' => ['$~*' => '^[a-z0-9_]+$'],
]

Null Checks

$null: field IS NULL
$notnull: field IS NOT NULL

[
	'$null' => 'deleted_at',
]

[
	'$notnull' => 'published_at',
]

You can also use plain null values directly:

[
	'deleted_at' => null,
]

Reference Comparison

$ref: compare to another SQL reference/expression instead of a bound value.

[
	'users.created_at' => ['$ref' => 'orders.created_at'],
]

This produces a reference comparison rather than a parameterized literal.

EXISTS

$exists: build one or more EXISTS (SELECT * FROM ... WHERE ...) predicates.

[
	'$exists' => [
		'orders' => [
			'orders.user_id' => ['$ref' => 'users.id'],
			'orders.status' => 'paid',
		],
	],
]

Subquery

$sub: compare against a subquery and nested criteria.

Expected structure:

Index 0: a query builder object for the subquery.
Index 1: criteria applied after the subquery expression.

[
	'id' => [
		'$sub' => [
			$subQuery,
			['$in' => [1, 2, 3]],
		],
	],
]

Nesting Actions

Actions can be nested to express complex conditions:

$criteria = [
	'$and' => [
		['status' => ['$in' => ['active', 'pending']]],
		[
			'$or' => [
				['total' => ['$gt' => 1000]],
				['priority' => 'high'],
			],
		],
		['deleted_at' => null],
	],
];

Using Actions with Adapter and Table APIs

Use the same criteria action format with either the adapter helper methods or the fluent Table API.

Adapter API Examples

Use actions directly in find():

$db = Hazaar\DBI\Adapter::create();

$result = $db->find('users', [
	'$or' => [
		['status' => 'active'],
		['last_login' => ['$lt' => '2026-01-01']],
	],
	'id' => ['$nin' => [1, 2, 3]],
]);

while ($row = $result->fetch()) {
	// Process rows
}

Approximate SQL WHERE clause:

(
	( status = :status0 )
	OR
	( last_login < :last_login0 )
)
AND id NOT IN (:id0, :id1, :id2)

Use actions in findOne() with selected columns:

$user = $db->findOne(
	'users',
	[
		'email' => ['$ilike' => 'admin@%'],
		'deleted_at' => null,
	],
	['id', 'email', 'role']
);

Approximate SQL WHERE clause:

email ILIKE :email0 AND deleted_at IS NULL

Use actions in update() and delete() criteria:

$updated = $db->update(
	'users',
	['status' => 'disabled'],
	[
		'failed_logins' => ['$gte' => 5],
		'status' => ['$ne' => 'disabled'],
	]
);

$deleted = $db->delete('sessions', [
	'expires_at' => ['$lt' => date('Y-m-d H:i:s')],
]);

Approximate SQL WHERE clauses:

failed_logins >= :failed_logins0 AND status != :status0

expires_at < :expires_at0

Table API Examples

Use where() with actions in fluent queries:

$result = $db->table('orders')
	->select(['id', 'user_id', 'total', 'created_at'])
	->where([
		'$and' => [
			['status' => ['$in' => ['paid', 'shipped']]],
			['total' => ['$bt' => [100, 1000]]],
			['created_at' => ['$gte' => '2026-01-01']],
		],
	])
	->order(['created_at' => SORT_DESC])
	->limit(50)
	->find();

Approximate SQL WHERE clause:

status IN (:status0, :status1)
AND total BETWEEN :total0 AND :total1
AND created_at >= :created_at0

Use references and existence checks:

$result = $db->table('users')
	->select(['users.id', 'users.email'])
	->where([
		'$exists' => [
			'orders' => [
				'orders.user_id' => ['$ref' => 'users.id'],
				'orders.status' => 'paid',
			],
		],
	])
	->find();

Approximate SQL WHERE clause:

EXISTS (
	SELECT *
	FROM orders
	WHERE orders.user_id = users.id AND orders.status = :orders_status0
)

Use actions with table update() criteria:

$updated = $db->table('products')->update(
	['is_active' => false],
	[
		'stock' => ['$lte' => 0],
		'discontinued_at' => null,
	]
);

Approximate SQL WHERE clause:

stock <= :stock0 AND discontinued_at IS NULL

Notes and Validation Rules

Unknown $... action keys are not interpreted as operators and may produce invalid SQL.
$in/$nin throw an exception if provided an empty array.
$bt throws an exception unless exactly 2 values are provided.
All literal values are parameterized via named placeholders (for example :status0, :id1).