Query Builder

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.

• Class namespace: \Luminova\Database\Builder
• Parent class namespace: \Luminova\Database\Connection

Methods

db

Get database connection instance.

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

Return Value:

\Luminova\Interface\DatabaseInterface|null - Return database driver instance.

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 new static instance of builder class.

Throws:

• \Luminova\Exceptions\DatabaseException - If the database connection fails.

table

Sets the database table name to build query for.

public table(string $table, string|null $alias = null): self

Parameters:

Return Value:

self - Returns the instance of builder class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - Throws if an invalid table name is provided.

join

To join main table table() with another using JOIN operators (e.g INNER JOIN, FULL JOIN).

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

Parameters:

Return Value:

self - Returns the instance of builder class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - Throws if invalid argument is provided.

Join Types

• INNER - Returns rows with matching values in both tables.
• LEFT - Returns all rows from the left table and matching rows from the right table, or NULLs for non-matching rows from the right table.
• RIGHT - Returns all rows from the right table and matching rows from the left table, or NULLs for non-matching rows from the left table.
• CROSS - Returns the Cartesian product of the two tables.
• FULL - Returns rows with matching values in either table, with NULLs for non-matching rows from either table.
• FULL OUTER - Returns all rows when there is a match in either the left or right table, or NULL from the side that does not have a match.

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): self

Parameters:

Return Value:

self - Returns the instance of builder class.

innerJoin

Sets table join condition as INNER JOIN.

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

Parameters:

Return Value:

self - Returns the instance of the class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - Throws if invalid argument is provided.

leftJoin

Sets table join condition as LEFT JOIN.

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

Parameters:

Return Value:

self - Returns the instance of the class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - Throws if invalid argument is provided.

rightJoin

Sets table join condition as RIGHT JOIN.

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

Parameters:

Return Value:

self - Returns the instance of the class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - Throws if invalid argument is provided.

crossJoin

Sets table join condition as CROSS JOIN.

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

Parameters:

Return Value:

self - Returns the instance of the class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - Throws if invalid argument is provided.

fullJoin

Sets table join condition as FULL JOIN.

public fullJoin(string $table, string|null $alias = null): self

Parameters:

Return Value:

self - Returns the instance of the class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - Throws if invalid argument is provided.

fullOuterJoin

Sets table join condition as FULL OUTER JOIN.

public fullOuterJoin(string $table, string|null $alias = null): self

Parameters:

Return Value:

self - Returns the instance of the class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - Throws if invalid argument is provided.

limit

Set the limit and offset for SELECT query execution.This method can be called when working while calling methods like total, select fetch, count, sum and average.

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

Parameters:

Return Value:

self - Returns the instance of the builder class.

max

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

public max(int $limit): self

Parameters:

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:

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:

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:

Return Value:

self - Return instance of builder class.

where

Set query condition for WHERE operator.This can be called while working with methods like: select, find, stmt, update, delete, sum, total or average methods execution.

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

Parameters:

Return Value:

self - Return instance of builder class.

and

Set query condition for AND operator.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:

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:

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:

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:

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:

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:

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:

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:

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<int,mixed> $list): self

Parameters:

Return Value:

self - Return instance of builder class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - If values is not provided.

inset

Set query operator FIND_IN_SET() expression.

public inset(string $search, string $operator, array<int,mixed> $list): self

Parameters:

Return Value:

self - Return instance of builder class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - If values is not provided.

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:

Return Value:

self - Return instance of builder class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - Throws if an invalid type is provided.

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:

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:

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:

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:

Return Value:

int - Return number of affected rows or 0 if none was inserted.

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:

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:

Return Value:

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

Throws:

• \Luminova\Exceptions\DatabaseException - If where method was not called.

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:

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:

Return Value:

self - Return instance of builder class.

Throws:

• \Luminova\Exceptions\DatabaseException - when query is empty.

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:

Return Value:

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

Modes

• Constant database return modes - To see reference on database return modes.

Throws:

• \Luminova\Exceptions\DatabaseException - If placeholder key is not a valid string.

find

Select next or a single record from database table.

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

Parameters:

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:

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:

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:

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:

Return Value:

int - Return number of affected rows.

Throws:

• \Luminova\Exceptions\DatabaseException - Throw if error occurred while updating.

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.

Throws:

• \Luminova\Exceptions\DatabaseException - Throw if error occurs.

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:

Return Value:

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

Throws:

• \Luminova\Exceptions\DatabaseException - Throws exception on PDO if failure to set transaction isolation level or create savepoint.

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:

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:

Return Value:

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

Throws:

• \Luminova\Exceptions\DatabaseException - Throws exception on PDO if failure to create SAVEPOINT.

truncate

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

public truncate(bool $transaction = true): bool

Parameters:

Return Value:

bool - Return true truncation was completed, otherwise false.

Throws:

• \Luminova\Exceptions\DatabaseException - Throws if execution encountered error.

temp

Creates a temporary table and copies all records from the main table to the temporary table.

public temp(bool $transaction = true): bool

Parameters:

Return Value:

bool - Returns true if the operation was successful; false otherwise.

Throws:

• \Luminova\Exceptions\DatabaseException - Throws an exception if a database error occurs during the operation.

Example

<?php
if ($builder->table('users')->temp()) {
   $data = $builder->table('temp_users')->select();
}

Note:Temporary tables are automatically deleted when the current session ends.To query the temporary table, use the temp_ prefix before the main table name.

exec

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

public exec(string $query): int

Parameters:

Return Value:

int - Return number affected rows.

Throws:

• \Luminova\Exceptions\DatabaseException - Throws if execution encountered error.

drop

Drop database table if table exists.

public drop(bool $transaction = false): bool

Parameters:

Return Value:

bool - Return true if table was successfully dropped, false otherwise.

Throws:

• \Luminova\Exceptions\DatabaseException - Throws if error occurs.

dropTemp

Drop a temporal database table if table exists.

public dropTemp(bool $transaction = false): bool

Parameters:

Return Value:

bool - Return true if table was successfully dropped, false otherwise.

Throws:

• \Luminova\Exceptions\DatabaseException - Throws if error occurs.

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:

Return Value:

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

Throws:

• \Luminova\Exceptions\DatabaseException - If an invalid format is provided or if unable to create the export.

backup

Creates a backup for the database.

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

Parameters:

Return Value:

True if backup is successful, false otherwise.

Throws:

• \Luminova\Exceptions\DatabaseException - If unable to create the backup directory or if failed to create the backup.

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