3
declare(strict_types=1);
6
* This file is part of CodeIgniter 4 framework.
8
* (c) CodeIgniter Foundation <admin@codeigniter.com>
10
* For the full copyright and license information, please view
11
* the LICENSE file that was distributed with this source code.
14
namespace CodeIgniter\Database;
17
use CodeIgniter\Database\Exceptions\DatabaseException;
18
use CodeIgniter\Database\Exceptions\DataException;
19
use CodeIgniter\Traits\ConditionalTrait;
21
use InvalidArgumentException;
26
* Provides the core Query Builder methods.
27
* Database-specific Builders might need to override
28
* certain methods to make them work.
35
* Reset DELETE data flag
39
protected $resetDeleteData = false;
46
protected $QBSelect = [];
53
protected $QBDistinct = false;
60
protected $QBFrom = [];
67
protected $QBJoin = [];
74
protected $QBWhere = [];
81
public $QBGroupBy = [];
88
protected $QBHaving = [];
92
* list of column names.
96
protected $QBKeys = [];
103
protected $QBLimit = false;
110
protected $QBOffset = false;
115
* @var array|string|null
117
public $QBOrderBy = [];
124
protected array $QBUnion = [];
127
* Whether to protect identifiers in SELECT
129
* @var list<bool|null> true=protect, false=not protect
131
public $QBNoEscape = [];
136
* @var array<string, string>|list<list<int|string>>
138
protected $QBSet = [];
141
* QB WHERE group started flag
145
protected $QBWhereGroupStarted = false;
148
* QB WHERE group count
152
protected $QBWhereGroupCount = 0;
155
* Ignore data that cause certain
156
* exceptions, for example in case of
161
protected $QBIgnore = false;
165
* Holds additional options and data used to render SQL
166
* and is reset by resetWrite()
169
* updateFieldsAdditional?: array,
170
* tableIdentity?: string,
171
* updateFields?: array,
172
* constraints?: array,
173
* setQueryAsData?: string,
176
* fieldTypes?: array<string, array<string, string>>
179
* fieldTypes: [ProtectedTableName => [FieldName => Type]]
181
protected $QBOptions;
184
* A reference to the database connection.
186
* @var BaseConnection
191
* Name of the primary table for this instance.
192
* Tracked separately because $QBFrom gets escaped
195
* When $tableName to the constructor has multiple tables,
196
* the value is empty string.
200
protected $tableName;
203
* ORDER BY random keyword
207
protected $randomKeyword = [
215
* @used-by CI_DB_driver::count_all()
216
* @used-by BaseBuilder::count_all_results()
220
protected $countString = 'SELECT COUNT(*) AS ';
223
* Collects the named parameters and
224
* their values for later binding
225
* in the Query object.
229
protected $binds = [];
232
* Collects the key count for named parameters
233
* in the Query object.
237
protected $bindsKeyCount = [];
240
* Some databases, like SQLite, do not by default
241
* allow limiting of delete clauses.
245
protected $canLimitDeletes = true;
248
* Some databases do not by default
249
* allow limit update queries with WHERE.
253
protected $canLimitWhereUpdates = true;
256
* Specifies which sql statements
257
* support the ignore option.
261
protected $supportedIgnoreStatements = [];
264
* Builder testing mode status.
268
protected $testMode = false;
271
* Tables relation types
275
protected $joinTypes = [
285
* Strings that determine if a string represents a literal value or a field name
289
protected $isLiteralStr = [];
292
* RegExp used to get operators
296
protected $pregOperators = [];
301
* @param array|string $tableName tablename or tablenames with or without aliases
303
* Examples of $tableName: `mytable`, `jobs j`, `jobs j, users u`, `['jobs j','users u']`
305
* @throws DatabaseException
307
public function __construct($tableName, ConnectionInterface $db, ?array $options = null)
309
if (empty($tableName)) {310
throw new DatabaseException('A table must be specified when creating a new Query Builder.');314
* @var BaseConnection $db
318
// If it contains `,`, it has multiple tables
319
if (is_string($tableName) && ! str_contains($tableName, ',')) {320
$this->tableName = $tableName; // @TODO remove alias if exists
322
$this->tableName = '';
325
$this->from($tableName);
327
if ($options !== null && $options !== []) {328
foreach ($options as $key => $value) {329
if (property_exists($this, $key)) {330
$this->{$key} = $value;337
* Returns the current database connection
339
* @return BaseConnection
341
public function db(): ConnectionInterface
347
* Sets a test mode status.
351
public function testMode(bool $mode = true)
353
$this->testMode = $mode;
359
* Gets the name of the primary table.
361
public function getTable(): string
363
return $this->tableName;
367
* Returns an array of bind values and their
368
* named parameters for binding in the Query object later.
370
public function getBinds(): array
378
* Set ignore Flag for next insert,
379
* update or delete query.
383
public function ignore(bool $ignore = true)
385
$this->QBIgnore = $ignore;
391
* Generates the SELECT portion of the query
393
* @param list<RawSql|string>|RawSql|string $select
394
* @param bool|null $escape Whether to protect identifiers
398
public function select($select = '*', ?bool $escape = null)
400
// If the escape value was not set, we will base it on the global setting
401
if (! is_bool($escape)) {402
$escape = $this->db->protectIdentifiers;
405
if ($select instanceof RawSql) {409
if (is_string($select)) {410
$select = ($escape === false) ? [$select] : explode(',', $select);413
foreach ($select as $val) {414
if ($val instanceof RawSql) {415
$this->QBSelect[] = $val;
416
$this->QBNoEscape[] = false;
424
$this->QBSelect[] = $val;
427
* When doing 'SELECT NULL as field_alias FROM table'
428
* null gets taken as a field, and therefore escaped
430
* This prevents NULL being escaped
431
* @see https://github.com/codeigniter4/CodeIgniter4/issues/1169
433
if (mb_stripos($val, 'NULL') === 0) {434
$this->QBNoEscape[] = false;
439
$this->QBNoEscape[] = $escape;
447
* Generates a SELECT MAX(field) portion of a query
451
public function selectMax(string $select = '', string $alias = '')
453
return $this->maxMinAvgSum($select, $alias);
457
* Generates a SELECT MIN(field) portion of a query
461
public function selectMin(string $select = '', string $alias = '')
463
return $this->maxMinAvgSum($select, $alias, 'MIN');
467
* Generates a SELECT AVG(field) portion of a query
471
public function selectAvg(string $select = '', string $alias = '')
473
return $this->maxMinAvgSum($select, $alias, 'AVG');
477
* Generates a SELECT SUM(field) portion of a query
481
public function selectSum(string $select = '', string $alias = '')
483
return $this->maxMinAvgSum($select, $alias, 'SUM');
487
* Generates a SELECT COUNT(field) portion of a query
491
public function selectCount(string $select = '', string $alias = '')
493
return $this->maxMinAvgSum($select, $alias, 'COUNT');
497
* Adds a subquery to the selection
499
public function selectSubquery(BaseBuilder $subquery, string $as): self
501
$this->QBSelect[] = $this->buildSubquery($subquery, true, $as);
507
* SELECT [MAX|MIN|AVG|SUM|COUNT]()
509
* @used-by selectMax()
510
* @used-by selectMin()
511
* @used-by selectAvg()
512
* @used-by selectSum()
516
* @throws DatabaseException
517
* @throws DataException
519
protected function maxMinAvgSum(string $select = '', string $alias = '', string $type = 'MAX')
521
if ($select === '') {522
throw DataException::forEmptyInputGiven('Select');525
if (str_contains($select, ',')) {526
throw DataException::forInvalidArgument('column name not separated by comma');529
$type = strtoupper($type);
531
if (! in_array($type, ['MAX', 'MIN', 'AVG', 'SUM', 'COUNT'], true)) {532
throw new DatabaseException('Invalid function type: ' . $type);536
$alias = $this->createAliasFromTable(trim($select));
539
$sql = $type . '(' . $this->db->protectIdentifiers(trim($select)) . ') AS ' . $this->db->escapeIdentifiers(trim($alias));541
$this->QBSelect[] = $sql;
542
$this->QBNoEscape[] = null;
548
* Determines the alias name based on the table
550
protected function createAliasFromTable(string $item): string
552
if (str_contains($item, '.')) {553
$item = explode('.', $item);562
* Sets a flag which tells the query string compiler to add DISTINCT
566
public function distinct(bool $val = true)
568
$this->QBDistinct = $val;
574
* Generates the FROM portion of the query
576
* @param array|string $from
580
public function from($from, bool $overwrite = false): self
582
if ($overwrite === true) {584
$this->db->setAliasedTables([]);
587
foreach ((array) $from as $table) {588
if (str_contains($table, ',')) {589
$this->from(explode(',', $table));591
$table = trim($table);
597
$this->trackAliases($table);
598
$this->QBFrom[] = $this->db->protectIdentifiers($table, true, null, false);
606
* @param BaseBuilder $from Expected subquery
607
* @param string $alias Subquery alias
611
public function fromSubquery(BaseBuilder $from, string $alias): self
613
$table = $this->buildSubquery($from, true, $alias);
615
$this->db->addTableAlias($alias);
616
$this->QBFrom[] = $table;
622
* Generates the JOIN portion of the query
624
* @param RawSql|string $cond
628
public function join(string $table, $cond, string $type = '', ?bool $escape = null)
631
$type = strtoupper(trim($type));
633
if (! in_array($type, $this->joinTypes, true)) {640
// Extract any aliases that might exist. We use this information
641
// in the protectIdentifiers to know whether to add a table prefix
642
$this->trackAliases($table);
644
if (! is_bool($escape)) {645
$escape = $this->db->protectIdentifiers;
648
// Do we want to escape the table name?
649
if ($escape === true) {650
$table = $this->db->protectIdentifiers($table, true, null, false);
653
if ($cond instanceof RawSql) {654
$this->QBJoin[] = $type . 'JOIN ' . $table . ' ON ' . $cond;
659
if (! $this->hasOperator($cond)) {660
$cond = ' USING (' . ($escape ? $this->db->escapeIdentifiers($cond) : $cond) . ')';661
} elseif ($escape === false) {662
$cond = ' ON ' . $cond;
664
// Split multiple conditions
665
// @TODO This does not parse `BETWEEN a AND b` correctly.
666
if (preg_match_all('/\sAND\s|\sOR\s/i', $cond, $joints, PREG_OFFSET_CAPTURE)) {668
$joints = $joints[0];
669
array_unshift($joints, ['', 0]);
671
for ($i = count($joints) - 1, $pos = strlen($cond); $i >= 0; $i--) {672
$joints[$i][1] += strlen($joints[$i][0]); // offset
673
$conditions[$i] = substr($cond, $joints[$i][1], $pos - $joints[$i][1]);
674
$pos = $joints[$i][1] - strlen($joints[$i][0]);
675
$joints[$i] = $joints[$i][0];
679
$conditions = [$cond];
685
foreach ($conditions as $i => $condition) {686
$operator = $this->getOperator($condition);
688
// Workaround for BETWEEN
689
if ($operator === false) {690
$cond .= $joints[$i] . $condition;
695
$cond .= $joints[$i];
696
$cond .= preg_match('/(\(*)?([\[\]\w\.\'-]+)' . preg_quote($operator, '/') . '(.*)/i', $condition, $match) ? $match[1] . $this->db->protectIdentifiers($match[2]) . $operator . $this->db->protectIdentifiers($match[3]) : $condition;700
// Assemble the JOIN statement
701
$this->QBJoin[] = $type . 'JOIN ' . $table . $cond;
707
* Generates the WHERE portion of the query.
708
* Separates multiple calls with 'AND'.
710
* @param array|RawSql|string $key
711
* @param mixed $value
715
public function where($key, $value = null, ?bool $escape = null)
717
return $this->whereHaving('QBWhere', $key, $value, 'AND ', $escape);723
* Generates the WHERE portion of the query.
724
* Separates multiple calls with 'OR'.
726
* @param array|RawSql|string $key
727
* @param mixed $value
731
public function orWhere($key, $value = null, ?bool $escape = null)
733
return $this->whereHaving('QBWhere', $key, $value, 'OR ', $escape);740
* @used-by orHaving()
742
* @param array|RawSql|string $key
743
* @param mixed $value
747
protected function whereHaving(string $qbKey, $key, $value = null, string $type = 'AND ', ?bool $escape = null)
751
if ($key instanceof RawSql) {752
if ($value === null) {753
$keyValue = [(string) $key => $key];
756
$keyValue = [(string) $key => $value];
758
} elseif (! is_array($key)) {759
$keyValue = [$key => $value];
764
// If the escape value was not set will base it on the global setting
765
if (! is_bool($escape)) {766
$escape = $this->db->protectIdentifiers;
769
foreach ($keyValue as $k => $v) {770
$prefix = empty($this->{$qbKey}) ? $this->groupGetType('') : $this->groupGetType($type);772
if ($rawSqlOnly === true) {775
} elseif ($v !== null) {776
$op = $this->getOperatorFromWhereKey($k);
782
$op = trim(current($op));
784
// Does the key end with operator?
785
if (str_ends_with($k, $op)) {786
$k = rtrim(substr($k, 0, -strlen($op)));
795
if ($this->isSubquery($v)) {796
$v = $this->buildSubquery($v, true);
798
$bind = $this->setBind($k, $v, $escape);
801
} elseif (! $this->hasOperator($k) && $qbKey !== 'QBHaving') {802
// value appears not to have been set, assign the test to IS NULL
805
// The key ends with !=, =, <>, IS, IS NOT
807
'/\s*(!?=|<>|IS(?:\s+NOT)?)\s*$/i',
813
$k = substr($k, 0, $match[0][1]);
814
$op = $match[1][0] === '=' ? ' IS NULL' : ' IS NOT NULL';
819
if ($v instanceof RawSql) {820
$this->{$qbKey}[] = [821
'condition' => $v->with($prefix . $k . $op . $v),
825
$this->{$qbKey}[] = [826
'condition' => $prefix . $k . $op . $v,
836
* Generates a WHERE field IN('item', 'item') SQL query,837
* joined with 'AND' if appropriate.
839
* @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
843
public function whereIn(?string $key = null, $values = null, ?bool $escape = null)
845
return $this->_whereIn($key, $values, false, 'AND ', $escape);
849
* Generates a WHERE field IN('item', 'item') SQL query,850
* joined with 'OR' if appropriate.
852
* @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
856
public function orWhereIn(?string $key = null, $values = null, ?bool $escape = null)
858
return $this->_whereIn($key, $values, false, 'OR ', $escape);
862
* Generates a WHERE field NOT IN('item', 'item') SQL query,863
* joined with 'AND' if appropriate.
865
* @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
869
public function whereNotIn(?string $key = null, $values = null, ?bool $escape = null)
871
return $this->_whereIn($key, $values, true, 'AND ', $escape);
875
* Generates a WHERE field NOT IN('item', 'item') SQL query,876
* joined with 'OR' if appropriate.
878
* @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
882
public function orWhereNotIn(?string $key = null, $values = null, ?bool $escape = null)
884
return $this->_whereIn($key, $values, true, 'OR ', $escape);
888
* Generates a HAVING field IN('item', 'item') SQL query,889
* joined with 'AND' if appropriate.
891
* @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
895
public function havingIn(?string $key = null, $values = null, ?bool $escape = null)
897
return $this->_whereIn($key, $values, false, 'AND ', $escape, 'QBHaving');
901
* Generates a HAVING field IN('item', 'item') SQL query,902
* joined with 'OR' if appropriate.
904
* @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
908
public function orHavingIn(?string $key = null, $values = null, ?bool $escape = null)
910
return $this->_whereIn($key, $values, false, 'OR ', $escape, 'QBHaving');
914
* Generates a HAVING field NOT IN('item', 'item') SQL query,915
* joined with 'AND' if appropriate.
917
* @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
921
public function havingNotIn(?string $key = null, $values = null, ?bool $escape = null)
923
return $this->_whereIn($key, $values, true, 'AND ', $escape, 'QBHaving');
927
* Generates a HAVING field NOT IN('item', 'item') SQL query,928
* joined with 'OR' if appropriate.
930
* @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
934
public function orHavingNotIn(?string $key = null, $values = null, ?bool $escape = null)
936
return $this->_whereIn($key, $values, true, 'OR ', $escape, 'QBHaving');
941
* @used-by orWhereIn()
942
* @used-by whereNotIn()
943
* @used-by orWhereNotIn()
945
* @param non-empty-string|null $key
946
* @param array|BaseBuilder|Closure|null $values The values searched on, or anonymous function with subquery
950
* @throws InvalidArgumentException
952
protected function _whereIn(?string $key = null, $values = null, bool $not = false, string $type = 'AND ', ?bool $escape = null, string $clause = 'QBWhere')
954
if ($key === null || $key === '') {955
throw new InvalidArgumentException(sprintf('%s() expects $key to be a non-empty string', debug_backtrace(0, 2)[1]['function']));958
if ($values === null || (! is_array($values) && ! $this->isSubquery($values))) {959
throw new InvalidArgumentException(sprintf('%s() expects $values to be of type array or closure', debug_backtrace(0, 2)[1]['function']));962
if (! is_bool($escape)) {963
$escape = $this->db->protectIdentifiers;
968
if ($escape === true) {969
$key = $this->db->protectIdentifiers($key);
972
$not = ($not) ? ' NOT' : '';
974
if ($this->isSubquery($values)) {975
$whereIn = $this->buildSubquery($values, true);
978
$whereIn = array_values($values);
981
$ok = $this->setBind($ok, $whereIn, $escape);
983
$prefix = empty($this->{$clause}) ? $this->groupGetType('') : $this->groupGetType($type);986
'condition' => "{$prefix}{$key}{$not} IN :{$ok}:",990
$this->{$clause}[] = $whereIn;996
* Generates a %LIKE% portion of the query.
997
* Separates multiple calls with 'AND'.
999
* @param array|RawSql|string $field
1003
public function like($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1005
return $this->_like($field, $match, 'AND ', $side, '', $escape, $insensitiveSearch);
1009
* Generates a NOT LIKE portion of the query.
1010
* Separates multiple calls with 'AND'.
1012
* @param array|RawSql|string $field
1016
public function notLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1018
return $this->_like($field, $match, 'AND ', $side, 'NOT', $escape, $insensitiveSearch);
1022
* Generates a %LIKE% portion of the query.
1023
* Separates multiple calls with 'OR'.
1025
* @param array|RawSql|string $field
1029
public function orLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1031
return $this->_like($field, $match, 'OR ', $side, '', $escape, $insensitiveSearch);
1035
* Generates a NOT LIKE portion of the query.
1036
* Separates multiple calls with 'OR'.
1038
* @param array|RawSql|string $field
1042
public function orNotLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1044
return $this->_like($field, $match, 'OR ', $side, 'NOT', $escape, $insensitiveSearch);
1048
* Generates a %LIKE% portion of the query.
1049
* Separates multiple calls with 'AND'.
1051
* @param array|RawSql|string $field
1055
public function havingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1057
return $this->_like($field, $match, 'AND ', $side, '', $escape, $insensitiveSearch, 'QBHaving');
1061
* Generates a NOT LIKE portion of the query.
1062
* Separates multiple calls with 'AND'.
1064
* @param array|RawSql|string $field
1068
public function notHavingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1070
return $this->_like($field, $match, 'AND ', $side, 'NOT', $escape, $insensitiveSearch, 'QBHaving');
1074
* Generates a %LIKE% portion of the query.
1075
* Separates multiple calls with 'OR'.
1077
* @param array|RawSql|string $field
1081
public function orHavingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1083
return $this->_like($field, $match, 'OR ', $side, '', $escape, $insensitiveSearch, 'QBHaving');
1087
* Generates a NOT LIKE portion of the query.
1088
* Separates multiple calls with 'OR'.
1090
* @param array|RawSql|string $field
1094
public function orNotHavingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1096
return $this->_like($field, $match, 'OR ', $side, 'NOT', $escape, $insensitiveSearch, 'QBHaving');
1102
* @used-by notLike()
1103
* @used-by orNotLike()
1104
* @used-by havingLike()
1105
* @used-by orHavingLike()
1106
* @used-by notHavingLike()
1107
* @used-by orNotHavingLike()
1109
* @param array<string, string>|RawSql|string $field
1113
protected function _like($field, string $match = '', string $type = 'AND ', string $side = 'both', string $not = '', ?bool $escape = null, bool $insensitiveSearch = false, string $clause = 'QBWhere')
1115
$escape = is_bool($escape) ? $escape : $this->db->protectIdentifiers;
1116
$side = strtolower($side);
1118
if ($field instanceof RawSql) {1119
$k = (string) $field;
1121
$insensitiveSearch = false;
1123
$prefix = empty($this->{$clause}) ? $this->groupGetType('') : $this->groupGetType($type);1125
if ($side === 'none') {1126
$bind = $this->setBind($field->getBindingKey(), $v, $escape);
1127
} elseif ($side === 'before') {1128
$bind = $this->setBind($field->getBindingKey(), "%{$v}", $escape);1129
} elseif ($side === 'after') {1130
$bind = $this->setBind($field->getBindingKey(), "{$v}%", $escape);1132
$bind = $this->setBind($field->getBindingKey(), "%{$v}%", $escape);1135
$likeStatement = $this->_like_statement($prefix, $k, $not, $bind, $insensitiveSearch);
1137
// some platforms require an escape sequence definition for LIKE wildcards
1138
if ($escape === true && $this->db->likeEscapeStr !== '') {1139
$likeStatement .= sprintf($this->db->likeEscapeStr, $this->db->likeEscapeChar);
1142
$this->{$clause}[] = [1143
'condition' => $field->with($likeStatement),
1144
'escape' => $escape,
1150
$keyValue = ! is_array($field) ? [$field => $match] : $field;
1152
foreach ($keyValue as $k => $v) {1153
if ($insensitiveSearch === true) {1154
$v = strtolower($v);
1157
$prefix = empty($this->{$clause}) ? $this->groupGetType('') : $this->groupGetType($type);1159
if ($side === 'none') {1160
$bind = $this->setBind($k, $v, $escape);
1161
} elseif ($side === 'before') {1162
$bind = $this->setBind($k, "%{$v}", $escape);1163
} elseif ($side === 'after') {1164
$bind = $this->setBind($k, "{$v}%", $escape);1166
$bind = $this->setBind($k, "%{$v}%", $escape);1169
$likeStatement = $this->_like_statement($prefix, $k, $not, $bind, $insensitiveSearch);
1171
// some platforms require an escape sequence definition for LIKE wildcards
1172
if ($escape === true && $this->db->likeEscapeStr !== '') {1173
$likeStatement .= sprintf($this->db->likeEscapeStr, $this->db->likeEscapeChar);
1176
$this->{$clause}[] = [1177
'condition' => $likeStatement,
1178
'escape' => $escape,
1186
* Platform independent LIKE statement builder.
1188
protected function _like_statement(?string $prefix, string $column, ?string $not, string $bind, bool $insensitiveSearch = false): string
1190
if ($insensitiveSearch === true) {1191
return "{$prefix} LOWER(" . $this->db->escapeIdentifiers($column) . ") {$not} LIKE :{$bind}:";1194
return "{$prefix} {$column} {$not} LIKE :{$bind}:";1198
* Add UNION statement
1200
* @param BaseBuilder|Closure $union
1204
public function union($union)
1206
return $this->addUnionStatement($union);
1210
* Add UNION ALL statement
1212
* @param BaseBuilder|Closure $union
1216
public function unionAll($union)
1218
return $this->addUnionStatement($union, true);
1223
* @used-by unionAll()
1225
* @param BaseBuilder|Closure $union
1229
protected function addUnionStatement($union, bool $all = false)
1231
$this->QBUnion[] = "\nUNION "
1232
. ($all ? 'ALL ' : '')
1234
. $this->buildSubquery($union, true, 'uwrp' . (count($this->QBUnion) + 1));
1240
* Starts a query group.
1244
public function groupStart()
1246
return $this->groupStartPrepare();
1250
* Starts a query group, but ORs the group
1254
public function orGroupStart()
1256
return $this->groupStartPrepare('', 'OR ');1260
* Starts a query group, but NOTs the group
1264
public function notGroupStart()
1266
return $this->groupStartPrepare('NOT ');1270
* Starts a query group, but OR NOTs the group
1274
public function orNotGroupStart()
1276
return $this->groupStartPrepare('NOT ', 'OR ');1280
* Ends a query group
1284
public function groupEnd()
1286
return $this->groupEndPrepare();
1290
* Starts a query group for HAVING clause.
1294
public function havingGroupStart()
1296
return $this->groupStartPrepare('', 'AND ', 'QBHaving');1300
* Starts a query group for HAVING clause, but ORs the group.
1304
public function orHavingGroupStart()
1306
return $this->groupStartPrepare('', 'OR ', 'QBHaving');1310
* Starts a query group for HAVING clause, but NOTs the group.
1314
public function notHavingGroupStart()
1316
return $this->groupStartPrepare('NOT ', 'AND ', 'QBHaving');1320
* Starts a query group for HAVING clause, but OR NOTs the group.
1324
public function orNotHavingGroupStart()
1326
return $this->groupStartPrepare('NOT ', 'OR ', 'QBHaving');1330
* Ends a query group for HAVING clause.
1334
public function havingGroupEnd()
1336
return $this->groupEndPrepare('QBHaving');1340
* Prepate a query group start.
1344
protected function groupStartPrepare(string $not = '', string $type = 'AND ', string $clause = 'QBWhere')
1346
$type = $this->groupGetType($type);
1348
$this->QBWhereGroupStarted = true;
1349
$prefix = empty($this->{$clause}) ? '' : $type;1351
'condition' => $prefix . $not . str_repeat(' ', ++$this->QBWhereGroupCount) . ' (',1355
$this->{$clause}[] = $where;1361
* Prepate a query group end.
1365
protected function groupEndPrepare(string $clause = 'QBWhere')
1367
$this->QBWhereGroupStarted = false;
1369
'condition' => str_repeat(' ', $this->QBWhereGroupCount--) . ')',1373
$this->{$clause}[] = $where;1379
* @used-by groupStart()
1381
* @used-by whereHaving()
1382
* @used-by _whereIn()
1383
* @used-by havingGroupStart()
1385
protected function groupGetType(string $type): string
1387
if ($this->QBWhereGroupStarted) {1389
$this->QBWhereGroupStarted = false;
1396
* @param array|string $by
1400
public function groupBy($by, ?bool $escape = null)
1402
if (! is_bool($escape)) {1403
$escape = $this->db->protectIdentifiers;
1406
if (is_string($by)) {1407
$by = ($escape === true) ? explode(',', $by) : [$by];1410
foreach ($by as $val) {1416
'escape' => $escape,
1419
$this->QBGroupBy[] = $val;
1427
* Separates multiple calls with 'AND'.
1429
* @param array|RawSql|string $key
1430
* @param mixed $value
1434
public function having($key, $value = null, ?bool $escape = null)
1436
return $this->whereHaving('QBHaving', $key, $value, 'AND ', $escape);1440
* Separates multiple calls with 'OR'.
1442
* @param array|RawSql|string $key
1443
* @param mixed $value
1447
public function orHaving($key, $value = null, ?bool $escape = null)
1449
return $this->whereHaving('QBHaving', $key, $value, 'OR ', $escape);1453
* @param string $direction ASC, DESC or RANDOM
1457
public function orderBy(string $orderBy, string $direction = '', ?bool $escape = null)
1459
if ($orderBy === '') {1465
$direction = strtoupper(trim($direction));
1467
if ($direction === 'RANDOM') {1469
$orderBy = ctype_digit($orderBy) ? sprintf($this->randomKeyword[1], $orderBy) : $this->randomKeyword[0];
1471
} elseif ($direction !== '') {1472
$direction = in_array($direction, ['ASC', 'DESC'], true) ? ' ' . $direction : '';
1475
if ($escape === null) {1476
$escape = $this->db->protectIdentifiers;
1479
if ($escape === false) {1481
'field' => $orderBy,
1482
'direction' => $direction,
1486
foreach (explode(',', $orderBy) as $field) {1487
$qbOrderBy[] = ($direction === '' && preg_match('/\s+(ASC|DESC)$/i', rtrim($field), $match, PREG_OFFSET_CAPTURE))1489
'field' => ltrim(substr($field, 0, $match[0][1])),
1490
'direction' => ' ' . $match[1][0],
1494
'field' => trim($field),
1495
'direction' => $direction,
1501
$this->QBOrderBy = array_merge($this->QBOrderBy, $qbOrderBy);
1509
public function limit(?int $value = null, ?int $offset = 0)
1511
$limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
1512
if ($limitZeroAsAll && $value === 0) {1516
if ($value !== null) {1517
$this->QBLimit = $value;
1520
if ($offset !== null && $offset !== 0) {1521
$this->QBOffset = $offset;
1528
* Sets the OFFSET value
1532
public function offset(int $offset)
1534
if ($offset !== 0) {1535
$this->QBOffset = $offset;
1542
* Generates a platform-specific LIMIT clause.
1544
protected function _limit(string $sql, bool $offsetIgnore = false): string
1546
return $sql . ' LIMIT ' . ($offsetIgnore === false && $this->QBOffset ? $this->QBOffset . ', ' : '') . $this->QBLimit;
1550
* Allows key/value pairs to be set for insert(), update() or replace().
1552
* @param array|object|string $key Field name, or an array of field/value pairs, or an object
1553
* @param mixed $value Field value, if $key is a single field
1554
* @param bool|null $escape Whether to escape values
1558
public function set($key, $value = '', ?bool $escape = null)
1560
$key = $this->objectToArray($key);
1562
if (! is_array($key)) {1563
$key = [$key => $value];
1566
$escape = is_bool($escape) ? $escape : $this->db->protectIdentifiers;
1568
foreach ($key as $k => $v) {1570
$bind = $this->setBind($k, $v, $escape);
1572
$this->QBSet[$this->db->protectIdentifiers($k, false)] = ":{$bind}:";1574
$this->QBSet[$this->db->protectIdentifiers($k, false)] = $v;
1582
* Returns the previously set() data, alternatively resetting it if needed.
1584
public function getSetData(bool $clean = false): array
1586
$data = $this->QBSet;
1596
* Compiles a SELECT query string and returns the sql.
1598
public function getCompiledSelect(bool $reset = true): string
1600
$select = $this->compileSelect();
1602
if ($reset === true) {1603
$this->resetSelect();
1606
return $this->compileFinalQuery($select);
1610
* Returns a finalized, compiled query string with the bindings
1611
* inserted and prefixes swapped out.
1613
protected function compileFinalQuery(string $sql): string
1615
$query = new Query($this->db);
1616
$query->setQuery($sql, $this->binds, false);
1618
if (! empty($this->db->swapPre) && ! empty($this->db->DBPrefix)) {1619
$query->swapPrefix($this->db->DBPrefix, $this->db->swapPre);
1622
return $query->getQuery();
1626
* Compiles the select statement based on the other functions called
1627
* and runs the query
1629
* @return false|ResultInterface
1631
public function get(?int $limit = null, int $offset = 0, bool $reset = true)
1633
$limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
1634
if ($limitZeroAsAll && $limit === 0) {1638
if ($limit !== null) {1639
$this->limit($limit, $offset);
1642
$result = $this->testMode
1643
? $this->getCompiledSelect($reset)
1644
: $this->db->query($this->compileSelect(), $this->binds, false);
1646
if ($reset === true) {1647
$this->resetSelect();
1649
// Clear our binds so we don't eat up memory
1657
* Generates a platform-specific query string that counts all records in
1658
* the particular table
1660
* @return int|string
1662
public function countAll(bool $reset = true)
1664
$table = $this->QBFrom[0];
1666
$sql = $this->countString . $this->db->escapeIdentifiers('numrows') . ' FROM ' .1667
$this->db->protectIdentifiers($table, true, null, false);
1669
if ($this->testMode) {1673
$query = $this->db->query($sql, null, false);
1675
if (empty($query->getResult())) {1679
$query = $query->getRow();
1681
if ($reset === true) {1682
$this->resetSelect();
1685
return (int) $query->numrows;
1689
* Generates a platform-specific query string that counts all records
1690
* returned by an Query Builder query.
1692
* @return int|string
1694
public function countAllResults(bool $reset = true)
1696
// ORDER BY usage is often problematic here (most notably
1697
// on Microsoft SQL Server) and ultimately unnecessary
1698
// for selecting COUNT(*) ...
1701
if (! empty($this->QBOrderBy)) {1702
$orderBy = $this->QBOrderBy;
1704
$this->QBOrderBy = null;
1707
// We cannot use a LIMIT when getting the single row COUNT(*) result
1708
$limit = $this->QBLimit;
1710
$this->QBLimit = false;
1712
if ($this->QBDistinct === true || ! empty($this->QBGroupBy)) {1713
// We need to backup the original SELECT in case DBPrefix is used
1714
$select = $this->QBSelect;
1715
$sql = $this->countString . $this->db->protectIdentifiers('numrows') . "\nFROM (\n" . $this->compileSelect() . "\n) CI_count_all_results";1717
// Restore SELECT part
1718
$this->QBSelect = $select;
1721
$sql = $this->compileSelect($this->countString . $this->db->protectIdentifiers('numrows'));1724
if ($this->testMode) {1728
$result = $this->db->query($sql, $this->binds, false);
1730
if ($reset === true) {1731
$this->resetSelect();
1732
} elseif (! isset($this->QBOrderBy)) {1733
$this->QBOrderBy = $orderBy;
1736
// Restore the LIMIT setting
1737
$this->QBLimit = $limit;
1739
$row = ! $result instanceof ResultInterface ? null : $result->getRow();
1745
return (int) $row->numrows;
1749
* Compiles the set conditions and returns the sql statement
1753
public function getCompiledQBWhere()
1755
return $this->QBWhere;
1759
* Allows the where clause, limit and offset to be added directly
1761
* @param array|string $where
1763
* @return ResultInterface
1765
public function getWhere($where = null, ?int $limit = null, ?int $offset = 0, bool $reset = true)
1767
if ($where !== null) {1768
$this->where($where);
1771
$limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
1772
if ($limitZeroAsAll && $limit === 0) {1776
if ($limit !== null) {1777
$this->limit($limit, $offset);
1780
$result = $this->testMode
1781
? $this->getCompiledSelect($reset)
1782
: $this->db->query($this->compileSelect(), $this->binds, false);
1784
if ($reset === true) {1785
$this->resetSelect();
1787
// Clear our binds so we don't eat up memory
1795
* Compiles batch insert/update/upsert strings and runs the queries
1797
* @param '_deleteBatch'|'_insertBatch'|'_updateBatch'|'_upsertBatch' $renderMethod
1799
* @return false|int|list<string> Number of rows inserted or FALSE on failure, SQL array when testMode
1801
* @throws DatabaseException
1803
protected function batchExecute(string $renderMethod, int $batchSize = 100)
1805
if (empty($this->QBSet)) {1806
if ($this->db->DBDebug) {1807
throw new DatabaseException(trim($renderMethod, '_') . '() has no data.');
1810
return false; // @codeCoverageIgnore
1813
$table = $this->db->protectIdentifiers($this->QBFrom[0], true, null, false);
1817
$cnt = count($this->QBSet);
1819
// batch size 0 for unlimited
1820
if ($batchSize === 0) {1824
for ($i = 0, $total = $cnt; $i < $total; $i += $batchSize) {1825
$QBSet = array_slice($this->QBSet, $i, $batchSize);
1827
$sql = $this->{$renderMethod}($table, $this->QBKeys, $QBSet);1830
return false; // @codeCoverageIgnore
1833
if ($this->testMode) {1836
$this->db->query($sql, null, false);
1837
$affectedRows += $this->db->affectedRows();
1841
if (! $this->testMode) {1842
$this->resetWrite();
1845
return $this->testMode ? $savedSQL : $affectedRows;
1849
* Allows a row or multiple rows to be set for batch inserts/upserts/updates
1851
* @param array|object $set
1852
* @param string $alias alias for sql table
1854
* @return $this|null
1856
public function setData($set, ?bool $escape = null, string $alias = '')
1859
if ($this->db->DBDebug) {1860
throw new DatabaseException('setData() has no data.');1863
return null; // @codeCoverageIgnore
1866
$this->setAlias($alias);
1868
// this allows to set just one row at a time
1869
if (is_object($set) || (! is_array(current($set)) && ! is_object(current($set)))) {1873
$set = $this->batchObjectToArray($set);
1875
$escape = is_bool($escape) ? $escape : $this->db->protectIdentifiers;
1877
$keys = array_keys($this->objectToArray(current($set)));
1880
foreach ($set as $row) {1881
$row = $this->objectToArray($row);
1882
if (array_diff($keys, array_keys($row)) !== [] || array_diff(array_keys($row), $keys) !== []) {1883
// batchExecute() function returns an error on an empty array
1884
$this->QBSet[] = [];
1889
ksort($row); // puts $row in the same order as our keys
1893
foreach ($row as $rowValue) {1894
$clean[] = $escape ? $this->db->escape($rowValue) : $rowValue;
1899
$this->QBSet[] = $row;
1902
foreach ($keys as $k) {1903
$k = $this->db->protectIdentifiers($k, false);
1905
if (! in_array($k, $this->QBKeys, true)) {1906
$this->QBKeys[] = $k;
1914
* Compiles an upsert query and returns the sql
1918
* @throws DatabaseException
1920
public function getCompiledUpsert()
1922
[$currentTestMode, $this->testMode] = [$this->testMode, true];
1924
$sql = implode(";\n", $this->upsert());1926
$this->testMode = $currentTestMode;
1928
return $this->compileFinalQuery($sql);
1932
* Converts call to batchUpsert
1934
* @param array|object|null $set
1936
* @return false|int|list<string> Number of affected rows or FALSE on failure, SQL array when testMode
1938
* @throws DatabaseException
1940
public function upsert($set = null, ?bool $escape = null)
1942
// if set() has been used merge QBSet with binds and then setData()
1943
if ($set === null && ! is_array(current($this->QBSet))) {1946
foreach ($this->QBSet as $field => $value) {1947
$k = trim($field, $this->db->escapeChar);
1948
// use binds if available else use QBSet value but with RawSql to avoid escape
1949
$set[$k] = isset($this->binds[$k]) ? $this->binds[$k][0] : new RawSql($value);
1959
$this->setData($set, true); // unescaped items are RawSql now
1960
} elseif ($set !== null) {1961
$this->setData($set, $escape);
1962
} // else setData() has already been used and we need to do nothing
1964
return $this->batchExecute('_upsertBatch');1968
* Compiles batch upsert strings and runs the queries
1970
* @param array|object|null $set a dataset
1972
* @return false|int|list<string> Number of affected rows or FALSE on failure, SQL array when testMode
1974
* @throws DatabaseException
1976
public function upsertBatch($set = null, ?bool $escape = null, int $batchSize = 100)
1978
if (isset($this->QBOptions['setQueryAsData'])) {1979
$sql = $this->_upsertBatch($this->QBFrom[0], $this->QBKeys, []);
1982
return false; // @codeCoverageIgnore
1985
if ($this->testMode === false) {1986
$this->db->query($sql, null, false);
1989
$this->resetWrite();
1991
return $this->testMode ? $sql : $this->db->affectedRows();
1994
if ($set !== null) {1995
$this->setData($set, $escape);
1998
return $this->batchExecute('_upsertBatch', $batchSize);2002
* Generates a platform-specific upsertBatch string from the supplied data
2004
* @used-by batchExecute()
2006
* @param string $table Protected table name
2007
* @param list<string> $keys QBKeys
2008
* @param list<list<int|string>> $values QBSet
2010
protected function _upsertBatch(string $table, array $keys, array $values): string
2012
$sql = $this->QBOptions['sql'] ?? '';
2014
// if this is the first iteration of batch then we need to build skeleton sql
2016
$updateFields = $this->QBOptions['updateFields'] ?? $this->updateFields($keys)->QBOptions['updateFields'] ?? [];
2018
$sql = 'INSERT INTO ' . $table . ' (' . implode(', ', $keys) . ")\n{:_table_:}ON DUPLICATE KEY UPDATE\n" . implode(2021
static fn ($key, $value) => $table . '.' . $key . ($value instanceof RawSql ?
2023
' = VALUES(' . $value . ')'),2024
array_keys($updateFields),
2029
$this->QBOptions['sql'] = $sql;
2032
if (isset($this->QBOptions['setQueryAsData'])) {2033
$data = $this->QBOptions['setQueryAsData'] . "\n";
2035
$data = 'VALUES ' . implode(', ', $this->formatValues($values)) . "\n";2038
return str_replace('{:_table_:}', $data, $sql);2042
* Set table alias for dataset pseudo table.
2044
private function setAlias(string $alias): BaseBuilder
2046
if ($alias !== '') {2047
$this->db->addTableAlias($alias);
2048
$this->QBOptions['alias'] = $this->db->protectIdentifiers($alias);
2055
* Sets update fields for upsert, update
2057
* @param list<RawSql>|list<string>|string $set
2058
* @param bool $addToDefault adds update fields to the default ones
2059
* @param array|null $ignore ignores items in set
2063
public function updateFields($set, bool $addToDefault = false, ?array $ignore = null)
2065
if (! empty($set)) {2066
if (! is_array($set)) {2067
$set = explode(',', $set);2070
foreach ($set as $key => $value) {2071
if (! ($value instanceof RawSql)) {2072
$value = $this->db->protectIdentifiers($value);
2075
if (is_numeric($key)) {2079
if ($ignore === null || ! in_array($key, $ignore, true)) {2080
if ($addToDefault) {2081
$this->QBOptions['updateFieldsAdditional'][$this->db->protectIdentifiers($key)] = $value;
2083
$this->QBOptions['updateFields'][$this->db->protectIdentifiers($key)] = $value;
2088
if ($addToDefault === false && isset($this->QBOptions['updateFieldsAdditional'], $this->QBOptions['updateFields'])) {2089
$this->QBOptions['updateFields'] = array_merge($this->QBOptions['updateFields'], $this->QBOptions['updateFieldsAdditional']);
2091
unset($this->QBOptions['updateFieldsAdditional']);
2099
* Sets constraints for batch upsert, update
2101
* @param array|RawSql|string $set a string of columns, key value pairs, or RawSql
2105
public function onConstraint($set)
2107
if (! empty($set)) {2108
if (is_string($set)) {2109
$set = explode(',', $set);2111
$set = array_map(static fn ($key) => trim($key), $set);
2114
if ($set instanceof RawSql) {2118
foreach ($set as $key => $value) {2119
if (! ($value instanceof RawSql)) {2120
$value = $this->db->protectIdentifiers($value);
2123
if (is_string($key)) {2124
$key = $this->db->protectIdentifiers($key);
2127
$this->QBOptions['constraints'][$key] = $value;
2135
* Sets data source as a query for insertBatch()/updateBatch()/upsertBatch()/deleteBatch()
2137
* @param BaseBuilder|RawSql $query
2138
* @param array|string|null $columns an array or comma delimited string of columns
2140
public function setQueryAsData($query, ?string $alias = null, $columns = null): BaseBuilder
2142
if (is_string($query)) {2143
throw new InvalidArgumentException('$query parameter must be BaseBuilder or RawSql class.');2146
if ($query instanceof BaseBuilder) {2147
$query = $query->getCompiledSelect();
2148
} elseif ($query instanceof RawSql) {2149
$query = $query->__toString();
2152
if (is_string($query)) {2153
if ($columns !== null && is_string($columns)) {2154
$columns = explode(',', $columns);2155
$columns = array_map(static fn ($key) => trim($key), $columns);
2158
$columns = (array) $columns;
2160
if ($columns === []) {2161
$columns = $this->fieldsFromQuery($query);
2164
if ($alias !== null) {2165
$this->setAlias($alias);
2168
foreach ($columns as $key => $value) {2169
$columns[$key] = $this->db->escapeChar . $value . $this->db->escapeChar;
2172
$this->QBOptions['setQueryAsData'] = $query;
2173
$this->QBKeys = $columns;
2181
* Gets column names from a select query
2183
protected function fieldsFromQuery(string $sql): array
2185
return $this->db->query('SELECT * FROM (' . $sql . ') _u_ LIMIT 1')->getFieldNames();2189
* Converts value array of array to array of strings
2191
protected function formatValues(array $values): array
2193
return array_map(static fn ($index) => '(' . implode(',', $index) . ')', $values);2197
* Compiles batch insert strings and runs the queries
2199
* @param array|object|null $set a dataset
2201
* @return false|int|list<string> Number of rows inserted or FALSE on failure, SQL array when testMode
2203
public function insertBatch($set = null, ?bool $escape = null, int $batchSize = 100)
2205
if (isset($this->QBOptions['setQueryAsData'])) {2206
$sql = $this->_insertBatch($this->QBFrom[0], $this->QBKeys, []);
2209
return false; // @codeCoverageIgnore
2212
if ($this->testMode === false) {2213
$this->db->query($sql, null, false);
2216
$this->resetWrite();
2218
return $this->testMode ? $sql : $this->db->affectedRows();
2221
if ($set !== null && $set !== []) {2222
$this->setData($set, $escape);
2225
return $this->batchExecute('_insertBatch', $batchSize);2229
* Generates a platform-specific insert string from the supplied data.
2231
* @used-by batchExecute()
2233
* @param string $table Protected table name
2234
* @param list<string> $keys QBKeys
2235
* @param list<list<int|string>> $values QBSet
2237
protected function _insertBatch(string $table, array $keys, array $values): string
2239
$sql = $this->QBOptions['sql'] ?? '';
2241
// if this is the first iteration of batch then we need to build skeleton sql
2243
$sql = 'INSERT ' . $this->compileIgnore('insert') . 'INTO ' . $table2244
. ' (' . implode(', ', $keys) . ")\n{:_table_:}";2246
$this->QBOptions['sql'] = $sql;
2249
if (isset($this->QBOptions['setQueryAsData'])) {2250
$data = $this->QBOptions['setQueryAsData'];
2252
$data = 'VALUES ' . implode(', ', $this->formatValues($values));2255
return str_replace('{:_table_:}', $data, $sql);2259
* Allows key/value pairs to be set for batch inserts
2263
* @return $this|null
2267
public function setInsertBatch($key, string $value = '', ?bool $escape = null)
2269
if (! is_array($key)) {2270
$key = [[$key => $value]];
2273
return $this->setData($key, $escape);
2277
* Compiles an insert query and returns the sql
2279
* @return bool|string
2281
* @throws DatabaseException
2283
public function getCompiledInsert(bool $reset = true)
2285
if ($this->validateInsert() === false) {2289
$sql = $this->_insert(
2290
$this->db->protectIdentifiers(
2291
$this->removeAlias($this->QBFrom[0]),
2296
array_keys($this->QBSet),
2297
array_values($this->QBSet)
2300
if ($reset === true) {2301
$this->resetWrite();
2304
return $this->compileFinalQuery($sql);
2308
* Compiles an insert string and runs the query
2310
* @param array|object|null $set
2312
* @return BaseResult|bool|Query
2314
* @throws DatabaseException
2316
public function insert($set = null, ?bool $escape = null)
2318
if ($set !== null) {2319
$this->set($set, '', $escape);
2322
if ($this->validateInsert() === false) {2326
$sql = $this->_insert(
2327
$this->db->protectIdentifiers(
2328
$this->removeAlias($this->QBFrom[0]),
2333
array_keys($this->QBSet),
2334
array_values($this->QBSet)
2337
if (! $this->testMode) {2338
$this->resetWrite();
2340
$result = $this->db->query($sql, $this->binds, false);
2342
// Clear our binds so we don't eat up memory
2352
* @internal This is a temporary solution.
2354
* @see https://github.com/codeigniter4/CodeIgniter4/pull/5376
2356
* @TODO Fix a root cause, and this method should be removed.
2358
protected function removeAlias(string $from): string
2360
if (str_contains($from, ' ')) {2361
// if the alias is written with the AS keyword, remove it
2362
$from = preg_replace('/\s+AS\s+/i', ' ', $from);2364
$parts = explode(' ', $from);2372
* This method is used by both insert() and getCompiledInsert() to
2373
* validate that the there data is actually being set and that table
2374
* has been chosen to be inserted into.
2376
* @throws DatabaseException
2378
protected function validateInsert(): bool
2380
if (empty($this->QBSet)) {2381
if ($this->db->DBDebug) {2382
throw new DatabaseException('You must use the "set" method to insert an entry.');2385
return false; // @codeCoverageIgnore
2392
* Generates a platform-specific insert string from the supplied data
2394
* @param string $table Protected table name
2395
* @param list<string> $keys Keys of QBSet
2396
* @param list<int|string> $unescapedKeys Values of QBSet
2398
protected function _insert(string $table, array $keys, array $unescapedKeys): string
2400
return 'INSERT ' . $this->compileIgnore('insert') . 'INTO ' . $table . ' (' . implode(', ', $keys) . ') VALUES (' . implode(', ', $unescapedKeys) . ')';2404
* Compiles a replace into string and runs the query
2406
* @return BaseResult|false|Query|string
2408
* @throws DatabaseException
2410
public function replace(?array $set = null)
2412
if ($set !== null) {2416
if (empty($this->QBSet)) {2417
if ($this->db->DBDebug) {2418
throw new DatabaseException('You must use the "set" method to update an entry.');2421
return false; // @codeCoverageIgnore
2424
$table = $this->QBFrom[0];
2426
$sql = $this->_replace($table, array_keys($this->QBSet), array_values($this->QBSet));
2428
$this->resetWrite();
2430
return $this->testMode ? $sql : $this->db->query($sql, $this->binds, false);
2434
* Generates a platform-specific replace string from the supplied data
2436
* @param string $table Protected table name
2437
* @param list<string> $keys Keys of QBSet
2438
* @param list<int|string> $values Values of QBSet
2440
protected function _replace(string $table, array $keys, array $values): string
2442
return 'REPLACE INTO ' . $table . ' (' . implode(', ', $keys) . ') VALUES (' . implode(', ', $values) . ')';2446
* Groups tables in FROM clauses if needed, so there is no confusion
2447
* about operator precedence.
2449
* Note: This is only used (and overridden) by MySQL and SQLSRV.
2451
protected function _fromTables(): string
2453
return implode(', ', $this->QBFrom);2457
* Compiles an update query and returns the sql
2459
* @return bool|string
2461
public function getCompiledUpdate(bool $reset = true)
2463
if ($this->validateUpdate() === false) {2467
$sql = $this->_update($this->QBFrom[0], $this->QBSet);
2469
if ($reset === true) {2470
$this->resetWrite();
2473
return $this->compileFinalQuery($sql);
2477
* Compiles an update string and runs the query.
2479
* @param array|object|null $set
2480
* @param array|RawSql|string|null $where
2482
* @throws DatabaseException
2484
public function update($set = null, $where = null, ?int $limit = null): bool
2486
if ($set !== null) {2490
if ($this->validateUpdate() === false) {2494
if ($where !== null) {2495
$this->where($where);
2498
$limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
2499
if ($limitZeroAsAll && $limit === 0) {2503
if ($limit !== null) {2504
if (! $this->canLimitWhereUpdates) {2505
throw new DatabaseException('This driver does not allow LIMITs on UPDATE queries using WHERE.');2508
$this->limit($limit);
2511
$sql = $this->_update($this->QBFrom[0], $this->QBSet);
2513
if (! $this->testMode) {2514
$this->resetWrite();
2516
$result = $this->db->query($sql, $this->binds, false);
2518
if ($result !== false) {2519
// Clear our binds so we don't eat up memory
2532
* Generates a platform-specific update string from the supplied data
2534
* @param string $table Protected table name
2535
* @param array<string, string> $values QBSet
2537
protected function _update(string $table, array $values): string
2541
foreach ($values as $key => $val) {2542
$valStr[] = $key . ' = ' . $val;
2545
$limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
2546
if ($limitZeroAsAll) {2547
return 'UPDATE ' . $this->compileIgnore('update') . $table . ' SET ' . implode(', ', $valStr)2548
. $this->compileWhereHaving('QBWhere')2549
. $this->compileOrderBy()
2550
. ($this->QBLimit ? $this->_limit(' ', true) : '');2553
return 'UPDATE ' . $this->compileIgnore('update') . $table . ' SET ' . implode(', ', $valStr)2554
. $this->compileWhereHaving('QBWhere')2555
. $this->compileOrderBy()
2556
. ($this->QBLimit !== false ? $this->_limit(' ', true) : '');2560
* This method is used by both update() and getCompiledUpdate() to
2561
* validate that data is actually being set and that a table has been
2562
* chosen to be updated.
2564
* @throws DatabaseException
2566
protected function validateUpdate(): bool
2568
if (empty($this->QBSet)) {2569
if ($this->db->DBDebug) {2570
throw new DatabaseException('You must use the "set" method to update an entry.');2573
return false; // @codeCoverageIgnore
2580
* Sets data and calls batchExecute to run queries
2582
* @param array|object|null $set a dataset
2583
* @param array|RawSql|string|null $constraints
2585
* @return false|int|list<string> Number of rows affected or FALSE on failure, SQL array when testMode
2587
public function updateBatch($set = null, $constraints = null, int $batchSize = 100)
2589
$this->onConstraint($constraints);
2591
if (isset($this->QBOptions['setQueryAsData'])) {2592
$sql = $this->_updateBatch($this->QBFrom[0], $this->QBKeys, []);
2595
return false; // @codeCoverageIgnore
2598
if ($this->testMode === false) {2599
$this->db->query($sql, null, false);
2602
$this->resetWrite();
2604
return $this->testMode ? $sql : $this->db->affectedRows();
2607
if ($set !== null && $set !== []) {2608
$this->setData($set, true);
2611
return $this->batchExecute('_updateBatch', $batchSize);2615
* Generates a platform-specific batch update string from the supplied data
2617
* @used-by batchExecute()
2619
* @param string $table Protected table name
2620
* @param list<string> $keys QBKeys
2621
* @param list<list<int|string>> $values QBSet
2623
protected function _updateBatch(string $table, array $keys, array $values): string
2625
$sql = $this->QBOptions['sql'] ?? '';
2627
// if this is the first iteration of batch then we need to build skeleton sql
2629
$constraints = $this->QBOptions['constraints'] ?? [];
2631
if ($constraints === []) {2632
if ($this->db->DBDebug) {2633
throw new DatabaseException('You must specify a constraint to match on for batch updates.'); // @codeCoverageIgnore2636
return ''; // @codeCoverageIgnore
2639
$updateFields = $this->QBOptions['updateFields'] ??
2640
$this->updateFields($keys, false, $constraints)->QBOptions['updateFields'] ??
2643
$alias = $this->QBOptions['alias'] ?? '_u';
2645
$sql = 'UPDATE ' . $this->compileIgnore('update') . $table . "\n";2652
static fn ($key, $value) => $key . ($value instanceof RawSql ?
2654
' = ' . $alias . '.' . $value),
2655
array_keys($updateFields),
2660
$sql .= "FROM (\n{:_table_:}";2662
$sql .= ') ' . $alias . "\n";
2664
$sql .= 'WHERE ' . implode(
2667
static fn ($key, $value) => (
2668
($value instanceof RawSql && is_string($key))
2670
$table . '.' . $key . ' = ' . $value
2673
$value instanceof RawSql
2677
$table . '.' . $value . ' = ' . $alias . '.' . $value
2680
array_keys($constraints),
2685
$this->QBOptions['sql'] = $sql;
2688
if (isset($this->QBOptions['setQueryAsData'])) {2689
$data = $this->QBOptions['setQueryAsData'];
2694
static fn ($value) => 'SELECT ' . implode(', ', array_map(2695
static fn ($key, $index) => $index . ' ' . $key,
2704
return str_replace('{:_table_:}', $data, $sql);2708
* Allows key/value pairs to be set for batch updating
2710
* @param array|object $key
2714
* @throws DatabaseException
2718
public function setUpdateBatch($key, string $index = '', ?bool $escape = null)
2720
if ($index !== '') {2721
$this->onConstraint($index);
2724
$this->setData($key, $escape);
2730
* Compiles a delete string and runs "DELETE FROM table"
2732
* @return bool|string TRUE on success, FALSE on failure, string on testMode
2734
public function emptyTable()
2736
$table = $this->QBFrom[0];
2738
$sql = $this->_delete($table);
2740
if ($this->testMode) {2744
$this->resetWrite();
2746
return $this->db->query($sql, null, false);
2750
* Compiles a truncate string and runs the query
2751
* If the database does not support the truncate() command
2752
* This function maps to "DELETE FROM table"
2754
* @return bool|string TRUE on success, FALSE on failure, string on testMode
2756
public function truncate()
2758
$table = $this->QBFrom[0];
2760
$sql = $this->_truncate($table);
2762
if ($this->testMode) {2766
$this->resetWrite();
2768
return $this->db->query($sql, null, false);
2772
* Generates a platform-specific truncate string from the supplied data
2774
* If the database does not support the truncate() command,
2775
* then this method maps to 'DELETE FROM table'
2777
* @param string $table Protected table name
2779
protected function _truncate(string $table): string
2781
return 'TRUNCATE ' . $table;
2785
* Compiles a delete query string and returns the sql
2787
public function getCompiledDelete(bool $reset = true): string
2789
$sql = $this->testMode()->delete('', null, $reset);2790
$this->testMode(false);
2792
return $this->compileFinalQuery($sql);
2796
* Compiles a delete string and runs the query
2798
* @param array|RawSql|string $where
2800
* @return bool|string Returns a SQL string if in test mode.
2802
* @throws DatabaseException
2804
public function delete($where = '', ?int $limit = null, bool $resetData = true)
2806
$table = $this->db->protectIdentifiers($this->QBFrom[0], true, null, false);
2808
if ($where !== '') {2809
$this->where($where);
2812
if (empty($this->QBWhere)) {2813
if ($this->db->DBDebug) {2814
throw new DatabaseException('Deletes are not allowed unless they contain a "where" or "like" clause.');2817
return false; // @codeCoverageIgnore
2820
$sql = $this->_delete($this->removeAlias($table));
2822
$limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
2823
if ($limitZeroAsAll && $limit === 0) {2827
if ($limit !== null) {2828
$this->QBLimit = $limit;
2831
if (! empty($this->QBLimit)) {2832
if (! $this->canLimitDeletes) {2833
throw new DatabaseException('SQLite3 does not allow LIMITs on DELETE queries.');2836
$sql = $this->_limit($sql, true);
2840
$this->resetWrite();
2843
return $this->testMode ? $sql : $this->db->query($sql, $this->binds, false);
2847
* Sets data and calls batchExecute to run queries
2849
* @param array|object|null $set a dataset
2850
* @param array|RawSql|null $constraints
2852
* @return false|int|list<string> Number of rows affected or FALSE on failure, SQL array when testMode
2854
public function deleteBatch($set = null, $constraints = null, int $batchSize = 100)
2856
$this->onConstraint($constraints);
2858
if (isset($this->QBOptions['setQueryAsData'])) {2859
$sql = $this->_deleteBatch($this->QBFrom[0], $this->QBKeys, []);
2862
return false; // @codeCoverageIgnore
2865
if ($this->testMode === false) {2866
$this->db->query($sql, null, false);
2869
$this->resetWrite();
2871
return $this->testMode ? $sql : $this->db->affectedRows();
2874
if ($set !== null && $set !== []) {2875
$this->setData($set, true);
2878
return $this->batchExecute('_deleteBatch', $batchSize);2882
* Generates a platform-specific batch update string from the supplied data
2884
* @used-by batchExecute()
2886
* @param string $table Protected table name
2887
* @param list<string> $keys QBKeys
2888
* @param list<int|string> $values QBSet
2890
protected function _deleteBatch(string $table, array $keys, array $values): string
2892
$sql = $this->QBOptions['sql'] ?? '';
2894
// if this is the first iteration of batch then we need to build skeleton sql
2896
$constraints = $this->QBOptions['constraints'] ?? [];
2898
if ($constraints === []) {2899
if ($this->db->DBDebug) {2900
throw new DatabaseException('You must specify a constraint to match on for batch deletes.'); // @codeCoverageIgnore2903
return ''; // @codeCoverageIgnore
2906
$alias = $this->QBOptions['alias'] ?? '_u';
2908
$sql = 'DELETE ' . $table . ' FROM ' . $table . "\n";
2910
$sql .= "INNER JOIN (\n{:_table_:}";2912
$sql .= ') ' . $alias . "\n";
2914
$sql .= 'ON ' . implode(
2917
static fn ($key, $value) => (
2918
$value instanceof RawSql ?
2922
$table . '.' . $key . ' = ' . $alias . '.' . $value :
2923
$table . '.' . $value . ' = ' . $alias . '.' . $value
2926
array_keys($constraints),
2931
// convert binds in where
2932
foreach ($this->QBWhere as $key => $where) {2933
foreach ($this->binds as $field => $bind) {2934
$this->QBWhere[$key]['condition'] = str_replace(':' . $field . ':', $bind[0], $where['condition']);2938
$sql .= ' ' . $this->compileWhereHaving('QBWhere');2940
$this->QBOptions['sql'] = trim($sql);
2943
if (isset($this->QBOptions['setQueryAsData'])) {2944
$data = $this->QBOptions['setQueryAsData'];
2949
static fn ($value) => 'SELECT ' . implode(', ', array_map(2950
static fn ($key, $index) => $index . ' ' . $key,
2959
return str_replace('{:_table_:}', $data, $sql);2963
* Increments a numeric column by the specified value.
2967
public function increment(string $column, int $value = 1)
2969
$column = $this->db->protectIdentifiers($column);
2971
$sql = $this->_update($this->QBFrom[0], [$column => "{$column} + {$value}"]);2973
if (! $this->testMode) {2974
$this->resetWrite();
2976
return $this->db->query($sql, $this->binds, false);
2983
* Decrements a numeric column by the specified value.
2987
public function decrement(string $column, int $value = 1)
2989
$column = $this->db->protectIdentifiers($column);
2991
$sql = $this->_update($this->QBFrom[0], [$column => "{$column}-{$value}"]);2993
if (! $this->testMode) {2994
$this->resetWrite();
2996
return $this->db->query($sql, $this->binds, false);
3003
* Generates a platform-specific delete string from the supplied data
3005
* @param string $table Protected table name
3007
protected function _delete(string $table): string
3009
return 'DELETE ' . $this->compileIgnore('delete') . 'FROM ' . $table . $this->compileWhereHaving('QBWhere');3013
* Used to track SQL statements written with aliased tables.
3015
* @param array|string $table The table to inspect
3017
* @return string|void
3019
protected function trackAliases($table)
3021
if (is_array($table)) {3022
foreach ($table as $t) {3023
$this->trackAliases($t);
3029
// Does the string contain a comma? If so, we need to separate
3030
// the string into discreet statements
3031
if (str_contains($table, ',')) {3032
return $this->trackAliases(explode(',', $table));3035
// if a table alias is used we can recognize it by a space
3036
if (str_contains($table, ' ')) {3037
// if the alias is written with the AS keyword, remove it
3038
$table = preg_replace('/\s+AS\s+/i', ' ', $table);3041
$table = trim(strrchr($table, ' '));
3043
// Store the alias, if it doesn't already exist
3044
$this->db->addTableAlias($table);
3049
* Compile the SELECT statement
3051
* Generates a query string based on which functions were used.
3052
* Should not be called directly.
3054
* @param mixed $selectOverride
3056
protected function compileSelect($selectOverride = false): string
3058
if ($selectOverride !== false) {3059
$sql = $selectOverride;
3061
$sql = (! $this->QBDistinct) ? 'SELECT ' : 'SELECT DISTINCT ';
3063
if (empty($this->QBSelect)) {3066
// Cycle through the "select" portion of the query and prep each column name.
3067
// The reason we protect identifiers here rather than in the select() function
3068
// is because until the user calls the from() function we don't know if there are aliases
3069
foreach ($this->QBSelect as $key => $val) {3070
if ($val instanceof RawSql) {3071
$this->QBSelect[$key] = (string) $val;
3073
$protect = $this->QBNoEscape[$key] ?? null;
3074
$this->QBSelect[$key] = $this->db->protectIdentifiers($val, false, $protect);
3078
$sql .= implode(', ', $this->QBSelect);3082
if (! empty($this->QBFrom)) {3083
$sql .= "\nFROM " . $this->_fromTables();
3086
if (! empty($this->QBJoin)) {3087
$sql .= "\n" . implode("\n", $this->QBJoin);3090
$sql .= $this->compileWhereHaving('QBWhere')3091
. $this->compileGroupBy()
3092
. $this->compileWhereHaving('QBHaving')3093
. $this->compileOrderBy();
3095
$limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
3096
if ($limitZeroAsAll) {3097
if ($this->QBLimit) {3098
$sql = $this->_limit($sql . "\n");
3100
} elseif ($this->QBLimit !== false || $this->QBOffset) {3101
$sql = $this->_limit($sql . "\n");
3104
return $this->unionInjection($sql);
3108
* Checks if the ignore option is supported by
3109
* the Database Driver for the specific statement.
3113
protected function compileIgnore(string $statement)
3115
if ($this->QBIgnore && isset($this->supportedIgnoreStatements[$statement])) {3116
return trim($this->supportedIgnoreStatements[$statement]) . ' ';
3123
* Escapes identifiers in WHERE and HAVING statements at execution time.
3125
* Required so that aliases are tracked properly, regardless of whether
3126
* where(), orWhere(), having(), orHaving are called prior to from(),
3127
* join() and prefixTable is added only if needed.
3129
* @param string $qbKey 'QBWhere' or 'QBHaving'
3131
* @return string SQL statement
3133
protected function compileWhereHaving(string $qbKey): string
3135
if (! empty($this->{$qbKey})) {3136
foreach ($this->{$qbKey} as &$qbkey) {3137
// Is this condition already compiled?
3138
if (is_string($qbkey)) {3142
if ($qbkey instanceof RawSql) {3146
if ($qbkey['condition'] instanceof RawSql) {3147
$qbkey = $qbkey['condition'];
3152
if ($qbkey['escape'] === false) {3153
$qbkey = $qbkey['condition'];
3158
// Split multiple conditions
3159
$conditions = preg_split(
3160
'/((?:^|\s+)AND\s+|(?:^|\s+)OR\s+)/i',
3161
$qbkey['condition'],
3163
PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY
3166
foreach ($conditions as &$condition) {3167
$op = $this->getOperator($condition);
3171
'/^(\(?)(.*)(' . preg_quote($op, '/') . ')\s*(.*(?<!\)))?(\)?)$/i',3179
// 0 => '(test <= foo)', /* the whole thing */
3180
// 1 => '(', /* optional */3181
// 2 => 'test', /* the field name */
3182
// 3 => ' <= ', /* $op */
3183
// 4 => 'foo', /* optional, if $op is e.g. 'IS NULL' */
3184
// 5 => ')' /* optional */
3187
if (isset($matches[4]) && $matches[4] !== '') {3188
$protectIdentifiers = false;
3189
if (str_contains($matches[4], '.')) {3190
$protectIdentifiers = true;
3193
if (! str_contains($matches[4], ':')) {3194
$matches[4] = $this->db->protectIdentifiers(trim($matches[4]), false, $protectIdentifiers);
3197
$matches[4] = ' ' . $matches[4];
3200
$condition = $matches[1] . $this->db->protectIdentifiers(trim($matches[2]))
3201
. ' ' . trim($matches[3]) . $matches[4] . $matches[5];
3204
$qbkey = implode('', $conditions);3207
return ($qbKey === 'QBHaving' ? "\nHAVING " : "\nWHERE ")
3208
. implode("\n", $this->{$qbKey});3215
* Escapes identifiers in GROUP BY statements at execution time.
3217
* Required so that aliases are tracked properly, regardless of whether
3218
* groupBy() is called prior to from(), join() and prefixTable is added
3221
protected function compileGroupBy(): string
3223
if (! empty($this->QBGroupBy)) {3224
foreach ($this->QBGroupBy as &$groupBy) {3225
// Is it already compiled?
3226
if (is_string($groupBy)) {3230
$groupBy = ($groupBy['escape'] === false || $this->isLiteral($groupBy['field']))
3232
: $this->db->protectIdentifiers($groupBy['field']);
3235
return "\nGROUP BY " . implode(', ', $this->QBGroupBy);3242
* Escapes identifiers in ORDER BY statements at execution time.
3244
* Required so that aliases are tracked properly, regardless of whether
3245
* orderBy() is called prior to from(), join() and prefixTable is added
3248
protected function compileOrderBy(): string
3250
if (is_array($this->QBOrderBy) && $this->QBOrderBy !== []) {3251
foreach ($this->QBOrderBy as &$orderBy) {3252
if ($orderBy['escape'] !== false && ! $this->isLiteral($orderBy['field'])) {3253
$orderBy['field'] = $this->db->protectIdentifiers($orderBy['field']);
3256
$orderBy = $orderBy['field'] . $orderBy['direction'];
3259
return $this->QBOrderBy = "\nORDER BY " . implode(', ', $this->QBOrderBy);3262
if (is_string($this->QBOrderBy)) {3263
return $this->QBOrderBy;
3269
protected function unionInjection(string $sql): string
3271
if ($this->QBUnion === []) {3275
return 'SELECT * FROM (' . $sql . ') '3276
. ($this->db->protectIdentifiers ? $this->db->escapeIdentifiers('uwrp0') : 'uwrp0')3277
. implode("\n", $this->QBUnion);3281
* Takes an object as input and converts the class variables to array key/vals
3283
* @param array|object $object
3287
protected function objectToArray($object)
3289
if (! is_object($object)) {3293
if ($object instanceof RawSql) {3294
throw new InvalidArgumentException('RawSql "' . $object . '" cannot be used here.');3299
foreach (get_object_vars($object) as $key => $val) {3300
if ((! is_object($val) || $val instanceof RawSql) && ! is_array($val)) {3301
$array[$key] = $val;
3309
* Takes an object as input and converts the class variables to array key/vals
3311
* @param array|object $object
3315
protected function batchObjectToArray($object)
3317
if (! is_object($object)) {3322
$out = get_object_vars($object);
3323
$fields = array_keys($out);
3325
foreach ($fields as $val) {3328
foreach ($out[$val] as $data) {3329
$array[$i++][$val] = $data;
3337
* Determines if a string represents a literal value or a field name
3339
protected function isLiteral(string $str): bool
3344
|| ctype_digit($str)
3345
|| (string) (float) $str === $str
3346
|| in_array(strtoupper($str), ['TRUE', 'FALSE'], true)
3351
if ($this->isLiteralStr === []) {3352
$this->isLiteralStr = $this->db->escapeChar !== '"' ? ['"', "'"] : ["'"];
3355
return in_array($str[0], $this->isLiteralStr, true);
3359
* Publicly-visible method to reset the QB values.
3363
public function resetQuery()
3365
$this->resetSelect();
3366
$this->resetWrite();
3372
* Resets the query builder values. Called by the get() function
3374
* @param array $qbResetItems An array of fields to reset
3376
protected function resetRun(array $qbResetItems)
3378
foreach ($qbResetItems as $item => $defaultValue) {3379
$this->{$item} = $defaultValue;3384
* Resets the query builder values. Called by the get() function
3386
protected function resetSelect()
3396
'QBDistinct' => false,
3398
'QBOffset' => false,
3402
if (! empty($this->db)) {3403
$this->db->setAliasedTables([]);
3406
// Reset QBFrom part
3407
if (! empty($this->QBFrom)) {3408
$this->from(array_shift($this->QBFrom), true);
3413
* Resets the query builder "write" values.
3415
* Called by the insert() update() insertBatch() updateBatch() and delete() functions
3417
protected function resetWrite()
3426
'QBIgnore' => false,
3432
* Tests whether the string has an SQL operator
3434
protected function hasOperator(string $str): bool
3437
'/(<|>|!|=|\sIS NULL|\sIS NOT NULL|\sEXISTS|\sBETWEEN|\sLIKE|\sIN\s*\(|\s)/i',
3443
* Returns the SQL string operator
3445
* @return array|false|string
3447
protected function getOperator(string $str, bool $list = false)
3449
if ($this->pregOperators === []) {3450
$_les = $this->db->likeEscapeStr !== ''
3451
? '\s+' . preg_quote(trim(sprintf($this->db->likeEscapeStr, $this->db->likeEscapeChar)), '/')
3453
$this->pregOperators = [
3454
'\s*(?:<|>|!)?=\s*', // =, <=, >=, !=
3455
'\s*<>?\s*', // <, <>
3457
'\s+IS NULL', // IS NULL
3458
'\s+IS NOT NULL', // IS NOT NULL
3459
'\s+EXISTS\s*\(.*\)', // EXISTS (sql)
3460
'\s+NOT EXISTS\s*\(.*\)', // NOT EXISTS(sql)
3461
'\s+BETWEEN\s+', // BETWEEN value AND value
3462
'\s+IN\s*\(.*\)', // IN (list)
3463
'\s+NOT IN\s*\(.*\)', // NOT IN (list)
3464
'\s+LIKE\s+\S.*(' . $_les . ')?', // LIKE 'expr'[ ESCAPE '%s']3465
'\s+NOT LIKE\s+\S.*(' . $_les . ')?', // NOT LIKE 'expr'[ ESCAPE '%s']3469
return preg_match_all(
3470
'/' . implode('|', $this->pregOperators) . '/i',3473
) ? ($list ? $match[0] : $match[0][0]) : false;
3477
* Returns the SQL string operator from where key
3479
* @return false|list<string>
3481
private function getOperatorFromWhereKey(string $whereKey)
3483
$whereKey = trim($whereKey);
3486
'\s*(?:<|>|!)?=', // =, <=, >=, !=
3489
'\s+IS NULL', // IS NULL
3490
'\s+IS NOT NULL', // IS NOT NULL
3491
'\s+EXISTS\s*\(.*\)', // EXISTS (sql)
3492
'\s+NOT EXISTS\s*\(.*\)', // NOT EXISTS (sql)
3493
'\s+BETWEEN\s+', // BETWEEN value AND value
3494
'\s+IN\s*\(.*\)', // IN (list)
3495
'\s+NOT IN\s*\(.*\)', // NOT IN (list)
3497
'\s+NOT LIKE', // NOT LIKE
3500
return preg_match_all(
3501
'/' . implode('|', $pregOperators) . '/i',3504
) ? $match[0] : false;
3508
* Stores a bind value after ensuring that it's unique.
3509
* While it might be nicer to have named keys for our binds array
3510
* with PHP 7+ we get a huge memory/performance gain with indexed
3511
* arrays instead, so lets take advantage of that here.
3513
* @param mixed $value
3515
protected function setBind(string $key, $value = null, bool $escape = true): string
3517
if (! array_key_exists($key, $this->binds)) {3518
$this->binds[$key] = [
3526
if (! array_key_exists($key, $this->bindsKeyCount)) {3527
$this->bindsKeyCount[$key] = 1;
3530
$count = $this->bindsKeyCount[$key]++;
3532
$this->binds[$key . '.' . $count] = [
3537
return $key . '.' . $count;
3541
* Returns a clone of a Base Builder with reset query builder values.
3547
protected function cleanClone()
3549
return (clone $this)->from([], true)->resetQuery();
3553
* @param mixed $value
3555
protected function isSubquery($value): bool
3557
return $value instanceof BaseBuilder || $value instanceof Closure;
3561
* @param BaseBuilder|Closure $builder
3562
* @param bool $wrapped Wrap the subquery in brackets
3563
* @param string $alias Subquery alias
3565
protected function buildSubquery($builder, bool $wrapped = false, string $alias = ''): string
3567
if ($builder instanceof Closure) {3568
$builder($builder = $this->db->newQuery());
3571
if ($builder === $this) {3572
throw new DatabaseException('The subquery cannot be the same object as the main query object.');3575
$subquery = strtr($builder->getCompiledSelect(false), "\n", ' ');
3578
$subquery = '(' . $subquery . ')';3579
$alias = trim($alias);
3581
if ($alias !== '') {3582
$subquery .= ' ' . ($this->db->protectIdentifiers ? $this->db->escapeIdentifiers($alias) : $alias);