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:

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:

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

table

To set database table name to use in execution.

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

Parameters:

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:

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:

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:

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:

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:

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:

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:

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:

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 where condition for 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 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:

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 $lists = []): self

Parameters:

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:

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:

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.

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.

errors

Get query execution errors.

public errors(): array

Return Value:

array - Returns array of error information

transaction

To begin a database query execution transaction.

public transaction(): self

Return Value:

self - Return instance of builder class.

commit

To commit a transaction.

public commit(): void

rollback

To rollback a transaction to default on failure.

public rollback(): void

truncate

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

public truncate(bool $transaction = true): bool

Parameters:

Return Value:

bool - Returns true if completed else false.

Throws:

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

exec

Execute an SQL statement 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

To drop database table.

public drop(): int

Return Value:

int|bool - Return number affected rows.

Throws:

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

create

Create table database table if it doesn't exist.

public create(\Luminova\Database\Scheme|string $scheme): bool

Parameters:

Return Value:

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

Throws:

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

scheme

Initializes a database schema instance with the provided table name.

public scheme(): \Luminova\Database\Scheme

Return Value:

\Luminova\Database\Scheme - Return instance of the database scheme class.

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