Luminova Framework

Query Builder

Last updated: 2024-07-09 12:27:23

The Query Builder is a powerful ORM (Object-Relational Mapping) tool designed to simplify database interactions for developers. It enables you to work with higher-level objects and methods, abstracting the process of constructing SQL queries.

With its flexible CRUD (Create, Read, Update, Delete) functionality, you can effortlessly create complex SQL queries without dealing directly with SQL syntax. This allows you to focus more on building your applications and less on database queries.

To learn more about how to use the Query Builder, see examples.



Methods

db

Get database connection instance.

public db(): \Luminova\Instance\DatabaseInterface|null

Return Value:

DatabaseInterface|null - Return database connection instance based on used driver.

Return Raw Connection Object

To get the raw database connection instance of PDO or MYSQLI.

<?php 
$conn = $builder->db()->raw();

getInstance

To get a shared singleton instance of builder class.

public static getInstance(): static

Return Value:

static - Return shared instance of database builder class.

Throws:


table

To set database table name to use in execution.

public table(string $table, string $alias = ''): self

Parameters:

ParameterTypeDescription
$tablestringThe table name.
$aliasstringOptional table alias.

Return Value:

self - Return instance of builder class.


join

Specifies a table join operation for table(), to join query.

public join(string $table, string $type = 'INNER', string $alias = ''): self

Parameters:

ParameterTypeDescription
$tablestringThe name of the table to join.
$typestringThe type of join (default: "INNER").
$aliasstringOptional alias for the joined table (optional).

Return Value:

self - Return instance of builder class.


on

Specifies join conditions to query table, the on method can be called multiple times with different arguments, each call to on sets a condition for the query.

public on(string $condition, string $operator = '=', mixed $value = null): self

Parameters:

ParameterTypeDescription
$conditionstringJoin condition or column name.
$operatorstringJoin operator (default: '=').
$valuemixedValue to bind to the condition or another table column.

Return Value:

self - Return instance of builder class.


innerJoin

This method os shorthand for join method using inner join table operation.

public innerJoin(string $table, string $alias = ''): self

Parameters:

ParameterTypeDescription
$tablestringThe table name to join.
$aliasstringThe table join alias.

Return Value:

self - Return instance of builder class.


leftJoin

This method os shorthand for join method using left join table operation.

public leftJoin(string $table, string $alias = ''): self

Parameters:

ParameterTypeDescription
$tablestringThe table name to join.
$aliasstringThe table join alias.

Return Value:

self - Return instance of builder class.


rightJoin

This method os shorthand for join method using right join table operation.

public rightJoin(string $table, string $alias = ''): self

Parameters:

ParameterTypeDescription
$tablestringThe table name to join.
$aliasstringThe table join alias.

Return Value:

self - Return instance of builder class.


crossJoin

This method os shorthand for join method using cross join table operation.

public crossJoin(string $table, string $alias = ''): self

Parameters:

ParameterTypeDescription
$tablestringThe table name to join.
$aliasstringThe table join alias.

Return Value:

self - Return instance of builder class.


limit

Set the limit and offset for result query execution, while calling methods like total, select fetch, count, sum and average.

public limit(int $limit, int $offset): self

Parameters:

ParameterTypeDescription
$limitintThe limit threshold.
$offsetintThe start offset.

Return Value:

self - Return instance of builder class.


max

Set the maximin record to execute when called update or deletemethod.

public max(int $limit): self

Parameters:

ParameterTypeDescription
$limitintmaximum number of records to update or delete

Return Value:

self - Return instance of builder class.


order

Set result return order for query selection (e.g., "id ASC", "date DESC").The order Method can be called multiple times with different arguments to allows chaining of multiple query orders.

public order(string $column, string $order = 'ASC'): self

Parameters:

ParameterTypeDescription
$columnstringThe column name to set the order for.
$orderstringThe order algorithm to use (either "ASC" or "DESC")

Return Value:

self - Return instance of builder class.


orderByMatch

Set the result return order when match against method is called.

public orderByMatch(array $columns, string|int|float $value, string $mode = 'NATURAL_LANGUAGE', string $order = 'ASC'): self

Parameters:

ParameterTypeDescription
$columnsarrayThe column names to index match order.
$valuestring|int|floatThe value to match against in order.
$modestringThe comparison match mode operator.
Optionally you can choose any of these modes or pass your own mode.
- NATURAL_LANGUAGE
- BOOLEAN
- NATURAL_LANGUAGE_WITH_QUERY_EXPANSION
- WITH_QUERY_EXPANSION
$orderstringThe order algorithm to use (either "ASC" or "DESC").

Return Value:

self - Returns an instance of the class.


group

Set query grouping for the SELECT statement.The group Method can be called multiple times with different arguments to allows chaining of multiple query grouping.

public group(string $group): self

Parameters:

ParameterTypeDescription
$groupstringThe column name to group by.

Return Value:

self - Return instance of builder class.


where

Set query where condition for select, find, stmt, update, delete, sum, total or average methods execution.

public where(string $column, string $operator, mixed $key): self

Parameters:

ParameterTypeDescription
$columnstringThe column name.
$operatorstringThe comparison Operator.
$keymixedThe column value.

Return Value:

self - Return instance of builder class.


and

Set query AND conditions for statement execution.The and method can be called multiple times with different arguments to allows chaining of multiple query conditions.

public and(string $column, string $operator, mixed $value): self

Parameters:

ParameterTypeDescription
$columnstringcolumn name
$operatorstringComparison operator
$valuemixedcolumn key value

Return Value:

self - Return instance of builder class.


against

The against method allows you to perform database querying with the match operator for efficient matching against specified columns. It sets the query match columns and mode. It also support chaining multiple against conditions.

public against(array $columns, string $mode, mixed $value): self

Parameters:

ParameterTypeDescription
$columnsarrayThe column names to match against.
$modestringThe comparison match mode operator.
$valuemixedThe value to match against.

Modes:

You can use any of the following matching modes or pass your own mode:

  • NATURAL_LANGUAGE: This mode provides a natural language search experience.
  • BOOLEAN: This mode enables Boolean search capabilities.
  • NATURAL_LANGUAGE_WITH_QUERY_EXPANSION: This mode extends the NATURAL_LANGUAGE mode with query expansion.
  • WITH_QUERY_EXPANSION: This mode extends the standard search with query expansion.

Return Value:

self - Return instance of builder class.


set

To stage the table column name and value which will be use when called update method.The set Method can be called multiple times with different arguments to allows chaining of multiple query update key-values.

public set(string $column, mixed $value): self

Parameters:

ParameterTypeDescription
$columnstringThe column name.
$valuemixedThe new value to update.

Return Value:

self - Return instance of builder class.


or

To set query OR condition operator for execution.The or method can be called multiple times with different arguments to allows chaining of multiple query OR conditions.

public or(string $column, string $operator, mixed $value): self

Parameters:

ParameterTypeDescription
$columnstringThe column name.
$operatorstringThe comparison operator.
$valuemixedThe column value.

Return Value:

self - Return instance of builder class.


orGroup

To set an OR group of conditions (e.g (foo = 1 OR bar = 2)).

public orGroup(array $conditions): self

Parameters:

ParameterTypeDescription
$conditionsarrayArray of conditions to be grouped with OR.

Return Value:

self - Return instance of builder class.


andGroup

Adds a group of conditions combined with AND to the query.

public andGroup(array $conditions): self

Parameters:

ParameterTypeDescription
$conditionsarrayArray of conditions to be grouped with AND.

Return Value:

self - Return instance of builder class.


Group Example

Here is an example of how you can implement orGroup and andGroup.

<?php 
$builder->orGroup([
    ['column1' => ['operator' => '=', 'value' => 1]],
    ['column2' => ['operator' => '=', 'value' => 2]]
]);
<?php 
$builder->andGroup([
    ['column1' => ['operator' => '=', 'value' => 1]],
    ['column2' => ['operator' => '=', 'value' => 2]]
]);

Note:

The binding columns must be the array key while the child elements must have only 2 index with keys operator and value.


orBind

To bind multiple OR group conditions and combined to a single group (e.g: ((foo = 1 OR bar = 2) OR (baz = 3 OR bra = 4))).

public orBind(array $group1, array $group2, string $bind = 'OR'): self

Parameters:

ParameterTypeDescription
$group1arrayFirst group of conditions.
$group2arraySecond group of conditions.
$bindstringThe type of logical operator to use in binding groups together (default: 'OR').
- AND or OR.

Return Value:

self - Return instance of builder class.


andBind

Adds two groups of conditions combined with AND condition.The andBind is similar with orBind, except that it uses AND operator to bind group conditions before combining to a single group (e.g: ((foo = 1 AND bar = 2) OR (baz = 3 AND bra = 4))).

public andBind(array $group1, array $group2, string $bind = 'AND'): self

Parameters:

ParameterTypeDescription
$group1arrayFirst group of conditions.
$group2arraySecond group of conditions.
$bindstringThe type of logical operator to use in binding groups together (default: 'AND').
- AND or OR.

Return Value:

self - Return instance of builder class.


Binds Group Example

Here is an example of how you can implement orBind and andBind.

<?php 
$builder->orBind([
    ['column1' => ['operator' => '=', 'value' => 1]],
    ['column2' => ['operator' => '=', 'value' => 2]]
],
[
    ['column3' => ['operator' => '=', 'value' => 3]],
    ['column4' => ['operator' => '=', 'value' => 4]]
]);
<?php 
$builder->andBind([
    ['column1' => ['operator' => '=', 'value' => 1]],
    ['column2' => ['operator' => '=', 'value' => 2]]
],
[
    ['column3' => ['operator' => '=', 'value' => 3]],
    ['column4' => ['operator' => '=', 'value' => 4]]
]);

Note:

The binding columns must be the parent array key while the child elements should only have 2 elements with key operator and value.


in

Set query where IN operator expression, allows you to specify array values to search.

public in(string $column, array $lists = []): self

Parameters:

ParameterTypeDescription
$columnstringcolumn name
$listsarray<int, mixed>Array values to search

Return Value:

self - Return instance of builder class.


inset

Set query operator FIND_IN_SET() expression.

public inset(string $search, string $operator = '=', array $list = []): self

Parameters:

ParameterTypeDescription
$searchstringThe string search search in set.
$operatorstringallow specifying the operator for matching (e.g., > or =)
$listarray<int,mixed>The array values to find in set.

Return Value:

self - Return instance of builder class.


returns

To change the result return type to either object or array, call this method before finally executing fetch, find or select method. The default result response is object.

public returns(string $type): self

Parameters:

ParameterTypeDescription
$typestringThe result return type object or array.

Return Value:

self - Return instance of builder class.

Throws:


debug

To enable query string debugging, this allow you to preview the compiled query string ready to be executed.

public debug(): self

Return Value:

self - Return instance of builder class.

If this method is called in a production environment, the query string will be logged using the debug level.Additionally when debug is enabled, execution will always fail and caching will be disabled.


printDebug

To get the query string debug information, this method will return an array containing the query string in mysql and pdo placeholder format. Additionally it will show the exactly the binding mapping for every column in the query string.

public printDebug(): array<string,mixed>;

Return Value:

array<string,mixed> - Return array containing query information.


datetime

Get date/time format for storing SQL.

public static datetime(string $format = 'datetime', ?int $timestamp = null): string

Parameters:

ParameterTypeDescription
$formatstringFormat to return default is datetime.
Available time formats.
- time - Return time format from timestamp
- datetime - Return SQL datetime format
- date - Return SQL date format.
$timestampint|nullOptional timestamp

Return Value:

string - Returns formatted date/time/timestamp.


caching

The caching method provides the flexibility to enable or disable all caches universally. This is particularly useful for debugging or temporarily disabling database caching throughout your application without the need to manually remove cache() method calls from your all code.

To disable all database caching, simply call this method with a false parameter.

public caching(bool $enable): self

Parameters:

ParameterTypeDescription
$enableboolThe caching status action

Return Value:

self - Return instance of builder class.

By default caching is enabled once you call cache method.


cache

To cache query result response, and return cached data when next user request the same content.In other for caching to work, you must call cache method before select, find, execute, fetch, sum, average or total method is called.

public cache(string $key, string $storage = null, DateTimeInterface|int $expiry = 7 * 24 * 60 * 60, string|null $folder = null): self

Parameters:

ParameterTypeDescription
$keystringThe cache storage key
$storagestringOptional storage name, but it's recommended to void storing large data in one file.
$expiryDateTimeInterface|intThe cache expiry time in seconds or datetime interface (default: 7 days).
$folderstring|nullOptionally set a sub folder name to store caches.

Return Value:

self - Return instance of builder class.

Using stmt method for returning preferred statement object doesn't support caching.To use cache, you will need to manually implement it.


insert

To Insert records into a database table, either by passing an array of key-value pairs with values, or by using the set() method to specify the data to update.

Additionally the insert method supports inserting array value to database, but The array will be JSON encoded, if wish to insert a serialized string, you should do that before passing to insert

public insert(array<string,mixed> $values, bool $prepare = true): int

Parameters:

ParameterTypeDescription
$valuesarray<string,mixed>The array of values to insert into table.
$prepareboolUse bind values and execute prepare statement instead of query

Return Value:

int - Return number of affected rows.


select

To select multiple records from table, call select method after you have prepared other conditional methods.

public select(array<int,string> $columns = ['*']): mixed

Parameters:

ParameterTypeDescription
$columnsarray<int,string>Array of column names to select.

Return Value:

mixed - Return selected rows, otherwise false on failure.


fetch

The fetch method allows you to retrieve records from table, by passing desired fetch mode and result type.

public fetch(string $result = 'all', int $mode = FETCH_OBJ, array<int,string> $columns = ['*']): object|null|array|int|float|bool

Parameters:

ParameterTypeDescription
$resultstringThe fetch result type (next or all).
$modeintThe fetch result mode FETCH_*.
$columnsarray<int,string>The table columns to return (default: *).

Return Value:

object|null|array|int|float|bool - Return selected records, otherwise false on failure.

Throws:


stmt

Returns query prepared statement based on build up method conditions.

public stmt(array<int,string> $columns = ['*']): \Luminova\Interface\DatabaseInterface;

Note:

To return prepared statement object don't need to call any of these methods total, sum, find, select, average, fetch or execute, calling them will result in stmt returning NULL.

Parameters:

ParameterTypeDescription
$columnsarray<int,string>The table columns to return (default: *).

Return Value:

\Luminova\Interface\DatabaseInterface - Return prepared statement object if query is successful otherwise null.

Example

To learn more about methods included in statement object see Database driver documentation.

<?php
$tbl = $builder->table('users');
$tbl->where('pref_code', '=', 'PHP');
$stmt = $tbl->stmt();

// Do what you want with statement object 
var_export($stmt->getAll());
$stmt->free();

query

Build a custom SQL query string to execute when calling the execute method.This allows you to execute more customized query string, it also support caching result response.

public query(string $query): self

Parameters:

ParameterTypeDescription
$querystringSQL query string

Return Value:

self - Return instance of builder class.

Throws:

Example

The below example show how you can use custom query builder to create and execute your SQL queries.

<?php
$query = $builder->query("
    UPDATE addresses 
    SET address_default = (address_id = :address_id) 
    WHERE address_customer_id = :customer_id
");
$response = $query->execute([
    'address_id' => $id,
    'customer_id' => $customer_id
]);

execute

To execute an SQL query string that was previously constructed in the query method, tenable caching of query result, you must call cache method before calling the execute method.

Optionally, it allows you to pass an array of placeholders, of key-value pairs with values to be executed with query.The key will be usage as placeholder name while value is the value to bind to the placeholder.

It uses prepared statements if placeholder array is passed, otherwise, it will fallback to query execution, so ensure that values passed directly to the query are escaped.

public execute(?array $placeholder = null, int $mode = RETURN_ALL): mixed

Parameters:

ParameterTypeDescription
$bindsarray<string,mixed>|nullThe placeholder and value to binds with query string (default: NULL).
$modeintResult return mode RETURN_* (default: RETURN_ALL).

Return Value:

mixed - Return result or prepared statement depending on $mode otherwise false on failed.

Modes

Throws:


find

Select next or a single record from database table.

public find(array<int,string> $columns = ['*']): mixed

Parameters:

ParameterTypeDescription
$columnsarray<int,string>select columns to return

Return Value:

mixed - returns selected row or false on failure.


total

To calculate the total number of records table,

public total(string $column = '*'): int|bool

Parameters:

ParameterTypeDescription
$columnstringThe column to index calculation (default: *).

Return Value:

int|bool - Return total number of records in table, otherwise false if execution failed.


sum

To calculate a total sum of a numeric column in the table.

public sum(string $column): int|float|bool

Parameters:

ParameterTypeDescription
$columnstringThe column to index summing

Return Value:

int|float|bool - Return total sum columns, otherwise false if execution failed.

**

average

To calculate the average value of a numeric column in the table.

public average(string $column): int|float|bool

Parameters:

ParameterTypeDescription
$columnstringThe column to calculate the average.

Return Value:

int|float|bool - Return total average of columns, otherwise false if execution failed.


update

To update database table records with a new data.You can pass an array of column key pairs with values to update method or use the set method stage your columns and values to update.

public update(array<string,mixed> $setValues = []): int|bool

Parameters:

ParameterTypeDescription
$setValuesarray<string,mixed>Optionally pass an associative array of columns and values to update

Return Value:

int - Return number of affected rows.

Throws:

Note:

Passing an array value to update field will be automatically convert to JSON string, so ensure that your table field is designed to accept json string before passing an array.


delete

To delete record from database table.

public delete(): int

Return Value:

int - Return number of affected rows.


errors

Get query execution errors.

public errors(): array

Return Value:

array - Returns array of error information


transaction

Begins a transaction with optional read-only isolation level and SAVEPOINT for PDO.

public transaction(int $flags = 0, ?string $name = null): bool

Parameters:

ParameterTypeDescription
$flagsintOptional flags to set transaction properties. (Default: 0).
No predefined flags for PDO, specify 4 to create read-only isolation.
$namestring|nullOptional name.
If provided in PDO, SAVEPOINT will be created with name instead.

Return Value:

bool - Return true if transaction started successfully, otherwise false.

Throws:

Note:

  • If $flags is set to 4 in PDO, which is equivalent to MYSQLI_TRANS_START_READ_ONLY, a read-only isolation level will be established. If the transaction starts successfully, it will return true.

  • If $name is specified in PDO, a SAVEPOINT will be created. If the savepoint creation is successful, the transaction will return true.


commit

Commits a transaction.

public commit(int $flags = 0, ?string $name = null): bool

Parameters:

ParameterTypeDescription
$flagsintOptional flags to set transaction properties. (Default: 0).
Only supported in MySQLi.
$namestring|nullOptional name.
Only supported in MySQLi.

Return Value:

bool - Returns true if the transaction was successfully committed, otherwise false.


rollback

Rolls back the current transaction or to a specific name while in PDO uses SAVEPOINT.

public rollback(int $flags = 0, ?string $name = null): bool

Parameters:

ParameterTypeDescription
$flagsintOptional flags to set transaction properties. (Default: 0).
Only supported in MySQLi.
$namestring|nullOptional name.
If provided in PDO, rolls back to the SAVEPOINT named

Return Value:

bool - Return true if rolled back was successful, otherwise false.

Throws:


truncate

Delete all records in a table, and reset table auto increment to 1.

public truncate(bool $transaction = true): bool

Parameters:

ParameterTypeDescription
$transactionboolWeather to use transaction (default: true).

Return Value:

bool - Return true truncation was completed, otherwise false.

Throws:


exec

Execute an SQL query string and return the number of affected rows.

public exec(string $query): int

Parameters:

ParameterTypeDescription
$querystringThe SQL query string to execute.

Return Value:

int - Return number affected rows.

Throws:


drop

To drop current database table.

public drop(): int

Return Value:

int - Return number affected rows.

Throws:


manager

Retrieves the database manager class shared instance, initialized with the current database connection.

public manager(): \Luminova\Database\Manager

Return Value:

\Luminova\Database\Manager - Database manager class instance.

See Also:

Database Manager - Database manager documentation.


export

Exports a database table and downloads it to the browser as JSON or CSV format.

public export(string $as = 'csv', string|null $filename = null, array $columns = ['*']): bool

Parameters:

ParameterTypeDescription
$asstringExport as csv or json format.
$filenamestring|nullFilename to download.
$columnsarrayTable columns to export (default: all).

Return Value:

bool - Return true if export is successful, false otherwise.

Throws:


backup

Creates a backup for the database.

public backup(string|null $filename = null): bool

Parameters:

ParameterTypeDescription
$filenamestring|nullFilename to store the backup.

Return Value:

True if backup is successful, false otherwise.

Throws:


dump

Debug dump statement information for the last statement execution for debugging.

public dump(): bool|null

Return Value:

bool|null - Returns trues else false or null on failure.


reset

Reset query conditions, parameters and Free database resources ready to execute next query without interruption.

public reset(): void

free

Frees up the statement cursor and sets the statement object to null.

public free(): void

close

Close database connection and free statement cursor.

public close(): void