SQL

SQL query builder for constructing and managing SQL statements in a structured, programmatic way.

class SQL implements \Hazaar\DBI\Interface\QueryBuilder

Implements: Hazaar\DBI\Interface\QueryBuilder

Supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE, and advanced SQL features such as joins, unions, conflict handling, and more.

Properties

type

The type of SQL query being constructed (SELECT, INSERT, etc.).

protected QueryType $type = 'QueryType'

Type: Hazaar\DBI\DBD\Enums\QueryType

selectGroups

protected array $selectGroups

Type: array

quoteSpecial

The character used to quote SQL identifiers (e.g., table or column names).

protected string $quoteSpecial = '"'

Type: string

reservedWords

protected array $reservedWords

Type: array

supportsIlike

Whether the target driver has a native case-insensitive LIKE (Postgres's ILIKE). Drivers

protected bool $supportsIlike = true

Type: bool

without one (e.g. SQLite) fall back to plain LIKE for the $ilike criteria operator - see setSupportsIlike(). Defaults to true so any backend that doesn't opt out keeps today's behaviour of emitting ILIKE unconditionally.

select

The list of columns to select in the query.

protected mixed $select

Type: mixed

This array holds the column names or expressions that will be included in the SELECT clause of the generated SQL statement.

fields

The fields to update or insert.

protected mixed $fields

Type: mixed

This can be a bunch of things, like an array, strings, Table or even a Model or stdClass.

primaryTable

protected array $primaryTable

Type: array

tables

protected array $tables

Type: array

where

protected array $where

Type: array

group

protected array $group

Type: array

having

protected array $having

Type: array

window

protected array $window

Type: array

joins

protected array $joins

Type: array

combine

protected array $combine

Type: array

unions

protected array $unions

Type: array

order

protected array $order

Type: array

fetch

protected array $fetch

Type: array

returning

The columns or expressions to return from an INSERT, UPDATE, or DELETE statement.

protected mixed $returning

Type: mixed

cascade

Whether to use CASCADE for TRUNCATE statements.

protected bool $cascade

Type: bool

distinct

protected array|bool $distinct

Type: array|bool

limit

public int $limit

Type: int

offset

public int $offset

Type: int

conflictTarget

protected array|string|null $conflictTarget

Type: array|string|null

conflictUpdate

protected array|bool|null $conflictUpdate

Type: array|bool|null

schemaName

public string $schemaName

Type: string

valueIndex

private array $valueIndex

Type: array

Methods

__construct

Constructs a new SQL query builder instance.

public __construct(?string $schemaName): void

Parameters

Parameter	Type	Description
$schemaName	string	optional schema name to use for table references

__toString

Returns the SQL query as a string, terminated with a semicolon.

public __toString(): string

Returns: string

setReservedWords

Sets the list of reserved SQL words for identifier quoting.

public setReservedWords(array $words): void

Returns: void

Parameters

Parameter	Type	Description
$words	array	list of reserved words

setSupportsIlike

Sets whether the target driver supports a native case-insensitive LIKE (ILIKE).

public setSupportsIlike(bool $supported): void

Returns: void

Parameters

Parameter	Type	Description
$supported	bool	true if the driver understands ILIKE natively

getSchemaName

Gets the schema name used for table references.

public getSchemaName(): ?string

Returns: string

parseSchemaName

public parseSchemaName(string $tableName): array

Returns: array

Parameters

Parameter	Type	Description
$tableName	string

normalizeTableName

Normalises a user-supplied table reference to a string.

private normalizeTableName(array|string $tableName): string

Returns: string

A [schema, table] array is collapsed to a schema.table string so it flows through the builder (and introspection via parseSchemaName()) as an explicit, quoted cross-schema reference. Passing the schema as an array element lets it come from application config rather than being hard-coded into a dotted string.

Parameters

Parameter	Type	Description
$tableName	array	string

schemaName

public schemaName(array|string $tableName): string

Returns: string

Parameters

Parameter	Type	Description
$tableName	array	string

quote

Quotes a string value for use in SQL queries, optionally escaping special characters.

public quote(string $string, bool $addSlashes = true): string

Returns: string

Parameters

Parameter	Type	Description
$string	string	the string to quote
$addSlashes	bool	whether to escape special characters

quoteSpecial

Quotes SQL identifiers (such as table or column names) using the configured quote character.

public quoteSpecial(mixed $value): mixed

Returns: mixed

Parameters

Parameter	Type	Description
$value	mixed	the identifier to quote

reset

Resets the query builder to its initial state, clearing all query components.

public reset(): self

Returns: self

create

Generates a CREATE statement for the specified object type and name.

public create(string $name, string $type, bool $ifNotExists): string

Returns: string

Parameters

Parameter	Type	Description
$name	string	The name of the object to create (table, index, etc.).
$type	string	The type of object to create (e.g., 'table', 'index').
$ifNotExists	bool	whether to include IF NOT EXISTS in the statement

insert

Sets the query type to INSERT and assigns the fields to insert.

public insert(mixed $fields): self

Returns: self

Parameters

Parameter	Type	Description
$fields	mixed	The fields to insert (array, Table, Model, etc.).

update

Sets the query type to UPDATE and assigns the fields to update.

public update(array $fields): self

Returns: self

Parameters

Parameter	Type	Description
$fields	array	the fields to update

delete

Sets the query type to DELETE.

public delete(): self

Returns: self

truncate

Sets the query type to TRUNCATE and optionally enables CASCADE.

public truncate(bool $cascade): self

Returns: self

Parameters

Parameter	Type	Description
$cascade	bool	whether to use CASCADE for the TRUNCATE statement

count

Sets the SELECT clause to COUNT(*) and returns the resulting SQL string.

public count(): string

Returns: string

exists

Generates a SELECT EXISTS query for the specified table and criteria.

public exists(string $tableName, null|array|string $criteria): string

Returns: string

Parameters

Parameter	Type	Description
$tableName	string	the table to check for existence
$criteria	null	array

select

Sets the query type to SELECT and assigns the columns to select.

public select(mixed $columns): self

Returns: self

Parameters

Parameter	Type	Description
$columns	mixed

distinct

Selects only distinct rows that match based on the specified expressions.

public distinct(string $columns): self

Returns: self

Parameters

Parameter	Type	Description
$columns	string

from

Sets the table to select from, with optional alias.

public from(array|string $table, ?string $alias): self

Returns: self

Parameters

Parameter	Type	Description
$table	array	string
$alias	string	optional alias for the table

table

Sets the primary table to use for INSERT, UPDATE or DELETE.

public table(array|string $table, ?string $alias): self

Returns: self

Parameters

Parameter	Type	Description
$table	array	string
$alias	string	An optional alias for the table

where

Defines a WHERE selection criteria.

public where(array|string $criteria): self

Returns: self

Parameters

Parameter	Type	Description
$criteria	array	string

group

Sets the GROUP BY columns for the query.

public group(string $columns): self

Returns: self

Parameters

Parameter	Type	Description
$columns	string

having

Sets the HAVING clause for the query.

public having(array $columns): self

Returns: self

Parameters

Parameter	Type	Description
$columns	array	the columns or conditions for the HAVING clause

window

public window(string $name, string $partitionBy, array|string|null $orderBy): self

Returns: self

Parameters

Parameter	Type	Description
$name	string
$partitionBy	string
$orderBy	array	string

join

Adds a JOIN clause to the query.

public join(array|string $references, array|string|null $on, ?string $alias, string $type = 'INNER'): self

Returns: self

Parameters

Parameter	Type	Description
$references	array	string
$on	array	string
$alias	string	optional alias for the joined table
$type	string	The type of join (e.g., 'INNER', 'LEFT').

union

Adds a UNION clause to the query with another QueryBuilder instance.

public union(QueryBuilder $query): self

Returns: self

Parameters

Parameter	Type	Description
$query	Hazaar\DBI\Interface\QueryBuilder	the query to union with

unionAll

Adds a UNION ALL clause to the query with another QueryBuilder instance.

public unionAll(QueryBuilder $query): QueryBuilder

Returns: Hazaar\DBI\Interface\QueryBuilder

Parameters

Parameter	Type	Description
$query	Hazaar\DBI\Interface\QueryBuilder	the query to union all with

intersect

Adds an INTERSECT clause to the query with another QueryBuilder instance.

public intersect(QueryBuilder $query): self

Returns: self

Parameters

Parameter	Type	Description
$query	Hazaar\DBI\Interface\QueryBuilder	the query to intersect with

except

Adds an EXCEPT clause to the query with another QueryBuilder instance.

public except(QueryBuilder $query): self

Returns: self

Parameters

Parameter	Type	Description
$query	Hazaar\DBI\Interface\QueryBuilder	the query to except with

order

Sets the ORDER BY clause for the query.

public order(array|string $fieldDef, int $sortDirection = 'SORT_ASC'): self

Returns: self

Parameters

Parameter	Type	Description
$fieldDef	array	string
$sortDirection	int	the sort direction (SORT_ASC or SORT_DESC)

limit

Sets or gets the LIMIT value for the query.

public limit(?int $limit): int|self

Returns: int|self

Parameters

Parameter	Type	Description
$limit	int	the limit value to set, or null to get the current value

offset

Sets or gets the OFFSET value for the query.

public offset(?int $offset): int|self

Returns: int|self

Parameters

Parameter	Type	Description
$offset	int	the offset value to set, or null to get the current value

toString

Return the current selection as a valid SQL string.

public toString(bool $terminateWithColon): string

Returns: string

Parameters

Parameter	Type	Description
$terminateWithColon	bool

field

Quotes a field name if it is a reserved word.

public field(string $string): string

Returns: string

Parameters

Parameter	Type	Description
$string	string	the field name

getSelectGroups

public getSelectGroups(): array

Returns: array

prepareFields

public prepareFields(mixed $fields, array $exclude, array $tables): string

Returns: string

Parameters

Parameter	Type	Description
$fields	mixed
$exclude	array
$tables	array

prepareValue

Prepares a value for use in a SQL statement, handling special value actions and parameter indexing.

public prepareValue(string $key, mixed $value): mixed

Returns: mixed

Parameters

Parameter	Type	Description
$key	string	the parameter key
$value	mixed	the value to prepare

prepareArrayAliases

public prepareArrayAliases(array $array): array

Returns: array

Parameters

Parameter	Type	Description
$array	array

prepareCriteria

public prepareCriteria(array|string $criteria, string $bindType = 'AND', string $tissue = '=', ?string $parentRef, bool $setKey = true, int $depth): string

Returns: string

Parameters

Parameter	Type	Description
$criteria	array	string
$bindType	string
$tissue	string
$parentRef	string
$setKey	bool
$depth	int

getCriteriaValues

Retrieves the criteria values by combining the provided parameter values with the default values.

public getCriteriaValues(): array

Returns: array

Iterates over the internal value index and, for each key and its associated values, constructs a criteria value array. If a value for the key exists in the provided $paramValues array, it is used; otherwise, the default value is used.

normalizeCriteriaValue

Normalizes a raw or BoundValue entry from the value index into a value/type pair

private normalizeCriteriaValue(mixed $value): array

Returns: array

ready for parameter binding.

Parameters

Parameter	Type	Description
$value	mixed

paramType

Determines the PDO parameter type for a raw (non-BoundValue) value being bound.

private paramType(mixed $value): int

Returns: int

PDO::PARAM_STR causes the driver to bind the value's string form. For a native PHP bool that silently does the wrong thing on strict-typed backends like Postgres: (string) false is '', which Postgres rejects for a boolean column/expression ("invalid input syntax for type boolean"), while (string) true happens to be '1', which Postgres accepts -- so the bug only ever surfaces for false values, never true ones. Callers that already have a native bool (the overwhelmingly common case -- eg; ['active' => false]) shouldn't need to reach for the ['$bool' => $value] value action just to bind correctly.

Parameters

Parameter	Type	Description
$value	mixed

prepareCriteriaValues

Prepares criteria values for use in SQL queries by mapping parameter values to indexed keys.

public prepareCriteriaValues(array $paramValues): array

Returns: array

Parameters

Parameter	Type	Description
$paramValues	array

returning

public returning(mixed $columns): self

Returns: self

Parameters

Parameter	Type	Description
$columns	mixed

onConflict

public onConflict(array|string|null $target, array|bool|null $update): self

Returns: self

Parameters

Parameter	Type	Description
$target	array	string
$update	array	bool

prepareValueAction

private prepareValueAction(string $action, mixed $value): mixed

Returns: mixed

Parameters

Parameter	Type	Description
$action	string
$value	mixed

formatDateTimeValue

Normalizes and formats a date/time value for SQL binding.

private formatDateTimeValue(mixed $value, string $format = 'Y-m-d H:i:s'): string

Returns: string

The single place responsible for turning a date/time value into its bound string form, whether it arrives as a raw DateTimeInterface column value (see prepareValue()) or via an explicit $date/$datetime value action. Anything that isn't already a DateTimeInterface is first passed through Hazaar\Util\DateTime's constructor (accepts Unix timestamps and strtotime()-style strings).

Parameters

Parameter	Type	Description
$value	mixed
$format	string

formatArrayValue

Encodes a PHP array as a Postgres array literal (eg; {a,b,c}) for text[]/int[]/etc columns.

private formatArrayValue(array $value): string

Returns: string

Strings are quoted and escaped; null becomes NULL; booleans become true/false; nested arrays recurse into nested literals.

Parameters

Parameter	Type	Description
$value	array

prepareCriteriaAction

private prepareCriteriaAction(string $action, mixed $value, string $key, ?string $tissue = '=', bool $setKey = true, int $depth): array|string|null

Returns: array|string|null

Parameters

Parameter	Type	Description
$action	string
$value	mixed
$key	string
$tissue	string
$setKey	bool
$depth	int

tables

private tables(): array

Returns: array

joinExistsClause

Builds an EXISTS(...) fragment scoping a DELETE/UPDATE by this query's joins.

private joinExistsClause(): ?string

Returns: string

Table::join() lets a DELETE/UPDATE be scoped by a related table's columns -- e.g. deleting rows from project_parts for every project owned by a user, via ->join('projects', 'project_parts.project_uuid=projects.uuid')->delete(['projects.owner_uuid' => $uuid]). Unlike SELECT, DELETE/UPDATE have no portable way to reference a second table directly (Postgres's USING/FROM extensions aren't supported by SQLite, and there's no per-driver branching in this class), so each joined table plus the accumulated WHERE criteria are folded into a correlated EXISTS subquery instead -- semantically equivalent to an INNER JOIN for filtering purposes, and valid on every supported driver.

Returns null when no joins are registered, so callers fall back to their existing behaviour (including the older $this->tables-based USING/FROM handling) untouched.

prepareOrder

private prepareOrder(array|string $orderDefinition): string

Returns: string

Parameters

Parameter	Type	Description
$orderDefinition	array	string

toSELECTString

private toSELECTString(): string

Returns: string

toINSERTString

private toINSERTString(): string

Returns: string

toUPDATEString

private toUPDATEString(): string

Returns: string

toDELETEString

private toDELETEString(): string

Returns: string

toTRUNCATEString

private toTRUNCATEString(): string

Returns: string

---

Generated by Hazaar API Doc Generator on Fri, 04 Sep 2026 23:32:16 +0000