Luminova Framework

PHP Luminova: ORM Database Query Builder

Last updated: 2026-08-23 18:04:25

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 array

Now 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 for Builder::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 more

Builder 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\Expression instance 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:

  1. Table Definition – table(...), with(...), from(...)
  2. Selector Method – select, find, sum, average, min, max, count, copy, etc.
  3. Conditions and Filters – where, join, whereIn, whereLike, limit, etc.
  4. 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 fetch

Note:

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, or exists must 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 Builder instance
  • a Closure returning a Builder instance or null (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,and whereGroup() 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

ConstantValueDescription
DEBUG_NONE0Disable query debugging.
DEBUG_BUILDER_COLLECT1Collects builder-level SQL statements and bindings for later inspection with getDebug().
DEBUG_DRIVER_DUMP2Enables the underlying database driver's own debugging output.
DEBUG_BUILDER_OUTPUT3Outputs SQL statements and bindings as they're generated.

Return Types

ConstantValueDescription
RETURN_ARRAYarrayReturn results as associative arrays.
RETURN_OBJECTobjectReturn results as stdClass objects (default).
RETURN_OBJECT_LISTlistReturn the result collection itself as an object while each row remains an object (legacy compatibility mode).
RETURN_STATEMENTstmtReturn 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().

ConstantSQL EquivalentDescription
MATCH_NATURALIN NATURAL LANGUAGE MODEDefault mode; interprets the search string as natural human language.
MATCH_BOOLEANIN BOOLEAN MODESupports boolean operators like +, -, >, *, etc., for fine control.
MATCH_NATURAL_EXPANDEDIN NATURAL LANGUAGE MODE WITH QUERY EXPANSIONUses natural language search with automatic query expansion.
MATCH_EXPANSIONWITH QUERY EXPANSIONPerforms 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.

ConstantDescription
REGULARStandard comparison (e.g., WHERE column = value).
CONJOINCombined expressions (e.g., WHERE (a = 1 OR b = 2)).
RAWRaw conditions passed directly (e.g., WHERE NOT EXISTS (SELECT ...)).
NESTEDDeeply grouped conditions (e.g., WHERE ((a = 1 AND b = 2) OR (c = 3))).
AGAINSTFull-text match using MATCH (...) AGAINST (...).
INARRAYFilters using IN (...) with array values.

Search Patterns

Used with whereSearch().

ConstantPatternDescription
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_REGEXregexPerforms 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:

ParameterTypeDescription
$tablestring|nullOptional database table name (must be a valid non-empty string).
$aliasstring|nullOptional table alias (default: null).

Throws:

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(): Builder

Return Value:

Luminova\Database\Query\Builder - Return a shared singleton instance of builder.

Throws:

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

Parameters:

ParameterTypeDescription
$tablestringThe name of the database table (must be a non-empty string).
$aliasstring|nullOptional alias for the table (default: null).

Return Value:

Luminova\Database\Query\Builder - Returns a new instance of builder class.

Throws:

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

Parameters:

ParameterTypeDescription
$tablestringThe CTE name used as the temporary result set identifier.
$querystringA SELECT-only SQL statement (or full WITH ... statement) representing the CTE body.
$aliasstring|nullOptional alias for the resulting dataset.

Return Value:

Luminova\Database\Query\Builder - Returns a new instance of builder, initialized with the provided CTE query.

Throws:

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

Parameters:

ParameterTypeDescription
$querystringComplete CTE SQL statement starting with WITH.
$finalboolWhether the CTE query already contains the final SQL operation (SELECT, INSERT, UPDATE, or DELETE).

Return Value:

self - Return the current builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$querystringThe SQL query string (must be non-empty).

Return Value:

Luminova\Database\Query\Builder - Returns a new instance of builder class.

Throws:

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 the execute() method.


exec

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

public static exec(string $query): int

Parameters:

ParameterTypeDescription
$querystringThe SQL query string to execute.

Return Value:

int - Return number of affected rows or 0 if failed.

Throws:

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

Parameters:

ParameterTypeDescription
$sharedboolUse shared connection instance (default: true).

Return Value:

Luminova\Interface\DatabaseInterface - Return connected database driver.

Throws:


tableExists

Check if a database table exists.

public static tableExists(string $table): bool

Parameters:

ParameterTypeDescription
$tablestringThe table name to check.

Return Value:

bool - Returns true if the table exists, otherwise false.

Throws:

Example:

$exists = Builder::tableExists('users');

Note: This method does not require a WHERE clause 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): Expression

Parameters:

ParameterTypeDescription
$sqlstringThe raw SQL expression.

Return Value:

Luminova\Database\Query\Expression - Return an instance of a raw expression representing the SQL fragment.

Throws:

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(): Expression

Return 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
): string

Parameters:

ParameterTypeDescription
$formatstringOutput format (default: datetime).
$timezoneDateTimeZone|string|nullOptional timezone object or name.
$timestampint|nullOptional 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): Column

Parameters:

ParameterTypeDescription
$namestringThe name of the column.
$operatorstringThe comparison operator (e.g., =, !=, <, >, LIKE).
$value(Closure(Builder $static):mixed)|mixedThe value to compare against.

Return Value:

Luminova\Database\Query\Column - Returns instance of column condition structure.

Throws:

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 $strict flag.
  • Arrays and objects are encoded as JSON. Existing valid JSON strings are preserved.
  • Expression instances 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
): mixed

Parameters:

ParameterTypeDescription
$valuemixedThe value to escape.
$enQuoteboolIf true, wraps the value in single quotes unless it's JSON.
$strictboolWhether to use strict type casting (default: false). See below.
$numericCheckboolIf true, numeric strings are cast to int/float and JSON_NUMERIC_CHECK is applied to JSON encoding.
$addSlashesboolIf true, string values are passed through addslashes().

$strict behavior:

  • If true: null returns null; bool returns 1|0; resources return raw stream content; empty arrays return [].
  • If false: null returns 'NULL' (as a string); bool returns 'TRUE'/'FALSE'; resources return base64-encoded content; empty arrays return '[]'.

Return Value:

mixed - Returns a properly escaped and type-safe value.

Throws:


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

Parameters:

ParameterTypeDescription
$connDatabaseInterface|Connection|nullConnection source or null to assign a new connection.
$freeStmtboolWhether to free active statements and release the cursor before use.

Return Value:

self - Return instance of the database builder.

Throws:

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

Parameters:

ParameterTypeDescription
$tablestringThe name of the database table (must be a non-empty string).
$aliasstring|nullOptional alias for the table (default: null).

Return Value:

self - Returns instance of the builder class.

Throws:

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

Parameters:

ParameterTypeDescription
$tablestringThe table name to join.
$aliasstring|nullOptional alias for the joined table.
$typestring|nullThe type of join to use (INNER, LEFT, RIGHT, FULL, or CROSS).

Return Value:

self - Returns the instance of the builder class.

Throws:

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

Parameters:

ParameterTypeDescription
$aliasstring|nullOptional table alias for the subquery.
$typestring|nullThe join type (INNER, LEFT, RIGHT, FULL, CROSS).

Return Value:

self - Returns the instance of the builder.

Throws:

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

Parameters:

ParameterTypeDescription
$tablestringThe table name.
$aliasstring|nullOptional table join alias (default: null).
$forSubqueryboolSet to true if the joined source is a subquery instead of a normal table.

Return Value:

self - Return the current builder instance.

Throws:

Note: fullOuterJoin() is not supported by MySQL or SQLite; use a UNION of LEFT JOIN and RIGHT JOIN instead.


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

Parameters:

ParameterTypeDescription
$columnstringLeft-side column or SQL expression.
$operatorstringComparison operator (=, <>, >, <, LIKE, etc.).
$valuemixedRight-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.
$connectorstringLogical connector joining this condition with previous ON conditions (AND or OR).

Return Value:

self - Returns the builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$sqlExpression|stringA raw SQL string or expression object to use in the join condition.
$connectorstringLogical 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): self

Parameters:

ParameterTypeDescription
$sqlExpression|stringA 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'
): self

Parameters:

ParameterTypeDescription
$columnsColumn[]Column conditions definitions built via Builder::column(...).
$nestedConnectorstringLogical connector used inside the group (AND or OR).
$connectorstringLogical connector used to join the group with existing join conditions (AND or OR).

Return Value:

self - Returns the builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$limitintThe maximum number of rows to return or affect.
$offsetintThe 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, 10

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

Parameters:

ParameterTypeDescription
$offsetintThe 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,10

top

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

Parameters:

ParameterTypeDescription
$limitint|stringMaximum 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 users

pagination

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

Parameters:

ParameterTypeDescription
$pageintThe requested page number (starting from 1).
$limitintThe maximum number of records per page.
$recordsintOptional total number of records in the dataset. If provided, pagination metadata will be calculated.
&$infoarray|nullOptional 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): self

Parameters:

ParameterTypeDescription
$enableboolWhether 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): self

Parameters:

ParameterTypeDescription
$aliasstringThe alias to assign to the UNION result set.

Return Value:

self - Returns the instance of the builder class.

Throws:


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

Parameters:

ParameterTypeDescription
$expressionstringThe column name or raw SQL expression to order by.
$orderstringThe sorting direction, ASC or DESC (default: ASC).

Return Value:

self - Return the current builder instance.

Throws:

Examples:

Builder::table('users')
     ->order('created_at', 'DESC');
// Generates: ORDER BY created_at DESC
Builder::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 DESC

random

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

Parameters:

ParameterTypeDescription
$seedint|nullOptional 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): self

Parameters:

ParameterTypeDescription
$columnstringThe column to sort by in descending order.

Return Value:

self - Return the current builder instance.

Throws:


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

Parameters:

ParameterTypeDescription
$columnstringThe column to sort by in ascending order.

Return Value:

self - Return the current builder instance.

Throws:


group

Sets a GROUP BY clause for the query.

public group(string $group): self

Parameters:

ParameterTypeDescription
$groupstringThe 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 country

whereHaving

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

Parameters:

ParameterTypeDescription
$expressionExpression|stringColumn name or SQL expression to evaluate.
$operatorstringComparison operator used for evaluation (=, >, <, >=, <=, etc.).
$valueExpression|array|string|int|float|nullValue or expression to compare against.
$connectorstringLogical 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 > 1000
Builder::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) > 10

whereNotHaving

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

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

Parameters:

ParameterTypeDescription
$columnstringThe name of the column to filter by.
$operatorstringThe comparison operator (e.g., =, >=, <>, LIKE, REGEXP).
$value(Closure(Builder $static):mixed)|mixedThe 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): self

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

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

Parameters:

ParameterTypeDescription
$sqlExpression|stringRaw SQL fragment to append to the WHERE clause.
$connectorstringLogical 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'
): self

Parameters:

ParameterTypeDescription
$columnstringColumn name to apply the condition.
$valuesarrayRange boundaries. Must contain an even number of values — each pair represents one range.
$groupConnectorstringLogical operator between multiple ranges (default: OR).
$connectorstringLogical operator to join with previous conditions (default: AND).

Return Value:

self - Return the current builder instance.

Throws:

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 — use whereRaw() 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'
): self

Parameters:

ParameterTypeDescription
$columnstringColumn name to apply the condition.
$valuesarrayRange boundaries. Must contain an even number of values.
$groupConnectorstringLogical operator between multiple ranges (default: AND).
$connectorstringLogical operator to join with previous conditions (default: AND).

Return Value:

self - Return the current builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$columnsstring[]A list of column names to include in search matching.

Return Value:

self - Returns the builder instance for method chaining.

Throws:

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

Parameters:

ParameterTypeDescription
$keywordstringThe keyword or phrase to search for.
$patternstringThe search pattern to apply, e.g. Builder::SEARCH_CONTAINS.
$splitKeywordboolWhether to split a phrase into individual keywords.
$caseSensitiveboolWhether the search should be case-sensitive.
$collationstring|nullThe SQL collation to apply when comparing search values (e.g. utf8mb4_bin).
$connectorstringLogical operator to join with previous conditions (AND or OR).

Return Value:

self - Returns the builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$connectorstringLogical connector (AND or OR).
$columnstringColumn name or expression.
$operatorstringComparison operator (=, !=, >, LIKE, IN, NOT, etc.).
$valuemixedScalar 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:

Note: When $value is an array, it is transformed into an IN or NOT IN clause 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'): self

Parameters:

ParameterTypeDescription
$columnstringThe column name to compare.
$expressionstringThe pattern to match using SQL LIKE (e.g. %value%).
$connectorstringLogical 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'): self

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

Parameters:

ParameterTypeDescription
$columnstringThe column name to apply the regular expression against.
$patternstringThe regular expression pattern used for matching.
$connectorstringLogical 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'): self

Parameters:

ParameterTypeDescription
$columnstringThe column name to apply the regular expression against.
$valuestringThe value to match using a generated regular expression.
$connectorstringLogical 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'
): self

Parameters:

ParameterTypeDescription
$valuemixedThe search value to match against. Supports literal values or a closure.
$modestring|intSearch mode constant or raw MySQL AGAINST mode.
$connectorstringLogical connector used to combine this condition with previous conditions (AND or OR).

Return Value:

self - Return the current builder instance.

Throws:

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

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

Parameters:

ParameterTypeDescription
$valuestring|int|floatThe value to search against.
$modestring|intThe match mode — one of Builder::MATCH_* or a raw string.
$orderstringThe sort direction, ASC or DESC (default: ASC).

Return Value:

self - Return the current builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$columnstringThe column name to check for non-null values.
$connectorstringLogical 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'): self

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

Parameters:

ParameterTypeDescription
$lngColumnstringLongitude column name.
$latColumnstringLatitude column name.
$longitudeExprstring|float|nullLongitude value or SQL placeholder (default: :longitude).
$latitudeExprstring|float|nullLatitude value or SQL placeholder (default: :latitude).
$unitstringDistance unit: km, mi, m, or ft.
$methodstringDistance calculation method: sphere or haversine.
$aliasstring|nullDistance column alias (default: distance).

Return Value:

self - Return the builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$radiusfloatMaximum distance boundary from the reference coordinates.
$connectorstringLogical connector used to combine this condition with previous query conditions (AND or OR).

Return Value:

self - Return the current builder instance.

Throws:

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

Parameters: Same as whereWithin().

Return Value:

self - Return the current builder instance.

Throws:


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

Parameters:

ParameterTypeDescription
$lngColumnstringLongitude column name.
$latColumnstringLatitude column name.
$longitudeExprstring|floatLongitude value or SQL placeholder.
$latitudeExprstring|floatLatitude value or SQL placeholder.
$radiusfloatThe maximum search radius (default: 10.0).
$unitstringDistance unit: km, mi, m, or ft.
$connectorstringLogical operator to join with previous conditions (AND or OR).

Return Value:

self - Return the builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$columnstringThe column name to match against.
$valuesClosure|arrayA list of values, or a closure returning an array of values.
$connectorstringLogical operator to join with previous conditions (AND or OR).

Return Value:

self - Return the current builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$callbackClosureCallback used to configure the EXISTS query.
$connectorstringLogical operator to join with previous conditions (AND or OR).

Return Value:

self - Return the current builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$searchstringThe value to search for, or column name when $searchAsColumn is true.
$operatorstringComparison operator or predefined matching mode.
$listarray|stringComma-separated values, array of values, or column name containing the list.
$searchAsColumnboolWhether $search should be treated as a column reference.
$connectorstringLogical 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:

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

Parameters:

ParameterTypeDescription
$searchstringThe value to search for, or column name when $searchAsColumn is true.
$listarray|stringComma-separated values, array of values, or column name containing the list.
$searchAsColumnboolWhether $search should be treated as a column reference.
$connectorstringLogical connector used to join this condition (AND or OR).

Return Value:

self - Return the current builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$conditionsColumn[]The column conditions to group. Build via Builder::column(...).
$groupConnectorstringThe AND or OR logical connector within the group (default: AND).
$connectorstringThe base logical connector to connect previous conditions (default: AND).

Return Value:

self - Return the current builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$leftConditionsColumn[]An array of first column conditions group.
$rightConditionsColumn[]An array of second column conditions group.
$groupConnectorstringThe AND or OR logical connector within each group (default: AND).
$nestedConnectorstringThe AND or OR logical connector to bind groups (default: AND).
$connectorstringThe base logical connector to connect previous conditions (default: AND).

Return Value:

self - Return the current builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$columnstringThe name of the column to set.
$valuemixedThe value to assign (can be a simple value, a closure, or an expression).

Return Value:

self - Return the current builder instance.

Throws:

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 = 1

values

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

Parameters:

ParameterTypeDescription
$valuesarray<string,mixed>Associative array of column-value pairs.

Return Value:

self - Return the current builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$placeholderstringThe named placeholder. Must start with a colon : (e.g. :id).
$value(Closure(Builder $static):mixed)|mixedThe value to bind. Arrays are JSON-encoded.

Return Value:

self - Return the current builder instance.

Throws:

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

Parameters:

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

Parameters:

ParameterTypeDescription
$typeclass-string|stringBuilder::RETURN_OBJECT, Builder::RETURN_OBJECT_LIST, Builder::RETURN_ARRAY, Builder::RETURN_STATEMENT, or a valid class name.
$argumentsarray|(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:

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

Parameters:

ParameterTypeDescription
$distinctboolWhether 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 users

replace

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

Parameters:

ParameterTypeDescription
$useReplaceboolSet to true to use REPLACE instead of INSERT (default: true).

Return Value:

self - Return the current builder instance.

Warning: Since REPLACE removes 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): self

Parameters:

ParameterTypeDescription
$closeboolWhether 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): self

Parameters:

ParameterTypeDescription
$enableboolWhether 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(): ?Cache

Return 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(): array

Return 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(): bool

Return Value:

bool - Returns true if the cache item was deleted, otherwise false.

Throws:

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(): bool

Return Value:

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

Throws:

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

Parameters:

ParameterTypeDescription
$keystring|nullUnique identifier for the cached result.
$storagestring|nullOptional cache storage name (defaults: table name or 'default').
$expiryDateTimeInterface|int|nullCache expiration time (default: 7 days).
$persistentIdstring|nullOptional persistent ID for memcached/redis, or a subdirectory for the file cache driver.

Return Value:

self - Returns the current builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$enableboolThe 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(): bool

Return 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(): ?string

Return 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(): bool

Return 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(): bool

Return 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): int

Parameters:

ParameterTypeDescription
$valuesarray|nullRecords to insert, or values built via set(). Keys are column names, values are the values to insert.
$usePrepareboolWhether to use prepared statements (default: true).
$escapeValuesboolWhether to escape values if $usePrepare is true (default: true).

Return Value:

int - Returns the number of rows inserted.

Throws:

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

Parameters:

ParameterTypeDescription
$columnstringThe column to update on duplicate key.
$operationstringThe operation to apply ("=", "+=", "-=").
$valuemixed|ClosureThe 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 with replace(true) or ignoreDuplicate(true).


ignoreDuplicate

Sets whether duplicates should be ignored during insertion.

public ignoreDuplicate(bool $ignore = true): self

Parameters:

ParameterTypeDescription
$ignoreboolWhether to ignore duplicates (default: true).

Return Value:

self - Return the current builder instance.

Note: Cannot be combined with replace(true) or onDuplicate(...).


increment

Increment a numeric column value during update, without requiring a prior read.

public increment(string $column, float|int $count = 1): self

Parameters:

ParameterTypeDescription
$columnstringThe column name to increment.
$countfloat|intThe 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): self

Parameters:

ParameterTypeDescription
$columnstringThe column name to decrement.
$countfloat|intThe amount to subtract (default: 1).
$allowNegativeboolWhether 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): int

Parameters:

ParameterTypeDescription
$valuesarray|nullOptional column-value pairs to update. If omitted, values assigned via builder mutation methods are used.

Return Value:

int - Number of affected rows.

Throws:

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

Parameters:

ParameterTypeDescription
$columnsstring[]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): int

Parameters:

ParameterTypeDescription
$tablestringTarget table to insert copied data into.
$columnsstring[]Target table columns receiving the copied values.

Return Value:

int - Returns the number of affected rows.

Throws:

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

Parameters:

ParameterTypeDescription
$placeholderarray|nullOptional key-value pairs for query placeholders.
$returnModeintRecord return mode, e.g. RETURN_NEXT (default: RETURN_ALL).
$fetchAsintResult fetch mode, e.g. FETCH_ASSOC, FETCH_CLASS (default: FETCH_OBJ).
$escapeboolWhether to escape placeholder values (default: false).

Return Value:

mixed - Returns the query result, a statement object, or false on failure.

Throws:

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 = ['*']): self

Parameters:

ParameterTypeDescription
$columnsstring[]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 = ['*']): self

Parameters:

ParameterTypeDescription
$columnsstring[]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): self

Parameters:

ParameterTypeDescription
$columnstring[]|stringThe column name, list of columns, or * to count all records (default: *).
$distinctboolWhether 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): self

Parameters:

ParameterTypeDescription
$columnstringThe 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): self

Parameters:

ParameterTypeDescription
$columnstringThe 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): self

Parameters:

ParameterTypeDescription
$columnstring[]|stringThe column name or list of columns to sum.
$distinctboolWhether 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): self

Parameters:

ParameterTypeDescription
$columnstring[]|stringThe column name or list of columns to average.
$distinctboolWhether 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): self

Example:

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

Parameters:

ParameterTypeDescription
$columnsstring[]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): self

Parameters:

ParameterTypeDescription
$unionBuilder|ClosureAnother Builder instance, or a closure that returns a Builder instance to union with.

Return Value:

self - Return current parent Builder object.

Throws:

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() or stmt() 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): self

Parameters: Same as union().

Return Value:

self - Return current parent Builder object.

Throws:


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

Parameters:

ParameterTypeDescription
$fetchAsintResult fetch mode, e.g. FETCH_ASSOC, FETCH_CLASS (default: FETCH_OBJ).
$returnModeint|nullRecord return mode, e.g. RETURN_NEXT (default: RETURN_ALL).

Return Value:

mixed - Returns query result on success, or false/null on failure.

Throws:

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

Parameters:

ParameterTypeDescription
$modeintResult 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:

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

Executes and fetches one row at a time from the previously prepared statement.

Throws:


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): ?array

Parameters:

ParameterTypeDescription
$columnstringInteger indexed column used for cursor tracking (e.g. id, age).
&$lastIdintReference cursor (last seen column value); updated after each call.
$limitintNumber of rows to fetch per iteration.

Return Value:

array|null - Returns an array of results, or null when no more data exists.

Throws:

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): ?array

Parameters:

ParameterTypeDescription
&$iteratorint|nullReference cursor (offset position).
$limitintNumber of rows to fetch per iteration.

Return Value:

array|null - Returns an array of results, or null when no more data exists.

Throws:

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

Parameters:

ParameterTypeDescription
$fetchAsintResult fetch mode, e.g. FETCH_ASSOC, FETCH_CLASS (default: FETCH_OBJ).
$returnModeint|nullRecord 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(): ?DatabaseInterface

Return Value:

Luminova\Interface\DatabaseInterface|null - Returns a statement object on success, or null if execution fails.

Throws:

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(): bool

Return Value:

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

Throws:

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(): array

Return 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): bool

Parameters:

ParameterTypeDescription
$flagsintOptional flags to set transaction properties.
$namestring|nullOptional transaction savepoint name to create.

Return Value:

bool - Returns true if the transaction and optional savepoint were successfully started.

Throws:

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|null

Parameters:

ParameterTypeDescription
$closeCursorboolWhether 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): bool

Parameters:

ParameterTypeDescription
$namestringThe name for a savepoint to create.

Return Value:

bool - Returns true on success, or false if no transaction is active or on failure.

Throws:


inTransaction

Checks if a transaction is currently active.

public inTransaction(): bool

Return Value:

bool - Returns true if a transaction is active, false otherwise.


commit

Commits a transaction.

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

Parameters:

ParameterTypeDescription
$flagsintOptional flags for custom handling (MySQLi only).
$namestring|nullOptional 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:


rollback

Rolls back the current transaction or to a specific savepoint.

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

Parameters:

ParameterTypeDescription
$flagsintOptional flags for custom handling (MySQLi only).
$namestring|nullOptional 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:


release

Removes the named savepoint from the set of savepoints of the current transaction.

public release(string $name): bool

Parameters:

ParameterTypeDescription
$namestringThe savepoint name to release.

Return Value:

bool - Returns true on success, or false if no transaction is active or on failure.

Throws:


rename

Rename the current table to a new name.

public rename(string $to): bool

Parameters:

ParameterTypeDescription
$tostringThe new table name.

Return Value:

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

Throws:

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(): int

Return Value:

int - Return the number of affected rows.

Throws:

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

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

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

Parameters:

ParameterTypeDescription
$modestringThe lock mode: update (exclusive) or shared/share (default: update).

Return Value:

self - Returns the current Builder instance.

Throws:

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

Parameters:

ParameterTypeDescription
$resetIncrementint|nullIndex to reset auto-increment to, if applicable (default: null).

Return Value:

bool - Return true if truncation completed, otherwise false.

Throws:

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(): bool

Return Value:

bool - Returns true if the table was created successfully; false if the operation failed or no changes were made.

Throws:

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_'): bool

Parameters:

ParameterTypeDescription
$prefixstring|nullOptional temp table name prefix (default: temp_).

Return Value:

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

Throws:

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

Parameters:

ParameterTypeDescription
$isTemporalTableboolWhether the table is a temporary table (default false).

Return Value:

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

Throws:

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(): bool

Return 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(): string

Return Value:

string - Unique builder identifier.


getLastInsertedId

Retrieve the last inserted ID from the database after insert() has been called.

public getLastInsertedId(): mixed

Return 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(): Manager

Return Value:

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

Throws:

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 = ['*']): bool

Parameters:

ParameterTypeDescription
$asstringExport format, csv or json.
$filenamestring|nullFilename to download.
$columnsstring[]Table columns to export (default: all).

Return Value:

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

Throws:


backup

Creates a backup of the database table.

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

Parameters:

ParameterTypeDescription
$filenamestring|nullOptional 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:


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

Parameters:

ParameterTypeDescription
$modeintDebug 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 details

Note: Using Builder::DEBUG_DRIVER_DUMP does 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(): array

Return 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): void

Parameters:

ParameterTypeDescription
$formatstring|nullOutput 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(): bool

Return 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(): bool

Return 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(): bool

Return 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(): void

Example:

$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();