27340411392

Committed 11 Jun 2026 10:25AM UTC coverage: 89.024% (+0.03%) from 88.991%

Build # 27340411392

Build Type

Pull #10266

github

Committed by

web-flow

Commit Message

Merge 445e6d6a7 into f88932939

Pull Request Pull Request #10266: feat: add Query Builder date helpers

Coverage Stats

100 of 102 new or added lines in 5 files covered. (98.04%)

24867 of 27933 relevant lines covered (89.02%)

224.34 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

95.15

/system/Database/BaseBuilder.php

<?php

declare(strict_types=1);

/**
 * This file is part of CodeIgniter 4 framework.
 *
 * (c) CodeIgniter Foundation <admin@codeigniter.com>
 *
 * For the full copyright and license information, please view
 * the LICENSE file that was distributed with this source code.
 */

namespace CodeIgniter\Database;

use BackedEnum;
use Closure;
use CodeIgniter\Database\Exceptions\DatabaseException;
use CodeIgniter\Database\Exceptions\DataException;
use CodeIgniter\Exceptions\InvalidArgumentException;
use CodeIgniter\Traits\ConditionalTrait;
use Config\Feature;
use DateTimeInterface;
use TypeError;

/**
 * Class BaseBuilder
 *
 * Provides the core Query Builder methods.
 * Database-specific Builders might need to override
 * certain methods to make them work.
 */
class BaseBuilder
{
    use ConditionalTrait;

    /**
     * Reset DELETE data flag
     *
     * @var bool
     */
    protected $resetDeleteData = false;

    /**
     * QB SELECT data
     *
     * @var list<string>
     */
    protected $QBSelect = [];

    /**
     * QB DISTINCT flag
     *
     * @var bool
     */
    protected $QBDistinct = false;

    /**
     * QB FROM data
     *
     * @var array
     */
    protected $QBFrom = [];

    /**
     * QB JOIN data
     *
     * @var array
     */
    protected $QBJoin = [];

    /**
     * QB WHERE data
     *
     * @var array
     */
    protected $QBWhere = [];

    /**
     * QB GROUP BY data
     *
     * @var array
     */
    public $QBGroupBy = [];

    /**
     * QB HAVING data
     *
     * @var array
     */
    protected $QBHaving = [];

    /**
     * QB keys
     * list of column names.
     *
     * @var list<string>
     */
    protected $QBKeys = [];

    /**
     * QB LIMIT data
     *
     * @var bool|int
     */
    protected $QBLimit = false;

    /**
     * QB OFFSET data
     *
     * @var bool|int
     */
    protected $QBOffset = false;

    /**
     * QB FOR UPDATE flag
     */
    protected bool $QBLockForUpdate = false;

    /**
     * QB SELECT aggregate helper flag
     */
    protected bool $QBSelectUsesAggregate = false;

    /**
     * QB ORDER BY data
     *
     * @var array|string|null
     */
    public $QBOrderBy = [];

    /**
     * QB UNION data
     *
     * @var list<string>
     */
    protected array $QBUnion = [];

    /**
     * Whether to protect identifiers in SELECT
     *
     * @var list<bool|null> true=protect, false=not protect
     */
    public $QBNoEscape = [];

    /**
     * QB data sets
     *
     * @var array<string, string>|list<list<int|string>>
     */
    protected $QBSet = [];

    /**
     * QB WHERE group started flag
     *
     * @var bool
     */
    protected $QBWhereGroupStarted = false;

    /**
     * QB WHERE group count
     *
     * @var int
     */
    protected $QBWhereGroupCount = 0;

    /**
     * Ignore data that cause certain
     * exceptions, for example in case of
     * duplicate keys.
     *
     * @var bool
     */
    protected $QBIgnore = false;

    /**
     * QB Options data
     * Holds additional options and data used to render SQL
     * and is reset by resetWrite()
     *
     * @var array{
     *   updateFieldsAdditional?: array,
     *   tableIdentity?: string,
     *   updateFields?: array,
     *   constraints?: array,
     *   setQueryAsData?: string,
     *   sql?: string,
     *   alias?: string,
     *   fieldTypes?: array<string, array<string, string>>
     * }
     *
     * fieldTypes: [ProtectedTableName => [FieldName => Type]]
     */
    protected $QBOptions;

    /**
     * A reference to the database connection.
     *
     * @var BaseConnection
     */
    protected $db;

    /**
     * Name of the primary table for this instance.
     * Tracked separately because $QBFrom gets escaped
     * and prefixed.
     *
     * When $tableName to the constructor has multiple tables,
     * the value is empty string.
     *
     * @var string
     */
    protected $tableName;

    /**
     * ORDER BY random keyword
     *
     * @var array
     */
    protected $randomKeyword = [
        'RAND()',
        'RAND(%d)',
    ];

    /**
     * COUNT string
     *
     * @used-by CI_DB_driver::count_all()
     * @used-by BaseBuilder::count_all_results()
     *
     * @var string
     */
    protected $countString = 'SELECT COUNT(*) AS ';

    /**
     * Collects the named parameters and
     * their values for later binding
     * in the Query object.
     *
     * @var array
     */
    protected $binds = [];

    /**
     * Collects the key count for named parameters
     * in the Query object.
     *
     * @var array
     */
    protected $bindsKeyCount = [];

    /**
     * Some databases, like SQLite, do not by default
     * allow limiting of delete clauses.
     *
     * @var bool
     */
    protected $canLimitDeletes = true;

    /**
     * Some databases do not by default
     * allow limit update queries with WHERE.
     *
     * @var bool
     */
    protected $canLimitWhereUpdates = true;

    /**
     * Specifies which sql statements
     * support the ignore option.
     *
     * @var array<string, string>
     */
    protected $supportedIgnoreStatements = [];

    /**
     * Builder testing mode status.
     *
     * @var bool
     */
    protected $testMode = false;

    /**
     * Tables relation types
     *
     * @var array
     */
    protected $joinTypes = [
        'LEFT',
        'RIGHT',
        'OUTER',
        'INNER',
        'LEFT OUTER',
        'RIGHT OUTER',
    ];

    /**
     * Strings that determine if a string represents a literal value or a field name
     *
     * @var list<string>
     */
    protected $isLiteralStr = [];

    /**
     * RegExp used to get operators
     *
     * @var list<string>
     */
    protected $pregOperators = [];

    /**
     * Constructor
     *
     * @param array|string|TableName $tableName tablename or tablenames with or without aliases
     *
     * Examples of $tableName: `mytable`, `jobs j`, `jobs j, users u`, `['jobs j','users u']`
     *
     * @throws DatabaseException
     */
    public function __construct($tableName, ConnectionInterface $db, ?array $options = null)
    {
        if (empty($tableName)) {
            throw new DatabaseException('A table must be specified when creating a new Query Builder.');
        }

        /** @var BaseConnection $db */
        $this->db = $db;

        if ($tableName instanceof TableName) {
            $this->tableName = $tableName->getTableName();
            $this->QBFrom[]  = $this->db->escapeIdentifier($tableName);
            $this->db->addTableAlias($tableName->getAlias());
        }
        // If it contains `,`, it has multiple tables
        elseif (is_string($tableName) && ! str_contains($tableName, ',')) {
            $this->tableName = $tableName;  // @TODO remove alias if exists
            $this->from($tableName);
        } else {
            $this->tableName = '';
            $this->from($tableName);
        }

        if ($options !== null && $options !== []) {
            foreach ($options as $key => $value) {
                if (property_exists($this, $key)) {
                    $this->{$key} = $value;
                }
            }
        }
    }

    /**
     * Returns the current database connection
     *
     * @return BaseConnection
     */
    public function db(): ConnectionInterface
    {
        return $this->db;
    }

    /**
     * Sets a test mode status.
     *
     * @return $this
     */
    public function testMode(bool $mode = true)
    {
        $this->testMode = $mode;

        return $this;
    }

    /**
     * Gets the name of the primary table.
     */
    public function getTable(): string
    {
        return $this->tableName;
    }

    /**
     * Returns an array of bind values and their
     * named parameters for binding in the Query object later.
     */
    public function getBinds(): array
    {
        return $this->binds;
    }

    /**
     * Ignore
     *
     * Set ignore Flag for next insert,
     * update or delete query.
     *
     * @return $this
     */
    public function ignore(bool $ignore = true)
    {
        $this->QBIgnore = $ignore;

        return $this;
    }

    /**
     * Generates the SELECT portion of the query
     *
     * @param list<RawSql|string>|RawSql|string $select
     * @param bool|null                         $escape Whether to protect identifiers
     *
     * @return $this
     */
    public function select($select = '*', ?bool $escape = null)
    {
        // If the escape value was not set, we will base it on the global setting
        if (! is_bool($escape)) {
            $escape = $this->db->protectIdentifiers;
        }

        if ($select instanceof RawSql) {
            $select = [$select];
        }

        if (is_string($select)) {
            $select = ($escape === false) ? [$select] : explode(',', $select);
        }

        foreach ($select as $val) {
            if ($val instanceof RawSql) {
                $this->QBSelect[]   = $val;
                $this->QBNoEscape[] = false;

                continue;
            }

            $val = trim($val);

            if ($val !== '') {
                $this->QBSelect[] = $val;

                /*
                 * When doing 'SELECT NULL as field_alias FROM table'
                 * null gets taken as a field, and therefore escaped
                 * with backticks.
                 * This prevents NULL being escaped
                 * @see https://github.com/codeigniter4/CodeIgniter4/issues/1169
                 */
                if (mb_stripos($val, 'NULL') === 0) {
                    $this->QBNoEscape[] = false;

                    continue;
                }

                $this->QBNoEscape[] = $escape;
            }
        }

        return $this;
    }

    /**
     * Generates a SELECT MAX(field) portion of a query
     *
     * @return $this
     */
    public function selectMax(string $select = '', string $alias = '')
    {
        return $this->maxMinAvgSum($select, $alias);
    }

    /**
     * Generates a SELECT MIN(field) portion of a query
     *
     * @return $this
     */
    public function selectMin(string $select = '', string $alias = '')
    {
        return $this->maxMinAvgSum($select, $alias, 'MIN');
    }

    /**
     * Generates a SELECT AVG(field) portion of a query
     *
     * @return $this
     */
    public function selectAvg(string $select = '', string $alias = '')
    {
        return $this->maxMinAvgSum($select, $alias, 'AVG');
    }

    /**
     * Generates a SELECT SUM(field) portion of a query
     *
     * @return $this
     */
    public function selectSum(string $select = '', string $alias = '')
    {
        return $this->maxMinAvgSum($select, $alias, 'SUM');
    }

    /**
     * Generates a SELECT COUNT(field) portion of a query
     *
     * @return $this
     */
    public function selectCount(string $select = '', string $alias = '')
    {
        return $this->maxMinAvgSum($select, $alias, 'COUNT');
    }

    /**
     * Adds a subquery to the selection
     */
    public function selectSubquery(BaseBuilder $subquery, string $as): self
    {
        $this->QBSelect[] = $this->buildSubquery($subquery, true, $as);

        return $this;
    }

    /**
     * SELECT [MAX|MIN|AVG|SUM|COUNT]()
     *
     * @used-by selectMax()
     * @used-by selectMin()
     * @used-by selectAvg()
     * @used-by selectSum()
     *
     * @return $this
     *
     * @throws DatabaseException
     * @throws DataException
     */
    protected function maxMinAvgSum(string $select = '', string $alias = '', string $type = 'MAX')
    {
        if ($select === '') {
            throw DataException::forEmptyInputGiven('Select');
        }

        if (str_contains($select, ',')) {
            throw DataException::forInvalidArgument('column name not separated by comma');
        }

        $type = strtoupper($type);

        if (! in_array($type, ['MAX', 'MIN', 'AVG', 'SUM', 'COUNT'], true)) {
            throw new DatabaseException('Invalid function type: ' . $type);
        }

        if ($alias === '') {
            $alias = $this->createAliasFromTable(trim($select));
        }

        $sql = $type . '(' . $this->db->protectIdentifiers(trim($select)) . ') AS ' . $this->db->escapeIdentifiers(trim($alias));

        $this->QBSelect[]            = $sql;
        $this->QBNoEscape[]          = null;
        $this->QBSelectUsesAggregate = true;

        return $this;
    }

    /**
     * Determines the alias name based on the table
     */
    protected function createAliasFromTable(string $item): string
    {
        if (str_contains($item, '.')) {
            $item = explode('.', $item);

            return end($item);
        }

        return $item;
    }

    /**
     * Sets a flag which tells the query string compiler to add DISTINCT
     *
     * @return $this
     */
    public function distinct(bool $val = true)
    {
        $this->QBDistinct = $val;

        return $this;
    }

    /**
     * Generates the FROM portion of the query
     *
     * @param array|string $from
     *
     * @return $this
     */
    public function from($from, bool $overwrite = false): self
    {
        if ($overwrite) {
            $this->QBFrom = [];
            $this->db->setAliasedTables([]);
        }

        foreach ((array) $from as $table) {
            if (str_contains($table, ',')) {
                $this->from(explode(',', $table));
            } else {
                $table = trim($table);

                if ($table === '') {
                    continue;
                }

                $this->trackAliases($table);
                $this->QBFrom[] = $this->db->protectIdentifiers($table, true, null, false);
            }
        }

        return $this;
    }

    /**
     * @param BaseBuilder $from  Expected subquery
     * @param string      $alias Subquery alias
     *
     * @return $this
     */
    public function fromSubquery(BaseBuilder $from, string $alias): self
    {
        $table = $this->buildSubquery($from, true, $alias);

        $this->db->addTableAlias($alias);
        $this->QBFrom[] = $table;

        return $this;
    }

    /**
     * Generates the JOIN portion of the query
     *
     * @param RawSql|string $cond
     *
     * @return $this
     */
    public function join(string $table, $cond, string $type = '', ?bool $escape = null)
    {
        $type = $this->compileJoinType($type);

        // Extract any aliases that might exist. We use this information
        // in the protectIdentifiers to know whether to add a table prefix
        $this->trackAliases($table);

        if (! is_bool($escape)) {
            $escape = $this->db->protectIdentifiers;
        }

        $table = $this->compileJoinTable($table, $escape);
        $cond  = $this->compileJoinCondition($cond, $escape);

        // Assemble the JOIN statement
        $this->QBJoin[] = $type . 'JOIN ' . $table . $cond;

        return $this;
    }

    /**
     * Compiles the JOIN table name.
     */
    protected function compileJoinTable(string $table, bool $escape): string
    {
        if ($escape) {
            return $this->db->protectIdentifiers($table, true, null, false);
        }

        return $table;
    }

    private function compileJoinType(string $type): string
    {
        if ($type === '') {
            return '';
        }

        $type = strtoupper(trim($type));

        return in_array($type, $this->joinTypes, true) ? $type . ' ' : '';
    }

    /**
     * Compiles the JOIN ON or USING condition.
     */
    private function compileJoinCondition(RawSql|string $condition, bool $escape): string
    {
        if ($condition instanceof RawSql) {
            return ' ON ' . $condition;
        }

        if (! $this->hasOperator($condition)) {
            return ' USING (' . ($escape ? $this->db->escapeIdentifiers($condition) : $condition) . ')';
        }

        if ($escape === false) {
            return ' ON ' . $condition;
        }

        return $this->compileProtectedJoinCondition($condition);
    }

    private function compileProtectedJoinCondition(string $condition): string
    {
        // Split multiple conditions
        // @TODO This does not parse `BETWEEN a AND b` correctly.
        if (preg_match_all('/\sAND\s|\sOR\s/i', $condition, $joints, PREG_OFFSET_CAPTURE) >= 1) {
            $conditions = [];
            $joints     = $joints[0];
            array_unshift($joints, ['', 0]);

            for ($i = count($joints) - 1, $pos = strlen($condition); $i >= 0; $i--) {
                $joints[$i][1] += strlen($joints[$i][0]); // offset
                $conditions[$i] = substr($condition, $joints[$i][1], $pos - $joints[$i][1]);
                $pos            = $joints[$i][1] - strlen($joints[$i][0]);
                $joints[$i]     = $joints[$i][0];
            }
            ksort($conditions);
        } else {
            $conditions = [$condition];
            $joints     = [''];
        }

        $condition = ' ON ';

        foreach ($conditions as $i => $conditionPart) {
            $operator = $this->getOperator($conditionPart);

            // Workaround for BETWEEN
            if ($operator === false) {
                $condition .= $joints[$i] . $conditionPart;

                continue;
            }

            $condition .= $joints[$i];
            $condition .= preg_match('/(\(*)?([\[\]\w\.\'-]+)' . preg_quote($operator, '/') . '(.*)/i', $conditionPart, $match) ? $match[1] . $this->db->protectIdentifiers($match[2]) . $operator . $this->db->protectIdentifiers($match[3]) : $conditionPart;
        }

        return $condition;
    }

    /**
     * Generates the WHERE portion of the query.
     * Separates multiple calls with 'AND'.
     *
     * @param array|RawSql|string $key
     * @param mixed               $value
     *
     * @return $this
     */
    public function where($key, $value = null, ?bool $escape = null)
    {
        return $this->whereHaving('QBWhere', $key, $value, 'AND ', $escape);
    }

    /**
     * OR WHERE
     *
     * Generates the WHERE portion of the query.
     * Separates multiple calls with 'OR'.
     *
     * @param array|RawSql|string $key
     * @param mixed               $value
     *
     * @return $this
     */
    public function orWhere($key, $value = null, ?bool $escape = null)
    {
        return $this->whereHaving('QBWhere', $key, $value, 'OR ', $escape);
    }

    /**
     * Generates a WHERE clause that compares the date portion of a field.
     *
     * @param non-empty-string $key   Field name, optionally with comparison operator
     * @param mixed            $value
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function whereDate(string $key, $value, ?bool $escape = null): static
    {
        return $this->whereDatePart('date', $key, $value, 'AND ', $escape);
    }

    /**
     * Generates an OR WHERE clause that compares the date portion of a field.
     *
     * @param non-empty-string $key   Field name, optionally with comparison operator
     * @param mixed            $value
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function orWhereDate(string $key, $value, ?bool $escape = null): static
    {
        return $this->whereDatePart('date', $key, $value, 'OR ', $escape);
    }

    /**
     * Generates a WHERE clause that compares the year portion of a field.
     *
     * @param non-empty-string $key   Field name, optionally with comparison operator
     * @param mixed            $value
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function whereYear(string $key, $value, ?bool $escape = null): static
    {
        return $this->whereDatePart('year', $key, $value, 'AND ', $escape);
    }

    /**
     * Generates an OR WHERE clause that compares the year portion of a field.
     *
     * @param non-empty-string $key   Field name, optionally with comparison operator
     * @param mixed            $value
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function orWhereYear(string $key, $value, ?bool $escape = null): static
    {
        return $this->whereDatePart('year', $key, $value, 'OR ', $escape);
    }

    /**
     * Generates a WHERE clause that compares the month portion of a field.
     *
     * @param non-empty-string $key   Field name, optionally with comparison operator
     * @param mixed            $value
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function whereMonth(string $key, $value, ?bool $escape = null): static
    {
        return $this->whereDatePart('month', $key, $value, 'AND ', $escape);
    }

    /**
     * Generates an OR WHERE clause that compares the month portion of a field.
     *
     * @param non-empty-string $key   Field name, optionally with comparison operator
     * @param mixed            $value
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function orWhereMonth(string $key, $value, ?bool $escape = null): static
    {
        return $this->whereDatePart('month', $key, $value, 'OR ', $escape);
    }

    /**
     * Generates a WHERE clause that compares the day portion of a field.
     *
     * @param non-empty-string $key   Field name, optionally with comparison operator
     * @param mixed            $value
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function whereDay(string $key, $value, ?bool $escape = null): static
    {
        return $this->whereDatePart('day', $key, $value, 'AND ', $escape);
    }

    /**
     * Generates an OR WHERE clause that compares the day portion of a field.
     *
     * @param non-empty-string $key   Field name, optionally with comparison operator
     * @param mixed            $value
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function orWhereDay(string $key, $value, ?bool $escape = null): static
    {
        return $this->whereDatePart('day', $key, $value, 'OR ', $escape);
    }

    /**
     * @used-by whereDate()
     * @used-by orWhereDate()
     * @used-by whereYear()
     * @used-by orWhereYear()
     * @used-by whereMonth()
     * @used-by orWhereMonth()
     * @used-by whereDay()
     * @used-by orWhereDay()
     *
     * @param 'date'|'day'|'month'|'year' $part
     * @param non-empty-string            $key   Field name, optionally with comparison operator
     * @param mixed                       $value
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    private function whereDatePart(string $part, string $key, $value, string $type = 'AND ', ?bool $escape = null): static
    {
        [$key, $operator] = $this->parseDatePartKey($key);

        if ($key === '') {
            throw new InvalidArgumentException(sprintf('%s() expects $key to be a non-empty string', debug_backtrace(0, 2)[1]['function']));
        }

        $escape ??= $this->db->protectIdentifiers;

        if (is_array($value) || $this->isSubquery($value)) {
            throw new InvalidArgumentException(sprintf('%s() does not accept array or subquery values', debug_backtrace(0, 2)[1]['function']));
        }

        if ($value === null) {
            $nullOperator = match ($operator) {
                '='        => 'IS NULL',
                '!=', '<>' => 'IS NOT NULL',
                default    => throw new InvalidArgumentException(sprintf('%s() supports null values only with =, !=, or <> operators', debug_backtrace(0, 2)[1]['function'])),
            };

            $this->addWhereHavingCondition('QBWhere', [
                'condition'          => '',
                'datePartComparison' => true,
                'escape'             => $escape,
                'key'                => $key,
                'nullComparison'     => true,
                'nullOperator'       => $nullOperator,
                'part'               => $part,
            ], $type);

            return $this;
        }

        $value = $this->normalizeDatePartValue($part, $value);
        $bind  = $this->setBind($key, $value, $escape);

        $this->addWhereHavingCondition('QBWhere', [
            'condition'          => '',
            'datePartComparison' => true,
            'escape'             => $escape,
            'key'                => $key,
            'operator'           => $operator,
            'part'               => $part,
            'rawValue'           => $value instanceof RawSql,
            'valueBind'          => $bind,
        ], $type);

        return $this;
    }

    /**
     * Extracts the comparison operator from date helper keys.
     *
     * @return array{string, string}
     *
     * @throws InvalidArgumentException
     */
    private function parseDatePartKey(string $key): array
    {
        $key = trim($key);

        if (
            preg_match('/\s+(?:IS(?:\s+NOT)?(?:\s+NULL)?|(?:NOT\s+)?(?:LIKE|IN|BETWEEN|EXISTS|REGEXP|RLIKE))(?:\s+.*|\s*\(.*\))?$/i', $key) === 1
            || preg_match('/\s*<=>\s*$/', $key) === 1
        ) {
            throw new InvalidArgumentException(sprintf('%s() supports only comparison operators: =, !=, <>, <, >, <=, >=', debug_backtrace(0, 3)[2]['function']));
        }

        if (preg_match('/\s*(!=|<>|<=|>=|=|<|>)\s*$/', $key, $match) === 1) {
            return [rtrim(substr($key, 0, -strlen($match[0]))), trim($match[1])];
        }

        return [$key, '='];
    }

    /**
     * Converts DateTime values into deterministic date helper comparison values.
     *
     * @param 'date'|'day'|'month'|'year' $part
     * @param mixed                       $value
     *
     * @return mixed
     */
    private function normalizeDatePartValue(string $part, $value)
    {
        if ($value instanceof DateTimeInterface) {
            return match ($part) {
                'date'  => $value->format('Y-m-d'),
                'year'  => (int) $value->format('Y'),
                'month' => (int) $value->format('m'),
                'day'   => (int) $value->format('d'),
            };
        }

        if (! $value instanceof RawSql && $part !== 'date' && (is_int($value) || (is_string($value) && preg_match('/^-?\d+$/', $value) === 1))) {
            return (int) $value;
        }

        return $value;
    }

    /**
     * Generates a WHERE clause that compares two columns.
     *
     * @param non-empty-string $first  First column name, optionally with comparison operator
     * @param non-empty-string $second Second column name
     * @param bool|null        $escape Whether to protect identifiers
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function whereColumn(string $first, string $second, ?bool $escape = null): static
    {
        return $this->whereColumnHaving('QBWhere', $first, $second, 'AND ', $escape);
    }

    /**
     * Generates an OR WHERE clause that compares two columns.
     *
     * @param non-empty-string $first  First column name, optionally with comparison operator
     * @param non-empty-string $second Second column name
     * @param bool|null        $escape Whether to protect identifiers
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function orWhereColumn(string $first, string $second, ?bool $escape = null): static
    {
        return $this->whereColumnHaving('QBWhere', $first, $second, 'OR ', $escape);
    }

    /**
     * Generates a WHERE EXISTS subquery.
     *
     * @param BaseBuilder|(Closure(BaseBuilder): BaseBuilder) $subquery
     *
     * @return $this
     */
    public function whereExists($subquery): static
    {
        return $this->whereExistsSubquery($subquery);
    }

    /**
     * Generates an OR WHERE EXISTS subquery.
     *
     * @param BaseBuilder|(Closure(BaseBuilder): BaseBuilder) $subquery
     *
     * @return $this
     */
    public function orWhereExists($subquery): static
    {
        return $this->whereExistsSubquery($subquery, false, 'OR ');
    }

    /**
     * Generates a WHERE NOT EXISTS subquery.
     *
     * @param BaseBuilder|(Closure(BaseBuilder): BaseBuilder) $subquery
     *
     * @return $this
     */
    public function whereNotExists($subquery): static
    {
        return $this->whereExistsSubquery($subquery, true);
    }

    /**
     * Generates an OR WHERE NOT EXISTS subquery.
     *
     * @param BaseBuilder|(Closure(BaseBuilder): BaseBuilder) $subquery
     *
     * @return $this
     */
    public function orWhereNotExists($subquery): static
    {
        return $this->whereExistsSubquery($subquery, true, 'OR ');
    }

    /**
     * @used-by whereColumn()
     * @used-by orWhereColumn()
     *
     * @param 'QBHaving'|'QBWhere' $qbKey
     * @param non-empty-string     $first  First column name, optionally with comparison operator
     * @param non-empty-string     $second Second column name
     * @param non-empty-string     $type
     * @param bool|null            $escape Whether to protect identifiers
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    protected function whereColumnHaving(string $qbKey, string $first, string $second, string $type = 'AND ', ?bool $escape = null): static
    {
        [$first, $operator] = $this->parseWhereColumnFirst($first);
        $second             = trim($second);

        if ($first === '' || $second === '') {
            $caller = debug_backtrace(0, 2)[1]['function'];

            throw new InvalidArgumentException(sprintf('%s() expects $first and $second to be non-empty strings', $caller));
        }

        $escape ??= $this->db->protectIdentifiers;

        $this->addWhereHavingCondition($qbKey, [
            'columnComparison' => true,
            'condition'        => '',
            'escape'           => $escape,
            'first'            => $first,
            'operator'         => $operator,
            'second'           => $second,
        ], $type);

        return $this;
    }

    /**
     * Extracts the operator from the first whereColumn() column.
     *
     * @param string $first The first column, optionally ending with a comparison operator
     *
     * @return array{string, string}
     */
    private function parseWhereColumnFirst(string $first): array
    {
        $first = trim($first);

        if (preg_match('/\s*(!=|<>|<=|>=|=|<|>)\s*$/', $first, $match) === 1) {
            return [rtrim(substr($first, 0, -strlen($match[0]))), trim($match[1])];
        }

        return [$first, '='];
    }

    /**
     * Generates a WHERE field BETWEEN minimum AND maximum SQL query,
     * joined with 'AND' if appropriate.
     *
     * @param array<array-key, mixed>|null $values The range values searched on
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function whereBetween(?string $key = null, $values = null, ?bool $escape = null): static
    {
        return $this->whereBetweenHaving('QBWhere', $key, $values, false, 'AND ', $escape);
    }

    /**
     * Generates a WHERE field BETWEEN minimum AND maximum SQL query,
     * joined with 'OR' if appropriate.
     *
     * @param array<array-key, mixed>|null $values The range values searched on
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function orWhereBetween(?string $key = null, $values = null, ?bool $escape = null): static
    {
        return $this->whereBetweenHaving('QBWhere', $key, $values, false, 'OR ', $escape);
    }

    /**
     * Generates a WHERE field NOT BETWEEN minimum AND maximum SQL query,
     * joined with 'AND' if appropriate.
     *
     * @param array<array-key, mixed>|null $values The range values searched on
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function whereNotBetween(?string $key = null, $values = null, ?bool $escape = null): static
    {
        return $this->whereBetweenHaving('QBWhere', $key, $values, true, 'AND ', $escape);
    }

    /**
     * Generates a WHERE field NOT BETWEEN minimum AND maximum SQL query,
     * joined with 'OR' if appropriate.
     *
     * @param array<array-key, mixed>|null $values The range values searched on
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function orWhereNotBetween(?string $key = null, $values = null, ?bool $escape = null): static
    {
        return $this->whereBetweenHaving('QBWhere', $key, $values, true, 'OR ', $escape);
    }

    /**
     * Generates a HAVING field BETWEEN minimum AND maximum SQL query,
     * joined with 'AND' if appropriate.
     *
     * @param array<array-key, mixed>|null $values The range values searched on
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function havingBetween(?string $key = null, $values = null, ?bool $escape = null): static
    {
        return $this->whereBetweenHaving('QBHaving', $key, $values, false, 'AND ', $escape);
    }

    /**
     * Generates a HAVING field BETWEEN minimum AND maximum SQL query,
     * joined with 'OR' if appropriate.
     *
     * @param array<array-key, mixed>|null $values The range values searched on
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function orHavingBetween(?string $key = null, $values = null, ?bool $escape = null): static
    {
        return $this->whereBetweenHaving('QBHaving', $key, $values, false, 'OR ', $escape);
    }

    /**
     * Generates a HAVING field NOT BETWEEN minimum AND maximum SQL query,
     * joined with 'AND' if appropriate.
     *
     * @param array<array-key, mixed>|null $values The range values searched on
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function havingNotBetween(?string $key = null, $values = null, ?bool $escape = null): static
    {
        return $this->whereBetweenHaving('QBHaving', $key, $values, true, 'AND ', $escape);
    }

    /**
     * Generates a HAVING field NOT BETWEEN minimum AND maximum SQL query,
     * joined with 'OR' if appropriate.
     *
     * @param array<array-key, mixed>|null $values The range values searched on
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function orHavingNotBetween(?string $key = null, $values = null, ?bool $escape = null): static
    {
        return $this->whereBetweenHaving('QBHaving', $key, $values, true, 'OR ', $escape);
    }

    /**
     * @used-by whereBetween()
     * @used-by orWhereBetween()
     * @used-by whereNotBetween()
     * @used-by orWhereNotBetween()
     * @used-by havingBetween()
     * @used-by orHavingBetween()
     * @used-by havingNotBetween()
     * @used-by orHavingNotBetween()
     *
     * @param 'QBHaving'|'QBWhere'         $qbKey
     * @param non-empty-string|null        $key
     * @param array<array-key, mixed>|null $values The range values searched on
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    private function whereBetweenHaving(string $qbKey, ?string $key = null, $values = null, bool $not = false, string $type = 'AND ', ?bool $escape = null): static
    {
        if ($key === null || $key === '') {
            throw new InvalidArgumentException(sprintf('%s() expects $key to be a non-empty string', debug_backtrace(0, 2)[1]['function']));
        }

        if (! is_array($values) || count($values) !== 2) {
            throw new InvalidArgumentException(sprintf('%s() expects $values to be an array containing exactly two values', debug_backtrace(0, 2)[1]['function']));
        }

        $escape ??= $this->db->protectIdentifiers;
        $values = array_values($values);

        $lowerBind = $this->setBind($key, $values[0], $escape);
        $upperBind = $this->setBind($key, $values[1], $escape);
        $not       = $not ? ' NOT' : '';
        $this->addWhereHavingCondition($qbKey, [
            'betweenComparison' => true,
            'condition'         => '',
            'escape'            => $escape,
            'key'               => $key,
            'lowerBind'         => $lowerBind,
            'not'               => $not,
            'upperBind'         => $upperBind,
        ], $type);

        return $this;
    }

    /**
     * @used-by whereExists()
     * @used-by orWhereExists()
     * @used-by whereNotExists()
     * @used-by orWhereNotExists()
     *
     * @param BaseBuilder|(Closure(BaseBuilder): BaseBuilder) $subquery
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    protected function whereExistsSubquery($subquery, bool $not = false, string $type = 'AND '): static
    {
        if (! $this->isSubquery($subquery)) {
            throw new InvalidArgumentException(sprintf('%s() expects $subquery to be of type BaseBuilder or closure', debug_backtrace(0, 2)[1]['function']));
        }

        $operator = $not ? 'NOT EXISTS' : 'EXISTS';

        $this->addWhereHavingCondition('QBWhere', [
            'condition' => "{$operator} {$this->buildSubquery($subquery, true)}",
            'escape'    => false,
        ], $type);

        return $this;
    }

    /**
     * @used-by where()
     * @used-by orWhere()
     * @used-by having()
     * @used-by orHaving()
     *
     * @param array|RawSql|string $key
     * @param mixed               $value
     *
     * @return $this
     */
    protected function whereHaving(string $qbKey, $key, $value = null, string $type = 'AND ', ?bool $escape = null)
    {
        $rawSqlOnly = false;

        if ($key instanceof RawSql) {
            if ($value === null) {
                $keyValue   = [(string) $key => $key];
                $rawSqlOnly = true;
            } else {
                $keyValue = [(string) $key => $value];
            }
        } elseif (! is_array($key)) {
            $keyValue = [$key => $value];
        } else {
            $keyValue = $key;
        }

        // If the escape value was not set will base it on the global setting
        if (! is_bool($escape)) {
            $escape = $this->db->protectIdentifiers;
        }

        foreach ($keyValue as $k => $v) {
            if ($rawSqlOnly) {
                $k  = '';
                $op = '';
            } elseif ($v !== null) {
                $op = $this->getOperatorFromWhereKey($k);

                if (! empty($op)) {
                    $k = trim($k);

                    end($op);
                    $op = trim(current($op));

                    // Does the key end with operator?
                    if (str_ends_with($k, $op)) {
                        $k  = rtrim(substr($k, 0, -strlen($op)));
                        $op = " {$op}";
                    } else {
                        $op = '';
                    }
                } else {
                    $op = ' =';
                }

                if ($this->isSubquery($v)) {
                    $v = $this->buildSubquery($v, true);
                } else {
                    $bind = $this->setBind($k, $v, $escape);
                    $v    = " :{$bind}:";
                }
            } elseif (! $this->hasOperator($k) && $qbKey !== 'QBHaving') {
                // value appears not to have been set, assign the test to IS NULL
                $op = ' IS NULL';
            } elseif (
                // The key ends with !=, =, <>, IS, IS NOT
                preg_match(
                    '/\s*(!?=|<>|IS(?:\s+NOT)?)\s*$/i',
                    $k,
                    $match,
                    PREG_OFFSET_CAPTURE,
                )
            ) {
                $k  = substr($k, 0, $match[0][1]);
                $op = $match[1][0] === '=' ? ' IS NULL' : ' IS NOT NULL';
            } else {
                $op = '';
            }

            $condition = $k . $op . $v;

            if ($v instanceof RawSql) {
                $this->{$qbKey}[] = [
                    'condition' => $v->with($this->getWhereHavingPrefix($qbKey, $type) . $condition),
                    'escape'    => $escape,
                ];

                continue;
            }

            $this->addWhereHavingCondition($qbKey, [
                'condition' => $condition,
                'escape'    => $escape,
            ], $type);
        }

        return $this;
    }

    /**
     * @param 'QBHaving'|'QBWhere' $clause
     * @param array<string, mixed> $condition
     * @param non-empty-string     $type
     */
    private function addWhereHavingCondition(string $clause, array $condition, string $type): void
    {
        $condition['condition'] = $this->getWhereHavingPrefix($clause, $type) . $condition['condition'];

        $this->{$clause}[] = $condition;
    }

    /**
     * @param 'QBHaving'|'QBWhere' $clause
     */
    private function getWhereHavingPrefix(string $clause, string $type): string
    {
        return $this->{$clause} === [] ? $this->groupGetType('') : $this->groupGetType($type);
    }

    /**
     * Generates a WHERE field IN('item', 'item') SQL query,
     * joined with 'AND' if appropriate.
     *
     * @param array|BaseBuilder|(Closure(BaseBuilder): BaseBuilder)|null $values The values searched on, or anonymous function with subquery
     *
     * @return $this
     */
    public function whereIn(?string $key = null, $values = null, ?bool $escape = null)
    {
        return $this->_whereIn($key, $values, false, 'AND ', $escape);
    }

    /**
     * Generates a WHERE field IN('item', 'item') SQL query,
     * joined with 'OR' if appropriate.
     *
     * @param array|BaseBuilder|(Closure(BaseBuilder): BaseBuilder)|null $values The values searched on, or anonymous function with subquery
     *
     * @return $this
     */
    public function orWhereIn(?string $key = null, $values = null, ?bool $escape = null)
    {
        return $this->_whereIn($key, $values, false, 'OR ', $escape);
    }

    /**
     * Generates a WHERE field NOT IN('item', 'item') SQL query,
     * joined with 'AND' if appropriate.
     *
     * @param array|BaseBuilder|(Closure(BaseBuilder): BaseBuilder)|null $values The values searched on, or anonymous function with subquery
     *
     * @return $this
     */
    public function whereNotIn(?string $key = null, $values = null, ?bool $escape = null)
    {
        return $this->_whereIn($key, $values, true, 'AND ', $escape);
    }

    /**
     * Generates a WHERE field NOT IN('item', 'item') SQL query,
     * joined with 'OR' if appropriate.
     *
     * @param array|BaseBuilder|(Closure(BaseBuilder): BaseBuilder)|null $values The values searched on, or anonymous function with subquery
     *
     * @return $this
     */
    public function orWhereNotIn(?string $key = null, $values = null, ?bool $escape = null)
    {
        return $this->_whereIn($key, $values, true, 'OR ', $escape);
    }

    /**
     * Generates a HAVING field IN('item', 'item') SQL query,
     * joined with 'AND' if appropriate.
     *
     * @param array|BaseBuilder|(Closure(BaseBuilder): BaseBuilder)|null $values The values searched on, or anonymous function with subquery
     *
     * @return $this
     */
    public function havingIn(?string $key = null, $values = null, ?bool $escape = null)
    {
        return $this->_whereIn($key, $values, false, 'AND ', $escape, 'QBHaving');
    }

    /**
     * Generates a HAVING field IN('item', 'item') SQL query,
     * joined with 'OR' if appropriate.
     *
     * @param array|BaseBuilder|(Closure(BaseBuilder): BaseBuilder)|null $values The values searched on, or anonymous function with subquery
     *
     * @return $this
     */
    public function orHavingIn(?string $key = null, $values = null, ?bool $escape = null)
    {
        return $this->_whereIn($key, $values, false, 'OR ', $escape, 'QBHaving');
    }

    /**
     * Generates a HAVING field NOT IN('item', 'item') SQL query,
     * joined with 'AND' if appropriate.
     *
     * @param array|BaseBuilder|(Closure(BaseBuilder):BaseBuilder)|null $values The values searched on, or anonymous function with subquery
     *
     * @return $this
     */
    public function havingNotIn(?string $key = null, $values = null, ?bool $escape = null)
    {
        return $this->_whereIn($key, $values, true, 'AND ', $escape, 'QBHaving');
    }

    /**
     * Generates a HAVING field NOT IN('item', 'item') SQL query,
     * joined with 'OR' if appropriate.
     *
     * @param array|BaseBuilder|(Closure(BaseBuilder): BaseBuilder)|null $values The values searched on, or anonymous function with subquery
     *
     * @return $this
     */
    public function orHavingNotIn(?string $key = null, $values = null, ?bool $escape = null)
    {
        return $this->_whereIn($key, $values, true, 'OR ', $escape, 'QBHaving');
    }

    /**
     * @used-by WhereIn()
     * @used-by orWhereIn()
     * @used-by whereNotIn()
     * @used-by orWhereNotIn()
     *
     * @param non-empty-string|null                                            $key
     * @param BaseBuilder|(Closure(BaseBuilder): BaseBuilder)|list<mixed>|null $values The values searched on, or anonymous function with subquery
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    protected function _whereIn(?string $key = null, $values = null, bool $not = false, string $type = 'AND ', ?bool $escape = null, string $clause = 'QBWhere')
    {
        if ($key === null || $key === '') {
            throw new InvalidArgumentException(sprintf('%s() expects $key to be a non-empty string', debug_backtrace(0, 2)[1]['function']));
        }

        if ($values === null || (! is_array($values) && ! $this->isSubquery($values))) {
            throw new InvalidArgumentException(sprintf('%s() expects $values to be of type array or closure', debug_backtrace(0, 2)[1]['function']));
        }

        if (! is_bool($escape)) {
            $escape = $this->db->protectIdentifiers;
        }

        $ok = $key;

        if ($escape === true) {
            $key = $this->db->protectIdentifiers($key);
        }

        $not = ($not) ? ' NOT' : '';

        if ($this->isSubquery($values)) {
            $whereIn = $this->buildSubquery($values, true);
            $escape  = false;
        } else {
            $whereIn = array_values($values);
        }

        $ok = $this->setBind($ok, $whereIn, $escape);

        $this->addWhereHavingCondition($clause, [
            'condition' => "{$key}{$not} IN :{$ok}:",
            'escape'    => false,
        ], $type);

        return $this;
    }

    /**
     * Generates a %LIKE% portion of the query.
     * Separates multiple calls with 'AND'.
     *
     * @param array|RawSql|string $field
     *
     * @return $this
     */
    public function like($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
    {
        return $this->_like($field, $match, 'AND ', $side, '', $escape, $insensitiveSearch);
    }

    /**
     * Generates grouped LIKE portions of the query joined with OR.
     *
     * @param list<non-empty-string|RawSql> $fields
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function likeAny(array $fields, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false): static
    {
        return $this->likeAnyGroup($fields, $match, 'AND ', $side, $escape, $insensitiveSearch, __FUNCTION__);
    }

    /**
     * Generates a NOT LIKE portion of the query.
     * Separates multiple calls with 'AND'.
     *
     * @param array|RawSql|string $field
     *
     * @return $this
     */
    public function notLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
    {
        return $this->_like($field, $match, 'AND ', $side, 'NOT', $escape, $insensitiveSearch);
    }

    /**
     * Generates a %LIKE% portion of the query.
     * Separates multiple calls with 'OR'.
     *
     * @param array|RawSql|string $field
     *
     * @return $this
     */
    public function orLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
    {
        return $this->_like($field, $match, 'OR ', $side, '', $escape, $insensitiveSearch);
    }

    /**
     * Generates grouped LIKE portions of the query joined with OR.
     *
     * @param list<non-empty-string|RawSql> $fields
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    public function orLikeAny(array $fields, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false): static
    {
        return $this->likeAnyGroup($fields, $match, 'OR ', $side, $escape, $insensitiveSearch, __FUNCTION__);
    }

    /**
     * Generates a NOT LIKE portion of the query.
     * Separates multiple calls with 'OR'.
     *
     * @param array|RawSql|string $field
     *
     * @return $this
     */
    public function orNotLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
    {
        return $this->_like($field, $match, 'OR ', $side, 'NOT', $escape, $insensitiveSearch);
    }

    /**
     * Generates a %LIKE% portion of the query.
     * Separates multiple calls with 'AND'.
     *
     * @param array|RawSql|string $field
     *
     * @return $this
     */
    public function havingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
    {
        return $this->_like($field, $match, 'AND ', $side, '', $escape, $insensitiveSearch, 'QBHaving');
    }

    /**
     * Generates a NOT LIKE portion of the query.
     * Separates multiple calls with 'AND'.
     *
     * @param array|RawSql|string $field
     *
     * @return $this
     */
    public function notHavingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
    {
        return $this->_like($field, $match, 'AND ', $side, 'NOT', $escape, $insensitiveSearch, 'QBHaving');
    }

    /**
     * Generates a %LIKE% portion of the query.
     * Separates multiple calls with 'OR'.
     *
     * @param array|RawSql|string $field
     *
     * @return $this
     */
    public function orHavingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
    {
        return $this->_like($field, $match, 'OR ', $side, '', $escape, $insensitiveSearch, 'QBHaving');
    }

    /**
     * Generates a NOT LIKE portion of the query.
     * Separates multiple calls with 'OR'.
     *
     * @param array|RawSql|string $field
     *
     * @return $this
     */
    public function orNotHavingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
    {
        return $this->_like($field, $match, 'OR ', $side, 'NOT', $escape, $insensitiveSearch, 'QBHaving');
    }

    /**
     * @param list<non-empty-string|RawSql> $fields
     *
     * @return $this
     *
     * @throws InvalidArgumentException
     */
    private function likeAnyGroup(array $fields, string $match, string $type, string $side, ?bool $escape, bool $insensitiveSearch, string $caller): static
    {
        $this->validateLikeAnyFields($fields, $caller);

        $this->groupStartPrepare('', $type);

        foreach ($fields as $index => $field) {
            $this->_like($field, $match, $index === 0 ? 'AND ' : 'OR ', $side, '', $escape, $insensitiveSearch);
        }

        return $this->groupEndPrepare();
    }

    /**
     * @param list<non-empty-string|RawSql> $fields
     *
     * @throws InvalidArgumentException
     */
    private function validateLikeAnyFields(array $fields, string $caller): void
    {
        if ($fields === [] || ! array_is_list($fields)) {
            throw new InvalidArgumentException(sprintf('%s() expects $fields to be a non-empty list of field names', $caller));
        }

        foreach ($fields as $field) {
            if ($field instanceof RawSql) {
                continue;
            }

            if (! is_string($field) || trim($field) === '') {
                throw new InvalidArgumentException(sprintf('%s() expects $fields to contain only non-empty strings or RawSql instances', $caller));
            }
        }
    }

    /**
     * @used-by like()
     * @used-by likeAny()
     * @used-by orLike()
     * @used-by orLikeAny()
     * @used-by notLike()
     * @used-by orNotLike()
     * @used-by havingLike()
     * @used-by orHavingLike()
     * @used-by notHavingLike()
     * @used-by orNotHavingLike()
     *
     * @param array<string, string>|RawSql|string $field
     *
     * @return $this
     */
    protected function _like($field, string $match = '', string $type = 'AND ', string $side = 'both', string $not = '', ?bool $escape = null, bool $insensitiveSearch = false, string $clause = 'QBWhere')
    {
        $escape = is_bool($escape) ? $escape : $this->db->protectIdentifiers;
        $side   = strtolower($side);

        if ($field instanceof RawSql) {
            $k                 = (string) $field;
            $v                 = $match;
            $insensitiveSearch = false;

            $prefix = $this->getWhereHavingPrefix($clause, $type);

            if ($side === 'none') {
                $bind = $this->setBind($field->getBindingKey(), $v, $escape);
            } elseif ($side === 'before') {
                $bind = $this->setBind($field->getBindingKey(), "%{$v}", $escape);
            } elseif ($side === 'after') {
                $bind = $this->setBind($field->getBindingKey(), "{$v}%", $escape);
            } else {
                $bind = $this->setBind($field->getBindingKey(), "%{$v}%", $escape);
            }

            $likeStatement = $this->_like_statement($prefix, $k, $not, $bind, $insensitiveSearch);

            // some platforms require an escape sequence definition for LIKE wildcards
            if ($escape === true && $this->db->likeEscapeStr !== '') {
                $likeStatement .= sprintf($this->db->likeEscapeStr, $this->db->likeEscapeChar);
            }

            $this->{$clause}[] = [
                'condition' => $field->with($likeStatement),
                'escape'    => $escape,
            ];

            return $this;
        }

        $keyValue = is_array($field) ? $field : [$field => $match];

        foreach ($keyValue as $k => $v) {
            if ($insensitiveSearch) {
                $v = mb_strtolower($v, 'UTF-8');
            }

            $prefix = $this->getWhereHavingPrefix($clause, $type);

            if ($side === 'none') {
                $bind = $this->setBind($k, $v, $escape);
            } elseif ($side === 'before') {
                $bind = $this->setBind($k, "%{$v}", $escape);
            } elseif ($side === 'after') {
                $bind = $this->setBind($k, "{$v}%", $escape);
            } else {
                $bind = $this->setBind($k, "%{$v}%", $escape);
            }

            $likeStatement = $this->_like_statement($prefix, $k, $not, $bind, $insensitiveSearch);

            // some platforms require an escape sequence definition for LIKE wildcards
            if ($escape === true && $this->db->likeEscapeStr !== '') {
                $likeStatement .= sprintf($this->db->likeEscapeStr, $this->db->likeEscapeChar);
            }

            $this->{$clause}[] = [
                'condition' => $likeStatement,
                'escape'    => $escape,
            ];
        }

        return $this;
    }

    /**
     * Platform independent LIKE statement builder.
     */
    protected function _like_statement(?string $prefix, string $column, ?string $not, string $bind, bool $insensitiveSearch = false): string
    {
        if ($insensitiveSearch) {
            return "{$prefix} LOWER(" . $this->db->escapeIdentifiers($column) . ") {$not} LIKE :{$bind}:";
        }

        return "{$prefix} {$column} {$not} LIKE :{$bind}:";
    }

    /**
     * Add UNION statement
     *
     * @param BaseBuilder|Closure(BaseBuilder): BaseBuilder $union
     *
     * @return $this
     */
    public function union($union)
    {
        return $this->addUnionStatement($union);
    }

    /**
     * Add UNION ALL statement
     *
     * @param BaseBuilder|Closure(BaseBuilder): BaseBuilder $union
     *
     * @return $this
     */
    public function unionAll($union)
    {
        return $this->addUnionStatement($union, true);
    }

    /**
     * @used-by union()
     * @used-by unionAll()
     *
     * @param BaseBuilder|Closure(BaseBuilder): BaseBuilder $union
     *
     * @return $this
     */
    protected function addUnionStatement($union, bool $all = false)
    {
        $this->QBUnion[] = "\nUNION "
            . ($all ? 'ALL ' : '')
            . 'SELECT * FROM '
            . $this->buildSubquery($union, true, 'uwrp' . (count($this->QBUnion) + 1));

        return $this;
    }

    /**
     * Starts a query group.
     *
     * @return $this
     */
    public function groupStart()
    {
        return $this->groupStartPrepare();
    }

    /**
     * Starts a query group, but ORs the group
     *
     * @return $this
     */
    public function orGroupStart()
    {
        return $this->groupStartPrepare('', 'OR ');
    }

    /**
     * Starts a query group, but NOTs the group
     *
     * @return $this
     */
    public function notGroupStart()
    {
        return $this->groupStartPrepare('NOT ');
    }

    /**
     * Starts a query group, but OR NOTs the group
     *
     * @return $this
     */
    public function orNotGroupStart()
    {
        return $this->groupStartPrepare('NOT ', 'OR ');
    }

    /**
     * Ends a query group
     *
     * @return $this
     */
    public function groupEnd()
    {
        return $this->groupEndPrepare();
    }

    /**
     * Starts a query group for HAVING clause.
     *
     * @return $this
     */
    public function havingGroupStart()
    {
        return $this->groupStartPrepare('', 'AND ', 'QBHaving');
    }

    /**
     * Starts a query group for HAVING clause, but ORs the group.
     *
     * @return $this
     */
    public function orHavingGroupStart()
    {
        return $this->groupStartPrepare('', 'OR ', 'QBHaving');
    }

    /**
     * Starts a query group for HAVING clause, but NOTs the group.
     *
     * @return $this
     */
    public function notHavingGroupStart()
    {
        return $this->groupStartPrepare('NOT ', 'AND ', 'QBHaving');
    }

    /**
     * Starts a query group for HAVING clause, but OR NOTs the group.
     *
     * @return $this
     */
    public function orNotHavingGroupStart()
    {
        return $this->groupStartPrepare('NOT ', 'OR ', 'QBHaving');
    }

    /**
     * Ends a query group for HAVING clause.
     *
     * @return $this
     */
    public function havingGroupEnd()
    {
        return $this->groupEndPrepare('QBHaving');
    }

    /**
     * Prepate a query group start.
     *
     * @return $this
     */
    protected function groupStartPrepare(string $not = '', string $type = 'AND ', string $clause = 'QBWhere')
    {
        $type = $this->groupGetType($type);

        $this->QBWhereGroupStarted = true;
        $prefix                    = empty($this->{$clause}) ? '' : $type;
        $where                     = [
            'condition' => $prefix . $not . str_repeat(' ', ++$this->QBWhereGroupCount) . ' (',
            'escape'    => false,
        ];

        $this->{$clause}[] = $where;

        return $this;
    }

    /**
     * Prepate a query group end.
     *
     * @return $this
     */
    protected function groupEndPrepare(string $clause = 'QBWhere')
    {
        $this->QBWhereGroupStarted = false;
        $where                     = [
            'condition' => str_repeat(' ', $this->QBWhereGroupCount--) . ')',
            'escape'    => false,
        ];

        $this->{$clause}[] = $where;

        return $this;
    }

    /**
     * @used-by groupStart()
     * @used-by _like()
     * @used-by whereHaving()
     * @used-by _whereIn()
     * @used-by whereColumnHaving()
     * @used-by havingGroupStart()
     */
    protected function groupGetType(string $type): string
    {
        if ($this->QBWhereGroupStarted) {
            $type                      = '';
            $this->QBWhereGroupStarted = false;
        }

        return $type;
    }

    /**
     * @param array|string $by
     *
     * @return $this
     */
    public function groupBy($by, ?bool $escape = null)
    {
        if (! is_bool($escape)) {
            $escape = $this->db->protectIdentifiers;
        }

        if (is_string($by)) {
            $by = ($escape === true) ? explode(',', $by) : [$by];
        }

        foreach ($by as $val) {
            $val = trim($val);

            if ($val !== '') {
                $val = [
                    'field'  => $val,
                    'escape' => $escape,
                ];

                $this->QBGroupBy[] = $val;
            }
        }

        return $this;
    }

    /**
     * Separates multiple calls with 'AND'.
     *
     * @param array|RawSql|string $key
     * @param mixed               $value
     *
     * @return $this
     */
    public function having($key, $value = null, ?bool $escape = null)
    {
        return $this->whereHaving('QBHaving', $key, $value, 'AND ', $escape);
    }

    /**
     * Separates multiple calls with 'OR'.
     *
     * @param array|RawSql|string $key
     * @param mixed               $value
     *
     * @return $this
     */
    public function orHaving($key, $value = null, ?bool $escape = null)
    {
        return $this->whereHaving('QBHaving', $key, $value, 'OR ', $escape);
    }

    /**
     * @param string $direction ASC, DESC or RANDOM
     *
     * @return $this
     */
    public function orderBy(string $orderBy, string $direction = '', ?bool $escape = null)
    {
        if ($orderBy === '') {
            return $this;
        }

        $qbOrderBy = [];

        $direction = strtoupper(trim($direction));

        if ($direction === 'RANDOM') {
            $direction = '';
            $orderBy   = ctype_digit($orderBy) ? sprintf($this->randomKeyword[1], $orderBy) : $this->randomKeyword[0];
            $escape    = false;
        } elseif ($direction !== '') {
            $direction = in_array($direction, ['ASC', 'DESC'], true) ? ' ' . $direction : '';
        }

        if ($escape === null) {
            $escape = $this->db->protectIdentifiers;
        }

        if ($escape === false) {
            $qbOrderBy[] = [
                'field'     => $orderBy,
                'direction' => $direction,
                'escape'    => false,
            ];
        } else {
            foreach (explode(',', $orderBy) as $field) {
                $qbOrderBy[] = ($direction === '' && preg_match('/\s+(ASC|DESC)$/i', rtrim($field), $match, PREG_OFFSET_CAPTURE))
                    ? [
                        'field'     => ltrim(substr($field, 0, $match[0][1])),
                        'direction' => ' ' . $match[1][0],
                        'escape'    => true,
                    ]
                    : [
                        'field'     => trim($field),
                        'direction' => $direction,
                        'escape'    => true,
                    ];
            }
        }

        $this->QBOrderBy = array_merge($this->QBOrderBy, $qbOrderBy);

        return $this;
    }

    /**
     * @return $this
     */
    public function limit(?int $value = null, ?int $offset = 0)
    {
        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true; // @phpstan-ignore nullCoalesce.property
        if ($limitZeroAsAll && $value === 0) {
            $value = null;
        }

        if ($value !== null) {
            $this->QBLimit = $value;
        }

        if ($offset !== null && $offset !== 0) {
            $this->QBOffset = $offset;
        }

        return $this;
    }

    /**
     * Locks the selected rows for update.
     */
    public function lockForUpdate(): static
    {
        $this->QBLockForUpdate = true;

        return $this;
    }

    /**
     * Sets the OFFSET value
     *
     * @return $this
     */
    public function offset(int $offset)
    {
        if ($offset !== 0) {
            $this->QBOffset = $offset;
        }

        return $this;
    }

    /**
     * Generates a platform-specific LIMIT clause.
     */
    protected function _limit(string $sql, bool $offsetIgnore = false): string
    {
        return $sql . ' LIMIT ' . ($offsetIgnore === false && $this->QBOffset ? $this->QBOffset . ', ' : '') . $this->QBLimit;
    }

    /**
     * Allows key/value pairs to be set for insert(), update() or replace().
     *
     * @param array|object|string $key    Field name, or an array of field/value pairs, or an object
     * @param mixed               $value  Field value, if $key is a single field
     * @param bool|null           $escape Whether to escape values
     *
     * @return $this
     */
    public function set($key, $value = '', ?bool $escape = null)
    {
        $key = $this->objectToArray($key);

        if (! is_array($key)) {
            $key = [$key => $value];
        }

        $escape = is_bool($escape) ? $escape : $this->db->protectIdentifiers;

        foreach ($key as $k => $v) {
            if ($escape) {
                $bind = $this->setBind($k, $v, $escape);

                $this->QBSet[$this->db->protectIdentifiers($k, false)] = ":{$bind}:";
            } else {
                $this->QBSet[$this->db->protectIdentifiers($k, false)] = $v;
            }
        }

        return $this;
    }

    /**
     * Returns the previously set() data, alternatively resetting it if needed.
     */
    public function getSetData(bool $clean = false): array
    {
        $data = $this->QBSet;

        if ($clean) {
            $this->QBSet = [];
        }

        return $data;
    }

    /**
     * Compiles a SELECT query string and returns the sql.
     */
    public function getCompiledSelect(bool $reset = true): string
    {
        $select = $this->compileSelect();

        if ($reset) {
            $this->resetSelect();
        }

        return $this->compileFinalQuery($select);
    }

    /**
     * Returns a finalized, compiled query string with the bindings
     * inserted and prefixes swapped out.
     */
    protected function compileFinalQuery(string $sql): string
    {
        $query = new Query($this->db);
        $query->setQuery($sql, $this->binds, false);

        if (! empty($this->db->swapPre) && ! empty($this->db->DBPrefix)) {
            $query->swapPrefix($this->db->DBPrefix, $this->db->swapPre);
        }

        return $query->getQuery();
    }

    /**
     * Compiles the select statement based on the other functions called
     * and runs the query
     *
     * @return false|ResultInterface
     */
    public function get(?int $limit = null, int $offset = 0, bool $reset = true)
    {
        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true; // @phpstan-ignore nullCoalesce.property
        if ($limitZeroAsAll && $limit === 0) {
            $limit = null;
        }

        if ($limit !== null) {
            $this->limit($limit, $offset);
        }

        $result = $this->testMode
            ? $this->getCompiledSelect($reset)
            : $this->db->query($this->compileSelect(), $this->binds, false);

        if ($reset) {
            $this->resetSelect();

            // Clear our binds so we don't eat up memory
            $this->binds = [];
        }

        return $result;
    }

    /**
     * Explains the select statement based on the other functions called
     * and runs the query.
     *
     * @return BaseResult|false|Query|string SQL string when test mode is enabled.
     */
    public function explain(bool $reset = true)
    {
        $this->assertExplainSupported();

        $sql = $this->compileExplain($this->compileSelect());

        $result = $this->testMode
            ? $this->compileFinalQuery($sql)
            : $this->db->query($sql, $this->binds, false);

        if ($reset) {
            $this->resetSelect();

            // Clear our binds so we don't eat up memory
            $this->binds = [];
        }

        return $result;
    }

    /**
     * Ensures the current driver supports explaining Query Builder selects.
     */
    protected function assertExplainSupported(): void
    {
    }

    /**
     * Compiles an execution-plan query for the current SELECT query.
     */
    protected function compileExplain(string $sql): string
    {
        return 'EXPLAIN ' . $sql;
    }

    /**
     * Generates a platform-specific query string that counts all records in
     * the particular table
     *
     * @return int|string
     */
    public function countAll(bool $reset = true)
    {
        $table = $this->QBFrom[0];

        $sql = $this->countString . $this->db->escapeIdentifiers('numrows') . ' FROM ' .
            $this->db->protectIdentifiers($table, true, null, false);

        if ($this->testMode) {
            return $sql;
        }

        $query = $this->db->query($sql, null, false);

        if (empty($query->getResult())) {
            return 0;
        }

        $query = $query->getRow();

        if ($reset) {
            $this->resetSelect();
        }

        return (int) $query->numrows;
    }

    /**
     * Determines whether the current Query Builder query would return at least one row.
     *
     * @return bool|string SQL string when test mode is enabled.
     */
    public function exists(bool $reset = true)
    {
        $exists = $this->doExists($reset);

        return $exists ?? false;
    }

    /**
     * Determines whether the current Query Builder query would not return any rows.
     *
     * @return bool|string SQL string when test mode is enabled.
     */
    public function doesntExist(bool $reset = true)
    {
        $exists = $this->doExists($reset);

        return is_string($exists) ? $exists : $exists === false;
    }

    /**
     * Runs an existence probe for the current Query Builder query.
     *
     * @return bool|string|null SQL string when test mode is enabled, or null when the query fails.
     */
    protected function doExists(bool $reset = true)
    {
        $sql = $this->compileExists();

        if ($this->testMode) {
            if ($reset) {
                $this->resetSelect();

                // Clear our binds so we don't eat up memory
                $this->binds = [];
            }

            return $sql;
        }

        $result = $this->db->query($sql, $this->binds, false);

        if ($reset) {
            $this->resetSelect();

            // Clear our binds so we don't eat up memory
            $this->binds = [];
        }

        return $result instanceof ResultInterface ? $result->getRow() !== null : null;
    }

    /**
     * Compiles an existence probe for the current Query Builder query.
     */
    protected function compileExists(): string
    {
        // ORDER BY and FOR UPDATE are unnecessary for checking row existence,
        // and can produce invalid or surprising SQL on some drivers.
        $orderBy       = $this->QBOrderBy;
        $limit         = $this->QBLimit;
        $offset        = $this->QBOffset;
        $lockForUpdate = $this->QBLockForUpdate;
        $select        = $this->QBSelect;
        $noEscape      = $this->QBNoEscape;
        $needsSubquery = $this->QBSelectUsesAggregate || $this->QBUnion !== [] || $this->QBGroupBy !== [] || $this->QBHaving !== [] || $this->QBOffset !== false;

        $this->QBOrderBy       = null;
        $this->QBLockForUpdate = false;

        if (! $needsSubquery && $this->QBLimit !== 0) {
            $this->QBLimit = 1;
        }

        try {
            if ($needsSubquery) {
                $sql = "SELECT 1 FROM (\n" . $this->compileSelect() . "\n) CI_exists";

                $this->QBLimit  = 1;
                $this->QBOffset = false;

                return $this->_limit($sql . "\n");
            }

            return $this->compileSelect('SELECT 1');
        } finally {
            $this->QBOrderBy       = $orderBy;
            $this->QBLimit         = $limit;
            $this->QBOffset        = $offset;
            $this->QBLockForUpdate = $lockForUpdate;
            $this->QBSelect        = $select;
            $this->QBNoEscape      = $noEscape;
        }
    }

    /**
     * Generates a platform-specific query string that counts all records
     * returned by an Query Builder query.
     *
     * @return int|string
     */
    public function countAllResults(bool $reset = true)
    {
        // ORDER BY usage is often problematic here (most notably
        // on Microsoft SQL Server) and ultimately unnecessary
        // for selecting COUNT(*) ...
        $orderBy = [];

        if (! empty($this->QBOrderBy)) {
            $orderBy = $this->QBOrderBy;

            $this->QBOrderBy = null;
        }

        // We cannot use a LIMIT when getting the single row COUNT(*) result
        $limit         = $this->QBLimit;
        $lockForUpdate = $this->QBLockForUpdate;

        $this->QBLimit         = false;
        $this->QBLockForUpdate = false;

        try {
            if ($this->QBDistinct === true || ! empty($this->QBGroupBy)) {
                // We need to backup the original SELECT in case DBPrefix is used
                $select = $this->QBSelect;
                $sql    = $this->countString . $this->db->protectIdentifiers('numrows') . "\nFROM (\n" . $this->compileSelect() . "\n) CI_count_all_results";

                // Restore SELECT part
                $this->QBSelect = $select;
                unset($select);
            } else {
                $sql = $this->compileSelect($this->countString . $this->db->protectIdentifiers('numrows'));
            }
        } finally {
            $this->QBLockForUpdate = $lockForUpdate;
        }

        if ($this->testMode) {
            return $sql;
        }

        $result = $this->db->query($sql, $this->binds, false);

        if ($reset) {
            $this->resetSelect();
        } elseif (! isset($this->QBOrderBy)) {
            $this->QBOrderBy = $orderBy;
        }

        // Restore the LIMIT setting
        $this->QBLimit = $limit;

        $row = $result instanceof ResultInterface ? $result->getRow() : null;

        if (empty($row)) {
            return 0;
        }

        return (int) $row->numrows;
    }

    /**
     * Compiles the set conditions and returns the sql statement
     *
     * @return array
     */
    public function getCompiledQBWhere()
    {
        return $this->QBWhere;
    }

    /**
     * Allows the where clause, limit and offset to be added directly
     *
     * @param array|string $where
     *
     * @return ResultInterface
     */
    public function getWhere($where = null, ?int $limit = null, ?int $offset = 0, bool $reset = true)
    {
        if ($where !== null) {
            $this->where($where);
        }

        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true; // @phpstan-ignore nullCoalesce.property
        if ($limitZeroAsAll && $limit === 0) {
            $limit = null;
        }

        if ($limit !== null) {
            $this->limit($limit, $offset);
        }

        $result = $this->testMode
            ? $this->getCompiledSelect($reset)
            : $this->db->query($this->compileSelect(), $this->binds, false);

        if ($reset) {
            $this->resetSelect();

            // Clear our binds so we don't eat up memory
            $this->binds = [];
        }

        return $result;
    }

    /**
     * Compiles batch insert/update/upsert strings and runs the queries
     *
     * @param '_deleteBatch'|'_insertBatch'|'_updateBatch'|'_upsertBatch' $renderMethod
     *
     * @return false|int|list<string> Number of rows inserted or FALSE on failure, SQL array when testMode
     *
     * @throws DatabaseException
     */
    protected function batchExecute(string $renderMethod, int $batchSize = 100)
    {
        if (empty($this->QBSet)) {
            if ($this->db->DBDebug) {
                throw new DatabaseException(trim($renderMethod, '_') . '() has no data.');
            }

            return false; // @codeCoverageIgnore
        }

        $table = $this->db->protectIdentifiers($this->QBFrom[0], true, null, false);

        $affectedRows = 0;
        $savedSQL     = [];
        $cnt          = count($this->QBSet);

        // batch size 0 for unlimited
        if ($batchSize === 0) {
            $batchSize = $cnt;
        }

        for ($i = 0, $total = $cnt; $i < $total; $i += $batchSize) {
            $QBSet = array_slice($this->QBSet, $i, $batchSize);

            $sql = $this->{$renderMethod}($table, $this->QBKeys, $QBSet);

            if ($sql === '') {
                return false; // @codeCoverageIgnore
            }

            if ($this->testMode) {
                $savedSQL[] = $sql;
            } else {
                $this->db->query($sql, null, false);
                $affectedRows += $this->db->affectedRows();
            }
        }

        if (! $this->testMode) {
            $this->resetWrite();
        }

        return $this->testMode ? $savedSQL : $affectedRows;
    }

    /**
     * Allows a row or multiple rows to be set for batch inserts/upserts/updates
     *
     * @param array|object $set
     * @param string       $alias alias for sql table
     *
     * @return $this|null
     */
    public function setData($set, ?bool $escape = null, string $alias = '')
    {
        if (empty($set)) {
            if ($this->db->DBDebug) {
                throw new DatabaseException('setData() has no data.');
            }

            return null; // @codeCoverageIgnore
        }

        $this->setAlias($alias);

        // this allows to set just one row at a time
        if (is_object($set) || (! is_array(current($set)) && ! is_object(current($set)))) {
            $set = [$set];
        }

        $set = $this->batchObjectToArray($set);

        $escape = is_bool($escape) ? $escape : $this->db->protectIdentifiers;

        $keys = array_keys($this->objectToArray(current($set)));
        sort($keys);

        foreach ($set as $row) {
            $row = $this->objectToArray($row);
            if (array_diff($keys, array_keys($row)) !== [] || array_diff(array_keys($row), $keys) !== []) {
                // batchExecute() function returns an error on an empty array
                $this->QBSet[] = [];

                return null;
            }

            ksort($row); // puts $row in the same order as our keys

            $clean = [];

            foreach ($row as $rowValue) {
                $clean[] = $escape ? $this->db->escape($rowValue) : $rowValue;
            }

            $row = $clean;

            $this->QBSet[] = $row;
        }

        foreach ($keys as $k) {
            $k = $this->db->protectIdentifiers($k, false);

            if (! in_array($k, $this->QBKeys, true)) {
                $this->QBKeys[] = $k;
            }
        }

        return $this;
    }

    /**
     * Compiles an upsert query and returns the sql
     *
     * @return string
     *
     * @throws DatabaseException
     */
    public function getCompiledUpsert()
    {
        [$currentTestMode, $this->testMode] = [$this->testMode, true];

        $sql = implode(";\n", $this->upsert());

        $this->testMode = $currentTestMode;

        return $this->compileFinalQuery($sql);
    }

    /**
     * Converts call to batchUpsert
     *
     * @param array|object|null $set
     *
     * @return false|int|list<string> Number of affected rows or FALSE on failure, SQL array when testMode
     *
     * @throws DatabaseException
     */
    public function upsert($set = null, ?bool $escape = null)
    {
        // if set() has been used merge QBSet with binds and then setData()
        if ($set === null && ! is_array(current($this->QBSet))) {
            $set = [];

            foreach ($this->QBSet as $field => $value) {
                $k = trim($field, $this->db->escapeChar);
                // use binds if available else use QBSet value but with RawSql to avoid escape
                $set[$k] = isset($this->binds[$k]) ? $this->binds[$k][0] : new RawSql($value);
            }

            $this->binds = [];

            $this->resetRun([
                'QBSet'  => [],
                'QBKeys' => [],
            ]);

            $this->setData($set, true); // unescaped items are RawSql now
        } elseif ($set !== null) {
            $this->setData($set, $escape);
        } // else setData() has already been used and we need to do nothing

        return $this->batchExecute('_upsertBatch');
    }

    /**
     * Compiles batch upsert strings and runs the queries
     *
     * @param array|object|null $set a dataset
     *
     * @return false|int|list<string> Number of affected rows or FALSE on failure, SQL array when testMode
     *
     * @throws DatabaseException
     */
    public function upsertBatch($set = null, ?bool $escape = null, int $batchSize = 100)
    {
        if (isset($this->QBOptions['setQueryAsData'])) {
            $sql = $this->_upsertBatch($this->QBFrom[0], $this->QBKeys, []);

            if ($sql === '') {
                return false; // @codeCoverageIgnore
            }

            if ($this->testMode === false) {
                $this->db->query($sql, null, false);
            }

            $this->resetWrite();

            return $this->testMode ? $sql : $this->db->affectedRows();
        }

        if ($set !== null) {
            $this->setData($set, $escape);
        }

        return $this->batchExecute('_upsertBatch', $batchSize);
    }

    /**
     * Generates a platform-specific upsertBatch string from the supplied data
     *
     * @used-by batchExecute()
     *
     * @param string                 $table  Protected table name
     * @param list<string>           $keys   QBKeys
     * @param list<list<int|string>> $values QBSet
     */
    protected function _upsertBatch(string $table, array $keys, array $values): string
    {
        $sql = $this->QBOptions['sql'] ?? '';

        // if this is the first iteration of batch then we need to build skeleton sql
        if ($sql === '') {
            $updateFields = $this->QBOptions['updateFields'] ?? $this->updateFields($keys)->QBOptions['updateFields'] ?? [];

            $sql = 'INSERT INTO ' . $table . ' (' . implode(', ', $keys) . ")\n{:_table_:}ON DUPLICATE KEY UPDATE\n" . implode(
                ",\n",
                array_map(
                    static fn ($key, $value): string => $table . '.' . $key . ($value instanceof RawSql ?
                        ' = ' . $value :
                        ' = VALUES(' . $value . ')'),
                    array_keys($updateFields),
                    $updateFields,
                ),
            );

            $this->QBOptions['sql'] = $sql;
        }

        if (isset($this->QBOptions['setQueryAsData'])) {
            $data = $this->QBOptions['setQueryAsData'] . "\n";
        } else {
            $data = 'VALUES ' . implode(', ', $this->formatValues($values)) . "\n";
        }

        return str_replace('{:_table_:}', $data, $sql);
    }

    /**
     * Set table alias for dataset pseudo table.
     */
    private function setAlias(string $alias): BaseBuilder
    {
        if ($alias !== '') {
            $this->db->addTableAlias($alias);
            $this->QBOptions['alias'] = $this->db->protectIdentifiers($alias);
        }

        return $this;
    }

    /**
     * Sets update fields for upsert, update
     *
     * @param list<RawSql>|list<string>|string $set
     * @param bool                             $addToDefault adds update fields to the default ones
     * @param array|null                       $ignore       ignores items in set
     *
     * @return $this
     */
    public function updateFields($set, bool $addToDefault = false, ?array $ignore = null)
    {
        if (! empty($set)) {
            if (! is_array($set)) {
                $set = explode(',', $set);
            }

            foreach ($set as $key => $value) {
                if (! ($value instanceof RawSql)) {
                    $value = $this->db->protectIdentifiers($value);
                }

                if (is_numeric($key)) {
                    $key = $value;
                }

                if ($ignore === null || ! in_array($key, $ignore, true)) {
                    if ($addToDefault) {
                        $this->QBOptions['updateFieldsAdditional'][$this->db->protectIdentifiers($key)] = $value;
                    } else {
                        $this->QBOptions['updateFields'][$this->db->protectIdentifiers($key)] = $value;
                    }
                }
            }

            if ($addToDefault === false && isset($this->QBOptions['updateFieldsAdditional'], $this->QBOptions['updateFields'])) {
                $this->QBOptions['updateFields'] = array_merge($this->QBOptions['updateFields'], $this->QBOptions['updateFieldsAdditional']);

                unset($this->QBOptions['updateFieldsAdditional']);
            }
        }

        return $this;
    }

    /**
     * Sets constraints for batch upsert, update
     *
     * @param array|RawSql|string $set a string of columns, key value pairs, or RawSql
     *
     * @return $this
     */
    public function onConstraint($set)
    {
        if (! empty($set)) {
            if (is_string($set)) {
                $set = explode(',', $set);

                $set = array_map(trim(...), $set);
            }

            if ($set instanceof RawSql) {
                $set = [$set];
            }

            foreach ($set as $key => $value) {
                if (! ($value instanceof RawSql)) {
                    $value = $this->db->protectIdentifiers($value);
                }

                if (is_string($key)) {
                    $key = $this->db->protectIdentifiers($key);
                }

                $this->QBOptions['constraints'][$key] = $value;
            }
        }

        return $this;
    }

    /**
     * Sets data source as a query for insertBatch()/updateBatch()/upsertBatch()/deleteBatch()
     *
     * @param BaseBuilder|RawSql $query
     * @param array|string|null  $columns an array or comma delimited string of columns
     */
    public function setQueryAsData($query, ?string $alias = null, $columns = null): BaseBuilder
    {
        if (is_string($query)) {
            throw new InvalidArgumentException('$query parameter must be BaseBuilder or RawSql class.');
        }

        if ($query instanceof BaseBuilder) {
            $query = $query->getCompiledSelect();
        } elseif ($query instanceof RawSql) {
            $query = $query->__toString();
        }

        if (is_string($query)) {
            if ($columns !== null && is_string($columns)) {
                $columns = explode(',', $columns);
                $columns = array_map(trim(...), $columns);
            }

            $columns = (array) $columns;

            if ($columns === []) {
                $columns = $this->fieldsFromQuery($query);
            }

            if ($alias !== null) {
                $this->setAlias($alias);
            }

            foreach ($columns as $key => $value) {
                $columns[$key] = $this->db->escapeChar . $value . $this->db->escapeChar;
            }

            $this->QBOptions['setQueryAsData'] = $query;
            $this->QBKeys                      = $columns;
            $this->QBSet                       = [];
        }

        return $this;
    }

    /**
     * Gets column names from a select query
     */
    protected function fieldsFromQuery(string $sql): array
    {
        return $this->db->query('SELECT * FROM (' . $sql . ') _u_ LIMIT 1')->getFieldNames();
    }

    /**
     * Converts value array of array to array of strings
     */
    protected function formatValues(array $values): array
    {
        return array_map(static fn ($index): string => '(' . implode(',', $index) . ')', $values);
    }

    /**
     * Compiles batch insert strings and runs the queries
     *
     * @param array|object|null $set a dataset
     *
     * @return false|int|list<string> Number of rows inserted or FALSE on no data to perform an insert operation, SQL array when testMode
     */
    public function insertBatch($set = null, ?bool $escape = null, int $batchSize = 100)
    {
        if (isset($this->QBOptions['setQueryAsData'])) {
            $sql = $this->_insertBatch($this->QBFrom[0], $this->QBKeys, []);

            if ($sql === '') {
                return false; // @codeCoverageIgnore
            }

            if ($this->testMode === false) {
                $this->db->query($sql, null, false);
            }

            $this->resetWrite();

            return $this->testMode ? $sql : $this->db->affectedRows();
        }

        if ($set !== null && $set !== []) {
            $this->setData($set, $escape);
        }

        return $this->batchExecute('_insertBatch', $batchSize);
    }

    /**
     * Generates a platform-specific insert string from the supplied data.
     *
     * @used-by batchExecute()
     *
     * @param string                 $table  Protected table name
     * @param list<string>           $keys   QBKeys
     * @param list<list<int|string>> $values QBSet
     */
    protected function _insertBatch(string $table, array $keys, array $values): string
    {
        $sql = $this->QBOptions['sql'] ?? '';

        // if this is the first iteration of batch then we need to build skeleton sql
        if ($sql === '') {
            $sql = 'INSERT ' . $this->compileIgnore('insert') . 'INTO ' . $table
                . ' (' . implode(', ', $keys) . ")\n{:_table_:}";

            $this->QBOptions['sql'] = $sql;
        }

        if (isset($this->QBOptions['setQueryAsData'])) {
            $data = $this->QBOptions['setQueryAsData'];
        } else {
            $data = 'VALUES ' . implode(', ', $this->formatValues($values));
        }

        return str_replace('{:_table_:}', $data, $sql);
    }

    /**
     * Compiles an insert query and returns the sql
     *
     * @return bool|string
     *
     * @throws DatabaseException
     */
    public function getCompiledInsert(bool $reset = true)
    {
        if ($this->validateInsert() === false) {
            return false;
        }

        $sql = $this->_insert(
            $this->db->protectIdentifiers(
                $this->removeAlias($this->QBFrom[0]),
                true,
                null,
                false,
            ),
            array_keys($this->QBSet),
            array_values($this->QBSet),
        );

        if ($reset) {
            $this->resetWrite();
        }

        return $this->compileFinalQuery($sql);
    }

    /**
     * Compiles an insert string and runs the query
     *
     * @param array|object|null $set
     *
     * @return BaseResult|bool|Query
     *
     * @throws DatabaseException
     */
    public function insert($set = null, ?bool $escape = null)
    {
        if ($set !== null) {
            $this->set($set, '', $escape);
        }

        if ($this->validateInsert() === false) {
            return false;
        }

        $sql = $this->_insert(
            $this->db->protectIdentifiers(
                $this->removeAlias($this->QBFrom[0]),
                true,
                $escape,
                false,
            ),
            array_keys($this->QBSet),
            array_values($this->QBSet),
        );

        if (! $this->testMode) {
            $this->resetWrite();

            $result = $this->db->query($sql, $this->binds, false);

            // Clear our binds so we don't eat up memory
            $this->binds = [];

            return $result;
        }

        return false;
    }

    /**
     * @internal This is a temporary solution.
     *
     * @see https://github.com/codeigniter4/CodeIgniter4/pull/5376
     *
     * @TODO Fix a root cause, and this method should be removed.
     */
    protected function removeAlias(string $from): string
    {
        if (str_contains($from, ' ')) {
            // if the alias is written with the AS keyword, remove it
            $from = preg_replace('/\s+AS\s+/i', ' ', $from);

            $parts = explode(' ', $from);
            $from  = $parts[0];
        }

        return $from;
    }

    /**
     * This method is used by both insert() and getCompiledInsert() to
     * validate that the there data is actually being set and that table
     * has been chosen to be inserted into.
     *
     * @throws DatabaseException
     */
    protected function validateInsert(): bool
    {
        if (empty($this->QBSet)) {
            if ($this->db->DBDebug) {
                throw new DatabaseException('You must use the "set" method to insert an entry.');
            }

            return false; // @codeCoverageIgnore
        }

        return true;
    }

    /**
     * Generates a platform-specific insert string from the supplied data
     *
     * @param string           $table         Protected table name
     * @param list<string>     $keys          Keys of QBSet
     * @param list<int|string> $unescapedKeys Values of QBSet
     */
    protected function _insert(string $table, array $keys, array $unescapedKeys): string
    {
        return 'INSERT ' . $this->compileIgnore('insert') . 'INTO ' . $table . ' (' . implode(', ', $keys) . ') VALUES (' . implode(', ', $unescapedKeys) . ')';
    }

    /**
     * Compiles a replace into string and runs the query
     *
     * @return BaseResult|false|Query|string
     *
     * @throws DatabaseException
     */
    public function replace(?array $set = null)
    {
        if ($set !== null) {
            $this->set($set);
        }

        if (empty($this->QBSet)) {
            if ($this->db->DBDebug) {
                throw new DatabaseException('You must use the "set" method to update an entry.');
            }

            return false; // @codeCoverageIgnore
        }

        $table = $this->QBFrom[0];

        $sql = $this->_replace($table, array_keys($this->QBSet), array_values($this->QBSet));

        $this->resetWrite();

        return $this->testMode ? $sql : $this->db->query($sql, $this->binds, false);
    }

    /**
     * Generates a platform-specific replace string from the supplied data
     *
     * @param string           $table  Protected table name
     * @param list<string>     $keys   Keys of QBSet
     * @param list<int|string> $values Values of QBSet
     */
    protected function _replace(string $table, array $keys, array $values): string
    {
        return 'REPLACE INTO ' . $table . ' (' . implode(', ', $keys) . ') VALUES (' . implode(', ', $values) . ')';
    }

    /**
     * Groups tables in FROM clauses if needed, so there is no confusion
     * about operator precedence.
     *
     * Note: This is only used (and overridden) by MySQL and SQLSRV.
     */
    protected function _fromTables(): string
    {
        return implode(', ', $this->QBFrom);
    }

    /**
     * Compiles an update query and returns the sql
     *
     * @return bool|string
     */
    public function getCompiledUpdate(bool $reset = true)
    {
        if ($this->validateUpdate() === false) {
            return false;
        }

        $sql = $this->_update($this->QBFrom[0], $this->QBSet);

        if ($reset) {
            $this->resetWrite();
        }

        return $this->compileFinalQuery($sql);
    }

    /**
     * Compiles an update string and runs the query.
     *
     * @param array|object|null        $set
     * @param array|RawSql|string|null $where
     *
     * @throws DatabaseException
     */
    public function update($set = null, $where = null, ?int $limit = null): bool
    {
        if ($set !== null) {
            $this->set($set);
        }

        if ($this->validateUpdate() === false) {
            return false;
        }

        if ($where !== null) {
            $this->where($where);
        }

        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true; // @phpstan-ignore nullCoalesce.property
        if ($limitZeroAsAll && $limit === 0) {
            $limit = null;
        }

        if ($limit !== null) {
            if (! $this->canLimitWhereUpdates) {
                throw new DatabaseException('This driver does not allow LIMITs on UPDATE queries using WHERE.');
            }

            $this->limit($limit);
        }

        $sql = $this->_update($this->QBFrom[0], $this->QBSet);

        if (! $this->testMode) {
            $this->resetWrite();

            $result = $this->db->query($sql, $this->binds, false);

            if ($result !== false) {
                // Clear our binds so we don't eat up memory
                $this->binds = [];

                return true;
            }

            return false;
        }

        return true;
    }

    /**
     * Generates a platform-specific update string from the supplied data
     *
     * @param string                $table  Protected table name
     * @param array<string, string> $values QBSet
     */
    protected function _update(string $table, array $values): string
    {
        $valStr = [];

        foreach ($values as $key => $val) {
            $valStr[] = $key . ' = ' . $val;
        }

        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true; // @phpstan-ignore nullCoalesce.property
        if ($limitZeroAsAll) {
            return 'UPDATE ' . $this->compileIgnore('update') . $table . ' SET ' . implode(', ', $valStr)
                . $this->compileWhereHaving('QBWhere')
                . $this->compileOrderBy()
                . ($this->QBLimit ? $this->_limit(' ', true) : '');
        }

        return 'UPDATE ' . $this->compileIgnore('update') . $table . ' SET ' . implode(', ', $valStr)
            . $this->compileWhereHaving('QBWhere')
            . $this->compileOrderBy()
            . ($this->QBLimit !== false ? $this->_limit(' ', true) : '');
    }

    /**
     * This method is used by both update() and getCompiledUpdate() to
     * validate that data is actually being set and that a table has been
     * chosen to be updated.
     *
     * @throws DatabaseException
     */
    protected function validateUpdate(): bool
    {
        if (empty($this->QBSet)) {
            if ($this->db->DBDebug) {
                throw new DatabaseException('You must use the "set" method to update an entry.');
            }

            return false; // @codeCoverageIgnore
        }

        return true;
    }

    /**
     * Sets data and calls batchExecute to run queries
     *
     * @param array|object|null        $set         a dataset
     * @param array|RawSql|string|null $constraints
     *
     * @return false|int|list<string> Number of rows affected or FALSE on failure, SQL array when testMode
     */
    public function updateBatch($set = null, $constraints = null, int $batchSize = 100)
    {
        if ($this->QBWhere !== []) {
            throw new DatabaseException(
                'updateBatch() cannot be safely combined with existing Query Builder WHERE conditions. '
                . 'Use updateBatch($data, $constraints), onConstraint(), or include all required constraint fields in the batch data.',
            );
        }

        $this->onConstraint($constraints);

        if (isset($this->QBOptions['setQueryAsData'])) {
            $sql = $this->_updateBatch($this->QBFrom[0], $this->QBKeys, []);

            if ($sql === '') {
                return false; // @codeCoverageIgnore
            }

            if ($this->testMode === false) {
                $this->db->query($sql, null, false);
            }

            $this->resetWrite();

            return $this->testMode ? $sql : $this->db->affectedRows();
        }

        if ($set !== null && $set !== []) {
            $this->setData($set, true);
        }

        return $this->batchExecute('_updateBatch', $batchSize);
    }

    /**
     * Generates a platform-specific batch update string from the supplied data
     *
     * @used-by batchExecute()
     *
     * @param string                 $table  Protected table name
     * @param list<string>           $keys   QBKeys
     * @param list<list<int|string>> $values QBSet
     */
    protected function _updateBatch(string $table, array $keys, array $values): string
    {
        $sql = $this->QBOptions['sql'] ?? '';

        // if this is the first iteration of batch then we need to build skeleton sql
        if ($sql === '') {
            $constraints = $this->QBOptions['constraints'] ?? [];

            if ($constraints === []) {
                if ($this->db->DBDebug) {
                    throw new DatabaseException('You must specify a constraint to match on for batch updates.'); // @codeCoverageIgnore
                }

                return ''; // @codeCoverageIgnore
            }

            $updateFields = $this->QBOptions['updateFields'] ??
                $this->updateFields($keys, false, $constraints)->QBOptions['updateFields'] ??
                [];

            $alias = $this->QBOptions['alias'] ?? '_u';

            $sql = 'UPDATE ' . $this->compileIgnore('update') . $table . "\n";

            $sql .= "SET\n";

            $sql .= implode(
                ",\n",
                array_map(
                    static fn ($key, $value): string => $key . ($value instanceof RawSql ?
                        ' = ' . $value :
                        ' = ' . $alias . '.' . $value),
                    array_keys($updateFields),
                    $updateFields,
                ),
            ) . "\n";

            $sql .= "FROM (\n{:_table_:}";

            $sql .= ') ' . $alias . "\n";

            $sql .= 'WHERE ' . implode(
                ' AND ',
                array_map(
                    static fn ($key, $value) => (
                        ($value instanceof RawSql && is_string($key))
                        ?
                        $table . '.' . $key . ' = ' . $value
                        :
                        (
                            $value instanceof RawSql
                            ?
                            $value
                            :
                            $table . '.' . $value . ' = ' . $alias . '.' . $value
                        )
                    ),
                    array_keys($constraints),
                    $constraints,
                ),
            );

            $this->QBOptions['sql'] = $sql;
        }

        if (isset($this->QBOptions['setQueryAsData'])) {
            $data = $this->QBOptions['setQueryAsData'];
        } else {
            $data = implode(
                " UNION ALL\n",
                array_map(
                    static fn ($value): string => 'SELECT ' . implode(', ', array_map(
                        static fn ($key, $index): string => $index . ' ' . $key,
                        $keys,
                        $value,
                    )),
                    $values,
                ),
            ) . "\n";
        }

        return str_replace('{:_table_:}', $data, $sql);
    }

    /**
     * Compiles a delete string and runs "DELETE FROM table"
     *
     * @return bool|string TRUE on success, FALSE on failure, string on testMode
     */
    public function emptyTable()
    {
        $table = $this->QBFrom[0];

        $sql = $this->_delete($table);

        if ($this->testMode) {
            return $sql;
        }

        $this->resetWrite();

        return $this->db->query($sql, null, false);
    }

    /**
     * Compiles a truncate string and runs the query
     * If the database does not support the truncate() command
     * This function maps to "DELETE FROM table"
     *
     * @return bool|string TRUE on success, FALSE on failure, string on testMode
     */
    public function truncate()
    {
        $table = $this->QBFrom[0];

        $sql = $this->_truncate($table);

        if ($this->testMode) {
            return $sql;
        }

        $this->resetWrite();

        return $this->db->query($sql, null, false);
    }

    /**
     * Generates a platform-specific truncate string from the supplied data
     *
     * If the database does not support the truncate() command,
     * then this method maps to 'DELETE FROM table'
     *
     * @param string $table Protected table name
     */
    protected function _truncate(string $table): string
    {
        return 'TRUNCATE ' . $table;
    }

    /**
     * Compiles a delete query string and returns the sql
     */
    public function getCompiledDelete(bool $reset = true): string
    {
        $sql = $this->testMode()->delete('', null, $reset);
        $this->testMode(false);

        return $this->compileFinalQuery($sql);
    }

    /**
     * Compiles a delete string and runs the query
     *
     * @param array|RawSql|string $where
     *
     * @return bool|string Returns a SQL string if in test mode.
     *
     * @throws DatabaseException
     */
    public function delete($where = '', ?int $limit = null, bool $resetData = true)
    {
        $table = $this->db->protectIdentifiers($this->QBFrom[0], true, null, false);

        if ($where !== '') {
            $this->where($where);
        }

        if (empty($this->QBWhere)) {
            if ($this->db->DBDebug) {
                throw new DatabaseException('Deletes are not allowed unless they contain a "where" or "like" clause.');
            }

            return false; // @codeCoverageIgnore
        }

        $sql = $this->_delete($this->removeAlias($table));

        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true; // @phpstan-ignore nullCoalesce.property
        if ($limitZeroAsAll && $limit === 0) {
            $limit = null;
        }

        if ($limit !== null) {
            $this->QBLimit = $limit;
        }

        if (! empty($this->QBLimit)) {
            if (! $this->canLimitDeletes) {
                throw new DatabaseException('SQLite3 does not allow LIMITs on DELETE queries.');
            }

            $sql = $this->_limit($sql, true);
        }

        if ($resetData) {
            $this->resetWrite();
        }

        return $this->testMode ? $sql : $this->db->query($sql, $this->binds, false);
    }

    /**
     * Sets data and calls batchExecute to run queries
     *
     * @param array|object|null $set         a dataset
     * @param array|RawSql|null $constraints
     *
     * @return false|int|list<string> Number of rows affected or FALSE on failure, SQL array when testMode
     */
    public function deleteBatch($set = null, $constraints = null, int $batchSize = 100)
    {
        $this->onConstraint($constraints);

        if (isset($this->QBOptions['setQueryAsData'])) {
            $sql = $this->_deleteBatch($this->QBFrom[0], $this->QBKeys, []);

            if ($sql === '') {
                return false; // @codeCoverageIgnore
            }

            if ($this->testMode === false) {
                $this->db->query($sql, null, false);
            }

            $this->resetWrite();

            return $this->testMode ? $sql : $this->db->affectedRows();
        }

        if ($set !== null && $set !== []) {
            $this->setData($set, true);
        }

        return $this->batchExecute('_deleteBatch', $batchSize);
    }

    /**
     * Generates a platform-specific batch update string from the supplied data
     *
     * @used-by batchExecute()
     *
     * @param string           $table  Protected table name
     * @param list<string>     $keys   QBKeys
     * @param list<int|string> $values QBSet
     */
    protected function _deleteBatch(string $table, array $keys, array $values): string
    {
        $sql = $this->QBOptions['sql'] ?? '';

        // if this is the first iteration of batch then we need to build skeleton sql
        if ($sql === '') {
            $constraints = $this->QBOptions['constraints'] ?? [];

            if ($constraints === []) {
                if ($this->db->DBDebug) {
                    throw new DatabaseException('You must specify a constraint to match on for batch deletes.'); // @codeCoverageIgnore
                }

                return ''; // @codeCoverageIgnore
            }

            $alias = $this->QBOptions['alias'] ?? '_u';

            $sql = 'DELETE ' . $table . ' FROM ' . $table . "\n";

            $sql .= "INNER JOIN (\n{:_table_:}";

            $sql .= ') ' . $alias . "\n";

            $sql .= 'ON ' . implode(
                ' AND ',
                array_map(
                    static fn ($key, $value) => (
                        $value instanceof RawSql ?
                        $value :
                        (
                            is_string($key) ?
                            $table . '.' . $key . ' = ' . $alias . '.' . $value :
                            $table . '.' . $value . ' = ' . $alias . '.' . $value
                        )
                    ),
                    array_keys($constraints),
                    $constraints,
                ),
            );

            // convert binds in where
            foreach ($this->QBWhere as $key => $where) {
                foreach ($this->binds as $field => $bind) {
                    $this->QBWhere[$key]['condition'] = str_replace(':' . $field . ':', $bind[0], $where['condition']);
                }
            }

            $sql .= ' ' . $this->compileWhereHaving('QBWhere');

            $this->QBOptions['sql'] = trim($sql);
        }

        if (isset($this->QBOptions['setQueryAsData'])) {
            $data = $this->QBOptions['setQueryAsData'];
        } else {
            $data = implode(
                " UNION ALL\n",
                array_map(
                    static fn ($value): string => 'SELECT ' . implode(', ', array_map(
                        static fn ($key, $index): string => $index . ' ' . $key,
                        $keys,
                        $value,
                    )),
                    $values,
                ),
            ) . "\n";
        }

        return str_replace('{:_table_:}', $data, $sql);
    }

    /**
     * Increments a numeric column by the specified value.
     *
     * @return bool
     */
    public function increment(string $column, int $value = 1)
    {
        return $this->incrementMany([$column], $value);
    }

    /**
     * Increments multiple numeric columns by the specified value(s).
     *
     * @param array<string, int>|list<string> $columns A list of columns or array of column => value pairs to increment.
     * @param int                             $value   The value to increment by if $columns is a list of column names.
     */
    public function incrementMany(array $columns, int $value = 1): bool
    {
        if ($columns === []) {
            throw new InvalidArgumentException('Argument #1 ($columns) cannot be empty.');
        }

        if (array_is_list($columns)) {
            $columns = array_fill_keys($columns, $value);
        }

        $fields = [];

        foreach ($columns as $col => $val) {
            if (! is_int($val)) {
                throw new TypeError(sprintf(
                    'Argument #1 ($columns) must contain only int values, %s given for "%s".',
                    get_debug_type($val),
                    $col,
                ));
            }

            $col          = $this->db->protectIdentifiers($col);
            $fields[$col] = "{$col} + {$val}";
        }

        $sql = $this->_update($this->QBFrom[0], $fields);

        if (! $this->testMode) {
            $this->resetWrite();

            return $this->db->query($sql, $this->binds, false);
        }

        return true;
    }

    /**
     * Decrements a numeric column by the specified value.
     *
     * @return bool
     */
    public function decrement(string $column, int $value = 1)
    {
        return $this->decrementMany([$column], $value);
    }

    /**
     * Decrements multiple numeric columns by the specified value(s).
     *
     * @param array<string, int>|list<string> $columns A list of columns or array of column => value pairs to decrement.
     * @param int                             $value   The value to decrement by if $columns is a list of column names.
     */
    public function decrementMany(array $columns, int $value = 1): bool
    {
        if ($columns === []) {
            throw new InvalidArgumentException('Argument #1 ($columns) cannot be empty.');
        }

        if (array_is_list($columns)) {
            $columns = array_fill_keys($columns, $value);
        }

        $fields = [];

        foreach ($columns as $col => $val) {
            if (! is_int($val)) {
                throw new TypeError(sprintf(
                    'Argument #1 ($columns) must contain only int values, %s given for "%s".',
                    get_debug_type($val),
                    $col,
                ));
            }

            $col          = $this->db->protectIdentifiers($col);
            $fields[$col] = "{$col} - {$val}";
        }

        $sql = $this->_update($this->QBFrom[0], $fields);

        if (! $this->testMode) {
            $this->resetWrite();

            return $this->db->query($sql, $this->binds, false);
        }

        return true;
    }

    /**
     * Generates a platform-specific delete string from the supplied data
     *
     * @param string $table Protected table name
     */
    protected function _delete(string $table): string
    {
        return 'DELETE ' . $this->compileIgnore('delete') . 'FROM ' . $table . $this->compileWhereHaving('QBWhere');
    }

    /**
     * Used to track SQL statements written with aliased tables.
     *
     * @param array|string $table The table to inspect
     *
     * @return string|null
     */
    protected function trackAliases($table)
    {
        if (is_array($table)) {
            foreach ($table as $t) {
                $this->trackAliases($t);
            }

            return null;
        }

        // Does the string contain a comma?  If so, we need to separate
        // the string into discreet statements
        if (str_contains($table, ',')) {
            return $this->trackAliases(explode(',', $table));
        }

        // if a table alias is used we can recognize it by a space
        if (str_contains($table, ' ')) {
            // if the alias is written with the AS keyword, remove it
            $table = preg_replace('/\s+AS\s+/i', ' ', $table);

            // Grab the alias
            $alias = trim(strrchr($table, ' '));

            // Store the alias, if it doesn't already exist
            $this->db->addTableAlias($alias);
        }

        return null;
    }

    /**
     * Compile the SELECT statement
     *
     * Generates a query string based on which functions were used.
     * Should not be called directly.
     *
     * @param mixed $selectOverride
     */
    protected function compileSelect($selectOverride = false): string
    {
        if ($selectOverride !== false) {
            $sql = $selectOverride;
        } else {
            $sql = $this->QBDistinct ? 'SELECT DISTINCT ' : 'SELECT ';

            if (empty($this->QBSelect)) {
                $sql .= '*';
            } else {
                // Cycle through the "select" portion of the query and prep each column name.
                // The reason we protect identifiers here rather than in the select() function
                // is because until the user calls the from() function we don't know if there are aliases
                foreach ($this->QBSelect as $key => $val) {
                    if ($val instanceof RawSql) {
                        $this->QBSelect[$key] = (string) $val;
                    } else {
                        $protect              = $this->QBNoEscape[$key] ?? null;
                        $this->QBSelect[$key] = $this->db->protectIdentifiers($val, false, $protect);
                    }
                }

                $sql .= implode(', ', $this->QBSelect);
            }
        }

        if (! empty($this->QBFrom)) {
            $sql .= "\nFROM " . $this->_fromTables();
        }

        if (! empty($this->QBJoin)) {
            $sql .= "\n" . implode("\n", $this->QBJoin);
        }

        $sql .= $this->compileWhereHaving('QBWhere')
            . $this->compileGroupBy()
            . $this->compileWhereHaving('QBHaving')
            . $this->compileOrderBy();

        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true; // @phpstan-ignore nullCoalesce.property
        if ($limitZeroAsAll) {
            if ($this->QBLimit) {
                $sql = $this->_limit($sql . "\n");
            }
        } elseif ($this->QBLimit !== false || $this->QBOffset) {
            $sql = $this->_limit($sql . "\n");
        }

        $sql .= $this->compileLockForUpdate();

        return $this->unionInjection($sql);
    }

    /**
     * Compile the SELECT lock clause.
     */
    protected function compileLockForUpdate(): string
    {
        if ($this->QBLockForUpdate && $this->QBUnion !== []) {
            throw new DatabaseException('Query Builder does not support lockForUpdate() with union() or unionAll().');
        }

        return $this->QBLockForUpdate ? "\nFOR UPDATE" : '';
    }

    /**
     * Checks if the ignore option is supported by
     * the Database Driver for the specific statement.
     *
     * @return string
     */
    protected function compileIgnore(string $statement)
    {
        if ($this->QBIgnore && isset($this->supportedIgnoreStatements[$statement])) {
            return trim($this->supportedIgnoreStatements[$statement]) . ' ';
        }

        return '';
    }

    /**
     * Escapes identifiers in WHERE and HAVING statements at execution time.
     *
     * Required so that aliases are tracked properly, regardless of whether
     * where(), orWhere(), having(), orHaving are called prior to from(),
     * join() and prefixTable is added only if needed.
     *
     * @param string $qbKey 'QBWhere' or 'QBHaving'
     *
     * @return string SQL statement
     */
    protected function compileWhereHaving(string $qbKey): string
    {
        if (! empty($this->{$qbKey})) {
            foreach ($this->{$qbKey} as &$qbkey) {
                $qbkey = $this->compileWhereHavingCondition($qbkey);
            }

            return ($qbKey === 'QBHaving' ? "\nHAVING " : "\nWHERE ")
                . implode("\n", $this->{$qbKey});
        }

        return '';
    }

    /**
     * @used-by compileWhereHaving()
     *
     * @param array<string, mixed>|RawSql|string $condition
     */
    private function compileWhereHavingCondition(array|RawSql|string $condition): RawSql|string
    {
        // Is this condition already compiled?
        if (is_string($condition) || $condition instanceof RawSql) {
            return $condition;
        }

        if ($condition['condition'] instanceof RawSql) {
            return $condition['condition'];
        }

        if (($condition['columnComparison'] ?? false) === true) {
            return $this->compileColumnComparison($condition);
        }

        if (($condition['betweenComparison'] ?? false) === true) {
            return $this->compileBetweenComparison($condition);
        }

        if (($condition['datePartComparison'] ?? false) === true) {
            return $this->compileDatePartComparison($condition);
        }

        if ($condition['escape'] === false) {
            return $condition['condition'];
        }

        return $this->compileEscapedCondition($condition['condition']);
    }

    /**
     * @used-by compileWhereHavingCondition()
     */
    private function compileEscapedCondition(string $condition): string
    {
        // Split multiple conditions
        $conditions = preg_split(
            '/((?:^|\s+)AND\s+|(?:^|\s+)OR\s+)/i',
            $condition,
            -1,
            PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY,
        );

        foreach ($conditions as &$condition) {
            $op = $this->getOperator($condition);
            if (
                $op === false
                || preg_match(
                    '/^(\(?)(.*)(' . preg_quote($op, '/') . ')\s*(.*(?<!\)))?(\)?)$/i',
                    $condition,
                    $matches,
                ) !== 1
            ) {
                continue;
            }

            // $matches = [
            //  0 => '(test <= foo)',   /* the whole thing */
            //  1 => '(',               /* optional */
            //  2 => 'test',            /* the field name */
            //  3 => ' <= ',            /* $op */
            //  4 => 'foo',             /* optional, if $op is e.g. 'IS NULL' */
            //  5 => ')'                /* optional */
            // ];

            if ($matches[4] !== '') {
                $protectIdentifiers = false;
                if (str_contains($matches[4], '.')) {
                    $protectIdentifiers = true;
                }

                if (! str_contains($matches[4], ':')) {
                    $matches[4] = $this->db->protectIdentifiers(trim($matches[4]), false, $protectIdentifiers);
                }

                $matches[4] = ' ' . $matches[4];
            }

            $condition = $matches[1] . $this->db->protectIdentifiers(trim($matches[2]))
                . ' ' . trim($matches[3]) . $matches[4] . $matches[5];
        }

        return implode('', $conditions);
    }

    /**
     * @used-by compileWhereHavingCondition()
     *
     * @param array{columnComparison: true, condition: string, escape: bool, first: string, operator: string, second: string} $condition
     */
    private function compileColumnComparison(array $condition): string
    {
        if ($condition['escape']) {
            $condition['first']  = $this->db->protectIdentifiers($condition['first'], false, true);
            $condition['second'] = $this->db->protectIdentifiers($condition['second'], false, true);
        }

        return $condition['condition'] . $condition['first'] . ' ' . $condition['operator'] . ' ' . $condition['second'];
    }

    /**
     * @used-by compileWhereHavingCondition()
     *
     * @param array{betweenComparison: true, condition: string, escape: bool, key: string, lowerBind: string, not: string, upperBind: string} $condition
     */
    private function compileBetweenComparison(array $condition): string
    {
        if ($condition['escape']) {
            $condition['key'] = $this->db->protectIdentifiers($condition['key'], false, true);
        }

        return $condition['condition'] . $condition['key'] . $condition['not'] . ' BETWEEN :' . $condition['lowerBind'] . ': AND :' . $condition['upperBind'] . ':';
    }

    /**
     * @used-by compileWhereHavingCondition()
     *
     * @param array{condition: string, datePartComparison: true, escape: bool, key: string, nullComparison?: true, nullOperator?: string, operator?: string, part: 'date'|'day'|'month'|'year', rawValue?: bool, valueBind?: string} $condition
     */
    private function compileDatePartComparison(array $condition): string
    {
        if ($condition['escape']) {
            $condition['key'] = $this->db->protectIdentifiers($condition['key'], false, true);
        }

        $expression = $this->compileDatePartExpression($condition['part'], $condition['key']);

        if (($condition['nullComparison'] ?? false) === true) {
            return $condition['condition']
                . $expression
                . ' ' . $condition['nullOperator'];
        }

        return $condition['condition']
            . $expression
            . ' ' . $condition['operator'] . ' '
            . $this->compileDatePartValue($condition['part'], $condition['valueBind'], $condition['rawValue']);
    }

    /**
     * Compiles a driver-specific SQL expression for Query Builder date helpers.
     *
     * @param 'date'|'day'|'month'|'year' $part
     */
    protected function compileDatePartExpression(string $part, string $field): string
    {
        return match ($part) {
            'date'  => 'CAST(' . $field . ' AS DATE)',
            'year'  => 'EXTRACT(YEAR FROM ' . $field . ')',
            'month' => 'EXTRACT(MONTH FROM ' . $field . ')',
            'day'   => 'EXTRACT(DAY FROM ' . $field . ')',
        };
    }

    /**
     * Compiles the value side for Query Builder date helpers.
     *
     * @param 'date'|'day'|'month'|'year' $part
     */
    protected function compileDatePartValue(string $part, string $bind, bool $rawValue): string
    {
        return ':' . $bind . ':';
    }

    /**
     * Escapes identifiers in GROUP BY statements at execution time.
     *
     * Required so that aliases are tracked properly, regardless of whether
     * groupBy() is called prior to from(), join() and prefixTable is added
     * only if needed.
     */
    protected function compileGroupBy(): string
    {
        if (! empty($this->QBGroupBy)) {
            foreach ($this->QBGroupBy as &$groupBy) {
                // Is it already compiled?
                if (is_string($groupBy)) {
                    continue;
                }

                $groupBy = ($groupBy['escape'] === false || $this->isLiteral($groupBy['field']))
                    ? $groupBy['field']
                    : $this->db->protectIdentifiers($groupBy['field']);
            }

            return "\nGROUP BY " . implode(', ', $this->QBGroupBy);
        }

        return '';
    }

    /**
     * Escapes identifiers in ORDER BY statements at execution time.
     *
     * Required so that aliases are tracked properly, regardless of whether
     * orderBy() is called prior to from(), join() and prefixTable is added
     * only if needed.
     */
    protected function compileOrderBy(): string
    {
        if (is_array($this->QBOrderBy) && $this->QBOrderBy !== []) {
            foreach ($this->QBOrderBy as &$orderBy) {
                if (is_string($orderBy)) {
                    continue;
                }
                if ($orderBy['escape'] !== false && ! $this->isLiteral($orderBy['field'])) {
                    $orderBy['field'] = $this->db->protectIdentifiers($orderBy['field']);
                }

                $orderBy = $orderBy['field'] . $orderBy['direction'];
            }

            return "\nORDER BY " . implode(', ', $this->QBOrderBy);
        }

        return '';
    }

    protected function unionInjection(string $sql): string
    {
        if ($this->QBUnion === []) {
            return $sql;
        }

        return 'SELECT * FROM (' . $sql . ') '
            . ($this->db->protectIdentifiers ? $this->db->escapeIdentifiers('uwrp0') : 'uwrp0')
            . implode("\n", $this->QBUnion);
    }

    /**
     * Takes an object as input and converts the class variables to array key/vals
     *
     * @param array|object $object
     *
     * @return array
     */
    protected function objectToArray($object)
    {
        if (! is_object($object)) {
            return $object;
        }

        if ($object instanceof RawSql) {
            throw new InvalidArgumentException('RawSql "' . $object . '" cannot be used here.');
        }

        $array = [];

        foreach (get_object_vars($object) as $key => $val) {
            if (($val instanceof BackedEnum || ! is_object($val) || $val instanceof RawSql) && ! is_array($val)) {
                $array[$key] = $val;
            }
        }

        return $array;
    }

    /**
     * Takes an object as input and converts the class variables to array key/vals
     *
     * @param array|object $object
     *
     * @return array
     */
    protected function batchObjectToArray($object)
    {
        if (! is_object($object)) {
            return $object;
        }

        $array  = [];
        $out    = get_object_vars($object);
        $fields = array_keys($out);

        foreach ($fields as $val) {
            $i = 0;

            foreach ($out[$val] as $data) {
                $array[$i++][$val] = $data;
            }
        }

        return $array;
    }

    /**
     * Determines if a string represents a literal value or a field name
     */
    protected function isLiteral(string $str): bool
    {
        $str = trim($str);

        if ($str === ''
            || ctype_digit($str)
            || (string) (float) $str === $str
            || in_array(strtoupper($str), ['TRUE', 'FALSE'], true)
        ) {
            return true;
        }

        if ($this->isLiteralStr === []) {
            $this->isLiteralStr = $this->db->escapeChar !== '"' ? ['"', "'"] : ["'"];
        }

        return in_array($str[0], $this->isLiteralStr, true);
    }

    /**
     * Publicly-visible method to reset the QB values.
     *
     * @return $this
     */
    public function resetQuery()
    {
        $this->resetSelect();
        $this->resetWrite();

        return $this;
    }

    /**
     * Resets the query builder values.  Called by the get() function
     *
     * @param array $qbResetItems An array of fields to reset
     *
     * @return void
     */
    protected function resetRun(array $qbResetItems)
    {
        foreach ($qbResetItems as $item => $defaultValue) {
            $this->{$item} = $defaultValue;
        }
    }

    /**
     * Resets the query builder values.  Called by the get() function
     *
     * @return void
     */
    protected function resetSelect()
    {
        $this->resetRun([
            'QBSelect'              => [],
            'QBJoin'                => [],
            'QBWhere'               => [],
            'QBGroupBy'             => [],
            'QBHaving'              => [],
            'QBOrderBy'             => [],
            'QBNoEscape'            => [],
            'QBDistinct'            => false,
            'QBLimit'               => false,
            'QBOffset'              => false,
            'QBLockForUpdate'       => false,
            'QBSelectUsesAggregate' => false,
            'QBUnion'               => [],
        ]);

        if ($this->db instanceof BaseConnection) {
            $this->db->setAliasedTables([]);
        }

        // Reset QBFrom part
        if (! empty($this->QBFrom)) {
            $this->from(array_shift($this->QBFrom), true);
        }
    }

    /**
     * Resets the query builder "write" values.
     *
     * Called by the insert() update() insertBatch() updateBatch() and delete() functions
     *
     * @return void
     */
    protected function resetWrite()
    {
        $this->resetRun([
            'QBSet'     => [],
            'QBJoin'    => [],
            'QBWhere'   => [],
            'QBOrderBy' => [],
            'QBKeys'    => [],
            'QBLimit'   => false,
            'QBIgnore'  => false,
            'QBOptions' => [],
        ]);
    }

    /**
     * Tests whether the string has an SQL operator
     */
    protected function hasOperator(string $str): bool
    {
        return preg_match(
            '/(<|>|!|=|\sIS NULL|\sIS NOT NULL|\sEXISTS|\sBETWEEN|\sLIKE|\sIN\s*\(|\s)/i',
            trim($str),
        ) === 1;
    }

    /**
     * Returns the SQL string operator
     *
     * @return array|false|string
     */
    protected function getOperator(string $str, bool $list = false)
    {
        if ($this->pregOperators === []) {
            $_les = $this->db->likeEscapeStr !== ''
                ? '\s+' . preg_quote(trim(sprintf($this->db->likeEscapeStr, $this->db->likeEscapeChar)), '/')
                : '';
            $this->pregOperators = [
                '\s*(?:<|>|!)?=\s*', // =, <=, >=, !=
                '\s*<>?\s*',         // <, <>
                '\s*>\s*',           // >
                '\s+IS NULL',             // IS NULL
                '\s+IS NOT NULL',         // IS NOT NULL
                '\s+EXISTS\s*\(.*\)',     // EXISTS (sql)
                '\s+NOT EXISTS\s*\(.*\)', // NOT EXISTS(sql)
                '\s+BETWEEN\s+',          // BETWEEN value AND value
                '\s+IN\s*\(.*\)',         // IN (list)
                '\s+NOT IN\s*\(.*\)',     // NOT IN (list)
                '\s+LIKE\s+\S.*(' . $_les . ')?',     // LIKE 'expr'[ ESCAPE '%s']
                '\s+NOT LIKE\s+\S.*(' . $_les . ')?', // NOT LIKE 'expr'[ ESCAPE '%s']
            ];
        }

        return preg_match_all(
            '/' . implode('|', $this->pregOperators) . '/i',
            $str,
            $match,
        ) >= 1 ? ($list ? $match[0] : $match[0][0]) : false;
    }

    /**
     * Returns the SQL string operator from where key
     *
     * @return false|list<string>
     */
    private function getOperatorFromWhereKey(string $whereKey)
    {
        $whereKey = trim($whereKey);

        $pregOperators = [
            '\s*(?:<|>|!)?=',         // =, <=, >=, !=
            '\s*<>?',                 // <, <>
            '\s*>',                   // >
            '\s+IS NULL',             // IS NULL
            '\s+IS NOT NULL',         // IS NOT NULL
            '\s+EXISTS\s*\(.*\)',     // EXISTS (sql)
            '\s+NOT EXISTS\s*\(.*\)', // NOT EXISTS (sql)
            '\s+BETWEEN\s+',          // BETWEEN value AND value
            '\s+IN\s*\(.*\)',         // IN (list)
            '\s+NOT IN\s*\(.*\)',     // NOT IN (list)
            '\s+LIKE',                // LIKE
            '\s+NOT LIKE',            // NOT LIKE
        ];

        return preg_match_all(
            '/' . implode('|', $pregOperators) . '/i',
            $whereKey,
            $match,
        ) >= 1 ? $match[0] : false;
    }

    /**
     * Stores a bind value after ensuring that it's unique.
     * While it might be nicer to have named keys for our binds array
     * with PHP 7+ we get a huge memory/performance gain with indexed
     * arrays instead, so lets take advantage of that here.
     *
     * @param mixed $value
     */
    protected function setBind(string $key, $value = null, bool $escape = true): string
    {
        if (! array_key_exists($key, $this->binds)) {
            $this->binds[$key] = [
                $value,
                $escape,
            ];

            return $key;
        }

        if (! array_key_exists($key, $this->bindsKeyCount)) {
            $this->bindsKeyCount[$key] = 1;
        }

        $count = $this->bindsKeyCount[$key]++;

        $this->binds[$key . '.' . $count] = [
            $value,
            $escape,
        ];

        return $key . '.' . $count;
    }

    /**
     * @param mixed $value
     */
    protected function isSubquery($value): bool
    {
        return $value instanceof BaseBuilder || $value instanceof Closure;
    }

    /**
     * @param BaseBuilder|Closure(BaseBuilder): BaseBuilder $builder
     * @param bool                                          $wrapped Wrap the subquery in brackets
     * @param string                                        $alias   Subquery alias
     */
    protected function buildSubquery($builder, bool $wrapped = false, string $alias = ''): string
    {
        if ($builder instanceof Closure) {
            $builder($builder = $this->db->newQuery());
        }

        if ($builder === $this) {
            throw new DatabaseException('The subquery cannot be the same object as the main query object.');
        }

        $subquery = strtr($builder->getCompiledSelect(false), "\n", ' ');

        if ($wrapped) {
            $subquery = '(' . $subquery . ')';
            $alias    = trim($alias);

            if ($alias !== '') {
                $subquery .= ' ' . ($this->db->protectIdentifiers ? $this->db->escapeIdentifiers($alias) : $alias);
            }
        }

        return $subquery;
    }
}

codeigniter4 / CodeIgniter4 / 27340411392

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous