---

QueryBuilder

• Full name: \Qubus\Expressive\QueryBuilder
• This class implements: \IteratorAggregate, \Stringable, \Qubus\Expressive\Database

Constants

Constant	Visibility	Type	Value
OPERATOR_AND	public		' AND '
OPERATOR_OR	public		' OR '
ORDERBY_ASC	public		'ASC'
ORDERBY_DESC	public		'DESC'
JOIN_INNER	public		'INNER'
JOIN_OUTER	public		'OUTER'
JOIN_LEFT	public		'LEFT'
JOIN_RIGHT	public		'RIGHT'
JOIN_RIGHT_OUTER	public		'RIGHT OUTER'
JOIN_LEFT_OUTER	public		'LEFT OUTER'
EOL	public		"\n"
TAB	public		"\t"
EOL_TAB	public		"\n\t"

Properties

instance

protected static ?\Qubus\Expressive\QueryBuilder $instance

• This property is static.

---

connection

protected \Qubus\Expressive\Connection|null $connection

---

tableName

protected ?string $tableName

---

tableToken

protected string $tableToken

---

tableAlias

protected string $tableAlias

---

isSingle

protected bool $isSingle

---

pdoStmt

protected ?\PDOStatement $pdoStmt

---

selectFields

protected array $selectFields

---

joinSources

protected array $joinSources

---

limit

protected ?int $limit

---

offset

protected ?int $offset

---

orderBy

protected array $orderBy

---

groupBy

protected array $groupBy

---

whereParameters

protected array $whereParameters

---

whereConditions

protected array $whereConditions

---

andOrOperator

protected string $andOrOperator

---

having

protected array $having

---

returning

protected ?string $returning

---

upsert

protected ?array $upsert

---

wrapOpen

protected bool $wrapOpen

---

lastWrapPosition

protected int $lastWrapPosition

---

isFluentQuery

protected bool $isFluentQuery

---

pdoExecuted

protected bool $pdoExecuted

---

data

protected array $data

---

debugSqlQuery

protected bool $debugSqlQuery

---

sqlQuery

protected string $sqlQuery

---

sqlParameters

protected array $sqlParameters

---

dirtyFields

protected array $dirtyFields

---

referenceKeys

protected array $referenceKeys

---

joinOn

protected bool $joinOn

---

references

protected static array $references

• This property is static.

---

tablePrefix

protected ?string $tablePrefix

---

schema

protected ?\Qubus\Expressive\Schema $schema

---

tableStructure

public array $tableStructure

---

Methods

__construct

Constructor & set the table structure

public __construct(\Qubus\Expressive\Connection $connection, string|null $tablePrefix = null, string $primaryKeyName = 'id', string $foreignKeyName = '%s_id'): mixed

Parameters:

Parameter	Type	Description
$connection	\Qubus\Expressive\Connection	Database connection.
$tablePrefix	string|null	Prefix of database tables.
$primaryKeyName	string	Structure: table primary key. If it's an array, it must be the structure
$foreignKeyName	string	Structure: table foreignKeyName. It can be like %s_id where %s is the table name

---

fromInstance

public static fromInstance(\Qubus\Expressive\Connection $connection, string $primaryKeyName = 'id', ?string $tablePrefix = null): static

• This method is static.

Parameters:

Parameter	Type	Description
$connection	\Qubus\Expressive\Connection
$primaryKeyName	string
$tablePrefix	?string

---

table

Define the working table and create a new instance

public table(string $tableName, ?string $alias = null): static

Parameters:

Parameter	Type	Description
$tableName	string	Table name.
$alias	?string	The table alias name.

---

getTableName

Return the name of the table.

public getTableName(): string

---

setTableAlias

Set the table alias.

public setTableAlias(string $alias): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$alias	string

---

getTableAlias

Get table Alias

public getTableAlias(): string

---

setStructure

public setStructure(string $primaryKeyName = 'id', string $foreignKeyName = '%s_id'): \Qubus\Expressive\Database

Parameters:

Parameter	Type	Description
$primaryKeyName	string	The primary key, ie: id
$foreignKeyName	string	The foreign key as a pattern: %s_id, where %s will be substituted with the table name

---

setTablePrefix

public setTablePrefix(?string $tablePrefix = ''): static

Parameters:

Parameter	Type	Description
$tablePrefix	?string

---

getTablePrefix

Return the table prefix.

public getTablePrefix(): ?string

---

getStructure

Return the table structure.

public getStructure(): array

---

getPrimaryKeyname

Get the primary key name.

public getPrimaryKeyname(): string

---

getForeignKeyname

Get foreign key name.

public getForeignKeyname(): string

---

isSingleRow

Return if the entry is of a single row

public isSingleRow(): bool

---

query

To execute a raw query

public query(string $query, array $parameters = [], bool $returnAsPdoStmt = false): \Qubus\Expressive\Database|\PDOStatement

Parameters:

Parameter	Type	Description
$query	string
$parameters	array
$returnAsPdoStmt	bool	True, it will return the PDOStatement false, it will return $this, which can be used for chaining or access the properties of the results.

---

rowCount

Return the number of affected row by the last statement

public rowCount(): int

---

find

To find all rows and create their instances Use the query builder to build the where clause or $this->query with select If a callback function is provided, the 1st arg must accept the rows results

public find(?callable $callback = null): bool|\SplFixedArray|string|\ArrayIterator|\InternalIterator|array

Parameters:

Parameter	Type	Description
$callback	?callable	Run a function on the returned rows

---

findOne

Return one row

public findOne(int|string|null $id = null): \Qubus\Expressive\Database|false

Parameters:

Parameter	Type	Description
$id	int|string|null	Use to fetch by primary key.

---

getIterator

This method allow the iteration inside foreach().

public getIterator(): \ArrayIterator

---

fromArray

Create an instance from the given row (an associative array of data fetched from the database).

public fromArray(array $data): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$data	array

---

select

Create the select clause.

public select(mixed $columns = '*', ?string $alias = null): \Qubus\Expressive\Database

Parameters:

Parameter	Type	Description
$columns	mixed	The column(s) to select. Can be string or array of fields.
$alias	?string	An alias to the column.

---

where

Add where condition, more calls appends with AND.

public where(mixed $condition, mixed $parameters = null): \Qubus\Expressive\Database

Parameters:

Parameter	Type	Description
$condition	mixed	condition possibly containing ? or :name
$parameters	mixed	array accepted by PDOStatement::execute or a scalar value.

---

and

Create an AND operator in the where clause

public and(): \Qubus\Expressive\QueryBuilder

---

or

Create an OR operator in the where clause

public or(): \Qubus\Expressive\QueryBuilder

---

wrap

To group multiple where clauses together.

public wrap(): \Qubus\Expressive\QueryBuilder

---

wherePK

Where Primary key

public wherePK(int|string $id): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$id	int|string

---

whereNot

WHERE $columName != $value

public whereNot(string $columnName, mixed $value): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string
$value	mixed

---

whereLike

WHERE $columName LIKE $value

public whereLike(string $columnName, mixed $value): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string
$value	mixed

---

whereNotLike

WHERE $columName NOT LIKE $value

public whereNotLike(string $columnName, mixed $value): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string
$value	mixed

---

whereGt

WHERE $columName > $value

public whereGt(string $columnName, mixed $value): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string
$value	mixed

---

whereGte

WHERE $columName >= $value

public whereGte(string $columnName, mixed $value): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string
$value	mixed

---

whereLt

WHERE $columName < $value

public whereLt(string $columnName, mixed $value): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string
$value	mixed

---

whereLte

WHERE $columName <= $value

public whereLte(string $columnName, mixed $value): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string
$value	mixed

---

whereIn

WHERE $columName IN (?,?,?,...)

public whereIn(string $columnName, array $values): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string
$values	array

---

whereNotIn

WHERE $columName NOT IN (?,?,?,...)

public whereNotIn(string $columnName, array $values): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string
$values	array

---

whereNull

WHERE $columName IS NULL

public whereNull(string $columnName): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string

---

whereNotNull

WHERE $columName IS NOT NULL

public whereNotNull(string $columnName): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string

---

having

public having(mixed $statement, mixed $operator = self::OPERATOR_AND): static

Parameters:

Parameter	Type	Description
$statement	mixed
$operator	mixed

---

orderBy

ORDER BY $columnName (ASC | DESC)

public orderBy(string $columnName, string $ordering = ''): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string	- The name of the colum or an expression
$ordering	string	(DESC | ASC)

---

groupBy

GROUP BY $columnName

public groupBy(string $columnName): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$columnName	string

---

limit

LIMIT $limit

public limit(int|null $limit = null): \Qubus\Expressive\QueryBuilder|int|null

Parameters:

Parameter	Type	Description
$limit	int|null

---

offset

OFFSET $offset

public offset(int|null $offset = null): \Qubus\Expressive\QueryBuilder|int|null

Parameters:

Parameter	Type	Description
$offset	int|null

---

pagination

public pagination(int $perPage, int $page): $this

Parameters:

Parameter	Type	Description
$perPage	int
$page	int

---

join

Build a join

public join(string $tableName, string $constraint, string $tableAlias = '', string $joinOperator = self::JOIN_LEFT): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$tableName	string
$constraint	string	-> id = profile.user_id
$tableAlias	string	- The alias of the table name
$joinOperator	string	- LEFT | INNER | etc...

---

on

An alias to join by using a QueryBuilder instance.

public on(\Qubus\Expressive\QueryBuilder $query, string $joinOperator = self::JOIN_LEFT): \Qubus\Expressive\QueryBuilder

The QueryBuilder instance may have select and where statement for the ON clause

Parameters:

Parameter	Type	Description
$query	\Qubus\Expressive\QueryBuilder
$joinOperator	string

---

getSelectQuery

Return the built select query

public getSelectQuery(): string

---

schema

The associated schema instance.

public schema(): \Qubus\Expressive\Schema

---

getSelectString

Get the select fields as string for SQL.

public getSelectString(): string

---

getSelectFields

Return the select fields as array.

public getSelectFields(): array

---

getJoinString

Get a JOIN string.

public getJoinString(): string

---

getGroupbyString

Get the group by string.

public getGroupbyString(): string

---

getOrderbyString

Get the order by string.

public getOrderbyString(): string

---

getWhereString

Build the WHERE clause(s).

public getWhereString(): string

---

getJoinOnString

Create the JOIN ... ON string when there is a join. It will be called by on().

public getJoinOnString(): string

---

getHavingString

Return the HAVING clause.

protected getHavingString(): string

---

getWhereParameters

Return the values to be bound for where.

protected getWhereParameters(): array

---

setSingleWhere

Detect if it's a single row instance and reset it to PK.

protected setSingleWhere(): \Qubus\Expressive\QueryBuilder

---

resetWhere

Reset the where.

protected resetWhere(): \Qubus\Expressive\QueryBuilder

---

returning

public returning(string $cols = '*'): static

Parameters:

Parameter	Type	Description
$cols	string

---

upsert

public upsert(array $conflictCols, array $updateData): static

Parameters:

Parameter	Type	Description
$conflictCols	array
$updateData	array

---

lastInsertId

Retrieves the ID of the last record inserted.

public lastInsertId(string|null $pk = null): string|false

Parameters:

Parameter	Type	Description
$pk	string|null

---

insert

Insert new rows $data can be 2-dimensional to add a bulk insert If a single row is inserted, it will return its row instance

public insert(array $data): \Qubus\Expressive\Database|int

Parameters:

Parameter	Type	Description
$data	array	- data to populate

---

update

Update entries Use the query builder to create the where clause.

public update(?array $data = null): \Qubus\Expressive\Database|int|false

Parameters:

Parameter	Type	Description
$data	?array	the data to update

---

delete

Delete rows.

public delete(bool $deleteAll = false): \Qubus\Expressive\Database|int|false

Parameters:

Parameter	Type	Description
$deleteAll	bool	When there is no where condition, setting to true will delete all.

---

beginTransaction

Initiates a transaction.

public beginTransaction(): bool

---

inTransaction

Checks if inside transaction.

public inTransaction(): bool

---

commit

Commits a transaction

public commit(): bool

---

rollBack

Rolls back a transaction.

public rollBack(): bool

---

transactional

Run transactional queries.

public transactional(\Closure $callback, mixed $that = null, mixed $default = null): mixed

Parameters:

Parameter	Type	Description
$callback	\Closure	transaction callback
$that	mixed
$default	mixed

---

set

To set data for update or insert $key can be an array for mass set

public set(mixed $key, mixed|null $value = null): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$key	mixed
$value	mixed|null

---

save

Save, a shortcut to update() or insert().

public save(): \Qubus\Expressive\QueryBuilder|int|bool|static

---

count

Return the aggregate count of column

public count(string|null $column = null): float|int

Parameters:

Parameter	Type	Description
$column	string|null	- the column name

---

max

Return the aggregate max count of column

public max(string $column): float|int

Parameters:

Parameter	Type	Description
$column	string	- the column name

---

min

Return the aggregate min count of column

public min(string $column): float|int

Parameters:

Parameter	Type	Description
$column	string	- the column name

---

sum

Return the aggregate sum count of column

public sum(string $column): float|int

Parameters:

Parameter	Type	Description
$column	string	- the column name

---

avg

Return the aggregate average count of column

public avg(string $column): float|int

Parameters:

Parameter	Type	Description
$column	string	- the column name

---

aggregate

public aggregate(string $fn): float|int

Parameters:

Parameter	Type	Description
$fn	string	- The function to use for the aggregation

---

getPK

Return the primary key.

public getPK(): int|string|null

---

get

Get the key

public get(string $key): mixed

Parameters:

Parameter	Type	Description
$key	string

---

toArray

Return the raw data of this single instance.

public toArray(): array

---

__get

public __get(mixed $key): mixed

Parameters:

Parameter	Type	Description
$key	mixed

---

__set

public __set(mixed $key, mixed $value): mixed

Parameters:

Parameter	Type	Description
$key	mixed
$value	mixed

---

__isset

public __isset(mixed $key): mixed

Parameters:

Parameter	Type	Description
$key	mixed

---

__call

Association / Load

public __call(string $tablename, array $args): mixed

__call() will load a table by association or return the table object itself

To dynamically call a table

$db = new QueryBuilder($myPDO); on table 'users' $Users = $db->table("users");

Or to call a table association on table 'photos' where users can have many photos $allMyPhotos = $Users->findOne(1234)->photos();

Association allow you to associate the current table with another by using foreignKey and localKey. The data is eagerly loaded hence only making one round to the table to retrieve the data matching the foreign and protected keys foreign and protected keys are cached for subsequent queries, the keys are selected based on the foreignKeyname pattern. i.e: having the keys: id, user_id, friend_id, name, last_name id, user_id, friend_id will be cached so they can be queried upon request

Parameters:

Parameter	Type	Description
$tablename	string
$args	array	foreignKey localKey where sort callback model backref

---

reset

Reset fields

public reset(): \Qubus\Expressive\QueryBuilder

---

now

Return an Immutable YYYY-MM-DD HH:II:SS date format

public static now(string $datetime = 'now'): string

• This method is static.

Parameters:

Parameter	Type	Description
$datetime	string	- An english textual datetime description now, yesterday, 3 days ago, +1 week http://php.net/manual/en/function.strtotime.php

Return Value:

YYYY-MM-DD HH:II:SS

Throws:

• Exception

---

debugSqlQuery

To debug the query. It will not execute it but instead using debugSqlQuery() and getSqlParameters to get the data

public debugSqlQuery(bool $bool = true): \Qubus\Expressive\QueryBuilder

Parameters:

Parameter	Type	Description
$bool	bool

---

getSqlQuery

Get the SQL Query with

public getSqlQuery(): string

---

getSqlParameters

Return the parameters of the SQL

public getSqlParameters(): array

---

__clone

public __clone(): mixed

---

__toString

public __toString(): string

---

makePlaceholders

Return a string containing the given number of question marks, separated by commas. Eg "?, ?, ?"

protected makePlaceholders(int $numberOfPlaceholders = 1): string

Parameters:

Parameter	Type	Description
$numberOfPlaceholders	int	- total of placeholder to insert.

---

formatKeyname

Format the table{Primary|Foreign}KeyName

protected formatKeyname(string $pattern, string $tablename): string

Parameters:

Parameter	Type	Description
$pattern	string
$tablename	string

---

tokenize

To create a string that will be used as key for the relationship.

protected tokenize(string $key, string $suffix = ''): string

Parameters:

Parameter	Type	Description
$key	string
$suffix	string

---

isArrayMultiDim

Check if array is multi dim.

protected isArrayMultiDim(array $data): bool

Parameters:

Parameter	Type	Description
$data	array

---

prepareColumns

Prepare columns to include the table alias name.

protected prepareColumns(array $columns): array

Parameters:

Parameter	Type	Description
$columns	array

---

prepareColumn

protected prepareColumn(string $column): string

Parameters:

Parameter	Type	Description
$column	string

---

formatColumnName

Format a column name to add to the table alias.

public formatColumnName(string $column): string

Parameters:

Parameter	Type	Description
$column	string

---

---

Automatically generated on 2025-10-13

2025-10-132025-10-13