PHP Luminova: ORM Database Query Builder Module

The Luminova database query Builder class is a powerful ORM (Object-Relational Mapping) tool designed to simplify database interactions. It enables developers to work with intuitive, object-oriented methods, abstracting the complexities of constructing SQL queries.

With its versatile CRUD (Create, Read, Update, Delete) capabilities, you can effortlessly build both simple and complex SQL queries without writing raw SQL. This allows you to focus more on developing your application and less on the intricacies of database queries. Each method in the Builder class is carefully crafted with names and functionality that align closely with SQL syntax, making it easy to grasp if you're familiar with SQL.

For more detailed examples and usage instructions, refer to the query Builder documentation.

Class Definition

• Class namespace: \Luminova\Database\Builder
• This class implements: \Luminova\Interface\LazyInterface,

Methods

Note: This class constructor is private preventing instantiation from outside (e,g, new Builder()).To initialize the class you can either use the singleton methods Builder::getInstance() or Builder::table().

Example:

Create instance of Builder to access class methods without initializing table.

$builder = Builder::getInstance();

Create instance of Builder to access with a table name.

$table = Builder::table('table-name', 'optional-table-name-alias');

database

Get database connection driver instance (e.g, MySqlDriver or PdoDriver).

public static database(): Luminova\Instance\DatabaseInterface

Return Value:

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

Throws:

• \Luminova\Exceptions\DatabaseException - Throws if database connection failed.

Returning the Original Database Connection Object

This method retrieves the underlying database connection instance, which can be either PDO or MySqli, encapsulated within the Luminova\Interface\ConnInterface.

<?php 
$conn = Builder::database()->raw();

This is useful when you need to access the raw connection for operations not covered by the interface.

getInstance

To get a shared singleton instance of builder class.

public static getInstance(?string $table = null, ?string $alias = null): \Luminova\Database\Builder

Parameters:

Return Value:

\Luminova\Database\Builder - Return new static instance of builder class.

Throws:

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

getCache

Retrieves the current cache instance.

This method returns the static cache instance used by builder table method. The cache instance is typically set up earlier in the builders class's lifecycle.

public static getCache(): ?Luminova\Base\BaseCache

Return Value:

Luminova\Base\BaseCache|null - Returns the current cache instance if set, or null if no cache has been initialized.

table

Creates an instance of the builder class and sets the target database table.

This method is essential in the Builder class and is typically called first to define the table you want to work with.

public static table(string $table, ?string $alias = null): \Luminova\Database\Builder

Parameters:

Return Value:

\Luminova\Database\Builder - Returns the instance of builder class.

Throws:

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

Example:

Performing a table join and executing queries:

$tbl = Builder::table('users', 'u')
    ->innerJoin('roles', 'r')
    ->on('u.user_id', '=', 'r.role_user_id')
    ->where('u.user_id', '=', 1);

// Updating records
$result = $tbl->update(['r.role_id' => 1]);

// Selecting records
$result = $tbl->select(['r.role_id', 'u.name']);

query

Builds a custom SQL query string for execution.

This method allows direct execution of raw SQL queries with supports for caching result. If an array of values is passed to the execute method, prepared statements are used for security. Otherwise, ensure that manually embedded values in the query are properly escaped.

public static query(string $query): \Luminova\Database\Builder

Parameters:

Return Value:

\Luminova\Database\Builder - Return instance of builder class.

Throws:

• \Luminova\Exceptions\DatabaseException - Throws if the query is empty string or an error occur.

Examples:

Executing a raw query:

$updated = Builder::query("
    UPDATE addresses 
    SET default_address = (address_id = :address_id) 
    WHERE address_user_id = :user_id
")->execute([
    'address_id' => 1,
    'user_id' => 101
], RETURN_COUNT);

Selecting records and cache results:

$result = Builder::query("SELECT * FROM users WHERE user_id = :user_id")
    ->cache()
    ->execute(['user_id' => 101]);

join

Specifies a table join operation in the query.

This method defines how another table should be joined to the current query.

Join Types:

• INNER - Returns only rows with matching values in both tables.
• LEFT - Returns all rows from the left table and matching rows from the right table, filling in NULL for non-matching rows.
• RIGHT - Returns all rows from the right table and matching rows from the left table, filling in NULL for non-matching rows.
• CROSS - Returns the Cartesian product of the two tables.
• FULL - Returns rows with matches in either table, filling in NULL for non-matching rows.
• FULL OUTER - Returns all rows from both tables, with NULL in places where there is no match.

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 either $table or $type is an empty string.

Example:

Basic join usage:

Builder::table('product', 'p')->join('users', 'LEFT', 'u');

Joining without an alias:

Builder::table('users')->join('orders', 'INNER');

on

Adds a join conditions to the table query.

This method defines a condition for joining tables, allowing comparisons between columns or values.

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

Parameters:

Return Value:

self - Returns the instance of builder class.

Example:Using on() for table joins:

$tbl->leftJoin('users', 'u')
    ->on('u.user_id', '=', 'r.role_user_id') // Column comparison
    ->on('u.user_group', '=', 1)             // Value comparison
    ->on('u.user_name', '=', '"peter"');     // String literal (quoted)

Note: When using multiple joins in one query, always call on() immediately after each join().

innerJoin

Sets table join condition as INNER JOIN.

public innerJoin(string $table, ?string $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 $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 $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 $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 $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 $alias = null): self

Parameters:

Return Value:

self - Returns the instance of the class.

Throws:

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

limit

Sets the query limit for SELECT statements.

This method adds a LIMIT clause to the query, restricting the number of rows returned and optionally specifying an offset. Useful 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.

Example:

Limiting number of results:

Builder::table('users')
    ->where('country', '=', 'NG')
    ->limit(10, 5)
    ->select();

This will generate: LIMIT 5,10

max

Sets a maximum limit for UPDATE or DELETE operations.

This method applies a LIMIT clause to restrict the number of rows affected by UPDATE or DELETE queries.

public max(int $limit): self

Parameters:

Return Value:

self - Return instance of builder class.

Example:

Limiting number of rows to affect:

Builder::table('users')
    ->where('country', '=','NG')
    ->max(50)
    ->update(['is_local' => 1]);

This ensures the query affects at most 50 rows.

order

Sets the sorting order for the query results.

This method applies an ORDER BY clause to the query, allowing results to be sorted in ascending (ASC) or descending (DESC) order.

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

Parameters:

Return Value:

self - Return instance of builder class.

Example:

Ordering results:

$builder->order('created_at', 'DESC');

This will generate: ORDER BY created_at DESC

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

Sets a GROUP BY clause for the query.

This method adds a column to the GROUP BY clause, allowing aggregation of results based on the specified column.

public group(string $group): self

Parameters:

Return Value:

self - Return instance of builder class.

Example:

$builder->group('category_id');

This will generate: GROUP BY category_id

where

Adds a WHERE condition to the query.

This method sets a conditional clause where the specified column must satisfy the given comparison operator and value.

The where method can be called when working with methods like: select, find, stmt, update, delete, sum, total or average.

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

Parameters:

Return Value:

self - Return instance of builder class.

Example:

Using the WHERE conditioning:

Builder::table('users')->where('status', '=', 'active');

This will generate: WHERE status = 'active'

and

Adds an AND condition to the query.

This method appends an additional condition using the AND operator, requiring multiple conditions to be met.

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

Parameters:

Return Value:

self - Return instance of builder class.

Example:Using the AND conditioning:

Builder::table('users')->where('status', '=', 'active')
    ->and('role', '=', 'admin');

This will generate: WHERE status = 'active' AND role = 'admin'

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

Set columns and values to be updated in the query.

This method should be invoked before the update() method to specify which columns to update.

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

Parameters:

Return Value:

self - Return instance of builder class.

Example:

Setting update columns and values:

$builder->set('status', 'active')
    ->set('updated_at', time());

This will generate: UPDATE table SET status = 'active', updated_at = 1699999999

or

Add a condition to the query using the OR operator.

This method appends a conditional clause where the specified column must satisfy the given comparison operator and value.

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

Parameters:

Return Value:

self - Return instance of builder class.

Example:

$builder->or('status', '=', 'active')
    ->or('role', '!=', 'admin');

This will generate: WHERE status = 'active' OR role != 'admin'

orGroup

Groups multiple conditions using the OR operator and adds them to the query.

This method creates a logical group where conditions are combined with OR, ensuring that at least one of them must be met.

public orGroup(array $conditions): self

Parameters:

Return Value:

self - Return instance of builder class.

Example:

Using orGroup() to structure a query:

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

This will generate: WHERE (column1 = 1 OR column2 = 2)

andGroup

Groups multiple conditions using the AND operator and adds them to the query.

This method creates a logical group where all conditions must be met simultaneously.

public andGroup(array $conditions): self

Parameters:

Return Value:

self - Return instance of builder class.

Example:

Using andGroup() to structure a query:

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

This will generate: WHERE (column1 = 1 AND column2 = 2)

orBind

Combines two condition groups using the OR operator and wraps them in a single condition group.

This method allows you to group conditions using logical OR and then bind the entire group to the query using either AND or OR.

• AND - Groups are combined with AND (e.g., WHERE ((a OR b) AND (c OR d))).
• OR - Groups are combined with OR (e.g., WHERE ((a OR b) OR (c OR d))).

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

Parameters:

Return Value:

self - Return instance of builder class.

Example:

Generating a query with OR conditions:

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

This will generate: WHERE ((foo = 1 OR bar = 2) OR (baz = 3 AND bra = 4))

andBind

Combines two condition groups using the AND operator and wraps them in a single condition group.

This method allows you to group conditions using logical AND and then bind the entire group to the query using either AND or OR.

• AND - Groups are combined with AND (e.g., WHERE ((a AND b) AND (c AND d))).
• OR - Groups are combined with OR (e.g., WHERE ((a AND b) OR (c AND d))).

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

Parameters:

Return Value:

self - Return instance of builder class.

Example:

Generating a query with AND conditions:

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

This will generate: WHERE ((foo = 1 AND bar = 2) AND (baz = 3 AND bra = 4))

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.
• \Luminova\Exceptions\JsonException - If an error occurs while encoding values.

inset

Add a FIND_IN_SET condition to the query.

public inset(
    string $search, 
    string $operator, 
    array<int,mixed>|string $list, 
    bool $isSearchColumn = false
): self

Parameters:

Return Value:

self - Return instance of builder class.

Throws:

• \Luminova\Exceptions\InvalidArgumentException - Throws if list value is not empty.

Default Operators:

• exists|> - Check if exists or match any in the list.
• first|= - Check if it's the first in the list.
• last - Check if it's the first in the list.
• position - Position in the list (as inset_position).
• contains - Check if it contains the search term (uses the $search as the search value).
• none - No match in the list.

Usage Examples:

Using the custom Operator:

Builder::table('fruits')->inset('banana', '= 2', ['apple','banana','orange']);

Using the exists Operator with a column:

Builder::table('employees')->inset('PHP', 'exists', 'column_language_skills');

Using the exists Operator with a search column:

Builder::table('employees')->inset('department', 'exists', 'HR,Finance,Marketing', true);

returns

Set result return type to an object or array (default: object).

This method changes the result return type to either object or array, this method should be called before fetch, find or select method.

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.

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.

Available time formats:

• time - Return time format from timestamp
• datetime - Return SQL datetime format
• date - Return SQL date format.

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.

Note: By default caching is enabled once you call the cache method.

cacheDelete

Deletes the cached data associated with current table or a specific database table.

This method clears cached data for a specified table and subfolder within the cache system. It supports both file-based and memory-based caching (e.g., Memcached). If no table is specified, it defaults to the table name associated with the current instance.

public cacheDelete(?string $storage = null, ?string $subfolder = null, ?string $persistent_id = null): bool

Parameters:

Return Value:

bool - Returns true if the cache was successfully cleared; otherwise, false.

cacheDeleteAll

Deletes all cached items for the specified subfolder or the default database cache.

public cacheDeleteAll(?string $subfolder = null, ?string $persistent_id = null): bool

Parameters:

Return Value:

boo - Returns true if the cache was successfully flushed, false otherwise.

cache

Configures and manages caching for current query operation, to cache query result response, and return cached data when next user request the same content.

This method sets up the caching mechanism based on the provided parameters.It supports both memory-based caching (e.g., Memcached) and file-based caching.The method determines if the cache for the given key exists and whether it has expired.

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

Parameters:

Return Value:

self - Return instance of builder class.

Note: In other for caching to work, you must call cache method before select, find, execute, fetch, sum, average or total method is called.Additionally, using stmt method for returning statement object doesn't support caching, you will need to manually implement it if need.

Throws:

• \Luminova\Exceptions\CacheException - Throws if an error occurs while creating cache or reading expired cache.

insert

Insert records into a specified database table.

This method allows for inserting multiple records at once by accepting an array of associative arrays. Each associative array should contain the column names as keys and their corresponding values as values.

Optionally use the set method to to prepare inset values.

Note: If the insert value is an array, it will be converted to JSON encoded string, if wish to insert a serialized string, you should do that before passing the value.

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

Parameters:

Return Value:

int - Returns the number of affected rows, 0 if no rows were inserted.

Throws:

• \Luminova\Exceptions\DatabaseException - Throws if an error occurs during the insert operation or if the provided values are not associative arrays.
• \Luminova\Exceptions\JsonException - Throws if an error occurs while encoding array values to JSON format.

replace

Replaces records in the specified database table.

This method replaces existing records, it will first deletes existing rows with the same primary key or unique key before inserting new ones.

Note: This may lead to unintended data loss, especially if foreign key constraints exist.

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

Parameters:

Return Value:

int - Returns the number of affected rows (each REPLACE may count as two: one delete + one insert).

Throws:

• \Luminova\Exceptions\DatabaseException - Throws if an error occurs during the operation or if the provided values are invalid.
• \Luminova\Exceptions\JsonException - Throws if an error occurs while encoding array values to JSON format.

Warning: Since replace removes and re-inserts data, it can reset auto-increment values and trigger delete/insert events instead of update events.

copy

Prepares a copy selection query for copying data from the current table.

This method selects the specified columns from the current table context, allowing the data to be copied into another table using the to() method.

public copy(array $columns): self

Parameters:

Return Value:

self - Return instance of builder class.

Throws:

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

Example

Prepare a copy of specific columns

Builder::table('users')
    ->where('id', '=', 1)
    ->copy(['id', 'email', 'created_at'])
    ->to('backup_users', ['id', 'email', 'created_at']); // Destination

to

Executes the selected columns from copy method and insert into a target table.

This method must be used after calling copy(), as it executes the previously prepared selection query and inserts the results into the specified target table.

public to(string $targetTable, array $columns): int

Parameters:

Return Value:

int - Return the number of affected rows or 0 if the operation fails.

Throws:

• \Luminova\Exceptions\DatabaseException - Throws if an error occurs.
• \Luminova\Exceptions\InvalidArgumentException - Throws if empty table is specified.

execute

Executes an SQL query that was previously set using the query() method.

Caching

To cache the query result, call the cache() method before invoking execute(). If caching is enabled and a cached result exists, the method returns the cached value immediately.

Parameter Binding

This method supports placeholder binding via an associative array. The key represents the placeholder name, and the value is the corresponding value to bind.

• If placeholders are provided, the query is executed using prepared statements.
• If no placeholders are given, the query is executed directly. Ensure that all values in the query are properly escaped to prevent SQL injection.

Available Return Modes:

• RETURN_ALL - Returns all rows (default).
• RETURN_NEXT - Returns a single row or the next row from the result set.
• RETURN_2D_NUM - Returns a 2D array with numerical indices.
• RETURN_ID - Returns the last inserted ID.
• RETURN_COUNT - Returns the number of affected rows.
• RETURN_COLUMN - Returns columns from the result.
• RETURN_INT - Returns an integer count of records.
• RETURN_STMT - Returns a PDO or MySQLi prepared statement object.
• RETURN_RESULT - Returns a MySQLi result object or a PDO statement object.

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

Parameters:

Return Value:

mixed|\PDOStatement|\mysqli_result|\mysqli_stmt - Returns the query result, a prepared statement object, or false on failure.

• PDOStatement - If env(database.connection) is PDO and return mode is set to RETURN_STMT or RETURN_RESULT.
• mysqli_stmt - If env(database.connection) is MYSQLI and return mode is set to RETURN_STMT.
• mysqli_result - If env(database.connection) is MYSQLI and return mode is set to RETURN_RESULT.

Return Modes

• Refer to the Luminova constants documentation for details on database result return modes.

Throws:

• \Luminova\Exceptions\DatabaseException - If placeholder key is not a valid string or an error occur.

Example:

Executing a prepared query:

$query = Builder::query("SELECT * FROM users WHERE user_id = :user_id");

$result = $query->execute(['user_id' => 1]);

// Fetching a single row:
$user = $query->execute(['user_id' => 1], RETURN_NEXT);

total

Calculate the total number of records table,

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

Parameters:

Return Value:

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

Throws:

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

exists

Determine if a database table exists.

public exists(): bool

Return Value:

bool - Return true if table exists in database, otherwise false.

Throws:

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

Example:

Check if user table exists in database:

if(Builder::table('users')->exists()){
    echo 'Table exists in database';
}

has

Determine of a records exists in table.

public has(): bool

Return Value:

bool - Return true if records exists in table, otherwise false.

Throws:

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

Example:

Check if users in country Nigeria exists in table:

if(Builder::table('users')->where('country', '=', 'NG')->has()){
    echo 'Users in Nigeria exists';
}

sum

Calculate the total sum of a numeric column in the table.

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

Parameters:

Return Value:

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

Throws:

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

average

Calculate the average value of a numeric column in the table.

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

Parameters:

Return Value:

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

Throws:

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

find

Select next or a single record from database table.

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

Parameters:

Return Value:

mixed - Return selected single row, otherwise false if execution failed.

Throws:

• \Luminova\Exceptions\DatabaseException - Throw if where method was not called or an error occurs.

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 if execution failed.

Throws:

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

fetch

Fetch records from database table.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 = ['*']
): mixed

Parameters:

Return Value:

object|array|int|float|false|null - Return selected records, otherwise false if execution failed.

Throws:

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

stmt

Returns the prepared query statement based on the conditions built using previous method calls.

Note: To obtain a prepared statement object, do not call any of the following methods: total, sum, find, select, average, fetch, or execute. Calling any of these methods will result in stmt returning NULL.

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

Parameters:

Return Value:

\Luminova\Interface\DatabaseInterface|null - Returns the prepared statement object if the query is successful; otherwise, it returns NULL.

Throws:

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

Example

For more information about the methods available in the statement object, see the Database Driver Documentation.

$tbl = Builder::table('users');
$tbl->where('programming_language', '=', 'PHP');
$stmt = $tbl->stmt();

// Use the statement object as needed 
var_export($stmt->getAll());
$stmt->free();

update

Updates records in the database table with new data. You can pass an associative array of column-value pairs directly to the update method or use the set method to stage your columns and values for updating.

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

Parameters:

Return Value:

int - Returns the number of affected rows.

Throws:

• \Luminova\Exceptions\DatabaseException - Thrown if an error occurs while updating.
• \Luminova\Exceptions\JsonException - Thrown if an error occurs while encoding values.

Note: If you pass an array value to an update field, it will be automatically converted to a JSON string. Ensure that your table field is designed to accept JSON strings 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.

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 - Returns true if the transaction was successfully started, 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, it automatically copies all records from the main table into it.

public temp(bool $transaction = true): bool

Parameters:

Return Value:

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

Throws:

• \Luminova\Exceptions\DatabaseException - Thrown if a database error occurs during the operation.

Example

if (Builder::table('users')->temp()) {
   $data = Builder::table('temp_users')->select();
}

Note:Temporary tables are automatically deleted when the current session ends.You won't find these tables in phpMyAdmin or any other database manager as they are session-specific.To query a temporary table, use the temp_ prefix before the main table name.

exec

Execute an SQL query string directly without bind nor prepared statement and return the number of affected rows.

For DDL statements like CREATE, ALTER, DROP etc..., the return value is typically of executed 1 rather than 0, even though these statements do not affect rows directly.

public exec(string $query): int

Parameters:

Return Value:

int - Return number affected rows or 0 if failed.

Throws:

• \Luminova\Exceptions\DatabaseException - Throws an exception if a database error occurs during the operation.
• \Luminova\Exceptions\InvalidArgumentException - Throws if query string is empty.

drop

Drops a database table or a temporary table if it exists.

When $isTemporalTable is set to true, the temporal created table of the current table will be dropped instead.

public drop(bool $transaction = false, bool $isTemporalTable = false): bool

Parameters:

Return Value:

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

Throws:

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

Examples:

This will drop the users table from the database.

Builder::table('users')->drop(); // Table 'users' was dropped

This will drop the users temporal table from the database.

Builder::table('users')->drop(false, true); // Table 'temp_users' was dropped

lock

Handles database-level locking using advisory locks for PostgreSQL and MySQL.

• PostgreSQL: Uses pg_advisory_lock and pg_advisory_unlock, requiring an integer lock name.
• MySQL: Uses GET_LOCK, RELEASE_LOCK, and IS_FREE_LOCK, allowing string lock names.

public static lock(string|int $identifier, int $timeout = 300): bool

Parameters:

Return Value:

bool - Return true if the operation was successful, false otherwise.

Throws:

• \Luminova\Exceptions\DatabaseException - If an invalid action is provided or an invalid PostgreSQL lock name is used.

unlock

Releases the lock for the given name.

public static unlock(string|int $identifier): bool

Parameters:

Return Value:

bool - Return true if the lock was successfully released, false otherwise.

Throws:

• \Luminova\Exceptions\DatabaseException - If an invalid action is provided or an invalid PostgreSQL lock name is used.

isLocked

Checks if the given lock is free.

public static isLocked(string|int $identifier): bool

Parameters:

Return Value:

bool - Return true if the lock is free, false if it is currently held.

Throws:

• \Luminova\Exceptions\DatabaseException - If an invalid action is provided or an invalid PostgreSQL lock name is used.

isConnected

Check if database connected.

public static isConnected(): bool

Return Value:

bool - Return true if database connected, false otherwise.

manager

Returns the shared instance of the database manager class, initialized with the current database connection. The manager object can be used to create database table backups or export entire tables in CSV or JSON format.

public manager(): \Luminova\Database\Manager

Return Value:

\Luminova\Database\Manager - An instance of the database manager class.

Throws:

• \Luminova\Exceptions\DatabaseException - Throws if database connection failed.

See Also:

Database Manager - Detailed documentation on the database manager.

**

export

Exports the current database table and prompts the browser to download it as a JSON or CSV file. This method utilizes the manager class for the export operation.

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

Parameters:

Return Value:

bool - Returns true if the export is successful, false otherwise.

Throws:

• \Luminova\Exceptions\DatabaseException - If an invalid format is specified or if the export operation fails.

backup

Creates a backup of the current database table and saves the backup file in the writeable/backups directory. This method utilizes the manager class for the backup operation.

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

Parameters:

Return Value:

bool - Return 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.

errors

Retrieves the last query execution errors.

public errors(): array

Return Value:

array - Return an array containing error information from the last executed query.

debug

Enables query string debugging, allowing you to preview the compiled query string before execution.

public debug(): self

Return Value:

self - Returns the current instance of the builder class.

Note: In production environments, the query string will be logged at the debug level. When debugging is enabled, query execution will intentionally fail, and caching will be disabled.

printDebug

Returns detailed debug information about the query string, including formats for MySQL and PDO placeholders, as well as the exact binding mappings for each column.

public printDebug(): array<string,mixed>

Return Value:

array<string,mixed> - Return an array containing the debug information for the query string.

dump

Outputs the debug information for the last executed query.

public dump(): ?bool

Return Value:

bool|null - Returns true on success, false on failure, or null if no debug information is available.

reset

Resets query conditions, parameters, and frees database resources, preparing the builder for the next query execution.

public reset(): void

free

Releases the statement cursor and sets the statement object to null.

public free(): void

close

Closes the database connection and releases the statement cursor.

public close(): void