PHP Luminova: ORM Database Query Builder
Database Builder provides an ORM and query builder that simplifies CRUD operations and allows you to interact with the database using an object-oriented API instead of writing raw SQL.
The Luminova Database Query Builder class is an ORM (Object-Relational Mapping) tool that simplifies working with databases. It provides object-oriented methods that construct SQL queries and binding. Features include support for table Join, UNION queries, Common Table Expressions (CTE), promise-style resolution, geospatial distance queries, and more.
With flexible CRUD (Create, Read, Update, Delete) capabilities, you can build both simple and advanced SQL queries with ease. This reduces the need to write raw SQL, letting you focus on your application logic instead of query syntax. Each method in the Builder class closely mirrors SQL conventions, so if you're familiar with SQL, you'll feel right at home.
For detailed examples and usage patterns, see the Query Builder Usage Examples.
Usages
To create a Builder instance, use one of the following static methods:
Builder::table(...)Builder::with(...)(for CTE-backed queries)Builder::query(...)(for custom SQL)Builder::exec(...)(for custom SQL commands)new Builder(...)(manual instantiation, requires attaching a connection)
Shared Initialization
Use configure() to create a shared singleton Builder instance whose global configuration (return type, debug mode, safe mode, strict mode, auto-close, and connection) is inherited by every builder instance created afterward through table(), query(), or with():
Builder::configure()
->strict(true) // Enforce strict WHERE checks on DELETE and UPDATE
->cacheable(true) // Enable query caching for all tables
->closeAfter(true) // Automatically close DB connection after execution
->returns(Builder::RETURN_ARRAY); // Set return format to arrayNow every new builder table instance created afterward inherits these settings:
$result = Builder::table('users')
->select(['name'])
->where('id', '=', 100)
->get();Single Initializers
Initialize a Builder instance with a specific table name:
$table = Builder::table('table-name', 'optional-alias');Run a raw SQL query with optional bindings:
$result = Builder::query('SELECT * FROM users')->execute([...]);Execute a raw SQL command and get the number of affected rows:
$result = Builder::exec('ALTER TABLE users AUTO_INCREMENT = 2');Initialize a builder instance backed by a Common Table Expression:
$result = Builder::with('ranked_games', '
WITH ranked_games AS (
SELECT g.*,
ROW_NUMBER() OVER (
PARTITION BY g.home_id, g.away_id
ORDER BY g.created_at DESC
) AS rn
)', 'g')
->where('rn', '=', 1)
->get();Raw Expressions
The Expression class provides access to raw SQL that should not be escaped or quoted by the query builder. Use expressions when you need to insert or evaluate direct SQL values in methods like select, insert, or update.
Inline Expression
$expr = Builder::expression('COUNT(*) AS total');Deprecated:
Builder::raw(...)is a deprecated alias forBuilder::expression(...)and will forward to it.
Expression Class
use Luminova\Database\Query\Expression;
// Create a new expression instance
$raw = new Expression('NOW()');
// Predefined helpers
Expression::increment('column'); // column = column + 1
Expression::decrement('column', 2); // column = column - 2
Expression::now(); // NOW()
Expression::distance(...); // Spatial distance expression
// And moreBuilder also exposes a shortcut for the current database timestamp:
Builder::table('logs')->insert([
'message' => 'User login',
'created_at' => Builder::now() // Same as Expression::now()
]);Closure Value
Some builder methods (like on(), where(), whereIn(), and more) support closures as dynamic value sources.A closure lets you build subqueries or compute values at runtime within the query builder context.
When you pass a closure, it receives one argument — a static instance of the Builder class (or none, if your closure declares zero parameters). This instance inherits your default global builder configuration (e.g., caching, auto-close connection, and return type), so it behaves exactly like your main builder.
Signature:
The closure must return either:
- A scalar value (string, int, float, etc.), or
- A
Luminova\Database\Query\Expressioninstance wrapping a raw SQL expression.
(Closure(Builder $instance): mixed)Examples:
Using closure inside on().
$builder->on('u.user_id', '=', function (Builder $b): string {
$result = $b->from('foo')
->find(['id'])
->where('baz', '=', 'bra')
->get();
// Optionally terminate if no result
if (empty($result)) {
throw new Exception('Record not found', ErrorCode::TERMINATED);
}
return $result->id;
});Using closure inside where().
$builder->where('u.balance', '>', function (Builder $b): mixed {
return $b->from('payments')
->find(['SUM(amount) as totalBalance'])
->where('status', '=', '"completed"')
->get()->totalBalance; // return numeric value
});Builder Execution Order
The Builder class processes methods in a defined order. This ensures clarity and consistency when building queries:
- Table Definition –
table(...),with(...),from(...) - Selector Method –
select,find,sum,average,min,max,count,copy, etc. - Conditions and Filters –
where,join,whereIn,whereLike,limit, etc. - Execution Method –
get,next,promise,stmt,into,exists, etc.
Example Structure:
$result = Builder::table('users') // Step 1: Define table
->select(['name', 'email']) // Step 2: Select fields or use aggregate
->where('id', '=', 1) // Step 3: Add conditions
->limit(1) // Additional filters (limit, order, etc.)
->cache('user-1') // Optional settings
->get(); // Step 4: Execute and fetchNote:
The order of selector, condition, filter, or option methods doesn't affect the query. The only requirement is that a query executor method such as
get,next,update,insert,delete,promise,stmt,into, orexistsmust be called last to trigger execution.
Union Tables
To build a union of multiple queries, do not call execution methods like get(), next(), promise(), or stmt() on any part of the chain until the entire union structure is defined. Calling them prematurely will execute the current query instead of merging it with others.
Use union() or unionAll() to combine multiple queries. Each union source can be either:
- a
Builderinstance - a
Closurereturning aBuilderinstance ornull(to skip that union source)
After combining all queries, you can apply filters, limits, or sorting to the union result before calling the final executor.
Basic Union (using closure)
$result = Builder::table('users')
->select(['name', 'email'])
->where('id', '=', 1)
->union(function () {
return Builder::table('contacts')
->select(['name', 'email'])
->where('id', '=', 1);
})
->get();Union with Predefined Instances
$privates = Builder::table('users')->select(['id', 'email'])->where('visibility', '=', 'private');
$publics = Builder::table('users')->select(['id', 'email'])->where('visibility', '=', 'public');
$invisible = Builder::table('users')->select(['id', 'email'])->where('visibility', '=', 'invisible');
$results = $privates
->union($publics)
->union($invisible)
->unionColumns(['id', 'email'])
->descending('id')
->get();Advanced Union with Chained Closures
$result = Builder::table('users')
->select(['user_id AS id', 'user_email AS email'])
->where('user_visibility', '=', 'private')
->limit(3)
->union(function () {
return Builder::table('users')
->select(['user_id AS id', 'user_email AS email'])
->where('user_visibility', '=', 'public')
->limit(3);
})
->union(function () {
return Builder::table('users')
->select(['user_id AS id', 'user_username AS email'])
->where('user_visibility', '=', 'invisible')
->limit(3);
})
->unionColumns(['id', 'email']) // Optional: override final column structure
->descending('id')
->limit(9)
->returns(Builder::RETURN_ARRAY)
->get();Note:
You can still apply additional
where,limit,order,cache,andwhereGroup()filters to the final union result before execution.
OR / AND Condition Proxies
Most where*() condition methods (whereLike, whereNotLike, whereBetween, whereNotBetween, whereIn, whereNotIn, whereInset, whereNotInset, whereSearch, whereAgainst, whereNotAgainst, whereRegex, whereCondition, whereNull, whereNotNull, whereDistance, whereWithin, whereNotWithin, whereGroup, whereNested, whereHaving, whereNotHaving, whereRaw) can be called using an or or and prefixed variant instead of passing the connector argument manually. These are resolved dynamically through the builder's magic __call() method.
Builder::table('users')
->where('status', '=', 'active')
->orWhereLike('name', '%pet%') // Same as ->whereLike('name', '%pet%', 'OR')
->andWhereIn('role', ['admin']); // Same as ->whereIn('role', ['admin'], 'AND')Class Definition
- Class namespace:
Luminova\Database\Query\Builder - This class implements: \Luminova\Interface\LazyObjectInterface
- Related helper:
Luminova\Database\Query\Expression,Luminova\Database\Query\CaseExpression,Luminova\Database\Query\Column
Constants
Debug Modes
| Constant | Value | Description |
|---|---|---|
DEBUG_NONE | 0 | Disable query debugging. |
DEBUG_BUILDER_COLLECT | 1 | Collects builder-level SQL statements and bindings for later inspection with getDebug(). |
DEBUG_DRIVER_DUMP | 2 | Enables the underlying database driver's own debugging output. |
DEBUG_BUILDER_OUTPUT | 3 | Outputs SQL statements and bindings as they're generated. |
Return Types
| Constant | Value | Description |
|---|---|---|
RETURN_ARRAY | array | Return results as associative arrays. |
RETURN_OBJECT | object | Return results as stdClass objects (default). |
RETURN_OBJECT_LIST | list | Return the result collection itself as an object while each row remains an object (legacy compatibility mode). |
RETURN_STATEMENT | stmt | Return the prepared database statement instead of hydrated results. |
Full-Text Match Modes
The constants below are predefined modes for full-text search when using match(), whereAgainst(), whereNotAgainst() or orderAgainst().
| Constant | SQL Equivalent | Description |
|---|---|---|
MATCH_NATURAL | IN NATURAL LANGUAGE MODE | Default mode; interprets the search string as natural human language. |
MATCH_BOOLEAN | IN BOOLEAN MODE | Supports boolean operators like +, -, >, *, etc., for fine control. |
MATCH_NATURAL_EXPANDED | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION | Uses natural language search with automatic query expansion. |
MATCH_EXPANSION | WITH QUERY EXPANSION | Performs search with query expansion only, no base mode specified. |
Clause Modes
Used internally by the private whereCondition() builder and exposed for reference when reading debug output.
| Constant | Description |
|---|---|
REGULAR | Standard comparison (e.g., WHERE column = value). |
CONJOIN | Combined expressions (e.g., WHERE (a = 1 OR b = 2)). |
RAW | Raw conditions passed directly (e.g., WHERE NOT EXISTS (SELECT ...)). |
NESTED | Deeply grouped conditions (e.g., WHERE ((a = 1 AND b = 2) OR (c = 3))). |
AGAINST | Full-text match using MATCH (...) AGAINST (...). |
INARRAY | Filters using IN (...) with array values. |
Search Patterns
Used with whereSearch().
| Constant | Pattern | Description |
|---|---|---|
SEARCH_STARTS_WITH | {keyword}% | Matches values that start with the keyword. |
SEARCH_ENDS_WITH | %{keyword} | Matches values that end with the keyword. |
SEARCH_CONTAINS | %{keyword}% | Matches values that contain the keyword (default). |
SEARCH_ONE_BEFORE | _{keyword}% | Matches values with exactly one character before the keyword. |
SEARCH_TWO_BEFORE | __{keyword}% | Matches values with exactly two characters before the keyword. |
SEARCH_ONE_AFTER | {keyword}_% | Matches values with exactly one character after the keyword. |
SEARCH_TWO_AFTER | {keyword}__% | Matches values with exactly two characters after the keyword. |
SEARCH_SURROUND | {keyword}%{keyword} | Matches values that start and end with the keyword. |
SEARCH_REGEX | regex | Performs a regular-expression-based search instead of LIKE. |
Methods
constructor
Create a new instance of the builder class.
Initializes the Builder with an optional table name and alias. It does not attach a database connection object — you must attach one before executing a query (e.g. via useConnection()).
public __construct(?string $table = null, ?string $alias = null)Parameters:
| Parameter | Type | Description |
|---|---|---|
$table | string|null | Optional database table name (must be a valid non-empty string). |
$alias | string|null | Optional table alias (default: null). |
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the table name or alias is empty or contains invalid characters.
Example:
use Luminova\Database\Query\Builder;
$builder = new Builder();
$builder->useConnection(...);
$builder->from('users')->where('id', '=', 100)->get();configure
Initialize a shared configuration instance of the Builder class.
The shared instance stores global query configurations (return type, debug mode, safe mode, strict mode, auto-close, and connection) that are inherited by builder instances created through table(), query(), and with().
public static configure(): BuilderReturn Value:
Luminova\Database\Query\Builder - Return a shared singleton instance of builder.
Throws:
- \Luminova\Exceptions\DatabaseException - If the database connection fails.
Example:
$builder = Builder::configure()
->cacheable(true)
->returns(Builder::RETURN_ARRAY)
->strict(true);
$result = $builder->from('users')
->where('id', '=', 100)
->select(['name']);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): BuilderParameters:
| Parameter | Type | Description |
|---|---|---|
$table | string | The name of the database table (must be a non-empty string). |
$alias | string|null | Optional alias for the table (default: null). |
Return Value:
Luminova\Database\Query\Builder - Returns a new instance of builder class.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the provided table name is empty.
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']);with
Create a new query builder instance using a Common Table Expression (CTE).
This method accepts only a SELECT-only statement that represents the CTE dataset. If the query doesn't already start with WITH, the builder automatically normalizes and prefixes it.
public static with(string $table, string $query, ?string $alias = null): BuilderParameters:
| Parameter | Type | Description |
|---|---|---|
$table | string | The CTE name used as the temporary result set identifier. |
$query | string | A SELECT-only SQL statement (or full WITH ... statement) representing the CTE body. |
$alias | string|null | Optional alias for the resulting dataset. |
Return Value:
Luminova\Database\Query\Builder - Returns a new instance of builder, initialized with the provided CTE query.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the query is empty or not a valid CTE statement.
Example:
Deduplication using a window function:
$result = Builder::with('ranked_games', '
WITH ranked_games AS (
SELECT g.*,
ROW_NUMBER() OVER (
PARTITION BY g.home_id, g.away_id
ORDER BY g.created_at DESC
) AS rn
)', 'g')
->where('rn', '=', 1)
->get();cte
Attach a raw Common Table Expression (CTE) query to the builder.
Unlike with(), this method does not build, modify, or wrap the CTE — the provided SQL is preserved as-is. It accepts a complete SQL statement containing a WITH clause and is intended for advanced usage such as multiple CTE definitions and recursive queries.
public cte(string $query, bool $final = false): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$query | string | Complete CTE SQL statement starting with WITH. |
$final | bool | Whether the CTE query already contains the final SQL operation (SELECT, INSERT, UPDATE, or DELETE). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the query is empty or does not contain a valid
WITH ... AS (...)block.
Example:
$builder->cte('
WITH active_games AS (
SELECT * FROM games WHERE game_completed = 0
)
SELECT * FROM active_games
');query
Executes an SQL query with optional placeholders.
This method allows direct execution of raw SQL queries with support for caching results. If an array of values is passed to execute(), prepared statements are used for security. Otherwise, ensure that manually embedded values in the query are properly escaped.
public static query(string $query): BuilderParameters:
| Parameter | Type | Description |
|---|---|---|
$query | string | The SQL query string (must be non-empty). |
Return Value:
Luminova\Database\Query\Builder - Returns a new instance of builder class.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - Throws if the query string is empty.
Examples:
$user = Builder::query("SELECT * FROM users WHERE id = :user_id")
->execute(['user_id' => 1]);$user = Builder::query("SELECT name, email, id FROM users WHERE id = :user_id")
->returns(User::class)
->execute(['user_id' => 1]);Selecting records and caching the result:
$result = Builder::query("SELECT * FROM users WHERE id = :user_id")
->cache('cache-key')
->execute(['user_id' => 101]);Note: To cache the result, you must call
cache()before theexecute()method.
exec
Execute an SQL query string and return the number of affected rows.
public static exec(string $query): intParameters:
| Parameter | Type | Description |
|---|---|---|
$query | string | The SQL query string to execute. |
Return Value:
int - Return number of affected rows or 0 if failed.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the query string is empty.
- \Luminova\Exceptions\DatabaseException - Throws if a database error occurs while executing the query.
Example:
$result = Builder::exec("
ALTER TABLE `users`
ADD COLUMN `slug` CHAR(10) DEFAULT NULL AFTER `id`
");database
Return a connected database driver.
If $shared is true, reuse the shared connection instance. Otherwise create a new connection.
public static database(bool $shared = true): DatabaseInterfaceParameters:
| Parameter | Type | Description |
|---|---|---|
$shared | bool | Use shared connection instance (default: true). |
Return Value:
Luminova\Interface\DatabaseInterface - Return connected database driver.
Throws:
- \Luminova\Exceptions\DatabaseException - If connection cannot be established.
tableExists
Check if a database table exists.
public static tableExists(string $table): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$table | string | The table name to check. |
Return Value:
bool - Returns true if the table exists, otherwise false.
Throws:
- \Luminova\Exceptions\DatabaseException - If an error occurs or the database driver is unsupported.
Example:
$exists = Builder::tableExists('users');Note: This method does not require a
WHEREclause or logical operators.
expression
Creates a new raw SQL expression.
This method is used to pass raw SQL expressions that should not be escaped or quoted by the query builder. It is useful for performing operations like COUNT(*), NOW(), or scores + 1 directly in queries.
public static expression(string $sql): ExpressionParameters:
| Parameter | Type | Description |
|---|---|---|
$sql | string | The raw SQL expression. |
Return Value:
Luminova\Database\Query\Expression - Return an instance of a raw expression representing the SQL fragment.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If an empty string is passed.
Examples:
Using Expression in an INSERT query:
$result = Builder::table('logs')
->insert([
'message' => 'User login',
'created_at' => Builder::expression('NOW()'), // Use raw expression helper method
'updated_at' => Expression::now() // Or directly
]);Using REPLACE instead of INSERT:
$result = Builder::table('logs')
->replace(true)
->insert([
'message' => 'User login',
'created_at' => Builder::expression('NOW()')
]);now
Creates a SQL expression representing the current date and time.
This generates a database-side timestamp expression instead of binding a PHP-generated timestamp value — the database server determines the current time when the query executes.
public static now(): ExpressionReturn Value:
Luminova\Database\Query\Expression - Returns an SQL expression for the current timestamp.
Example:
Builder::table('logs')
->insert([
'message' => 'User login',
'created_at' => Builder::now()
]);datetime
Return a formatted date or time string suitable for SQL storage.
This helper generates common SQL date formats or a UNIX timestamp. If no timestamp is provided, the current time is used.
Default Formats:
time→HH:MM:SS(e.g.,14:30:45)date→YYYY-MM-DD(e.g.,2025-04-03)datetime→YYYY-MM-DD HH:MM:SS(e.g.,2025-04-03 14:30:45)unix→ UNIX timestamp (e.g.,1712256645)
Any other value is treated as a valid PHP date format string.
public static datetime(
string $format = 'datetime',
DateTimeZone|string|null $timezone = null,
?int $timestamp = null
): stringParameters:
| Parameter | Type | Description |
|---|---|---|
$format | string | Output format (default: datetime). |
$timezone | DateTimeZone|string|null | Optional timezone object or name. |
$timestamp | int|null | Optional UNIX timestamp to format. If null, the current time is used. |
Return Value:
string - Returns the formatted date/time string or UNIX timestamp.
column
Define a column condition for use in whereGroup() and whereNested().
This method simplifies specifying a column condition with a comparison operator and a value.
public static column(string $name, string $operator, mixed $value): ColumnParameters:
| Parameter | Type | Description |
|---|---|---|
$name | string | The name of the column. |
$operator | string | The comparison operator (e.g., =, !=, <, >, LIKE). |
$value | (Closure(Builder $static):mixed)|mixed | The value to compare against. |
Return Value:
Luminova\Database\Query\Column - Returns instance of column condition structure.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If an invalid column name is provided.
Examples:
Using column() with whereGroup():
$tbl = Builder::table('users')
->whereGroup([
Builder::column('age', '>=', 18),
Builder::column('status', '=', 'active')
], 'AND');
// Generates: WHERE (age >= 18 AND status = 'active')Using column() with whereNested():
$tbl = Builder::table('products')
->whereNested(
[Builder::column('price', '>', 100), Builder::column('rate', '>=', 10)],
[Builder::column('price', '>', 100), Builder::column('price', '>', 100)]
);
// Generates: WHERE ((price > 100 AND rate >= 10) OR (price > 100 AND rate >= 100))escape
Escapes a value for safe use in SQL queries, handling various types and formats.
null,bool, and numeric values are cast based on the$strictflag.- Arrays and objects are encoded as JSON. Existing valid JSON strings are preserved.
Expressioninstances are returned as-is, unescaped.- Resources are read using
stream_get_contents()and returned as string contents (or base64 unless strict). - Strings can optionally be escaped with
addslashes()and/or wrapped in quotes.
public static escape(
mixed $value,
bool $enQuote = false,
bool $strict = false,
bool $numericCheck = false,
bool $addSlashes = false
): mixedParameters:
| Parameter | Type | Description |
|---|---|---|
$value | mixed | The value to escape. |
$enQuote | bool | If true, wraps the value in single quotes unless it's JSON. |
$strict | bool | Whether to use strict type casting (default: false). See below. |
$numericCheck | bool | If true, numeric strings are cast to int/float and JSON_NUMERIC_CHECK is applied to JSON encoding. |
$addSlashes | bool | If true, string values are passed through addslashes(). |
$strict behavior:
- If
true:nullreturnsnull;boolreturns1|0; resources return raw stream content; empty arrays return[]. - If
false:nullreturns'NULL'(as a string);boolreturns'TRUE'/'FALSE'; resources return base64-encoded content; empty arrays return'[]'.
Return Value:
mixed - Returns a properly escaped and type-safe value.
Throws:
\JsonException- If JSON encoding fails for arrays or objects.- \Luminova\Exceptions\DatabaseException - If the value is a resource and failed to read content.
useConnection
Attach a database connection or driver to the builder.
By default, Luminova's builder uses a shared database object for query operations. This method lets you attach an isolated connection for a chained builder operation.
public useConnection(DatabaseInterface|Connection|null $conn = null, bool $freeStmt = false): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$conn | DatabaseInterface|Connection|null | Connection source or null to assign a new connection. |
$freeStmt | bool | Whether to free active statements and release the cursor before use. |
Return Value:
self - Return instance of the database builder.
Throws:
- \Luminova\Exceptions\DatabaseException - If no active connection is available.
Example:
$db = Builder::database(shared: false);
$db->beginTransaction();
$isUpdated = Builder::table('users')
->useConnection($db)
->where('id', '=', 100)
->update(['name' => 'Peter']);
if ($isUpdated) {
$db->commit();
} else {
$db->rollback();
}
$db->close();from
Reassign table name and alias to the builder object.
This can be used to re-assign a new table name after query execution, to continue a new query with the same object, or for a sub-query. If no connection is currently attached, a shared one is created automatically.
public from(string $table, ?string $alias = null): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$table | string | The name of the database table (must be a non-empty string). |
$alias | string|null | Optional alias for the table (default: null). |
Return Value:
self - Returns instance of the builder class.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the provided table name or alias is empty/invalid.
Example:
$tbl = Builder::table('users', 'u');
// Update table
$tbl->from('admins', 'a');join
Adds a table join to the current query.
Use this method to combine data from another table or subquery into your main query. You can specify the type of join (INNER, LEFT, etc.) and optionally assign an alias for the joined table.
public join(string $table, ?string $alias = null, ?string $type = null): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$table | string | The table name to join. |
$alias | string|null | Optional alias for the joined table. |
$type | string|null | The type of join to use (INNER, LEFT, RIGHT, FULL, or CROSS). |
Return Value:
self - Returns the instance of the builder class.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If
$table,$aliasor$typeis invalid or empty string.
Examples:
Basic join:
Builder::table('products', 'p')
->join('users', 'u', 'LEFT');Join without alias:
Builder::table('users')
->join('orders', type: 'INNER');See Also:
Supported join shortcuts: innerJoin(), leftJoin(), rightJoin(), fullJoin(), fullOuterJoin(), crossJoin(), and joinSubquery().
joinSubquery
Join a subquery to the current query.
This is a convenience wrapper around join() that automatically enables subquery join mode. It allows you to attach a derived table and later define the subquery source using onSubquery() and on().
public joinSubquery(?string $alias = null, ?string $type = null): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$alias | string|null | Optional table alias for the subquery. |
$type | string|null | The join type (INNER, LEFT, RIGHT, FULL, CROSS). |
Return Value:
self - Returns the instance of the builder.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If
$aliasor$typeis invalid or empty string.
Example:
$users = Builder::table('users', 'u')
->select(['u.name', 'o.id'])
->joinSubquery('o', 'LEFT')
->onSubquery('(SELECT user_id, COUNT(*) total FROM orders GROUP BY user_id)')
->on('o.role', '=', 'admin')
->get();innerJoin / leftJoin / rightJoin / crossJoin / fullJoin / fullOuterJoin
Convenience shortcuts around join() that set the join type automatically.
public innerJoin(string $table, ?string $alias = null, bool $forSubquery = false): self
public leftJoin(string $table, ?string $alias = null, bool $forSubquery = false): self
public rightJoin(string $table, ?string $alias = null, bool $forSubquery = false): self
public crossJoin(string $table, ?string $alias = null, bool $forSubquery = false): self
public fullJoin(string $table, ?string $alias = null, bool $forSubquery = false): self
public fullOuterJoin(string $table, ?string $alias = null, bool $forSubquery = false): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$table | string | The table name. |
$alias | string|null | Optional table join alias (default: null). |
$forSubquery | bool | Set to true if the joined source is a subquery instead of a normal table. |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If an invalid argument is provided.
Note:
fullOuterJoin()is not supported by MySQL or SQLite; use aUNIONofLEFT JOINandRIGHT JOINinstead.
on
Add a condition to the current JOIN ON clause.
Defines a comparison expression used by join operations. Intended for simple column or value comparisons. Use onCompound(), onRaw(), or onSubquery() for more advanced join conditions. The most recent join table receives the ON condition — call this method immediately after the corresponding join method.
public on(string $column, string $operator, mixed $value, string $connector = 'AND'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | Left-side column or SQL expression. |
$operator | string | Comparison operator (=, <>, >, <, LIKE, etc.). |
$value | mixed | Right-side value, column name, quoted literal (e.g. "admin"), named placeholder (e.g. :role_name, bound via bind()), or a closure returning a value/Expression. |
$connector | string | Logical connector joining this condition with previous ON conditions (AND or OR). |
Return Value:
self - Returns the builder instance.
Throws:
- \Luminova\Exceptions\LogicException - If no join operation exists.
- \Luminova\Exceptions\InvalidArgumentException - If an invalid connector or operator is provided.
Examples:
Builder::table('users', 'u')
->leftJoin('roles', 'r')
->on('u.id', '=', 'r.user_id')
->on('u.active', '=', 1)
->get();Closure for a subquery condition:
$users = Builder::table('users', 'u')
->select(['u.name', 'u.id', 'o.oid'])
->innerJoin('orders', 'o')
->on('u.id', '=', function (Builder $b): string {
$result = $b->from('payments')
->find(['id'])
->where('status', '=', '"completed"')
->get();
if (empty($result)) {
throw new Exception('User not found');
}
return $result->id;
})
->where('u.active', '=', 1)
->limit(5)
->get();onRaw
Adds a raw SQL condition to the current JOIN clause.
Use this method when you need full control over the SQL ON expression — for example, when building complex or non-standard join logic that can't be represented with the basic on() method.
Subquery Replace Filters: {{tableName}} and {{tableAlias}} are replaced with the join table's name and alias.
public onRaw(Expression|string $sql, string $connector = 'AND'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$sql | Expression|string | A raw SQL string or expression object to use in the join condition. |
$connector | string | Logical operator to join with previous conditions (AND or OR). |
Return Value:
self - Returns the instance of the builder class.
Example:
$users = Builder::table('users', 'u')
->find(['u.id', 'u.name', 'l.level', 'l.message'])
->leftJoin('logs', 'l')
->onRaw('(u.id IN (100,200))')
->onRaw(Builder::expression('DATE(l.created_at) = CURDATE()'))
->get();onSubquery
Defines a subquery as the source for the current JOIN operation.
This method attaches a complete SQL subquery directly to the join, when the join is marked as a subquery join (forSubquery: true).
Subquery Replace Filters: {{tableName}} and {{tableAlias}} are replaced with the join table's name and alias.
public onSubquery(Expression|string $sql): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$sql | Expression|string | A raw SQL string or expression representing the subquery. |
Return Value:
self - Returns the instance of the builder class.
Example:
Builder::table('users', 'u')
->select([...])
->leftJoin('orders', forSubquery: true)
->onSubquery('(
SELECT order_id
FROM {{tableName}}
WHERE status = "active"
) AS o')
->get();onCompound
Add a grouped join condition containing multiple column comparisons.
Combines multiple ON conditions using a nested logical connector and joins the resulting condition group with existing join conditions. Both conditions should use the structure returned by Builder::column().
public onCompound(
Luminova\Database\Query\Column[] $columns,
string $nestedConnector,
string $connector = 'AND'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$columns | Column[] | Column conditions definitions built via Builder::column(...). |
$nestedConnector | string | Logical connector used inside the group (AND or OR). |
$connector | string | Logical connector used to join the group with existing join conditions (AND or OR). |
Return Value:
self - Returns the builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If no conditions are provided or an invalid connector/operator is supplied.
Example:
Builder::table('users', 'u')
->leftJoin('contacts', 'c')
->onCompound([
Builder::column('u.user_id', '=', 'c.contact_user_id'),
Builder::column('u.user_group', '=', 2),
], 'OR')
->select([...])
->get();limit
Sets the maximum number of rows affected or returned by the query.
Adds a LIMIT clause to SELECT, UPDATE, or DELETE operations. For SELECT queries, it restricts the number of rows returned. For UPDATE and DELETE queries, it restricts the number of rows affected.
public limit(int $limit, int $offset = 0): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$limit | int | The maximum number of rows to return or affect. |
$offset | int | The starting offset for SELECT results (default: 0). |
Return Value:
self - Return the current builder instance.
Examples:
Limit selected records:
$users = Builder::table('users')
->where('country', '=', 'NG')
->limit(10, 5)
->get();
// Generates: SELECT * ... LIMIT 5, 10Limit affected rows:
Builder::table('users')
->where('country', '=', 'NG')
->limit(50)
->update(['is_local' => 1]);
// Ensures that at most 50 rows are updated.offset
Sets the query pagination offset for SELECT statements.
This method adds an offset to the LIMIT clause, specifying the start row for returned records.
public offset(int $offset): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$offset | int | The starting offset for the results. |
Return Value:
self - Returns the instance of the builder class.
Example:
Builder::table('users')
->where('country', '=', 'NG')
->limit(10)
->offset(5)
->select()
->get();
// Generates: SELECT * ... LIMIT 5,10top
Set the maximum number of rows returned by the query using SQL Server's TOP clause.
Generates a TOP clause for SQL Server and Microsoft Access. Numeric values are used directly, while SQL expressions or variables are wrapped in parentheses as required by SQL Server. Use limit() for MySQL and SQLite queries.
public top(string|int $limit): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$limit | int|string | Maximum number of rows to return. Numeric values must be 1 or greater. String values may be a SQL expression or variable supported by SQL Server. |
Return Value:
self - Return the query builder instance.
Examples:
$builder->top(10);
// SELECT TOP 10 * FROM users
$builder->top('@limit');
// SELECT TOP (@limit) * FROM users
$builder->top('5 + 5');
// SELECT TOP (5 + 5) * FROM userspagination
Apply pagination to the current query by calculating the SQL LIMIT and OFFSET values from the given page and limit.
This method ensures both the page and limit values are valid, then calculates the offset using the standard formula (page - 1) * limit. If the total number of records is provided, the method will also calculate the total number of pages, clamp the requested page so it does not exceed the last page, and prevent the offset from exceeding the available records.
public pagination(int $page, int $limit, int $records = 0, ?array &$info = null): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$page | int | The requested page number (starting from 1). |
$limit | int | The maximum number of records per page. |
$records | int | Optional total number of records in the dataset. If provided, pagination metadata will be calculated. |
&$info | array|null | Optional reference array {page,limit,offset,records,pages,hasNext,hasPrev,next,prev} to receive pagination details. |
Return Value:
self - Returns the instance of the builder class.
Examples:
Basic pagination:
$users = Builder::table('users')
->pagination(1, 10)
->get();Pagination with total records:
$total = Builder::table('users')->count()->get();
$users = Builder::table('users')
->pagination(2, 10, $total)
->get();Pagination with metadata:
$total = Builder::table('users')->count()->get();
$pageInfo = [];
$users = Builder::table('users')
->pagination(3, 10, $total, $pageInfo)
->get();
print_r($pageInfo);
// [
// 'page' => 3, 'limit' => 10, 'offset' => 20, 'records' => 125,
// 'pages' => 13, 'hasNext' => true, 'hasPrev' => true, 'next' => 4, 'prev' => 2
// ]strict
Enable or disable strict conditions for query execution.
When strict mode is enabled, certain operations (e.g., delete, update) may require a WHERE clause or logic operator to prevent accidental modifications of all records.
public strict(bool $enable = true): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$enable | bool | Whether to enable strict mode (default: true). |
Return Value:
self - Returns the instance of the builder class.
Examples:
Enabling strict mode — throws if no WHERE condition is set:
$deleted = Builder::table('users')
->strict()
->delete();Disabling strict mode — the query executes even without a WHERE condition:
$deleted = Builder::table('users')
->strict(false)
->delete();unionAlias
Sets the alias for the combined UNION subquery.
This alias is used to wrap the UNION result in an outer SELECT statement, e.g. SELECT [alias].column FROM ( ... UNION ... ) AS [alias]. Useful when applying filters, sorting, or pagination to the result of a UNION query.
public unionAlias(string $alias): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$alias | string | The alias to assign to the UNION result set. |
Return Value:
self - Returns the instance of the builder class.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If an invalid alias was provided.
order
Applies ascending or descending sorting order or a raw SQL expression to query results.
This method adds an ORDER BY clause to the query. You can specify either a column name or a raw SQL expression for advanced ordering logic (e.g., custom relevance scores).
public order(string $expression, string $order = 'ASC'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$expression | string | The column name or raw SQL expression to order by. |
$order | string | The sorting direction, ASC or DESC (default: ASC). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the expression is empty or the order is invalid.
Examples:
Builder::table('users')
->order('created_at', 'DESC');
// Generates: ORDER BY created_at DESCBuilder::table('blog')
->order("CASE
WHEN LOWER(title) LIKE '%php framework%' THEN 3
WHEN LOWER(description) LIKE '%php framework%' THEN 2
WHEN LOWER(body) LIKE '%php framework%' THEN 1
ELSE 0 END", 'DESC');
// Generates: ORDER BY CASE ... END DESCrandom
Apply random order to the result set.
Adds a random ordering to the query using MySQL's RAND() function. Optionally accepts a seed for repeatable randomness.
public random(?int $seed = null): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$seed | int|null | Optional seed for deterministic shuffling (default: null). |
Return Value:
self - Return the current builder instance.
Example:
Builder::table('posts')->random();
// Generates: ORDER BY RAND()
Builder::table('posts')->random(42);
// Generates: ORDER BY RAND(42)descending
Applies a descending order to the specified column in the result set.
Use when you want results from largest to smallest / newest to oldest.
public descending(string $column): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column to sort by in descending order. |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the column name is empty or invalid.
ascending
Applies an ascending order to the specified column in the result set.
Use when you want results from smallest to largest / oldest to newest.
public ascending(string $column): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column to sort by in ascending order. |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the column name is empty or invalid.
group
Sets a GROUP BY clause for the query.
public group(string $group): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$group | string | The name of the column to group by. |
Return Value:
self - Return the current builder instance.
Example:
Builder::table('users')
->select(['name'])
->where('status', '=', 'active')
->group('country')
->get();
// Generates: GROUP BY countrywhereHaving
Adds a HAVING condition to the query.
Applies filtering conditions to grouped query results after the GROUP BY clause. Supports columns, raw expressions, and aggregate expressions.
public whereHaving(
Expression|string $expression,
string $operator,
Expression|array|string|int|float|null $value,
string $connector = 'AND'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$expression | Expression|string | Column name or SQL expression to evaluate. |
$operator | string | Comparison operator used for evaluation (=, >, <, >=, <=, etc.). |
$value | Expression|array|string|int|float|null | Value or expression to compare against. |
$connector | string | Logical connector used to combine this condition with previous HAVING clauses (AND or OR). |
Return Value:
self - Return the current builder instance.
Examples:
Builder::table('orders')
->select(['category', 'SUM(sales) AS total_sales'])
->group('category')
->whereHaving('total_sales', '>', 1000)
->get();
// Generates: HAVING total_sales > 1000Builder::table('orders')
->select(['category'])
->group('category')
->whereHaving(Expression::sum('amount'), '>=', 1000)
->whereHaving(Expression::count('order_id'), '>', 10, 'OR')
->get();
// Generates: HAVING SUM(amount) >= 1000 OR COUNT(order_id) > 10whereNotHaving
Adds a negated HAVING condition to the query.
Filters grouped query results where the specified expression does not match the provided comparison condition.
public whereNotHaving(
Expression|string $expression,
string $operator,
Expression|array|string|int|float|null $value,
string $connector = 'AND'
): selfParameters: Same as whereHaving().
Return Value:
self - Return the current builder instance.
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.
public where(string $column, string $operator, mixed $value): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The name of the column to filter by. |
$operator | string | The comparison operator (e.g., =, >=, <>, LIKE, REGEXP). |
$value | (Closure(Builder $static):mixed)|mixed | The value to compare against. |
Return Value:
self - Return the current builder instance.
Examples:
Builder::table('users')
->select()
->where('status', '=', 'active')
->get();
// Generates: WHERE status = 'active'Builder::table('users')
->select()
->where('status', '', ['active', 'disabled'])
->get();
// Generates: WHERE status IN ('active', 'disabled')Builder::table('users')
->select()
->where('status', 'NOT', ['active', 'disabled'])
->get();
// Generates: WHERE status NOT IN ('active', 'disabled')Builder::table('users')
->select()
->where('status', 'NOT EXISTS', Builder::expression('(SELECT 1 FROM views WHERE id = 1)'))
->get();
// Generates: WHERE status NOT EXISTS (SELECT 1 FROM views WHERE id = 1)and
Adds an AND condition to the query.
public and(string $column, string $operator, mixed $value): selfParameters: Same as where().
Return Value:
self - Return the current builder instance.
Example:
Builder::table('users')
->where('status', '=', 'active')
->and('role', '=', 'admin')
->select()
->get();
// Generates: WHERE status = 'active' AND role = 'admin'or
Add a condition to the query using the OR operator.
public or(string $column, string $operator, mixed $value): selfParameters: Same as where().
Return Value:
self - Return the current builder instance.
Example:
Builder::table('users')
->select()
->or('status', '=', 'active')
->or('role', '!=', 'admin')
->get();
// Generates: WHERE status = 'active' OR role != 'admin'whereRaw
Add a raw SQL fragment to the WHERE clause.
Accepts a string or an Expression object. Useful when you need to insert custom SQL that can't be built using structured conditions.
public whereRaw(Expression|string $sql, string $connector = 'AND'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$sql | Expression|string | Raw SQL fragment to append to the WHERE clause. |
$connector | string | Logical operator to join with previous conditions (AND or OR). |
Return Value:
self - Return the current builder instance.
Notes:
- Use this method only when you're sure the input is safe.
- You must include the proper logical operator (e.g. AND, OR) in the raw SQL yourself.
Example:
Builder::table('users')
->select()
->whereRaw('status <> "archived"', 'WHERE')
->whereRaw(new Expression('deleted_at IS NULL'), 'OR')
->get();whereBetween
Add a BETWEEN condition to the query.
Adds a SQL BETWEEN clause for comparing a column value against one or more inclusive ranges. Multiple ranges are grouped and joined using the specified group connector.
public whereBetween(
string $column,
array $values,
string $groupConnector = 'OR',
string $connector = 'AND'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | Column name to apply the condition. |
$values | array | Range boundaries. Must contain an even number of values — each pair represents one range. |
$groupConnector | string | Logical operator between multiple ranges (default: OR). |
$connector | string | Logical operator to join with previous conditions (default: AND). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\DatabaseException - If the range values are invalid.
Examples:
Single range:
$query->whereBetween('balance', [0, 100]);
// (balance BETWEEN :balance_btw_0_a AND :balance_btw_0_b)Multiple ranges:
$query->whereBetween('balance', [0, 100, 300, 500]);
// AND (
// (balance BETWEEN :balance_btw_0_a AND :balance_btw_0_b)
// OR (balance BETWEEN :balance_btw_2_a AND :balance_btw_2_b)
// )Note: Values are bound as parameters. Raw SQL expressions such as
NOW()are not supported — usewhereRaw()for raw SQL conditions.
whereNotBetween
Add a NOT BETWEEN condition to the query.
Matches values outside one or more inclusive ranges. Multiple ranges are grouped and combined using the specified group connector.
public whereNotBetween(
string $column,
array $values,
string $groupConnector = 'AND',
string $connector = 'AND'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | Column name to apply the condition. |
$values | array | Range boundaries. Must contain an even number of values. |
$groupConnector | string | Logical operator between multiple ranges (default: AND). |
$connector | string | Logical operator to join with previous conditions (default: AND). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\DatabaseException - If fewer than two values are provided or an odd number of range values is passed.
Example:
Builder::table('transactions')
->select()
->where('status', 'active')
->whereNotBetween('balance', [0, 100])
->get();
// (balance NOT BETWEEN :balance_btw_0_a AND :balance_btw_0_b)match
Defines columns to be used for keyword and full-text searches.
Registers the columns that can be searched by subsequent search operations (whereSearch(), whereAgainst(), whereNotAgainst(), orderAgainst()). Multiple calls append additional searchable column groups — the most recently defined group applies.
public match(array $columns): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$columns | string[] | A list of column names to include in search matching. |
Return Value:
self - Returns the builder instance for method chaining.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the columns array is empty or is not a list.
Examples:
Builder::table('blogs')
->select()
->match(['title', 'description'])
->whereSearch('fast laptop')
->get();Builder::table('blogs')
->select()
->match(['title', 'description'])
->whereAgainst('fast laptop', Builder::MATCH_BOOLEAN)
->get();whereSearch
Adds keyword search conditions to the query.
The search is performed against columns previously defined via match(). Supports different search patterns, optional keyword splitting, and configurable case sensitivity.
public whereSearch(
string $keyword,
string $pattern = self::SEARCH_CONTAINS,
bool $splitKeyword = false,
bool $caseSensitive = false,
?string $collation = null,
string $connector = 'AND'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$keyword | string | The keyword or phrase to search for. |
$pattern | string | The search pattern to apply, e.g. Builder::SEARCH_CONTAINS. |
$splitKeyword | bool | Whether to split a phrase into individual keywords. |
$caseSensitive | bool | Whether the search should be case-sensitive. |
$collation | string|null | The SQL collation to apply when comparing search values (e.g. utf8mb4_bin). |
$connector | string | Logical operator to join with previous conditions (AND or OR). |
Return Value:
self - Returns the builder instance.
Throws:
- \Luminova\Exceptions\DatabaseException - If the search keyword is empty or no columns have been defined using
match().
Examples:
Builder::table('posts')
->select(['id', 'title', 'description'])
->match(['title', 'description'])
->whereSearch('wireless keyboard')
->get();Builder::table('posts')
->select(['id', 'title', 'description'])
->match(['title', 'description', 'tags'])
->whereSearch('wireless keyboard', splitKeyword: true)
->get();Builder::table('posts')
->select(['id', 'title', 'description'])
->match(['title'])
->whereSearch('PHP', caseSensitive: true)
->get();whereCondition
Add a conditional clause to the query.
Supports scalar values, arrays for IN/NOT IN, and closures for nested conditions. This is the underlying implementation used by where(), and(), and or().
public whereCondition(string $connector, string $column, string $operator, mixed $value): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$connector | string | Logical connector (AND or OR). |
$column | string | Column name or expression. |
$operator | string | Comparison operator (=, !=, >, LIKE, IN, NOT, etc.). |
$value | mixed | Scalar value for normal comparisons, array for IN/NOT IN, or a closure for nested conditions. |
Return Value:
self - Return the current query builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the condition is invalid.
Note: When
$valueis an array, it is transformed into anINorNOT INclause depending on$operator('IN'→IN (...),'NOT'→NOT IN (...)).
Example:
$builder = Builder::table('users')
->select()
->whereCondition('AND', 'id', '=', 100)
->whereCondition('OR', 'id', '=', 101)
->whereCondition('AND', 'name', '=', 'Peter')
->whereCondition('AND', 'roles', 'IN', ['admin', 'editor']);whereLike
Add a LIKE clause to the query for pattern matching.
public whereLike(string $column, string $expression, string $connector = 'AND'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column name to compare. |
$expression | string | The pattern to match using SQL LIKE (e.g. %value%). |
$connector | string | Logical operator to join with previous conditions (AND or OR). |
Return Value:
self - Return the current builder instance.
Example:
Builder::table('users')
->select([...])
->whereLike('name', '%pet%')
->whereLike('username', '%pet%', 'OR')
->get();
// Generates: WHERE name LIKE '%pet%' OR username LIKE '%pet%'whereNotLike
Add a NOT LIKE clause to the query to exclude pattern matches.
public whereNotLike(string $column, string $expression, string $connector = 'AND'): selfParameters: Same as whereLike().
Return Value:
self - Return the current builder instance.
Example:
Builder::table('users')
->select([...])
->whereNotLike('name', '%pet%')
->get();
// Generates: WHERE name NOT LIKE '%pet%'whereRegex
Adds a regular expression matching condition to the query, using the database REGEXP operator.
public whereRegex(string $column, string $pattern, string $connector = 'AND'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column name to apply the regular expression against. |
$pattern | string | The regular expression pattern used for matching. |
$connector | string | Logical connector used to combine this condition with previous conditions. |
Return Value:
self - Return the current builder instance.
wherePattern
Adds a whole-value regular expression matching condition to the query.
The provided value is escaped and converted into a regular expression pattern to safely match complete words or values within the specified column (bound via bind() internally).
public wherePattern(string $column, string $value, string $connector = 'AND'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column name to apply the regular expression against. |
$value | string | The value to match using a generated regular expression. |
$connector | string | Logical connector used to combine this condition with previous conditions. |
Return Value:
self - Return the current builder instance.
whereAgainst
Adds a full-text search condition using MATCH (...) AGAINST (...).
Uses the columns configured through match() to build a MySQL full-text search condition.
public whereAgainst(
mixed $value,
string|int $mode = self::MATCH_NATURAL,
string $connector = 'AND'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$value | mixed | The search value to match against. Supports literal values or a closure. |
$mode | string|int | Search mode constant or raw MySQL AGAINST mode. |
$connector | string | Logical connector used to combine this condition with previous conditions (AND or OR). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\DatabaseException - If match columns are missing or invalid.
Example:
Builder::table('blogs')
->match(['title', 'description'])
->whereAgainst('fast laptop', Builder::MATCH_BOOLEAN)
->get();
// WHERE MATCH(title, description) AGAINST('fast laptop' IN BOOLEAN MODE)whereNotAgainst
Adds a negative full-text search condition using NOT MATCH (...) AGAINST (...).
public whereNotAgainst(
mixed $value,
string|int $mode = self::MATCH_NATURAL,
string $connector = 'AND'
): selfParameters: Same as whereAgainst().
Return Value:
self - Return the current builder instance.
Example:
Builder::table('blogs')
->match(['title', 'description'])
->whereNotAgainst('deprecated mysql')
->get();
// WHERE NOT MATCH(title, description) AGAINST('deprecated mysql')orderAgainst
Set the ordering of full-text search results using MATCH ... AGAINST.
Allows ranking and sorting results based on a full-text search score.
public orderAgainst(
string|int|float $value,
string|int $mode = self::MATCH_NATURAL,
string $order = 'ASC'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$value | string|int|float | The value to search against. |
$mode | string|int | The match mode — one of Builder::MATCH_* or a raw string. |
$order | string | The sort direction, ASC or DESC (default: ASC). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\DatabaseException - If no match columns have been defined via
match(). - \Luminova\Exceptions\InvalidArgumentException - If the sort order is invalid.
See Also: match(), whereAgainst().
whereNotNull
Adds a condition to filter results where the given column is NOT NULL.
public whereNotNull(string $column, string $connector = 'AND'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column name to check for non-null values. |
$connector | string | Logical operator to join with previous conditions (AND or OR). |
Return Value:
self - Return the current builder instance.
Example:
Builder::table('users')
->where('country', '=', 'NG')
->whereNotNull('address')
->select()
->get();whereNull
Adds a condition to filter results where the given column is NULL.
public whereNull(string $column, string $connector = 'AND'): selfParameters: Same as whereNotNull().
Return Value:
self - Return the current builder instance.
distance
Add a calculated distance column to the SELECT clause.
Calculates the distance between a reference coordinate and each row using either the database's spatial functions or the Haversine formula. Call whereWithin() afterward to filter by distance.
public distance(
string $lngColumn,
string $latColumn,
string|float|null $longitudeExpr = null,
string|float|null $latitudeExpr = null,
string $unit = 'km',
string $method = 'sphere',
?string $alias = 'distance'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$lngColumn | string | Longitude column name. |
$latColumn | string | Latitude column name. |
$longitudeExpr | string|float|null | Longitude value or SQL placeholder (default: :longitude). |
$latitudeExpr | string|float|null | Latitude value or SQL placeholder (default: :latitude). |
$unit | string | Distance unit: km, mi, m, or ft. |
$method | string | Distance calculation method: sphere or haversine. |
$alias | string|null | Distance column alias (default: distance). |
Return Value:
self - Return the builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the unit, method, or alias is invalid.
Examples:
Using placeholders:
$user = Builder::table('users')
->distance('user_longitude', 'user_latitude', ':longitude', ':latitude')
->bind(':longitude', 3.3792)
->bind(':latitude', 6.524)
->get();
echo $user->distance;Using coordinates directly:
Builder::table('users')
->distance('user_longitude', 'user_latitude', 3.3792, 6.5244)
->get();See Also: whereWithin(), whereDistance().
whereWithin
Adds a geographic distance boundary condition to the query.
Applies a bounding box filter using the coordinates, columns, and unit configured by the most recent distance() call. Limits results to records within the specified radius from the configured reference point.
public whereWithin(float $radius, string $connector = 'AND'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$radius | float | Maximum distance boundary from the reference coordinates. |
$connector | string | Logical connector used to combine this condition with previous query conditions (AND or OR). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\LogicException - If
distance()has not been called first. - \Luminova\Exceptions\InvalidArgumentException - If the radius or configured distance unit is invalid.
Example:
Builder::table('users')
->select(['id', 'name'])
->distance('user_latitude', 'user_longitude', 6.5244, 3.3792)
->whereWithin(10)
->ascending('distance')
->get();whereNotWithin
Adds a geographic distance exclusion condition to the query.
Excludes records located within the specified radius from the reference coordinates configured by the most recent distance() call.
public whereNotWithin(float $radius, string $connector = 'AND'): selfParameters: Same as whereWithin().
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\LogicException - If
distance()has not been called first. - \Luminova\Exceptions\InvalidArgumentException - If the radius or configured distance unit is invalid.
whereDistance
Restrict results to rows within a geographic bounding box.
Applies a fast latitude/longitude range filter around the specified coordinate. Useful for reducing candidate rows before performing distance calculations or sorting by distance. Unlike distance(), this method does not add a calculated distance column.
public whereDistance(
string $lngColumn,
string $latColumn,
string|float $longitudeExpr,
string|float $latitudeExpr,
float $radius = 10.0,
string $unit = 'km',
string $connector = 'AND'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$lngColumn | string | Longitude column name. |
$latColumn | string | Latitude column name. |
$longitudeExpr | string|float | Longitude value or SQL placeholder. |
$latitudeExpr | string|float | Latitude value or SQL placeholder. |
$radius | float | The maximum search radius (default: 10.0). |
$unit | string | Distance unit: km, mi, m, or ft. |
$connector | string | Logical operator to join with previous conditions (AND or OR). |
Return Value:
self - Return the builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the radius, unit, or coordinates are invalid.
Example:
Builder::table('users')
->whereDistance('longitude', 'latitude', 3.3792, 6.5244, 10)
->get();whereIn
Adds an IN condition to the query using the IN (...) SQL expression.
Use this method to find rows where the given column's value matches any value in a provided list.
See Also: Builder::whereNotIn().
public whereIn(string $column, Closure|array $values, string $connector = 'AND'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column name to match against. |
$values | Closure|array | A list of values, or a closure returning an array of values. |
$connector | string | Logical operator to join with previous conditions (AND or OR). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the provided values are empty or invalid.
- \Luminova\Exceptions\JsonException - If an error occurs while encoding the values.
Example:
Builder::table('languages')
->select()
->whereIn('tag', ['php', 'sql'])
->get();
// Generates: IN ('php', 'sql')Where Not In:
Builder::table('users')
->where('country', '=', 'NG')
->whereNotIn('state', ['Enugu', 'Lagos', 'Abuja']);
// Generates: NOT IN ('Enugu', 'Lagos', 'Abuja')whereExists
Adds an EXISTS subquery condition to the current query.
Unlike Builder::exists(), this method does not execute a query todetermine whether records exist. Instead, it appends an SQL EXISTS conditionthat is evaluated as part of the current query.
The callback receives a query builder initialized for the target table.Conditions added inside the callback are compiled into the EXISTS subquery.
See Also: Builder::whereNotExists().
public whereExists(Closure $callback, string $connector = 'AND'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$callback | Closure | Callback used to configure the EXISTS query. |
$connector | string | Logical operator to join with previous conditions (AND or OR). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the table name is empty.
- \Luminova\Exceptions\LogicException - If the callback does not configure a valid subquery.
Example:
$user = Builder::table('users')
->find(['user_id'])
->and('user_posts', '=', [200, 2, 6])
->whereExists(function (Builder $query) {
$query->from('posts', 'p')
$query->where('post_status', '=', 'active');
})
->limit(3)
->get();whereInset
Adds a condition using MySQL's FIND_IN_SET() function.
Searches for a value within a comma-separated list and supports predefined matching modes or custom comparison operators. The list source can be a literal comma-separated value string, an array of values, or a column containing a comma-separated list.
public whereInset(
string $search,
string $operator,
array|string $list,
bool $searchAsColumn = false,
string $connector = 'AND'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$search | string | The value to search for, or column name when $searchAsColumn is true. |
$operator | string | Comparison operator or predefined matching mode. |
$list | array|string | Comma-separated values, array of values, or column name containing the list. |
$searchAsColumn | bool | Whether $search should be treated as a column reference. |
$connector | string | Logical connector used to join this condition (AND or OR). |
Supported Operators:
exists,>- Checks whether the value exists in the list.=,first- Checks whether the value is the first item in the list.last- Checks whether the value is the last item in the list.position- Returns the matched position from the list.contains- Performs a partial match against the list value.none- Checks that the value does not exist in the list.
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If
$listis empty.
Examples:
Builder::table('fruits')
->whereInset('banana', '= 2', ['apple', 'banana', 'orange'])
->get();Builder::table('employees')
->whereInset('PHP', 'exists', 'column_language_skills')
->get();Builder::table('employees')
->whereInset('department', 'exists', 'HR,Finance,Marketing', true)
->get();whereNotInset
Adds a negative FIND_IN_SET() condition — filters records where the given value does not exist in the specified comma-separated list.
public whereNotInset(
string $search,
array|string $list,
bool $searchAsColumn = false,
string $connector = 'AND'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$search | string | The value to search for, or column name when $searchAsColumn is true. |
$list | array|string | Comma-separated values, array of values, or column name containing the list. |
$searchAsColumn | bool | Whether $search should be treated as a column reference. |
$connector | string | Logical connector used to join this condition (AND or OR). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If
$listis empty.
Example:
Builder::table('employees')
->whereNotInset('PHP', 'column_language_skills')
->get();whereGroup
Conjoin multiple conditions using either AND or OR.
Creates a logical condition group where conditions are combined using the specified group connector, then joined to the query using the base connector.
public whereGroup(
Luminova\Database\Query\Column[] $conditions,
string $groupConnector = 'AND',
string $connector = 'AND'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$conditions | Column[] | The column conditions to group. Build via Builder::column(...). |
$groupConnector | string | The AND or OR logical connector within the group (default: AND). |
$connector | string | The base logical connector to connect previous conditions (default: AND). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If an invalid group operator or chain clause is specified.
Example:
Builder::table('fooTable')
->whereGroup([
Builder::column('column1', '=', 1),
Builder::column('column2', '=', 2)
], 'OR');
// Generates: WHERE (column1 = 1 OR column2 = 2)whereNested
Creates a nested conjoin condition group by combining two condition sets.
Groups two sets of conditions and binds them with the specified logical operator.
public whereNested(
Luminova\Database\Query\Column[] $leftConditions,
Luminova\Database\Query\Column[] $rightConditions,
string $groupConnector = 'AND',
string $nestedConnector = 'AND',
string $connector = 'AND'
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$leftConditions | Column[] | An array of first column conditions group. |
$rightConditions | Column[] | An array of second column conditions group. |
$groupConnector | string | The AND or OR logical connector within each group (default: AND). |
$nestedConnector | string | The AND or OR logical connector to bind groups (default: AND). |
$connector | string | The base logical connector to connect previous conditions (default: AND). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If an invalid group operator is specified.
Example:
Builder::table('fooTable')
->where('fooUser', '=', 100)
->whereNested(
[Builder::column('column1', '=', 1), Builder::column('column2', '=', 2)],
[Builder::column('column1', '=', 1), Builder::column('column2', '=', 2)],
'OR', // Inner group logical connector
'AND' // Outer group logical connector
);
// Generates: WHERE fooUser = 100 AND ((column1 = 1 OR column2 = 2) AND (column1 = 1 OR column2 = 2))set
Define a column value for an update operation.
This method stores column values used to build the SET clause of anUPDATE query. It can be called multiple times to define multiple columns.
Values may be scalar values, closures, or SQL expressions.
public set(string $column, mixed $value): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The name of the column to set. |
$value | mixed | The value to assign (can be a simple value, a closure, or an expression). |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the column name is empty or invalid.
Example:
Builder::table('users')
->where('id', 1)
->set('status', 'active')
->set('updated_at', Builder::datetime())
->update();
// UPDATE users SET status = 'active', updated_at = CURRENT_TIMESTAMP WHERE id = 1values
Add a row of column values for an insert operation.
This method stores associative column-value pairs used to build an INSERTquery. It can be called multiple times to insert multiple rows at once.
The insert() method uses these values to generate the inserted rows.
public values(array $values): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$values | array<string,mixed> | Associative array of column-value pairs. |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the provided array is a list instead of an associative column-value map.
Examples:
Single row insert:
Builder::table('users')
->values([
'name' => 'Peter',
'age' => 30,
])
->insert();
// INSERT INTO users (name, age) VALUES ('Peter', 30)Batch insert:
Builder::table('users')
->values([
'name' => 'Peter',
'age' => 30,
])
->values([
'name' => 'John',
'age' => 25,
])
->insert();
// INSERT INTO users (name, age) VALUES ('Peter', 30), ('John', 25)bind
Bind a named placeholder parameter to a value.
Manually assign values to SQL placeholders (:param) used anywhere in the query — including joins, clauses, or raw column expressions.
public bind(string $placeholder, mixed $value): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$placeholder | string | The named placeholder. Must start with a colon : (e.g. :id). |
$value | (Closure(Builder $static):mixed)|mixed | The value to bind. Arrays are JSON-encoded. |
Return Value:
self - Return the current builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the placeholder does not start with a colon
:.
Examples:
Binding inside a JOIN condition:
$result = Builder::table('users', 'u')
->select()
->innerJoin('orders', 'o')
->on('o.order_user_id', '=', 'u.user_id')
->on('o.order_id', '=', ':oid')
->bind(':oid', 13445)
->where('u.user_id', '=', 100)
->get();Binding inside a SELECT column expression:
$result = Builder::table('users', 'u')
->select([
'u.*',
'ST_Distance_Sphere(u.location, ST_SRID(POINT(:lng, :lat), 4326)) / 1000 AS distance'
])
->where('u.status', '=', 'active')
->whereHaving('distance', '<=', 10)
->bind(':lat', 1.3521)
->bind(':lng', 103.8198)
->get();unbind
Remove one or more previously bound placeholders. If a placeholder does not exist, it is silently ignored.
public unbind(string|array $placeholder): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$placeholder | string|string[] | A named placeholder or an array of placeholders (e.g. :id or [':id', ':name']). |
Return Value:
self - Return the current builder instance.
returns
Set the query result return type.
By default, query results are returned as stdClass objects. Use this method to customize how results are hydrated, including returning associative arrays, object lists, prepared statements, or instances of a specified class.
public returns(string $type, callable|array $arguments = []): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$type | class-string|string | Builder::RETURN_OBJECT, Builder::RETURN_OBJECT_LIST, Builder::RETURN_ARRAY, Builder::RETURN_STATEMENT, or a valid class name. |
$arguments | array|(callable():array) | Constructor arguments passed to the class constructor (only applies when $type is a class name). |
Return Value:
self - Return the current query builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If an invalid return type is specified.
Examples:
$builder->returns(Builder::RETURN_OBJECT)->get();
$builder->returns(Builder::RETURN_ARRAY)->get();
$stmt = $builder->returns(Builder::RETURN_STATEMENT)->select();
$users = $builder->returns(User::class)->get();
$users = $builder->returns(User::class, ['role' => 'admin'])->get();Note: Call this method before executing the query, such as with
get(),next(),scan(),cursor(), or similar methods.
distinct
Enable or disable DISTINCT for SELECT queries.
public distinct(bool $distinct = true): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$distinct | bool | Whether to apply DISTINCT (default: true). |
Return Value:
self - Return the current query builder instance.
Example:
Builder::table('users')
->select(['email'])
->distinct()
->get();
// SELECT DISTINCT email FROM usersreplace
Enable or disable the REPLACE feature when inserting or copying records.
Applies to: insert() (before calling it) and copy() (before calling into()). If enabled, existing rows with the same primary or unique key are deleted before the new row is inserted.
public replace(bool $useReplace = true): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$useReplace | bool | Set to true to use REPLACE instead of INSERT (default: true). |
Return Value:
self - Return the current builder instance.
Warning: Since
REPLACEremoves and re-inserts data, it can reset auto-increment values and trigger delete/insert events instead of update events.
closeAfter
Sets the auto-close connection status for the current query.
By default, the connection remains open after query execution.
public closeAfter(bool $close = true): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$close | bool | Whether to automatically close the connection after executing the query (default: true). |
Return Value:
self - Return the current builder instance.
safeMode
Enables or disables safe mode for write and altering operations on the current table.
When enabled, the next write operation automatically runs inside an internal transaction if no explicit transaction is already active.
public safeMode(bool $enable = true): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$enable | bool | Whether to enable or disable safe mode. |
Return Value:
self - Returns the current builder instance.
Applies to: insert(), update(), delete(), drop(), truncate(), copy(), create(), createTemp(), execute().
Example:
$tbl = Builder::table('users')
->safeMode()
->insert([...]);getCache
Retrieves the current cache instance.
public getCache(): ?CacheReturn Value:
Luminova\Base\Cache|null - Returns the current cache instance if set, or null if no cache has been initialized.
getCacheInfo
Get cache information for the current query.
public getCacheInfo(): arrayReturn Value:
array{driver:?string,storage:?string,persistentId:?string,expiry:?int,connected:bool,hit:bool,stored:bool} - Returns an associative array containing cache details:
driver: The cache driver in use (e.g.,file,redis).storage: The storage name (defaults to the table name).persistentId: The persistent identifier for the cache entry.expiry: The expiration time for the cached result (if applicable).hit: Boolean indicating if the result was retrieved from cache.connected: Boolean indicating if the cache connection is active.stored: Boolean indicating if the result was successfully stored in cache.
deleteCache
Delete a cached item for the current table query.
The cache must be initialized using cache() before calling this method.
public deleteCache(): boolReturn Value:
bool - Returns true if the cache item was deleted, otherwise false.
Throws:
- \Luminova\Exceptions\RuntimeException - If caching was not initialized or no cache key is available.
Example:
$deleted = Builder::table('users')
->cache('user-1')
->deleteCache();clearCache
Clear all cached items in the current cache storage.
The storage is determined by the cache() configuration. If no custom storage was defined, the table name is used as the default storage.
public clearCache(): boolReturn Value:
bool - Returns true if the storage was cleared successfully, otherwise false.
Throws:
- \Luminova\Exceptions\RuntimeException - If caching has not been initialized.
Examples:
$deleted = Builder::table('users')
->cache(storage: 'my-users')
->clearCache();$deleted = Builder::table('users')
->cache(storage: null)
->clearCache();cache
Enable result caching for the current query.
Configures the cache key, storage, expiration time, and optional settings for file-based, memcached, or redis cache drivers. When caching is enabled globally through cacheable(), this method prepares the cache and checks whether a valid cached result already exists.
public cache(
?string $key = null,
?string $storage = null,
DateTimeInterface|int|null $expiry = 7 * 24 * 60 * 60,
?string $persistentId = null
): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$key | string|null | Unique identifier for the cached result. |
$storage | string|null | Optional cache storage name (defaults: table name or 'default'). |
$expiry | DateTimeInterface|int|null | Cache expiration time (default: 7 days). |
$persistentId | string|null | Optional persistent ID for memcached/redis, or a subdirectory for the file cache driver. |
Return Value:
self - Returns the current builder instance.
Throws:
- \Luminova\Exceptions\CacheException - If cache initialization or cache access fails.
Examples:
Caching a query result:
$user = Builder::table('users')
->find([...])
->where('id', '=', 'u100')
->cache(
key: 'u100',
storage: 'userProfile',
expiry: 7 * 24 * 60 * 60,
persistentId: 'app-users'
)
->get();Caching a raw query result:
$user = Builder::query("SELECT * FROM users WHERE id = :id")
->cache(key: 'u100', storage: 'userProfile', persistentId: 'app-users')
->execute(['id' => 'u100']);See Also: cacheable(), hasCache(), isCached(), isCacheHit(), getCacheKey().
cacheable
Globally enable or disable all caching for subsequent select operations.
public cacheable(bool $enable): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$enable | bool | The caching status action. |
Return Value:
self - Return the current builder instance.
Note: By default caching is enabled once you call the
cache()method.
hasCache
Check if a cache result already exists for the current key in storage.
Requires calling cache() beforehand.
public hasCache(): boolReturn Value:
bool - Return true if a cache entry exists and has not expired, otherwise false.
getCacheKey
Retrieve the raw (unhashed) cache key for the current query context.
public getCacheKey(): ?stringReturn Value:
string|null - Returns the cache key if caching is enabled, otherwise null.
isCached
Check if the current query has been stored in cache.
public isCached(): boolReturn Value:
bool - Return true if the query is cacheable and stored, false otherwise.
isCacheHit
Check if the current query result was retrieved from cache.
public isCacheHit(): boolReturn Value:
bool - Return true if the result was retrieved from cache, false otherwise.
insert
Execute and insert one or many records into a database table.
Accepts either a single associative array (column => value) or an array of multiple associative arrays (to insert many rows at once). By default it uses prepared statements for safety and performance.
public insert(?array $values = null, bool $usePrepare = true, bool $escapeValues = true): intParameters:
| Parameter | Type | Description |
|---|---|---|
$values | array|null | Records to insert, or values built via set(). Keys are column names, values are the values to insert. |
$usePrepare | bool | Whether to use prepared statements (default: true). |
$escapeValues | bool | Whether to escape values if $usePrepare is true (default: true). |
Return Value:
int - Returns the number of rows inserted.
Throws:
- \Luminova\Exceptions\DatabaseException - If the data format is invalid (e.g., not associative arrays).
- \Luminova\Exceptions\JsonException - If array values cannot be encoded to JSON.
Examples:
Insert a single row:
Builder::table('logs')->insert([
'message' => 'User login',
'created_at' => Builder::now()
]);Insert multiple rows:
Builder::table('users')->insert([
['name' => 'Alice', 'age' => 28],
['name' => 'Bob', 'age' => 34]
]);Insert inside a transaction:
$tbl = Builder::table('users');
$tbl->transaction();
$inserted = $tbl->insert([
['name' => 'Charlie', 'age' => 40],
['name' => 'Diana', 'age' => 36]
]);
if ($inserted) {
$tbl->commit();
} else {
$tbl->rollback();
}Use REPLACE instead of INSERT:
Builder::table('logs')
->replace(true)
->insert([
'id' => 1,
'message' => 'System reboot',
'created_at' => Builder::expression('NOW()')
]);Insert with ON DUPLICATE KEY UPDATE:
Builder::table('users')
->onDuplicate('last_login', '=', Builder::expression('NOW()'))
->insert([
'user_id' => 1001,
'name' => 'John Doe',
'last_login' => Builder::expression('NOW()')
]);Insert while ignoring duplicate key errors:
Builder::table('users')
->ignoreDuplicate(true)
->insert(['user_id' => 1002, 'name' => 'Jane Doe']);onDuplicate
Specifies an action to take when a duplicate key conflict occurs during an INSERT operation.
Supported operations:
=→ Replace the value (e.g.,email = '[email protected]').+=→ Increment the existing value.-=→ Decrement the existing value.
public onDuplicate(string $column, string $operation, mixed $value): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column to update on duplicate key. |
$operation | string | The operation to apply ("=", "+=", "-="). |
$value | mixed|Closure | The new value or increment amount. |
Return Value:
self - Return the current builder instance.
Example:
Builder::table('users')
->onDuplicate('points', '=', Builder::expression('VALUES(points)'))
->onDuplicate('points', '+=', 10)
->onDuplicate('email', '=', '[email protected]')
->insert([
['id' => 1, 'name' => 'Alice', 'points' => 50, 'email' => '[email protected]']
]);Note:
onDuplicate()cannot be combined withreplace(true)orignoreDuplicate(true).
ignoreDuplicate
Sets whether duplicates should be ignored during insertion.
public ignoreDuplicate(bool $ignore = true): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$ignore | bool | Whether to ignore duplicates (default: true). |
Return Value:
self - Return the current builder instance.
Note: Cannot be combined with
replace(true)oronDuplicate(...).
increment
Increment a numeric column value during update, without requiring a prior read.
public increment(string $column, float|int $count = 1): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column name to increment. |
$count | float|int | The amount to add (default: 1). |
Return Value:
self - Return the current builder instance.
Example:
Builder::table('users')
->where('id', '=', 1)
->increment('points', 5)
->update();decrement
Decrement a numeric column value during update, without requiring a prior read.
public decrement(string $column, float|int $count = 1, bool $allowNegative = false): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column name to decrement. |
$count | float|int | The amount to subtract (default: 1). |
$allowNegative | bool | Whether to allow the resulting value to go negative. |
Return Value:
self - Return the current builder instance.
Example:
Builder::table('users')
->where('id', '=', 1)
->decrement('attempts', 1)
->update();update
Execute an update query on the current table.
Builds and executes an UPDATE statement using the provided values, or values previously assigned through set(), increment(), or decrement(). Existing constraints such as WHERE, joins, ordering, and limits are applied.
public update(?array $values = null): intParameters:
| Parameter | Type | Description |
|---|---|---|
$values | array|null | Optional column-value pairs to update. If omitted, values assigned via builder mutation methods are used. |
Return Value:
int - Number of affected rows.
Throws:
- \Luminova\Exceptions\DatabaseException - When no update values are provided, invalid values are supplied, or strict mode requires a
WHEREclause that is missing. - \Luminova\Exceptions\JsonException - When JSON encoding fails during value binding.
Examples:
Builder::table('users')
->where('id', '=', 1)
->update(['last_login' => Builder::datetime(), 'attempts' => 0]);Builder::table('users')
->where('id', '=', 1)
->set('last_login', Builder::datetime())
->set('attempts', 0)
->update();Builder::table('users')
->where('id', '=', 1)
->update(['score' => Builder::expression('score + 5')]);With joins and strict mode:
Builder::table('orders', 'o')
->innerJoin('users', 'u')
->on('u.id', '=', 'o.user_id')
->where('u.status', '=', 'inactive')
->strict(true) // prevents missing WHERE clause accidents
->update(['o.cancelled' => 1]);copy
Prepares a query to copy data from the current table into another table.
Defines the source columns used by an INSERT ... SELECT operation. Must be followed by into(). Rows are copied without loading data into application memory.
public copy(array $columns): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$columns | string[] | The source columns to copy. |
Return Value:
self - Returns the current builder instance.
Example:
$result = Builder::table('users')
->copy(['id', 'email', 'created_at'])
->where('id', '=', 100)
->into('backup_users', ['id', 'email', 'created_at']);
// Generates:
// INSERT INTO backup_users (id, email, created_at)
// SELECT id, email, created_at FROM users WHERE id = 100;See Also: into().
into
Executes a prepared copy() query and inserts the selected rows into a target table.
Finalizes a copy() operation by generating an INSERT ... SELECT statement. Can use normal insert mode, INSERT IGNORE, or REPLACE depending on the configured insert options.
public into(string $table, array $columns): intParameters:
| Parameter | Type | Description |
|---|---|---|
$table | string | Target table to insert copied data into. |
$columns | string[] | Target table columns receiving the copied values. |
Return Value:
int - Returns the number of affected rows.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If the target table name is empty.
- \Luminova\Exceptions\DatabaseException - If copy mode is not active, or the source and destination column counts don't match.
- \Luminova\Exceptions\JsonException - If JSON value encoding fails.
Warning: Source and destination columns must match in count and compatible order.
execute
Execute an SQL query that was set earlier with query().
You can use this method to execute prepared SQL statements with optional placeholder values and control how the result is returned.
Return Modes (see Luminova's global constants): RETURN_ALL, RETURN_NEXT, RETURN_2D_NUM, RETURN_ID, RETURN_COUNT, RETURN_COLUMN, RETURN_INT, RETURN_STMT, RETURN_RESULT.
public execute(
?array $placeholder = null,
int $returnMode = RETURN_ALL,
int $fetchAs = FETCH_OBJ,
bool $escape = false
): mixedParameters:
| Parameter | Type | Description |
|---|---|---|
$placeholder | array|null | Optional key-value pairs for query placeholders. |
$returnMode | int | Record return mode, e.g. RETURN_NEXT (default: RETURN_ALL). |
$fetchAs | int | Result fetch mode, e.g. FETCH_ASSOC, FETCH_CLASS (default: FETCH_OBJ). |
$escape | bool | Whether to escape placeholder values (default: false). |
Return Value:
mixed - Returns the query result, a statement object, or false on failure.
Throws:
- \Luminova\Exceptions\DatabaseException - If called before setting a query with
query().
Examples:
$result = Builder::query("SELECT * FROM users LIMIT 10")->execute();$user = Builder::query("SELECT * FROM users WHERE id = :id LIMIT 1")
->execute(['id' => 100], RETURN_NEXT);$user = Builder::query("SELECT * FROM users WHERE id = :id")
->cache()
->execute(['id' => 1]);select
Add query to select multiple records from the table.
public select(array $columns = ['*']): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$columns | string[] | The table columns to select (e.g. ['foo', 'bar'] or ['*']). |
Return Value:
self - Return the current builder instance.
Applies to: get(), promise(), stmt(), next().
Example:
$users = Builder::table('users')
->select(['votes', 'name'])
->where('country', '=', 'NG')
->get();find
Select a single/next record from the table.
Unlike select(), this selects a single row with a strict WHERE condition required.
public find(array $columns = ['*']): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$columns | string[] | The table columns to select. |
Return Value:
self - Return the current builder instance.
Applies to: get(), promise(), stmt(), next().
Example:
$user = Builder::table('users')
->find(['votes', 'name'])
->where('country', '=', 'NG')
->get();count
Build a query to count records or distinct column values.
When get() is called, returns an int representing the number of matching records — 0 if no match.
public count(array|string $column = '*', bool $distinct = false): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string[]|string | The column name, list of columns, or * to count all records (default: *). |
$distinct | bool | Whether to count only distinct values. |
Return Value:
self - Return the current builder instance.
Applies to: get(), promise(), stmt(), next().
Examples:
$total = Builder::table('users')
->count()
->where('country', '=', 'NG')
->get();$total = Builder::table('users')
->count('email', distinct: true)
->get();min
Build a query to retrieve the minimum value from a column.
public min(string $column): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column name to retrieve the minimum value from. |
Return Value:
self - Return the current builder instance.
Applies to: get(), promise(), stmt(), next().
Example:
$date = Builder::table('users')->min('created_at')->get();max
Build a query to retrieve the maximum value from a column.
public max(string $column): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | The column name to retrieve the maximum value from. |
Return Value:
self - Return the current builder instance.
Applies to: get(), promise(), stmt(), next().
Example:
$date = Builder::table('users')->max('created_at')->get();sum
Build a query to calculate the total sum of a numeric column.
public sum(array|string $column = '*', bool $distinct = false): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string[]|string | The column name or list of columns to sum. |
$distinct | bool | Whether to sum only distinct values. |
Return Value:
self - Return the current builder instance.
Applies to: get(), promise(), stmt(), next().
Example:
$votes = Builder::table('users')
->sum('votes')
->where('country', '=', 'NG')
->get();average
Build a query to calculate the average value of a numeric column.
public average(array|string $column = '*', bool $distinct = false): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string[]|string | The column name or list of columns to average. |
$distinct | bool | Whether to average only distinct values. |
Return Value:
self - Return the current builder instance.
Applies to: get(), promise(), stmt(), next().
Example:
$votes = Builder::table('users')
->where('country', '=', 'NG')
->average('votes')
->get();avg
Alias for average().
public avg(array|string $column = '*', bool $distinct = false): selfExample:
$votes = Builder::table('users')
->where('country', '=', 'NG')
->avg('votes')
->get();columns / unionColumns
Sets the columns to be used in a UNION/UNION ALL operation. By default, all columns (*) are included. columns() is an alias for unionColumns().
public unionColumns(array $columns = ['*']): self
public columns(array $columns = ['*']): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$columns | string[] | The columns to include in the union operation (default: ['*']). |
Return Value:
self - Returns the instance of builder class.
Example:
$result = $active->unionAll($inactive)
->unionColumns(['id', 'email'])
->get();See Also: union(), unionAll().
union
Combines the current query with another using the UNION operator.
Ensures both queries have the same number of columns. Resulting rows will be distinct.
public union(Builder|Closure $union): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$union | Builder|Closure | Another Builder instance, or a closure that returns a Builder instance to union with. |
Return Value:
self - Return current parent Builder object.
Throws:
- \Luminova\Exceptions\DatabaseException - If the column counts between queries differ or an error occurs.
Example:
$active = Builder::table('users')
->select(['id', 'email'])
->where('status', '=', 'active');
$inactive = Builder::table('users')->select(['id', 'email'])
->where('status', '=', 'inactive');
$all = $active->union($inactive)
->descending('id')
->limit(10)
->get();Note:
When using union tables, do not call
get(),promise()orstmt()before adding tables to another.Always call after all tables have been added.
unionAll
Combines the current query with another using the UNION ALL operator. Unlike union(), this includes duplicate rows from both queries.
public unionAll(Builder|Closure $union): selfParameters: Same as union().
Return Value:
self - Return current parent Builder object.
Throws:
- \Luminova\Exceptions\DatabaseException - If error occurs.
get
Execute selectable query and return the result.
Used after building a query with methods like select(), find(), count(), sum(), average(), min(), max().
public get(int $fetchAs = FETCH_OBJ, ?int $returnMode = null): mixedParameters:
| Parameter | Type | Description |
|---|---|---|
$fetchAs | int | Result fetch mode, e.g. FETCH_ASSOC, FETCH_CLASS (default: FETCH_OBJ). |
$returnMode | int|null | Record return mode, e.g. RETURN_NEXT (default: RETURN_ALL). |
Return Value:
mixed - Returns query result on success, or false/null on failure.
Throws:
- \Luminova\Exceptions\DatabaseException - If no query selector is set or execution fails.
Examples:
$result = Builder::table('users')
->select(['email', 'name'])
->where('country', '=', 'NG')
->get(FETCH_OBJ);$user = Builder::table('users')
->find(['email', 'name'])
->get(FETCH_ASSOC, RETURN_NEXT);next
Fetches the next row from the result set.
Unlike get() or scan(), this keeps an active database cursor and retrieves one row at a time — suitable for large datasets where loading all rows into memory is unnecessary.
public next(int $mode = FETCH_OBJ): mixedParameters:
| Parameter | Type | Description |
|---|---|---|
$mode | int | Result fetch mode, e.g. FETCH_ASSOC, FETCH_NUM (default: FETCH_OBJ). |
Return Value:
mixed - Returns the next row, or null when no more rows exist.
Throws:
- \Luminova\Exceptions\RuntimeException - If called on a non-select builder.
Example:
$stmt = Builder::table('users')
->select(['email', 'name'])
->where('country', '=', 'NG')
->limit(4);
while ($row = $stmt->next(FETCH_OBJ)) {
echo $row->email;
}
$stmt->free();Note:
next()executes statements directly, so query result caching is not supported.
fetch
Deprecated: Use next() instead.
public fetch(int $fetchAs = FETCH_OBJ): mixedExecutes and fetches one row at a time from the previously prepared statement.
Throws:
- \Luminova\Exceptions\DatabaseException - If no query selector is set or execution fails.
cursor
Iterates through a result set using keyset (cursor-based) pagination.
Designed for large datasets — uses indexed column traversal instead of OFFSET, making it significantly more efficient for deep scans. The provided column must be indexed and must exist in the selected result set.
public cursor(string $column, int &$lastId, int $limit = 100): ?arrayParameters:
| Parameter | Type | Description |
|---|---|---|
$column | string | Integer indexed column used for cursor tracking (e.g. id, age). |
&$lastId | int | Reference cursor (last seen column value); updated after each call. |
$limit | int | Number of rows to fetch per iteration. |
Return Value:
array|null - Returns an array of results, or null when no more data exists.
Throws:
- \Luminova\Exceptions\RuntimeException - If called on an unsupported builder object.
Example:
$lastId = 0;
$builder = Builder::table('users')
->select(['id', 'name'])
->where('country', '=', 'NN');
do {
$rows = $builder->cursor('id', $lastId, 100);
foreach ($rows as $row) {
// process row
}
} while ($lastId > 0);See Also: scan() for offset-based iteration.
scan
Iterates through a result set using offset-based pagination.
Suitable for small to medium datasets where OFFSET pagination is acceptable. The cursor represents the current offset position and increments automatically based on the number of rows returned; it resets to 0 when no more results are available.
public scan(?int &$iterator, int $limit = 100): ?arrayParameters:
| Parameter | Type | Description |
|---|---|---|
&$iterator | int|null | Reference cursor (offset position). |
$limit | int | Number of rows to fetch per iteration. |
Return Value:
array|null - Returns an array of results, or null when no more data exists.
Throws:
- \Luminova\Exceptions\RuntimeException - If called on an unsupported builder object.
Example:
$cursor = 0;
$builder = Builder::table('users')
->select(['id', 'name'])
->where('country', '=', 'NN');
do {
$rows = $builder->scan($cursor, 100);
foreach ($rows as $row) {
// process row
}
} while ($cursor > 0);See Also: cursor() for keyset-based iteration.
promise
Executes query and returns results wrapped in a promise object. Resolves with the query results or rejects on error.
Applies to: select(), find(), count(), sum(), average(), min(), max().
public promise(int $fetchAs = FETCH_OBJ, ?int $returnMode = null): PromiseInterfaceParameters:
| Parameter | Type | Description |
|---|---|---|
$fetchAs | int | Result fetch mode, e.g. FETCH_ASSOC, FETCH_CLASS (default: FETCH_OBJ). |
$returnMode | int|null | Record return mode, e.g. RETURN_NEXT (default: RETURN_ALL). |
Return Value:
Luminova\Promise\PromiseInterface - Returns a promise that resolves with query results or rejects with a Throwable.
Example:
Builder::table('users')
->find(['email', 'name'])
->where('country', '=', 'NG')
->promise(FETCH_OBJ)
->then(function (mixed $result) {
echo $result->name;
})
->catch(function (Throwable $e) {
echo $e->getMessage();
});stmt
Executes query and returns a prepared statement object.
Allows you to work directly with the underlying database driver object (PDOStatement, mysqli_stmt, or mysqli_result depending on your database driver).
Applies to: select(), find(), count(), sum(), average(), min(), max().
public stmt(): ?DatabaseInterfaceReturn Value:
Luminova\Interface\DatabaseInterface|null - Returns a statement object on success, or null if execution fails.
Throws:
- \Luminova\Exceptions\DatabaseException - If no query is set or execution fails.
Example:
$stmt = Builder::table('users')
->select(['email', 'name'])
->where('country', '=', 'NG')
->stmt();
$result = $stmt->fetchAll(FETCH_OBJ);
$stmt->free();Note: Query result caching is not supported when using
stmt().
exists
Check if a record exists.
Builds and executes a query to determine whether a record exists in the selected table.
public exists(): boolReturn Value:
bool - Return true if records exist in the table, otherwise false.
Throws:
- \Luminova\Exceptions\DatabaseException - If an error occurs.
Example:
$has = Builder::table('users')
->where('country', '=', 'NG')
->exists();See Also: tableExists() — to check if a table exists in the database.
errors
Get error information from the last executed query.
public errors(): arrayReturn Value:
array - Return an array containing error information.
transaction
Begins a transaction with an optional flags value and savepoint name.
public transaction(int $flags = 0, ?string $name = null): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$flags | int | Optional flags to set transaction properties. |
$name | string|null | Optional transaction savepoint name to create. |
Return Value:
bool - Returns true if the transaction and optional savepoint were successfully started.
Throws:
- \Luminova\Exceptions\DatabaseException - If an invalid savepoint name is used, or the transaction fails to start.
Example:
$tbl = Builder::table('users');
$tbl->transaction();
$tbl->where('country', '=', 'NG');
if ($tbl->update(['suburb' => 'Enugu'])) {
$tbl->commit();
} else {
$tbl->rollback();
}
$tbl->free();nestedTransaction
Start a nested transaction using an automatic savepoint name.
Useful when running transactions inside loops, allowing partial commits or rollbacks without affecting the outer transaction.
public nestedTransaction(bool $closeCursor = false): string|bool|nullParameters:
| Parameter | Type | Description |
|---|---|---|
$closeCursor | bool | Whether to close existing cursors before starting. |
Return Value:
string|false|null - The savepoint name, null if a new transaction was started, or false if it failed.
Example:
$tbl = Builder::table('users');
$updated = 0;
foreach ($users as $user) {
$sp = $tbl->nestedTransaction();
$tbl->where('country', '=', 'NG');
$tbl->and('id', '=', $user->id);
if ($tbl->update(['suburb' => 'Enugu'])) {
$tbl->release($sp);
$updated++;
}
}
if ($updated > 0) {
$tbl->commit();
} else {
$tbl->rollback();
}
$tbl->free();savepoint
Set a named transaction savepoint.
public savepoint(string $name): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$name | string | The name for a savepoint to create. |
Return Value:
bool - Returns true on success, or false if no transaction is active or on failure.
Throws:
- \Luminova\Exceptions\DatabaseException - If an invalid savepoint name or database error occurs.
inTransaction
Checks if a transaction is currently active.
public inTransaction(): boolReturn Value:
bool - Returns true if a transaction is active, false otherwise.
commit
Commits a transaction.
public commit(int $flags = 0, ?string $name = null): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$flags | int | Optional flags for custom handling (MySQLi only). |
$name | string|null | Optional savepoint name — if provided in PDO, releases the savepoint instead. |
Return Value:
bool - Returns true if the transaction was successfully committed (or if no transaction is active).
Throws:
- \Luminova\Exceptions\DatabaseException - If an invalid savepoint name is used.
rollback
Rolls back the current transaction or to a specific savepoint.
public rollback(int $flags = 0, ?string $name = null): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$flags | int | Optional flags for custom handling (MySQLi only). |
$name | string|null | Optional savepoint name to roll back to. |
Return Value:
bool - Return true if the rollback was successful (or if no transaction is active), otherwise false.
Throws:
- \Luminova\Exceptions\DatabaseException - If an invalid savepoint name is used.
release
Removes the named savepoint from the set of savepoints of the current transaction.
public release(string $name): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$name | string | The savepoint name to release. |
Return Value:
bool - Returns true on success, or false if no transaction is active or on failure.
Throws:
- \Luminova\Exceptions\DatabaseException - If an invalid savepoint name is used.
rename
Rename the current table to a new name.
public rename(string $to): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$to | string | The new table name. |
Return Value:
bool - Return true if the rename operation was successful, false otherwise.
Throws:
- \Luminova\Exceptions\DatabaseException - If the database driver is unsupported.
Example:
$renamed = Builder::table('users')->rename('new_users');delete
Execute a query to delete records from the table.
Constructs and executes a DELETE statement based on the current query conditions. In strict mode, execution is prevented without a WHERE clause.
public delete(): intReturn Value:
int - Return the number of affected rows.
Throws:
- \Luminova\Exceptions\DatabaseException - If an error occurs during execution or strict mode requires a
WHEREclause that is missing.
Example:
Builder::table('users')
->where('id', '=', 1)
->strict(true)
->delete();lockForUpdate
Apply a row-level update (exclusive) lock to the query for concurrency control. Prevents others from reading or writing. Call before find()/select().
public lockForUpdate(): selfReturn Value:
self - Returns the current Builder instance.
Note: Must be used inside a transaction.
Example:
$tbl = Builder::table('users');
$tbl->transaction();
$rows = $tbl->where('user_id', '=', 123)
->lockForUpdate()
->find();
$tbl->commit();lockInShare
Apply a row-level share lock to the query for concurrency control. Allows others to read, but not write. Call before find()/select().
public lockInShare(): selfReturn Value:
self - Returns the current Builder instance.
Note: Must be used inside a transaction.
Example:
$tbl = Builder::table('users');
$tbl->transaction();
$rows = $tbl->where('user_id', '=', 123)
->lockInShare()
->find();
$tbl->commit();lockFor
Apply a row-level lock to the query for concurrency control using an explicit mode.
public lockFor(string $mode = 'update'): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$mode | string | The lock mode: update (exclusive) or shared/share (default: update). |
Return Value:
self - Returns the current Builder instance.
Throws:
- \Luminova\Exceptions\InvalidArgumentException - If an invalid lock type is given.
See Also: lockForUpdate(), lockInShare().
truncate
Truncate database table records.
Attempts to clear all table records and optionally reset the auto-increment counter.
public truncate(?int $resetIncrement = null): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$resetIncrement | int|null | Index to reset auto-increment to, if applicable (default: null). |
Return Value:
bool - Return true if truncation completed, otherwise false.
Throws:
- \Luminova\Exceptions\DatabaseException - Throws if an error occurred during execution.
Examples:
Builder::table('users')->truncate();$stmt = Builder::table('users')->transaction();
if ($stmt->truncate()) {
$stmt->commit();
} else {
$stmt->rollback();
}create
Create the database table if it does not already exist.
Executes a CREATE TABLE IF NOT EXISTS statement for the current table. Creates an empty table structure and does not copy records or create a temporary table.
public create(): boolReturn Value:
bool - Returns true if the table was created successfully; false if the operation failed or no changes were made.
Throws:
- \Luminova\Exceptions\DatabaseException - Throws if a database error occurs.
Example:
$created = Builder::table('users')->create();
if ($created) {
echo 'Table created successfully.';
}See Also: createTemp() — to create a temporary table and back up data.
createTemp
Create a temporary table and copy data from the current table.
Creates a temporary table and copies all records from the main table into it.
public createTemp(?string $prefix = 'temp_'): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$prefix | string|null | Optional temp table name prefix (default: temp_). |
Return Value:
bool - Returns true if the operation was successful; false otherwise.
Throws:
- \Luminova\Exceptions\DatabaseException - Throws if a database error occurs during the operation.
Examples:
if (Builder::table('users')->createTemp()) {
$data = Builder::table('temp_users')->select();
}$stmt = Builder::table('users')->transaction();
if ($stmt->createTemp()) {
if ($stmt->commit()) {
$data = Builder::table('temp_users')->select();
}
} else {
$stmt->rollback();
}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.
drop
Drop the database table (or its temporary counterpart) if it exists.
public drop(bool $isTemporalTable = false): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$isTemporalTable | bool | Whether the table is a temporary table (default false). |
Return Value:
bool - Return true if the table was successfully dropped, false otherwise.
Throws:
- \Luminova\Exceptions\DatabaseException - Throws if an error occurs.
Examples:
Builder::table('users')->drop();Builder::table('users')->drop(true);$stmt = Builder::table('users')->transaction();
if ($stmt->drop()) {
$stmt->commit();
} else {
$stmt->rollback();
}isConnected
Check if the current builder database is connected.
public isConnected(): boolReturn Value:
bool - Return true if the database is connected, false otherwise.
getObjectId
Get a unique identifier for this builder context.
Combines the builder instance and database connection instance to avoid conflicts between builders using different connections.
public getObjectId(): stringReturn Value:
string - Unique builder identifier.
getLastInsertedId
Retrieve the last inserted ID from the database after insert() has been called.
public getLastInsertedId(): mixedReturn Value:
mixed - Return the last inserted id, or null if unavailable.
manager
Retrieves the database manager instance.
Returns a singleton instance of the Manager class initialized with the current database connection.
public manager(): ManagerReturn Value:
Luminova\Database\Manager - An instance of the database manager class.
Throws:
- \Luminova\Exceptions\DatabaseException - Throws if the database connection failed.
See Also: Database Manager.
export
Exports the database table and downloads it to the browser as JSON or CSV.
public export(string $as = 'csv', ?string $filename = null, array $columns = ['*']): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$as | string | Export format, csv or json. |
$filename | string|null | Filename to download. |
$columns | string[] | Table columns to export (default: all). |
Return Value:
bool - Return true if the export is successful, false otherwise.
Throws:
- \Luminova\Exceptions\DatabaseException - If an invalid format is provided or unable to create the export.
backup
Creates a backup of the database table.
public backup(?string $filename = null): boolParameters:
| Parameter | Type | Description |
|---|---|---|
$filename | string|null | Optional name of the backup file. If not provided, the table name and timestamp are used. |
Return Value:
bool - Return true if the backup is successful, false otherwise.
Throws:
- \Luminova\Exceptions\DatabaseException - If unable to create the backup directory or if the backup fails.
debug
Enables debugging for query execution.
Supports multiple debug modes for tracking or dumping query strings and parameters. In production, debug information is logged using the debug level when applicable.
public debug(int $mode = self::DEBUG_BUILDER_OUTPUT): selfParameters:
| Parameter | Type | Description |
|---|---|---|
$mode | int | Debug mode (default: Builder::DEBUG_BUILDER_OUTPUT). |
Return Value:
self - Return the current builder instance.
Examples:
$result = Builder::table('users')
->debug(Builder::DEBUG_BUILDER_OUTPUT)
->find([...])
->where('id', '=', 100)
->get();$tbl = Builder::table('users')
->debug(Builder::DEBUG_BUILDER_COLLECT)
->find([...])
->where('id', '=', 100);
$result = $tbl->get();
$detail = $tbl->getDebug(); // Array
$tbl->dump(); // Output detailsNote: Using
Builder::DEBUG_DRIVER_DUMPdoes not prevent the query from executing.
getDebug
Get an array of debug query information.
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 getDebug(): arrayReturn Value:
array{method:string,binding:array,query:array} - Return array containing query information.
dump / dumpDebug
Output collected query debug information in the requested format.
dumpDebug() is a convenience wrapper that forwards to dump(). Applies only when debug mode is Builder::DEBUG_BUILDER_COLLECT.
public dump(?string $format = null): void
public dumpDebug(?string $format = null): voidParameters:
| Parameter | Type | Description |
|---|---|---|
$format | string|null | Output format (html, json, or null for a readable array format). |
free
Release the active builder statement and database statement.
Frees the current statement cursor (if present) and clears the statement reference. Does not close the database connection.
public free(): boolReturn Value:
bool - Returns true if the statement was released, otherwise false.
close
Close the active database connection.
First releases any active statement resources via free(). If a transaction is still active, it is rolled back before closing the connection.
public close(): boolReturn Value:
bool - Returns true after attempting to close the connection, otherwise false if it failed.
reset
Reset the query builder state after execution.
Releases the active statement cursor unless the return mode requires the statement object (RETURN_STATEMENT). If closeAfter() is enabled, the database connection is also closed.
public reset(): boolReturn Value:
bool - Returns false when debug mode is enabled, otherwise true.
Note: This method is also invoked automatically in
__destruct().
__clone
Clone the query builder instance.
Creates a shallow copy of the builder state (where clauses, limits, bindings, etc.). The underlying connection object is not duplicated and remains shared — override it using useConnection() if needed.
public __clone(): voidExample:
$tbl = Builder::table('users')
->where('country', '=', 'NG');
$pagination = clone $tbl;
$result = $tbl->select()->limit(100)->get();
$records = $pagination->count()->get();
// Swap connection if required
$records = $pagination->useConnection(conn: null)->count()->get();