ci4

Форк
0
/
BaseBuilder.php 
3588 строк · 99.9 Кб
1
<?php
2

3
declare(strict_types=1);
4

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

14
namespace CodeIgniter\Database;
15

16
use Closure;
17
use CodeIgniter\Database\Exceptions\DatabaseException;
18
use CodeIgniter\Database\Exceptions\DataException;
19
use CodeIgniter\Traits\ConditionalTrait;
20
use Config\Feature;
21
use InvalidArgumentException;
22

23
/**
24
 * Class BaseBuilder
25
 *
26
 * Provides the core Query Builder methods.
27
 * Database-specific Builders might need to override
28
 * certain methods to make them work.
29
 */
30
class BaseBuilder
31
{
32
    use ConditionalTrait;
33

34
    /**
35
     * Reset DELETE data flag
36
     *
37
     * @var bool
38
     */
39
    protected $resetDeleteData = false;
40

41
    /**
42
     * QB SELECT data
43
     *
44
     * @var array
45
     */
46
    protected $QBSelect = [];
47

48
    /**
49
     * QB DISTINCT flag
50
     *
51
     * @var bool
52
     */
53
    protected $QBDistinct = false;
54

55
    /**
56
     * QB FROM data
57
     *
58
     * @var array
59
     */
60
    protected $QBFrom = [];
61

62
    /**
63
     * QB JOIN data
64
     *
65
     * @var array
66
     */
67
    protected $QBJoin = [];
68

69
    /**
70
     * QB WHERE data
71
     *
72
     * @var array
73
     */
74
    protected $QBWhere = [];
75

76
    /**
77
     * QB GROUP BY data
78
     *
79
     * @var array
80
     */
81
    public $QBGroupBy = [];
82

83
    /**
84
     * QB HAVING data
85
     *
86
     * @var array
87
     */
88
    protected $QBHaving = [];
89

90
    /**
91
     * QB keys
92
     * list of column names.
93
     *
94
     * @var list<string>
95
     */
96
    protected $QBKeys = [];
97

98
    /**
99
     * QB LIMIT data
100
     *
101
     * @var bool|int
102
     */
103
    protected $QBLimit = false;
104

105
    /**
106
     * QB OFFSET data
107
     *
108
     * @var bool|int
109
     */
110
    protected $QBOffset = false;
111

112
    /**
113
     * QB ORDER BY data
114
     *
115
     * @var array|string|null
116
     */
117
    public $QBOrderBy = [];
118

119
    /**
120
     * QB UNION data
121
     *
122
     * @var list<string>
123
     */
124
    protected array $QBUnion = [];
125

126
    /**
127
     * Whether to protect identifiers in SELECT
128
     *
129
     * @var list<bool|null> true=protect, false=not protect
130
     */
131
    public $QBNoEscape = [];
132

133
    /**
134
     * QB data sets
135
     *
136
     * @var array<string, string>|list<list<int|string>>
137
     */
138
    protected $QBSet = [];
139

140
    /**
141
     * QB WHERE group started flag
142
     *
143
     * @var bool
144
     */
145
    protected $QBWhereGroupStarted = false;
146

147
    /**
148
     * QB WHERE group count
149
     *
150
     * @var int
151
     */
152
    protected $QBWhereGroupCount = 0;
153

154
    /**
155
     * Ignore data that cause certain
156
     * exceptions, for example in case of
157
     * duplicate keys.
158
     *
159
     * @var bool
160
     */
161
    protected $QBIgnore = false;
162

163
    /**
164
     * QB Options data
165
     * Holds additional options and data used to render SQL
166
     * and is reset by resetWrite()
167
     *
168
     * @var array{
169
     *   updateFieldsAdditional?: array,
170
     *   tableIdentity?: string,
171
     *   updateFields?: array,
172
     *   constraints?: array,
173
     *   setQueryAsData?: string,
174
     *   sql?: string,
175
     *   alias?: string,
176
     *   fieldTypes?: array<string, array<string, string>>
177
     * }
178
     *
179
     * fieldTypes: [ProtectedTableName => [FieldName => Type]]
180
     */
181
    protected $QBOptions;
182

183
    /**
184
     * A reference to the database connection.
185
     *
186
     * @var BaseConnection
187
     */
188
    protected $db;
189

190
    /**
191
     * Name of the primary table for this instance.
192
     * Tracked separately because $QBFrom gets escaped
193
     * and prefixed.
194
     *
195
     * When $tableName to the constructor has multiple tables,
196
     * the value is empty string.
197
     *
198
     * @var string
199
     */
200
    protected $tableName;
201

202
    /**
203
     * ORDER BY random keyword
204
     *
205
     * @var array
206
     */
207
    protected $randomKeyword = [
208
        'RAND()',
209
        'RAND(%d)',
210
    ];
211

212
    /**
213
     * COUNT string
214
     *
215
     * @used-by CI_DB_driver::count_all()
216
     * @used-by BaseBuilder::count_all_results()
217
     *
218
     * @var string
219
     */
220
    protected $countString = 'SELECT COUNT(*) AS ';
221

222
    /**
223
     * Collects the named parameters and
224
     * their values for later binding
225
     * in the Query object.
226
     *
227
     * @var array
228
     */
229
    protected $binds = [];
230

231
    /**
232
     * Collects the key count for named parameters
233
     * in the Query object.
234
     *
235
     * @var array
236
     */
237
    protected $bindsKeyCount = [];
238

239
    /**
240
     * Some databases, like SQLite, do not by default
241
     * allow limiting of delete clauses.
242
     *
243
     * @var bool
244
     */
245
    protected $canLimitDeletes = true;
246

247
    /**
248
     * Some databases do not by default
249
     * allow limit update queries with WHERE.
250
     *
251
     * @var bool
252
     */
253
    protected $canLimitWhereUpdates = true;
254

255
    /**
256
     * Specifies which sql statements
257
     * support the ignore option.
258
     *
259
     * @var array
260
     */
261
    protected $supportedIgnoreStatements = [];
262

263
    /**
264
     * Builder testing mode status.
265
     *
266
     * @var bool
267
     */
268
    protected $testMode = false;
269

270
    /**
271
     * Tables relation types
272
     *
273
     * @var array
274
     */
275
    protected $joinTypes = [
276
        'LEFT',
277
        'RIGHT',
278
        'OUTER',
279
        'INNER',
280
        'LEFT OUTER',
281
        'RIGHT OUTER',
282
    ];
283

284
    /**
285
     * Strings that determine if a string represents a literal value or a field name
286
     *
287
     * @var list<string>
288
     */
289
    protected $isLiteralStr = [];
290

291
    /**
292
     * RegExp used to get operators
293
     *
294
     * @var list<string>
295
     */
296
    protected $pregOperators = [];
297

298
    /**
299
     * Constructor
300
     *
301
     * @param array|string $tableName tablename or tablenames with or without aliases
302
     *
303
     * Examples of $tableName: `mytable`, `jobs j`, `jobs j, users u`, `['jobs j','users u']`
304
     *
305
     * @throws DatabaseException
306
     */
307
    public function __construct($tableName, ConnectionInterface $db, ?array $options = null)
308
    {
309
        if (empty($tableName)) {
310
            throw new DatabaseException('A table must be specified when creating a new Query Builder.');
311
        }
312

313
        /**
314
         * @var BaseConnection $db
315
         */
316
        $this->db = $db;
317

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
321
        } else {
322
            $this->tableName = '';
323
        }
324

325
        $this->from($tableName);
326

327
        if ($options !== null && $options !== []) {
328
            foreach ($options as $key => $value) {
329
                if (property_exists($this, $key)) {
330
                    $this->{$key} = $value;
331
                }
332
            }
333
        }
334
    }
335

336
    /**
337
     * Returns the current database connection
338
     *
339
     * @return BaseConnection
340
     */
341
    public function db(): ConnectionInterface
342
    {
343
        return $this->db;
344
    }
345

346
    /**
347
     * Sets a test mode status.
348
     *
349
     * @return $this
350
     */
351
    public function testMode(bool $mode = true)
352
    {
353
        $this->testMode = $mode;
354

355
        return $this;
356
    }
357

358
    /**
359
     * Gets the name of the primary table.
360
     */
361
    public function getTable(): string
362
    {
363
        return $this->tableName;
364
    }
365

366
    /**
367
     * Returns an array of bind values and their
368
     * named parameters for binding in the Query object later.
369
     */
370
    public function getBinds(): array
371
    {
372
        return $this->binds;
373
    }
374

375
    /**
376
     * Ignore
377
     *
378
     * Set ignore Flag for next insert,
379
     * update or delete query.
380
     *
381
     * @return $this
382
     */
383
    public function ignore(bool $ignore = true)
384
    {
385
        $this->QBIgnore = $ignore;
386

387
        return $this;
388
    }
389

390
    /**
391
     * Generates the SELECT portion of the query
392
     *
393
     * @param list<RawSql|string>|RawSql|string $select
394
     * @param bool|null                         $escape Whether to protect identifiers
395
     *
396
     * @return $this
397
     */
398
    public function select($select = '*', ?bool $escape = null)
399
    {
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;
403
        }
404

405
        if ($select instanceof RawSql) {
406
            $select = [$select];
407
        }
408

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

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

418
                continue;
419
            }
420

421
            $val = trim($val);
422

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

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

436
                    continue;
437
                }
438

439
                $this->QBNoEscape[] = $escape;
440
            }
441
        }
442

443
        return $this;
444
    }
445

446
    /**
447
     * Generates a SELECT MAX(field) portion of a query
448
     *
449
     * @return $this
450
     */
451
    public function selectMax(string $select = '', string $alias = '')
452
    {
453
        return $this->maxMinAvgSum($select, $alias);
454
    }
455

456
    /**
457
     * Generates a SELECT MIN(field) portion of a query
458
     *
459
     * @return $this
460
     */
461
    public function selectMin(string $select = '', string $alias = '')
462
    {
463
        return $this->maxMinAvgSum($select, $alias, 'MIN');
464
    }
465

466
    /**
467
     * Generates a SELECT AVG(field) portion of a query
468
     *
469
     * @return $this
470
     */
471
    public function selectAvg(string $select = '', string $alias = '')
472
    {
473
        return $this->maxMinAvgSum($select, $alias, 'AVG');
474
    }
475

476
    /**
477
     * Generates a SELECT SUM(field) portion of a query
478
     *
479
     * @return $this
480
     */
481
    public function selectSum(string $select = '', string $alias = '')
482
    {
483
        return $this->maxMinAvgSum($select, $alias, 'SUM');
484
    }
485

486
    /**
487
     * Generates a SELECT COUNT(field) portion of a query
488
     *
489
     * @return $this
490
     */
491
    public function selectCount(string $select = '', string $alias = '')
492
    {
493
        return $this->maxMinAvgSum($select, $alias, 'COUNT');
494
    }
495

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

503
        return $this;
504
    }
505

506
    /**
507
     * SELECT [MAX|MIN|AVG|SUM|COUNT]()
508
     *
509
     * @used-by selectMax()
510
     * @used-by selectMin()
511
     * @used-by selectAvg()
512
     * @used-by selectSum()
513
     *
514
     * @return $this
515
     *
516
     * @throws DatabaseException
517
     * @throws DataException
518
     */
519
    protected function maxMinAvgSum(string $select = '', string $alias = '', string $type = 'MAX')
520
    {
521
        if ($select === '') {
522
            throw DataException::forEmptyInputGiven('Select');
523
        }
524

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

529
        $type = strtoupper($type);
530

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

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

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

541
        $this->QBSelect[]   = $sql;
542
        $this->QBNoEscape[] = null;
543

544
        return $this;
545
    }
546

547
    /**
548
     * Determines the alias name based on the table
549
     */
550
    protected function createAliasFromTable(string $item): string
551
    {
552
        if (str_contains($item, '.')) {
553
            $item = explode('.', $item);
554

555
            return end($item);
556
        }
557

558
        return $item;
559
    }
560

561
    /**
562
     * Sets a flag which tells the query string compiler to add DISTINCT
563
     *
564
     * @return $this
565
     */
566
    public function distinct(bool $val = true)
567
    {
568
        $this->QBDistinct = $val;
569

570
        return $this;
571
    }
572

573
    /**
574
     * Generates the FROM portion of the query
575
     *
576
     * @param array|string $from
577
     *
578
     * @return $this
579
     */
580
    public function from($from, bool $overwrite = false): self
581
    {
582
        if ($overwrite === true) {
583
            $this->QBFrom = [];
584
            $this->db->setAliasedTables([]);
585
        }
586

587
        foreach ((array) $from as $table) {
588
            if (str_contains($table, ',')) {
589
                $this->from(explode(',', $table));
590
            } else {
591
                $table = trim($table);
592

593
                if ($table === '') {
594
                    continue;
595
                }
596

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

602
        return $this;
603
    }
604

605
    /**
606
     * @param BaseBuilder $from  Expected subquery
607
     * @param string      $alias Subquery alias
608
     *
609
     * @return $this
610
     */
611
    public function fromSubquery(BaseBuilder $from, string $alias): self
612
    {
613
        $table = $this->buildSubquery($from, true, $alias);
614

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

618
        return $this;
619
    }
620

621
    /**
622
     * Generates the JOIN portion of the query
623
     *
624
     * @param RawSql|string $cond
625
     *
626
     * @return $this
627
     */
628
    public function join(string $table, $cond, string $type = '', ?bool $escape = null)
629
    {
630
        if ($type !== '') {
631
            $type = strtoupper(trim($type));
632

633
            if (! in_array($type, $this->joinTypes, true)) {
634
                $type = '';
635
            } else {
636
                $type .= ' ';
637
            }
638
        }
639

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);
643

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

648
        // Do we want to escape the table name?
649
        if ($escape === true) {
650
            $table = $this->db->protectIdentifiers($table, true, null, false);
651
        }
652

653
        if ($cond instanceof RawSql) {
654
            $this->QBJoin[] = $type . 'JOIN ' . $table . ' ON ' . $cond;
655

656
            return $this;
657
        }
658

659
        if (! $this->hasOperator($cond)) {
660
            $cond = ' USING (' . ($escape ? $this->db->escapeIdentifiers($cond) : $cond) . ')';
661
        } elseif ($escape === false) {
662
            $cond = ' ON ' . $cond;
663
        } else {
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)) {
667
                $conditions = [];
668
                $joints     = $joints[0];
669
                array_unshift($joints, ['', 0]);
670

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];
676
                }
677
                ksort($conditions);
678
            } else {
679
                $conditions = [$cond];
680
                $joints     = [''];
681
            }
682

683
            $cond = ' ON ';
684

685
            foreach ($conditions as $i => $condition) {
686
                $operator = $this->getOperator($condition);
687

688
                // Workaround for BETWEEN
689
                if ($operator === false) {
690
                    $cond .= $joints[$i] . $condition;
691

692
                    continue;
693
                }
694

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;
697
            }
698
        }
699

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

703
        return $this;
704
    }
705

706
    /**
707
     * Generates the WHERE portion of the query.
708
     * Separates multiple calls with 'AND'.
709
     *
710
     * @param array|RawSql|string $key
711
     * @param mixed               $value
712
     *
713
     * @return $this
714
     */
715
    public function where($key, $value = null, ?bool $escape = null)
716
    {
717
        return $this->whereHaving('QBWhere', $key, $value, 'AND ', $escape);
718
    }
719

720
    /**
721
     * OR WHERE
722
     *
723
     * Generates the WHERE portion of the query.
724
     * Separates multiple calls with 'OR'.
725
     *
726
     * @param array|RawSql|string $key
727
     * @param mixed               $value
728
     *
729
     * @return $this
730
     */
731
    public function orWhere($key, $value = null, ?bool $escape = null)
732
    {
733
        return $this->whereHaving('QBWhere', $key, $value, 'OR ', $escape);
734
    }
735

736
    /**
737
     * @used-by where()
738
     * @used-by orWhere()
739
     * @used-by having()
740
     * @used-by orHaving()
741
     *
742
     * @param array|RawSql|string $key
743
     * @param mixed               $value
744
     *
745
     * @return $this
746
     */
747
    protected function whereHaving(string $qbKey, $key, $value = null, string $type = 'AND ', ?bool $escape = null)
748
    {
749
        $rawSqlOnly = false;
750

751
        if ($key instanceof RawSql) {
752
            if ($value === null) {
753
                $keyValue   = [(string) $key => $key];
754
                $rawSqlOnly = true;
755
            } else {
756
                $keyValue = [(string) $key => $value];
757
            }
758
        } elseif (! is_array($key)) {
759
            $keyValue = [$key => $value];
760
        } else {
761
            $keyValue = $key;
762
        }
763

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;
767
        }
768

769
        foreach ($keyValue as $k => $v) {
770
            $prefix = empty($this->{$qbKey}) ? $this->groupGetType('') : $this->groupGetType($type);
771

772
            if ($rawSqlOnly === true) {
773
                $k  = '';
774
                $op = '';
775
            } elseif ($v !== null) {
776
                $op = $this->getOperatorFromWhereKey($k);
777

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

781
                    end($op);
782
                    $op = trim(current($op));
783

784
                    // Does the key end with operator?
785
                    if (str_ends_with($k, $op)) {
786
                        $k  = rtrim(substr($k, 0, -strlen($op)));
787
                        $op = " {$op}";
788
                    } else {
789
                        $op = '';
790
                    }
791
                } else {
792
                    $op = ' =';
793
                }
794

795
                if ($this->isSubquery($v)) {
796
                    $v = $this->buildSubquery($v, true);
797
                } else {
798
                    $bind = $this->setBind($k, $v, $escape);
799
                    $v    = " :{$bind}:";
800
                }
801
            } elseif (! $this->hasOperator($k) && $qbKey !== 'QBHaving') {
802
                // value appears not to have been set, assign the test to IS NULL
803
                $op = ' IS NULL';
804
            } elseif (
805
                // The key ends with !=, =, <>, IS, IS NOT
806
                preg_match(
807
                    '/\s*(!?=|<>|IS(?:\s+NOT)?)\s*$/i',
808
                    $k,
809
                    $match,
810
                    PREG_OFFSET_CAPTURE
811
                )
812
            ) {
813
                $k  = substr($k, 0, $match[0][1]);
814
                $op = $match[1][0] === '=' ? ' IS NULL' : ' IS NOT NULL';
815
            } else {
816
                $op = '';
817
            }
818

819
            if ($v instanceof RawSql) {
820
                $this->{$qbKey}[] = [
821
                    'condition' => $v->with($prefix . $k . $op . $v),
822
                    'escape'    => $escape,
823
                ];
824
            } else {
825
                $this->{$qbKey}[] = [
826
                    'condition' => $prefix . $k . $op . $v,
827
                    'escape'    => $escape,
828
                ];
829
            }
830
        }
831

832
        return $this;
833
    }
834

835
    /**
836
     * Generates a WHERE field IN('item', 'item') SQL query,
837
     * joined with 'AND' if appropriate.
838
     *
839
     * @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
840
     *
841
     * @return $this
842
     */
843
    public function whereIn(?string $key = null, $values = null, ?bool $escape = null)
844
    {
845
        return $this->_whereIn($key, $values, false, 'AND ', $escape);
846
    }
847

848
    /**
849
     * Generates a WHERE field IN('item', 'item') SQL query,
850
     * joined with 'OR' if appropriate.
851
     *
852
     * @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
853
     *
854
     * @return $this
855
     */
856
    public function orWhereIn(?string $key = null, $values = null, ?bool $escape = null)
857
    {
858
        return $this->_whereIn($key, $values, false, 'OR ', $escape);
859
    }
860

861
    /**
862
     * Generates a WHERE field NOT IN('item', 'item') SQL query,
863
     * joined with 'AND' if appropriate.
864
     *
865
     * @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
866
     *
867
     * @return $this
868
     */
869
    public function whereNotIn(?string $key = null, $values = null, ?bool $escape = null)
870
    {
871
        return $this->_whereIn($key, $values, true, 'AND ', $escape);
872
    }
873

874
    /**
875
     * Generates a WHERE field NOT IN('item', 'item') SQL query,
876
     * joined with 'OR' if appropriate.
877
     *
878
     * @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
879
     *
880
     * @return $this
881
     */
882
    public function orWhereNotIn(?string $key = null, $values = null, ?bool $escape = null)
883
    {
884
        return $this->_whereIn($key, $values, true, 'OR ', $escape);
885
    }
886

887
    /**
888
     * Generates a HAVING field IN('item', 'item') SQL query,
889
     * joined with 'AND' if appropriate.
890
     *
891
     * @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
892
     *
893
     * @return $this
894
     */
895
    public function havingIn(?string $key = null, $values = null, ?bool $escape = null)
896
    {
897
        return $this->_whereIn($key, $values, false, 'AND ', $escape, 'QBHaving');
898
    }
899

900
    /**
901
     * Generates a HAVING field IN('item', 'item') SQL query,
902
     * joined with 'OR' if appropriate.
903
     *
904
     * @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
905
     *
906
     * @return $this
907
     */
908
    public function orHavingIn(?string $key = null, $values = null, ?bool $escape = null)
909
    {
910
        return $this->_whereIn($key, $values, false, 'OR ', $escape, 'QBHaving');
911
    }
912

913
    /**
914
     * Generates a HAVING field NOT IN('item', 'item') SQL query,
915
     * joined with 'AND' if appropriate.
916
     *
917
     * @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
918
     *
919
     * @return $this
920
     */
921
    public function havingNotIn(?string $key = null, $values = null, ?bool $escape = null)
922
    {
923
        return $this->_whereIn($key, $values, true, 'AND ', $escape, 'QBHaving');
924
    }
925

926
    /**
927
     * Generates a HAVING field NOT IN('item', 'item') SQL query,
928
     * joined with 'OR' if appropriate.
929
     *
930
     * @param array|BaseBuilder|Closure|string $values The values searched on, or anonymous function with subquery
931
     *
932
     * @return $this
933
     */
934
    public function orHavingNotIn(?string $key = null, $values = null, ?bool $escape = null)
935
    {
936
        return $this->_whereIn($key, $values, true, 'OR ', $escape, 'QBHaving');
937
    }
938

939
    /**
940
     * @used-by WhereIn()
941
     * @used-by orWhereIn()
942
     * @used-by whereNotIn()
943
     * @used-by orWhereNotIn()
944
     *
945
     * @param non-empty-string|null          $key
946
     * @param array|BaseBuilder|Closure|null $values The values searched on, or anonymous function with subquery
947
     *
948
     * @return $this
949
     *
950
     * @throws InvalidArgumentException
951
     */
952
    protected function _whereIn(?string $key = null, $values = null, bool $not = false, string $type = 'AND ', ?bool $escape = null, string $clause = 'QBWhere')
953
    {
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']));
956
        }
957

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']));
960
        }
961

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

966
        $ok = $key;
967

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

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

974
        if ($this->isSubquery($values)) {
975
            $whereIn = $this->buildSubquery($values, true);
976
            $escape  = false;
977
        } else {
978
            $whereIn = array_values($values);
979
        }
980

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

983
        $prefix = empty($this->{$clause}) ? $this->groupGetType('') : $this->groupGetType($type);
984

985
        $whereIn = [
986
            'condition' => "{$prefix}{$key}{$not} IN :{$ok}:",
987
            'escape'    => false,
988
        ];
989

990
        $this->{$clause}[] = $whereIn;
991

992
        return $this;
993
    }
994

995
    /**
996
     * Generates a %LIKE% portion of the query.
997
     * Separates multiple calls with 'AND'.
998
     *
999
     * @param array|RawSql|string $field
1000
     *
1001
     * @return $this
1002
     */
1003
    public function like($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1004
    {
1005
        return $this->_like($field, $match, 'AND ', $side, '', $escape, $insensitiveSearch);
1006
    }
1007

1008
    /**
1009
     * Generates a NOT LIKE portion of the query.
1010
     * Separates multiple calls with 'AND'.
1011
     *
1012
     * @param array|RawSql|string $field
1013
     *
1014
     * @return $this
1015
     */
1016
    public function notLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1017
    {
1018
        return $this->_like($field, $match, 'AND ', $side, 'NOT', $escape, $insensitiveSearch);
1019
    }
1020

1021
    /**
1022
     * Generates a %LIKE% portion of the query.
1023
     * Separates multiple calls with 'OR'.
1024
     *
1025
     * @param array|RawSql|string $field
1026
     *
1027
     * @return $this
1028
     */
1029
    public function orLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1030
    {
1031
        return $this->_like($field, $match, 'OR ', $side, '', $escape, $insensitiveSearch);
1032
    }
1033

1034
    /**
1035
     * Generates a NOT LIKE portion of the query.
1036
     * Separates multiple calls with 'OR'.
1037
     *
1038
     * @param array|RawSql|string $field
1039
     *
1040
     * @return $this
1041
     */
1042
    public function orNotLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1043
    {
1044
        return $this->_like($field, $match, 'OR ', $side, 'NOT', $escape, $insensitiveSearch);
1045
    }
1046

1047
    /**
1048
     * Generates a %LIKE% portion of the query.
1049
     * Separates multiple calls with 'AND'.
1050
     *
1051
     * @param array|RawSql|string $field
1052
     *
1053
     * @return $this
1054
     */
1055
    public function havingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1056
    {
1057
        return $this->_like($field, $match, 'AND ', $side, '', $escape, $insensitiveSearch, 'QBHaving');
1058
    }
1059

1060
    /**
1061
     * Generates a NOT LIKE portion of the query.
1062
     * Separates multiple calls with 'AND'.
1063
     *
1064
     * @param array|RawSql|string $field
1065
     *
1066
     * @return $this
1067
     */
1068
    public function notHavingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1069
    {
1070
        return $this->_like($field, $match, 'AND ', $side, 'NOT', $escape, $insensitiveSearch, 'QBHaving');
1071
    }
1072

1073
    /**
1074
     * Generates a %LIKE% portion of the query.
1075
     * Separates multiple calls with 'OR'.
1076
     *
1077
     * @param array|RawSql|string $field
1078
     *
1079
     * @return $this
1080
     */
1081
    public function orHavingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1082
    {
1083
        return $this->_like($field, $match, 'OR ', $side, '', $escape, $insensitiveSearch, 'QBHaving');
1084
    }
1085

1086
    /**
1087
     * Generates a NOT LIKE portion of the query.
1088
     * Separates multiple calls with 'OR'.
1089
     *
1090
     * @param array|RawSql|string $field
1091
     *
1092
     * @return $this
1093
     */
1094
    public function orNotHavingLike($field, string $match = '', string $side = 'both', ?bool $escape = null, bool $insensitiveSearch = false)
1095
    {
1096
        return $this->_like($field, $match, 'OR ', $side, 'NOT', $escape, $insensitiveSearch, 'QBHaving');
1097
    }
1098

1099
    /**
1100
     * @used-by like()
1101
     * @used-by orLike()
1102
     * @used-by notLike()
1103
     * @used-by orNotLike()
1104
     * @used-by havingLike()
1105
     * @used-by orHavingLike()
1106
     * @used-by notHavingLike()
1107
     * @used-by orNotHavingLike()
1108
     *
1109
     * @param array<string, string>|RawSql|string $field
1110
     *
1111
     * @return $this
1112
     */
1113
    protected function _like($field, string $match = '', string $type = 'AND ', string $side = 'both', string $not = '', ?bool $escape = null, bool $insensitiveSearch = false, string $clause = 'QBWhere')
1114
    {
1115
        $escape = is_bool($escape) ? $escape : $this->db->protectIdentifiers;
1116
        $side   = strtolower($side);
1117

1118
        if ($field instanceof RawSql) {
1119
            $k                 = (string) $field;
1120
            $v                 = $match;
1121
            $insensitiveSearch = false;
1122

1123
            $prefix = empty($this->{$clause}) ? $this->groupGetType('') : $this->groupGetType($type);
1124

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);
1131
            } else {
1132
                $bind = $this->setBind($field->getBindingKey(), "%{$v}%", $escape);
1133
            }
1134

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

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);
1140
            }
1141

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

1147
            return $this;
1148
        }
1149

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

1152
        foreach ($keyValue as $k => $v) {
1153
            if ($insensitiveSearch === true) {
1154
                $v = strtolower($v);
1155
            }
1156

1157
            $prefix = empty($this->{$clause}) ? $this->groupGetType('') : $this->groupGetType($type);
1158

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);
1165
            } else {
1166
                $bind = $this->setBind($k, "%{$v}%", $escape);
1167
            }
1168

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

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);
1174
            }
1175

1176
            $this->{$clause}[] = [
1177
                'condition' => $likeStatement,
1178
                'escape'    => $escape,
1179
            ];
1180
        }
1181

1182
        return $this;
1183
    }
1184

1185
    /**
1186
     * Platform independent LIKE statement builder.
1187
     */
1188
    protected function _like_statement(?string $prefix, string $column, ?string $not, string $bind, bool $insensitiveSearch = false): string
1189
    {
1190
        if ($insensitiveSearch === true) {
1191
            return "{$prefix} LOWER(" . $this->db->escapeIdentifiers($column) . ") {$not} LIKE :{$bind}:";
1192
        }
1193

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

1197
    /**
1198
     * Add UNION statement
1199
     *
1200
     * @param BaseBuilder|Closure $union
1201
     *
1202
     * @return $this
1203
     */
1204
    public function union($union)
1205
    {
1206
        return $this->addUnionStatement($union);
1207
    }
1208

1209
    /**
1210
     * Add UNION ALL statement
1211
     *
1212
     * @param BaseBuilder|Closure $union
1213
     *
1214
     * @return $this
1215
     */
1216
    public function unionAll($union)
1217
    {
1218
        return $this->addUnionStatement($union, true);
1219
    }
1220

1221
    /**
1222
     * @used-by union()
1223
     * @used-by unionAll()
1224
     *
1225
     * @param BaseBuilder|Closure $union
1226
     *
1227
     * @return $this
1228
     */
1229
    protected function addUnionStatement($union, bool $all = false)
1230
    {
1231
        $this->QBUnion[] = "\nUNION "
1232
            . ($all ? 'ALL ' : '')
1233
            . 'SELECT * FROM '
1234
            . $this->buildSubquery($union, true, 'uwrp' . (count($this->QBUnion) + 1));
1235

1236
        return $this;
1237
    }
1238

1239
    /**
1240
     * Starts a query group.
1241
     *
1242
     * @return $this
1243
     */
1244
    public function groupStart()
1245
    {
1246
        return $this->groupStartPrepare();
1247
    }
1248

1249
    /**
1250
     * Starts a query group, but ORs the group
1251
     *
1252
     * @return $this
1253
     */
1254
    public function orGroupStart()
1255
    {
1256
        return $this->groupStartPrepare('', 'OR ');
1257
    }
1258

1259
    /**
1260
     * Starts a query group, but NOTs the group
1261
     *
1262
     * @return $this
1263
     */
1264
    public function notGroupStart()
1265
    {
1266
        return $this->groupStartPrepare('NOT ');
1267
    }
1268

1269
    /**
1270
     * Starts a query group, but OR NOTs the group
1271
     *
1272
     * @return $this
1273
     */
1274
    public function orNotGroupStart()
1275
    {
1276
        return $this->groupStartPrepare('NOT ', 'OR ');
1277
    }
1278

1279
    /**
1280
     * Ends a query group
1281
     *
1282
     * @return $this
1283
     */
1284
    public function groupEnd()
1285
    {
1286
        return $this->groupEndPrepare();
1287
    }
1288

1289
    /**
1290
     * Starts a query group for HAVING clause.
1291
     *
1292
     * @return $this
1293
     */
1294
    public function havingGroupStart()
1295
    {
1296
        return $this->groupStartPrepare('', 'AND ', 'QBHaving');
1297
    }
1298

1299
    /**
1300
     * Starts a query group for HAVING clause, but ORs the group.
1301
     *
1302
     * @return $this
1303
     */
1304
    public function orHavingGroupStart()
1305
    {
1306
        return $this->groupStartPrepare('', 'OR ', 'QBHaving');
1307
    }
1308

1309
    /**
1310
     * Starts a query group for HAVING clause, but NOTs the group.
1311
     *
1312
     * @return $this
1313
     */
1314
    public function notHavingGroupStart()
1315
    {
1316
        return $this->groupStartPrepare('NOT ', 'AND ', 'QBHaving');
1317
    }
1318

1319
    /**
1320
     * Starts a query group for HAVING clause, but OR NOTs the group.
1321
     *
1322
     * @return $this
1323
     */
1324
    public function orNotHavingGroupStart()
1325
    {
1326
        return $this->groupStartPrepare('NOT ', 'OR ', 'QBHaving');
1327
    }
1328

1329
    /**
1330
     * Ends a query group for HAVING clause.
1331
     *
1332
     * @return $this
1333
     */
1334
    public function havingGroupEnd()
1335
    {
1336
        return $this->groupEndPrepare('QBHaving');
1337
    }
1338

1339
    /**
1340
     * Prepate a query group start.
1341
     *
1342
     * @return $this
1343
     */
1344
    protected function groupStartPrepare(string $not = '', string $type = 'AND ', string $clause = 'QBWhere')
1345
    {
1346
        $type = $this->groupGetType($type);
1347

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

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

1357
        return $this;
1358
    }
1359

1360
    /**
1361
     * Prepate a query group end.
1362
     *
1363
     * @return $this
1364
     */
1365
    protected function groupEndPrepare(string $clause = 'QBWhere')
1366
    {
1367
        $this->QBWhereGroupStarted = false;
1368
        $where                     = [
1369
            'condition' => str_repeat(' ', $this->QBWhereGroupCount--) . ')',
1370
            'escape'    => false,
1371
        ];
1372

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

1375
        return $this;
1376
    }
1377

1378
    /**
1379
     * @used-by groupStart()
1380
     * @used-by _like()
1381
     * @used-by whereHaving()
1382
     * @used-by _whereIn()
1383
     * @used-by havingGroupStart()
1384
     */
1385
    protected function groupGetType(string $type): string
1386
    {
1387
        if ($this->QBWhereGroupStarted) {
1388
            $type                      = '';
1389
            $this->QBWhereGroupStarted = false;
1390
        }
1391

1392
        return $type;
1393
    }
1394

1395
    /**
1396
     * @param array|string $by
1397
     *
1398
     * @return $this
1399
     */
1400
    public function groupBy($by, ?bool $escape = null)
1401
    {
1402
        if (! is_bool($escape)) {
1403
            $escape = $this->db->protectIdentifiers;
1404
        }
1405

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

1410
        foreach ($by as $val) {
1411
            $val = trim($val);
1412

1413
            if ($val !== '') {
1414
                $val = [
1415
                    'field'  => $val,
1416
                    'escape' => $escape,
1417
                ];
1418

1419
                $this->QBGroupBy[] = $val;
1420
            }
1421
        }
1422

1423
        return $this;
1424
    }
1425

1426
    /**
1427
     * Separates multiple calls with 'AND'.
1428
     *
1429
     * @param array|RawSql|string $key
1430
     * @param mixed               $value
1431
     *
1432
     * @return $this
1433
     */
1434
    public function having($key, $value = null, ?bool $escape = null)
1435
    {
1436
        return $this->whereHaving('QBHaving', $key, $value, 'AND ', $escape);
1437
    }
1438

1439
    /**
1440
     * Separates multiple calls with 'OR'.
1441
     *
1442
     * @param array|RawSql|string $key
1443
     * @param mixed               $value
1444
     *
1445
     * @return $this
1446
     */
1447
    public function orHaving($key, $value = null, ?bool $escape = null)
1448
    {
1449
        return $this->whereHaving('QBHaving', $key, $value, 'OR ', $escape);
1450
    }
1451

1452
    /**
1453
     * @param string $direction ASC, DESC or RANDOM
1454
     *
1455
     * @return $this
1456
     */
1457
    public function orderBy(string $orderBy, string $direction = '', ?bool $escape = null)
1458
    {
1459
        if ($orderBy === '') {
1460
            return $this;
1461
        }
1462

1463
        $qbOrderBy = [];
1464

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

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

1475
        if ($escape === null) {
1476
            $escape = $this->db->protectIdentifiers;
1477
        }
1478

1479
        if ($escape === false) {
1480
            $qbOrderBy[] = [
1481
                'field'     => $orderBy,
1482
                'direction' => $direction,
1483
                'escape'    => false,
1484
            ];
1485
        } else {
1486
            foreach (explode(',', $orderBy) as $field) {
1487
                $qbOrderBy[] = ($direction === '' && preg_match('/\s+(ASC|DESC)$/i', rtrim($field), $match, PREG_OFFSET_CAPTURE))
1488
                    ? [
1489
                        'field'     => ltrim(substr($field, 0, $match[0][1])),
1490
                        'direction' => ' ' . $match[1][0],
1491
                        'escape'    => true,
1492
                    ]
1493
                    : [
1494
                        'field'     => trim($field),
1495
                        'direction' => $direction,
1496
                        'escape'    => true,
1497
                    ];
1498
            }
1499
        }
1500

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

1503
        return $this;
1504
    }
1505

1506
    /**
1507
     * @return $this
1508
     */
1509
    public function limit(?int $value = null, ?int $offset = 0)
1510
    {
1511
        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
1512
        if ($limitZeroAsAll && $value === 0) {
1513
            $value = null;
1514
        }
1515

1516
        if ($value !== null) {
1517
            $this->QBLimit = $value;
1518
        }
1519

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

1524
        return $this;
1525
    }
1526

1527
    /**
1528
     * Sets the OFFSET value
1529
     *
1530
     * @return $this
1531
     */
1532
    public function offset(int $offset)
1533
    {
1534
        if ($offset !== 0) {
1535
            $this->QBOffset = $offset;
1536
        }
1537

1538
        return $this;
1539
    }
1540

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

1549
    /**
1550
     * Allows key/value pairs to be set for insert(), update() or replace().
1551
     *
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
1555
     *
1556
     * @return $this
1557
     */
1558
    public function set($key, $value = '', ?bool $escape = null)
1559
    {
1560
        $key = $this->objectToArray($key);
1561

1562
        if (! is_array($key)) {
1563
            $key = [$key => $value];
1564
        }
1565

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

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

1572
                $this->QBSet[$this->db->protectIdentifiers($k, false)] = ":{$bind}:";
1573
            } else {
1574
                $this->QBSet[$this->db->protectIdentifiers($k, false)] = $v;
1575
            }
1576
        }
1577

1578
        return $this;
1579
    }
1580

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

1588
        if ($clean) {
1589
            $this->QBSet = [];
1590
        }
1591

1592
        return $data;
1593
    }
1594

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

1602
        if ($reset === true) {
1603
            $this->resetSelect();
1604
        }
1605

1606
        return $this->compileFinalQuery($select);
1607
    }
1608

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

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

1622
        return $query->getQuery();
1623
    }
1624

1625
    /**
1626
     * Compiles the select statement based on the other functions called
1627
     * and runs the query
1628
     *
1629
     * @return false|ResultInterface
1630
     */
1631
    public function get(?int $limit = null, int $offset = 0, bool $reset = true)
1632
    {
1633
        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
1634
        if ($limitZeroAsAll && $limit === 0) {
1635
            $limit = null;
1636
        }
1637

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

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

1646
        if ($reset === true) {
1647
            $this->resetSelect();
1648

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

1653
        return $result;
1654
    }
1655

1656
    /**
1657
     * Generates a platform-specific query string that counts all records in
1658
     * the particular table
1659
     *
1660
     * @return int|string
1661
     */
1662
    public function countAll(bool $reset = true)
1663
    {
1664
        $table = $this->QBFrom[0];
1665

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

1669
        if ($this->testMode) {
1670
            return $sql;
1671
        }
1672

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

1675
        if (empty($query->getResult())) {
1676
            return 0;
1677
        }
1678

1679
        $query = $query->getRow();
1680

1681
        if ($reset === true) {
1682
            $this->resetSelect();
1683
        }
1684

1685
        return (int) $query->numrows;
1686
    }
1687

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

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

1704
            $this->QBOrderBy = null;
1705
        }
1706

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

1710
        $this->QBLimit = false;
1711

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";
1716

1717
            // Restore SELECT part
1718
            $this->QBSelect = $select;
1719
            unset($select);
1720
        } else {
1721
            $sql = $this->compileSelect($this->countString . $this->db->protectIdentifiers('numrows'));
1722
        }
1723

1724
        if ($this->testMode) {
1725
            return $sql;
1726
        }
1727

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

1730
        if ($reset === true) {
1731
            $this->resetSelect();
1732
        } elseif (! isset($this->QBOrderBy)) {
1733
            $this->QBOrderBy = $orderBy;
1734
        }
1735

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

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

1741
        if (empty($row)) {
1742
            return 0;
1743
        }
1744

1745
        return (int) $row->numrows;
1746
    }
1747

1748
    /**
1749
     * Compiles the set conditions and returns the sql statement
1750
     *
1751
     * @return array
1752
     */
1753
    public function getCompiledQBWhere()
1754
    {
1755
        return $this->QBWhere;
1756
    }
1757

1758
    /**
1759
     * Allows the where clause, limit and offset to be added directly
1760
     *
1761
     * @param array|string $where
1762
     *
1763
     * @return ResultInterface
1764
     */
1765
    public function getWhere($where = null, ?int $limit = null, ?int $offset = 0, bool $reset = true)
1766
    {
1767
        if ($where !== null) {
1768
            $this->where($where);
1769
        }
1770

1771
        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
1772
        if ($limitZeroAsAll && $limit === 0) {
1773
            $limit = null;
1774
        }
1775

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

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

1784
        if ($reset === true) {
1785
            $this->resetSelect();
1786

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

1791
        return $result;
1792
    }
1793

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

1810
            return false; // @codeCoverageIgnore
1811
        }
1812

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

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

1819
        // batch size 0 for unlimited
1820
        if ($batchSize === 0) {
1821
            $batchSize = $cnt;
1822
        }
1823

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

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

1829
            if ($sql === '') {
1830
                return false; // @codeCoverageIgnore
1831
            }
1832

1833
            if ($this->testMode) {
1834
                $savedSQL[] = $sql;
1835
            } else {
1836
                $this->db->query($sql, null, false);
1837
                $affectedRows += $this->db->affectedRows();
1838
            }
1839
        }
1840

1841
        if (! $this->testMode) {
1842
            $this->resetWrite();
1843
        }
1844

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

1848
    /**
1849
     * Allows a row or multiple rows to be set for batch inserts/upserts/updates
1850
     *
1851
     * @param array|object $set
1852
     * @param string       $alias alias for sql table
1853
     *
1854
     * @return $this|null
1855
     */
1856
    public function setData($set, ?bool $escape = null, string $alias = '')
1857
    {
1858
        if (empty($set)) {
1859
            if ($this->db->DBDebug) {
1860
                throw new DatabaseException('setData() has no data.');
1861
            }
1862

1863
            return null; // @codeCoverageIgnore
1864
        }
1865

1866
        $this->setAlias($alias);
1867

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

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

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

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

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[] = [];
1885

1886
                return null;
1887
            }
1888

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

1891
            $clean = [];
1892

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

1897
            $row = $clean;
1898

1899
            $this->QBSet[] = $row;
1900
        }
1901

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

1905
            if (! in_array($k, $this->QBKeys, true)) {
1906
                $this->QBKeys[] = $k;
1907
            }
1908
        }
1909

1910
        return $this;
1911
    }
1912

1913
    /**
1914
     * Compiles an upsert query and returns the sql
1915
     *
1916
     * @return string
1917
     *
1918
     * @throws DatabaseException
1919
     */
1920
    public function getCompiledUpsert()
1921
    {
1922
        [$currentTestMode, $this->testMode] = [$this->testMode, true];
1923

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

1926
        $this->testMode = $currentTestMode;
1927

1928
        return $this->compileFinalQuery($sql);
1929
    }
1930

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

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);
1950
            }
1951

1952
            $this->binds = [];
1953

1954
            $this->resetRun([
1955
                'QBSet'  => [],
1956
                'QBKeys' => [],
1957
            ]);
1958

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
1963

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

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

1981
            if ($sql === '') {
1982
                return false; // @codeCoverageIgnore
1983
            }
1984

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

1989
            $this->resetWrite();
1990

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

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

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

2001
    /**
2002
     * Generates a platform-specific upsertBatch string from the supplied data
2003
     *
2004
     * @used-by batchExecute()
2005
     *
2006
     * @param string                 $table  Protected table name
2007
     * @param list<string>           $keys   QBKeys
2008
     * @param list<list<int|string>> $values QBSet
2009
     */
2010
    protected function _upsertBatch(string $table, array $keys, array $values): string
2011
    {
2012
        $sql = $this->QBOptions['sql'] ?? '';
2013

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

2018
            $sql = 'INSERT INTO ' . $table . ' (' . implode(', ', $keys) . ")\n{:_table_:}ON DUPLICATE KEY UPDATE\n" . implode(
2019
                ",\n",
2020
                array_map(
2021
                    static fn ($key, $value) => $table . '.' . $key . ($value instanceof RawSql ?
2022
                        ' = ' . $value :
2023
                        ' = VALUES(' . $value . ')'),
2024
                    array_keys($updateFields),
2025
                    $updateFields
2026
                )
2027
            );
2028

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

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

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

2041
    /**
2042
     * Set table alias for dataset pseudo table.
2043
     */
2044
    private function setAlias(string $alias): BaseBuilder
2045
    {
2046
        if ($alias !== '') {
2047
            $this->db->addTableAlias($alias);
2048
            $this->QBOptions['alias'] = $this->db->protectIdentifiers($alias);
2049
        }
2050

2051
        return $this;
2052
    }
2053

2054
    /**
2055
     * Sets update fields for upsert, update
2056
     *
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
2060
     *
2061
     * @return $this
2062
     */
2063
    public function updateFields($set, bool $addToDefault = false, ?array $ignore = null)
2064
    {
2065
        if (! empty($set)) {
2066
            if (! is_array($set)) {
2067
                $set = explode(',', $set);
2068
            }
2069

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

2075
                if (is_numeric($key)) {
2076
                    $key = $value;
2077
                }
2078

2079
                if ($ignore === null || ! in_array($key, $ignore, true)) {
2080
                    if ($addToDefault) {
2081
                        $this->QBOptions['updateFieldsAdditional'][$this->db->protectIdentifiers($key)] = $value;
2082
                    } else {
2083
                        $this->QBOptions['updateFields'][$this->db->protectIdentifiers($key)] = $value;
2084
                    }
2085
                }
2086
            }
2087

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

2091
                unset($this->QBOptions['updateFieldsAdditional']);
2092
            }
2093
        }
2094

2095
        return $this;
2096
    }
2097

2098
    /**
2099
     * Sets constraints for batch upsert, update
2100
     *
2101
     * @param array|RawSql|string $set a string of columns, key value pairs, or RawSql
2102
     *
2103
     * @return $this
2104
     */
2105
    public function onConstraint($set)
2106
    {
2107
        if (! empty($set)) {
2108
            if (is_string($set)) {
2109
                $set = explode(',', $set);
2110

2111
                $set = array_map(static fn ($key) => trim($key), $set);
2112
            }
2113

2114
            if ($set instanceof RawSql) {
2115
                $set = [$set];
2116
            }
2117

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

2123
                if (is_string($key)) {
2124
                    $key = $this->db->protectIdentifiers($key);
2125
                }
2126

2127
                $this->QBOptions['constraints'][$key] = $value;
2128
            }
2129
        }
2130

2131
        return $this;
2132
    }
2133

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

2146
        if ($query instanceof BaseBuilder) {
2147
            $query = $query->getCompiledSelect();
2148
        } elseif ($query instanceof RawSql) {
2149
            $query = $query->__toString();
2150
        }
2151

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);
2156
            }
2157

2158
            $columns = (array) $columns;
2159

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

2164
            if ($alias !== null) {
2165
                $this->setAlias($alias);
2166
            }
2167

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

2172
            $this->QBOptions['setQueryAsData'] = $query;
2173
            $this->QBKeys                      = $columns;
2174
            $this->QBSet                       = [];
2175
        }
2176

2177
        return $this;
2178
    }
2179

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

2188
    /**
2189
     * Converts value array of array to array of strings
2190
     */
2191
    protected function formatValues(array $values): array
2192
    {
2193
        return array_map(static fn ($index) => '(' . implode(',', $index) . ')', $values);
2194
    }
2195

2196
    /**
2197
     * Compiles batch insert strings and runs the queries
2198
     *
2199
     * @param array|object|null $set a dataset
2200
     *
2201
     * @return false|int|list<string> Number of rows inserted or FALSE on failure, SQL array when testMode
2202
     */
2203
    public function insertBatch($set = null, ?bool $escape = null, int $batchSize = 100)
2204
    {
2205
        if (isset($this->QBOptions['setQueryAsData'])) {
2206
            $sql = $this->_insertBatch($this->QBFrom[0], $this->QBKeys, []);
2207

2208
            if ($sql === '') {
2209
                return false; // @codeCoverageIgnore
2210
            }
2211

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

2216
            $this->resetWrite();
2217

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

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

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

2228
    /**
2229
     * Generates a platform-specific insert string from the supplied data.
2230
     *
2231
     * @used-by batchExecute()
2232
     *
2233
     * @param string                 $table  Protected table name
2234
     * @param list<string>           $keys   QBKeys
2235
     * @param list<list<int|string>> $values QBSet
2236
     */
2237
    protected function _insertBatch(string $table, array $keys, array $values): string
2238
    {
2239
        $sql = $this->QBOptions['sql'] ?? '';
2240

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

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

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

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

2258
    /**
2259
     * Allows key/value pairs to be set for batch inserts
2260
     *
2261
     * @param mixed $key
2262
     *
2263
     * @return $this|null
2264
     *
2265
     * @deprecated
2266
     */
2267
    public function setInsertBatch($key, string $value = '', ?bool $escape = null)
2268
    {
2269
        if (! is_array($key)) {
2270
            $key = [[$key => $value]];
2271
        }
2272

2273
        return $this->setData($key, $escape);
2274
    }
2275

2276
    /**
2277
     * Compiles an insert query and returns the sql
2278
     *
2279
     * @return bool|string
2280
     *
2281
     * @throws DatabaseException
2282
     */
2283
    public function getCompiledInsert(bool $reset = true)
2284
    {
2285
        if ($this->validateInsert() === false) {
2286
            return false;
2287
        }
2288

2289
        $sql = $this->_insert(
2290
            $this->db->protectIdentifiers(
2291
                $this->removeAlias($this->QBFrom[0]),
2292
                true,
2293
                null,
2294
                false
2295
            ),
2296
            array_keys($this->QBSet),
2297
            array_values($this->QBSet)
2298
        );
2299

2300
        if ($reset === true) {
2301
            $this->resetWrite();
2302
        }
2303

2304
        return $this->compileFinalQuery($sql);
2305
    }
2306

2307
    /**
2308
     * Compiles an insert string and runs the query
2309
     *
2310
     * @param array|object|null $set
2311
     *
2312
     * @return BaseResult|bool|Query
2313
     *
2314
     * @throws DatabaseException
2315
     */
2316
    public function insert($set = null, ?bool $escape = null)
2317
    {
2318
        if ($set !== null) {
2319
            $this->set($set, '', $escape);
2320
        }
2321

2322
        if ($this->validateInsert() === false) {
2323
            return false;
2324
        }
2325

2326
        $sql = $this->_insert(
2327
            $this->db->protectIdentifiers(
2328
                $this->removeAlias($this->QBFrom[0]),
2329
                true,
2330
                $escape,
2331
                false
2332
            ),
2333
            array_keys($this->QBSet),
2334
            array_values($this->QBSet)
2335
        );
2336

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

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

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

2345
            return $result;
2346
        }
2347

2348
        return false;
2349
    }
2350

2351
    /**
2352
     * @internal This is a temporary solution.
2353
     *
2354
     * @see https://github.com/codeigniter4/CodeIgniter4/pull/5376
2355
     *
2356
     * @TODO Fix a root cause, and this method should be removed.
2357
     */
2358
    protected function removeAlias(string $from): string
2359
    {
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);
2363

2364
            $parts = explode(' ', $from);
2365
            $from  = $parts[0];
2366
        }
2367

2368
        return $from;
2369
    }
2370

2371
    /**
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.
2375
     *
2376
     * @throws DatabaseException
2377
     */
2378
    protected function validateInsert(): bool
2379
    {
2380
        if (empty($this->QBSet)) {
2381
            if ($this->db->DBDebug) {
2382
                throw new DatabaseException('You must use the "set" method to insert an entry.');
2383
            }
2384

2385
            return false; // @codeCoverageIgnore
2386
        }
2387

2388
        return true;
2389
    }
2390

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

2403
    /**
2404
     * Compiles a replace into string and runs the query
2405
     *
2406
     * @return BaseResult|false|Query|string
2407
     *
2408
     * @throws DatabaseException
2409
     */
2410
    public function replace(?array $set = null)
2411
    {
2412
        if ($set !== null) {
2413
            $this->set($set);
2414
        }
2415

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

2421
            return false; // @codeCoverageIgnore
2422
        }
2423

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

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

2428
        $this->resetWrite();
2429

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

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

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

2456
    /**
2457
     * Compiles an update query and returns the sql
2458
     *
2459
     * @return bool|string
2460
     */
2461
    public function getCompiledUpdate(bool $reset = true)
2462
    {
2463
        if ($this->validateUpdate() === false) {
2464
            return false;
2465
        }
2466

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

2469
        if ($reset === true) {
2470
            $this->resetWrite();
2471
        }
2472

2473
        return $this->compileFinalQuery($sql);
2474
    }
2475

2476
    /**
2477
     * Compiles an update string and runs the query.
2478
     *
2479
     * @param array|object|null        $set
2480
     * @param array|RawSql|string|null $where
2481
     *
2482
     * @throws DatabaseException
2483
     */
2484
    public function update($set = null, $where = null, ?int $limit = null): bool
2485
    {
2486
        if ($set !== null) {
2487
            $this->set($set);
2488
        }
2489

2490
        if ($this->validateUpdate() === false) {
2491
            return false;
2492
        }
2493

2494
        if ($where !== null) {
2495
            $this->where($where);
2496
        }
2497

2498
        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
2499
        if ($limitZeroAsAll && $limit === 0) {
2500
            $limit = null;
2501
        }
2502

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

2508
            $this->limit($limit);
2509
        }
2510

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

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

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

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

2522
                return true;
2523
            }
2524

2525
            return false;
2526
        }
2527

2528
        return true;
2529
    }
2530

2531
    /**
2532
     * Generates a platform-specific update string from the supplied data
2533
     *
2534
     * @param string                $table  Protected table name
2535
     * @param array<string, string> $values QBSet
2536
     */
2537
    protected function _update(string $table, array $values): string
2538
    {
2539
        $valStr = [];
2540

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

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) : '');
2551
        }
2552

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

2559
    /**
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.
2563
     *
2564
     * @throws DatabaseException
2565
     */
2566
    protected function validateUpdate(): bool
2567
    {
2568
        if (empty($this->QBSet)) {
2569
            if ($this->db->DBDebug) {
2570
                throw new DatabaseException('You must use the "set" method to update an entry.');
2571
            }
2572

2573
            return false; // @codeCoverageIgnore
2574
        }
2575

2576
        return true;
2577
    }
2578

2579
    /**
2580
     * Sets data and calls batchExecute to run queries
2581
     *
2582
     * @param array|object|null        $set         a dataset
2583
     * @param array|RawSql|string|null $constraints
2584
     *
2585
     * @return false|int|list<string> Number of rows affected or FALSE on failure, SQL array when testMode
2586
     */
2587
    public function updateBatch($set = null, $constraints = null, int $batchSize = 100)
2588
    {
2589
        $this->onConstraint($constraints);
2590

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

2594
            if ($sql === '') {
2595
                return false; // @codeCoverageIgnore
2596
            }
2597

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

2602
            $this->resetWrite();
2603

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

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

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

2614
    /**
2615
     * Generates a platform-specific batch update string from the supplied data
2616
     *
2617
     * @used-by batchExecute()
2618
     *
2619
     * @param string                 $table  Protected table name
2620
     * @param list<string>           $keys   QBKeys
2621
     * @param list<list<int|string>> $values QBSet
2622
     */
2623
    protected function _updateBatch(string $table, array $keys, array $values): string
2624
    {
2625
        $sql = $this->QBOptions['sql'] ?? '';
2626

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

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

2636
                return ''; // @codeCoverageIgnore
2637
            }
2638

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

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

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

2647
            $sql .= "SET\n";
2648

2649
            $sql .= implode(
2650
                ",\n",
2651
                array_map(
2652
                    static fn ($key, $value) => $key . ($value instanceof RawSql ?
2653
                        ' = ' . $value :
2654
                        ' = ' . $alias . '.' . $value),
2655
                    array_keys($updateFields),
2656
                    $updateFields
2657
                )
2658
            ) . "\n";
2659

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

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

2664
            $sql .= 'WHERE ' . implode(
2665
                ' AND ',
2666
                array_map(
2667
                    static fn ($key, $value) => (
2668
                        ($value instanceof RawSql && is_string($key))
2669
                        ?
2670
                        $table . '.' . $key . ' = ' . $value
2671
                        :
2672
                        (
2673
                            $value instanceof RawSql
2674
                            ?
2675
                            $value
2676
                            :
2677
                            $table . '.' . $value . ' = ' . $alias . '.' . $value
2678
                        )
2679
                    ),
2680
                    array_keys($constraints),
2681
                    $constraints
2682
                )
2683
            );
2684

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

2688
        if (isset($this->QBOptions['setQueryAsData'])) {
2689
            $data = $this->QBOptions['setQueryAsData'];
2690
        } else {
2691
            $data = implode(
2692
                " UNION ALL\n",
2693
                array_map(
2694
                    static fn ($value) => 'SELECT ' . implode(', ', array_map(
2695
                        static fn ($key, $index) => $index . ' ' . $key,
2696
                        $keys,
2697
                        $value
2698
                    )),
2699
                    $values
2700
                )
2701
            ) . "\n";
2702
        }
2703

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

2707
    /**
2708
     * Allows key/value pairs to be set for batch updating
2709
     *
2710
     * @param array|object $key
2711
     *
2712
     * @return $this
2713
     *
2714
     * @throws DatabaseException
2715
     *
2716
     * @deprecated
2717
     */
2718
    public function setUpdateBatch($key, string $index = '', ?bool $escape = null)
2719
    {
2720
        if ($index !== '') {
2721
            $this->onConstraint($index);
2722
        }
2723

2724
        $this->setData($key, $escape);
2725

2726
        return $this;
2727
    }
2728

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

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

2740
        if ($this->testMode) {
2741
            return $sql;
2742
        }
2743

2744
        $this->resetWrite();
2745

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

2749
    /**
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"
2753
     *
2754
     * @return bool|string TRUE on success, FALSE on failure, string on testMode
2755
     */
2756
    public function truncate()
2757
    {
2758
        $table = $this->QBFrom[0];
2759

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

2762
        if ($this->testMode) {
2763
            return $sql;
2764
        }
2765

2766
        $this->resetWrite();
2767

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

2771
    /**
2772
     * Generates a platform-specific truncate string from the supplied data
2773
     *
2774
     * If the database does not support the truncate() command,
2775
     * then this method maps to 'DELETE FROM table'
2776
     *
2777
     * @param string $table Protected table name
2778
     */
2779
    protected function _truncate(string $table): string
2780
    {
2781
        return 'TRUNCATE ' . $table;
2782
    }
2783

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

2792
        return $this->compileFinalQuery($sql);
2793
    }
2794

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

2808
        if ($where !== '') {
2809
            $this->where($where);
2810
        }
2811

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.');
2815
            }
2816

2817
            return false; // @codeCoverageIgnore
2818
        }
2819

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

2822
        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
2823
        if ($limitZeroAsAll && $limit === 0) {
2824
            $limit = null;
2825
        }
2826

2827
        if ($limit !== null) {
2828
            $this->QBLimit = $limit;
2829
        }
2830

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

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

2839
        if ($resetData) {
2840
            $this->resetWrite();
2841
        }
2842

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

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

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

2861
            if ($sql === '') {
2862
                return false; // @codeCoverageIgnore
2863
            }
2864

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

2869
            $this->resetWrite();
2870

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

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

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

2881
    /**
2882
     * Generates a platform-specific batch update string from the supplied data
2883
     *
2884
     * @used-by batchExecute()
2885
     *
2886
     * @param string           $table  Protected table name
2887
     * @param list<string>     $keys   QBKeys
2888
     * @param list<int|string> $values QBSet
2889
     */
2890
    protected function _deleteBatch(string $table, array $keys, array $values): string
2891
    {
2892
        $sql = $this->QBOptions['sql'] ?? '';
2893

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

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

2903
                return ''; // @codeCoverageIgnore
2904
            }
2905

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

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

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

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

2914
            $sql .= 'ON ' . implode(
2915
                ' AND ',
2916
                array_map(
2917
                    static fn ($key, $value) => (
2918
                        $value instanceof RawSql ?
2919
                        $value :
2920
                        (
2921
                            is_string($key) ?
2922
                            $table . '.' . $key . ' = ' . $alias . '.' . $value :
2923
                            $table . '.' . $value . ' = ' . $alias . '.' . $value
2924
                        )
2925
                    ),
2926
                    array_keys($constraints),
2927
                    $constraints
2928
                )
2929
            );
2930

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']);
2935
                }
2936
            }
2937

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

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

2943
        if (isset($this->QBOptions['setQueryAsData'])) {
2944
            $data = $this->QBOptions['setQueryAsData'];
2945
        } else {
2946
            $data = implode(
2947
                " UNION ALL\n",
2948
                array_map(
2949
                    static fn ($value) => 'SELECT ' . implode(', ', array_map(
2950
                        static fn ($key, $index) => $index . ' ' . $key,
2951
                        $keys,
2952
                        $value
2953
                    )),
2954
                    $values
2955
                )
2956
            ) . "\n";
2957
        }
2958

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

2962
    /**
2963
     * Increments a numeric column by the specified value.
2964
     *
2965
     * @return bool
2966
     */
2967
    public function increment(string $column, int $value = 1)
2968
    {
2969
        $column = $this->db->protectIdentifiers($column);
2970

2971
        $sql = $this->_update($this->QBFrom[0], [$column => "{$column} + {$value}"]);
2972

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

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

2979
        return true;
2980
    }
2981

2982
    /**
2983
     * Decrements a numeric column by the specified value.
2984
     *
2985
     * @return bool
2986
     */
2987
    public function decrement(string $column, int $value = 1)
2988
    {
2989
        $column = $this->db->protectIdentifiers($column);
2990

2991
        $sql = $this->_update($this->QBFrom[0], [$column => "{$column}-{$value}"]);
2992

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

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

2999
        return true;
3000
    }
3001

3002
    /**
3003
     * Generates a platform-specific delete string from the supplied data
3004
     *
3005
     * @param string $table Protected table name
3006
     */
3007
    protected function _delete(string $table): string
3008
    {
3009
        return 'DELETE ' . $this->compileIgnore('delete') . 'FROM ' . $table . $this->compileWhereHaving('QBWhere');
3010
    }
3011

3012
    /**
3013
     * Used to track SQL statements written with aliased tables.
3014
     *
3015
     * @param array|string $table The table to inspect
3016
     *
3017
     * @return string|void
3018
     */
3019
    protected function trackAliases($table)
3020
    {
3021
        if (is_array($table)) {
3022
            foreach ($table as $t) {
3023
                $this->trackAliases($t);
3024
            }
3025

3026
            return;
3027
        }
3028

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));
3033
        }
3034

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);
3039

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

3043
            // Store the alias, if it doesn't already exist
3044
            $this->db->addTableAlias($table);
3045
        }
3046
    }
3047

3048
    /**
3049
     * Compile the SELECT statement
3050
     *
3051
     * Generates a query string based on which functions were used.
3052
     * Should not be called directly.
3053
     *
3054
     * @param mixed $selectOverride
3055
     */
3056
    protected function compileSelect($selectOverride = false): string
3057
    {
3058
        if ($selectOverride !== false) {
3059
            $sql = $selectOverride;
3060
        } else {
3061
            $sql = (! $this->QBDistinct) ? 'SELECT ' : 'SELECT DISTINCT ';
3062

3063
            if (empty($this->QBSelect)) {
3064
                $sql .= '*';
3065
            } else {
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;
3072
                    } else {
3073
                        $protect              = $this->QBNoEscape[$key] ?? null;
3074
                        $this->QBSelect[$key] = $this->db->protectIdentifiers($val, false, $protect);
3075
                    }
3076
                }
3077

3078
                $sql .= implode(', ', $this->QBSelect);
3079
            }
3080
        }
3081

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

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

3090
        $sql .= $this->compileWhereHaving('QBWhere')
3091
            . $this->compileGroupBy()
3092
            . $this->compileWhereHaving('QBHaving')
3093
            . $this->compileOrderBy();
3094

3095
        $limitZeroAsAll = config(Feature::class)->limitZeroAsAll ?? true;
3096
        if ($limitZeroAsAll) {
3097
            if ($this->QBLimit) {
3098
                $sql = $this->_limit($sql . "\n");
3099
            }
3100
        } elseif ($this->QBLimit !== false || $this->QBOffset) {
3101
            $sql = $this->_limit($sql . "\n");
3102
        }
3103

3104
        return $this->unionInjection($sql);
3105
    }
3106

3107
    /**
3108
     * Checks if the ignore option is supported by
3109
     * the Database Driver for the specific statement.
3110
     *
3111
     * @return string
3112
     */
3113
    protected function compileIgnore(string $statement)
3114
    {
3115
        if ($this->QBIgnore && isset($this->supportedIgnoreStatements[$statement])) {
3116
            return trim($this->supportedIgnoreStatements[$statement]) . ' ';
3117
        }
3118

3119
        return '';
3120
    }
3121

3122
    /**
3123
     * Escapes identifiers in WHERE and HAVING statements at execution time.
3124
     *
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.
3128
     *
3129
     * @param string $qbKey 'QBWhere' or 'QBHaving'
3130
     *
3131
     * @return string SQL statement
3132
     */
3133
    protected function compileWhereHaving(string $qbKey): string
3134
    {
3135
        if (! empty($this->{$qbKey})) {
3136
            foreach ($this->{$qbKey} as &$qbkey) {
3137
                // Is this condition already compiled?
3138
                if (is_string($qbkey)) {
3139
                    continue;
3140
                }
3141

3142
                if ($qbkey instanceof RawSql) {
3143
                    continue;
3144
                }
3145

3146
                if ($qbkey['condition'] instanceof RawSql) {
3147
                    $qbkey = $qbkey['condition'];
3148

3149
                    continue;
3150
                }
3151

3152
                if ($qbkey['escape'] === false) {
3153
                    $qbkey = $qbkey['condition'];
3154

3155
                    continue;
3156
                }
3157

3158
                // Split multiple conditions
3159
                $conditions = preg_split(
3160
                    '/((?:^|\s+)AND\s+|(?:^|\s+)OR\s+)/i',
3161
                    $qbkey['condition'],
3162
                    -1,
3163
                    PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY
3164
                );
3165

3166
                foreach ($conditions as &$condition) {
3167
                    $op = $this->getOperator($condition);
3168
                    if (
3169
                        $op === false
3170
                        || ! preg_match(
3171
                            '/^(\(?)(.*)(' . preg_quote($op, '/') . ')\s*(.*(?<!\)))?(\)?)$/i',
3172
                            $condition,
3173
                            $matches
3174
                        )
3175
                    ) {
3176
                        continue;
3177
                    }
3178
                    // $matches = [
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 */
3185
                    // ];
3186

3187
                    if (isset($matches[4]) && $matches[4] !== '') {
3188
                        $protectIdentifiers = false;
3189
                        if (str_contains($matches[4], '.')) {
3190
                            $protectIdentifiers = true;
3191
                        }
3192

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

3197
                        $matches[4] = ' ' . $matches[4];
3198
                    }
3199

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

3204
                $qbkey = implode('', $conditions);
3205
            }
3206

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

3211
        return '';
3212
    }
3213

3214
    /**
3215
     * Escapes identifiers in GROUP BY statements at execution time.
3216
     *
3217
     * Required so that aliases are tracked properly, regardless of whether
3218
     * groupBy() is called prior to from(), join() and prefixTable is added
3219
     * only if needed.
3220
     */
3221
    protected function compileGroupBy(): string
3222
    {
3223
        if (! empty($this->QBGroupBy)) {
3224
            foreach ($this->QBGroupBy as &$groupBy) {
3225
                // Is it already compiled?
3226
                if (is_string($groupBy)) {
3227
                    continue;
3228
                }
3229

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

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

3238
        return '';
3239
    }
3240

3241
    /**
3242
     * Escapes identifiers in ORDER BY statements at execution time.
3243
     *
3244
     * Required so that aliases are tracked properly, regardless of whether
3245
     * orderBy() is called prior to from(), join() and prefixTable is added
3246
     * only if needed.
3247
     */
3248
    protected function compileOrderBy(): string
3249
    {
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']);
3254
                }
3255

3256
                $orderBy = $orderBy['field'] . $orderBy['direction'];
3257
            }
3258

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

3262
        if (is_string($this->QBOrderBy)) {
3263
            return $this->QBOrderBy;
3264
        }
3265

3266
        return '';
3267
    }
3268

3269
    protected function unionInjection(string $sql): string
3270
    {
3271
        if ($this->QBUnion === []) {
3272
            return $sql;
3273
        }
3274

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

3280
    /**
3281
     * Takes an object as input and converts the class variables to array key/vals
3282
     *
3283
     * @param array|object $object
3284
     *
3285
     * @return array
3286
     */
3287
    protected function objectToArray($object)
3288
    {
3289
        if (! is_object($object)) {
3290
            return $object;
3291
        }
3292

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

3297
        $array = [];
3298

3299
        foreach (get_object_vars($object) as $key => $val) {
3300
            if ((! is_object($val) || $val instanceof RawSql) && ! is_array($val)) {
3301
                $array[$key] = $val;
3302
            }
3303
        }
3304

3305
        return $array;
3306
    }
3307

3308
    /**
3309
     * Takes an object as input and converts the class variables to array key/vals
3310
     *
3311
     * @param array|object $object
3312
     *
3313
     * @return array
3314
     */
3315
    protected function batchObjectToArray($object)
3316
    {
3317
        if (! is_object($object)) {
3318
            return $object;
3319
        }
3320

3321
        $array  = [];
3322
        $out    = get_object_vars($object);
3323
        $fields = array_keys($out);
3324

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

3328
            foreach ($out[$val] as $data) {
3329
                $array[$i++][$val] = $data;
3330
            }
3331
        }
3332

3333
        return $array;
3334
    }
3335

3336
    /**
3337
     * Determines if a string represents a literal value or a field name
3338
     */
3339
    protected function isLiteral(string $str): bool
3340
    {
3341
        $str = trim($str);
3342

3343
        if ($str === ''
3344
            || ctype_digit($str)
3345
            || (string) (float) $str === $str
3346
            || in_array(strtoupper($str), ['TRUE', 'FALSE'], true)
3347
        ) {
3348
            return true;
3349
        }
3350

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

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

3358
    /**
3359
     * Publicly-visible method to reset the QB values.
3360
     *
3361
     * @return $this
3362
     */
3363
    public function resetQuery()
3364
    {
3365
        $this->resetSelect();
3366
        $this->resetWrite();
3367

3368
        return $this;
3369
    }
3370

3371
    /**
3372
     * Resets the query builder values.  Called by the get() function
3373
     *
3374
     * @param array $qbResetItems An array of fields to reset
3375
     */
3376
    protected function resetRun(array $qbResetItems)
3377
    {
3378
        foreach ($qbResetItems as $item => $defaultValue) {
3379
            $this->{$item} = $defaultValue;
3380
        }
3381
    }
3382

3383
    /**
3384
     * Resets the query builder values.  Called by the get() function
3385
     */
3386
    protected function resetSelect()
3387
    {
3388
        $this->resetRun([
3389
            'QBSelect'   => [],
3390
            'QBJoin'     => [],
3391
            'QBWhere'    => [],
3392
            'QBGroupBy'  => [],
3393
            'QBHaving'   => [],
3394
            'QBOrderBy'  => [],
3395
            'QBNoEscape' => [],
3396
            'QBDistinct' => false,
3397
            'QBLimit'    => false,
3398
            'QBOffset'   => false,
3399
            'QBUnion'    => [],
3400
        ]);
3401

3402
        if (! empty($this->db)) {
3403
            $this->db->setAliasedTables([]);
3404
        }
3405

3406
        // Reset QBFrom part
3407
        if (! empty($this->QBFrom)) {
3408
            $this->from(array_shift($this->QBFrom), true);
3409
        }
3410
    }
3411

3412
    /**
3413
     * Resets the query builder "write" values.
3414
     *
3415
     * Called by the insert() update() insertBatch() updateBatch() and delete() functions
3416
     */
3417
    protected function resetWrite()
3418
    {
3419
        $this->resetRun([
3420
            'QBSet'     => [],
3421
            'QBJoin'    => [],
3422
            'QBWhere'   => [],
3423
            'QBOrderBy' => [],
3424
            'QBKeys'    => [],
3425
            'QBLimit'   => false,
3426
            'QBIgnore'  => false,
3427
            'QBOptions' => [],
3428
        ]);
3429
    }
3430

3431
    /**
3432
     * Tests whether the string has an SQL operator
3433
     */
3434
    protected function hasOperator(string $str): bool
3435
    {
3436
        return preg_match(
3437
            '/(<|>|!|=|\sIS NULL|\sIS NOT NULL|\sEXISTS|\sBETWEEN|\sLIKE|\sIN\s*\(|\s)/i',
3438
            trim($str)
3439
        ) === 1;
3440
    }
3441

3442
    /**
3443
     * Returns the SQL string operator
3444
     *
3445
     * @return array|false|string
3446
     */
3447
    protected function getOperator(string $str, bool $list = false)
3448
    {
3449
        if ($this->pregOperators === []) {
3450
            $_les = $this->db->likeEscapeStr !== ''
3451
                ? '\s+' . preg_quote(trim(sprintf($this->db->likeEscapeStr, $this->db->likeEscapeChar)), '/')
3452
                : '';
3453
            $this->pregOperators = [
3454
                '\s*(?:<|>|!)?=\s*', // =, <=, >=, !=
3455
                '\s*<>?\s*',         // <, <>
3456
                '\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']
3466
            ];
3467
        }
3468

3469
        return preg_match_all(
3470
            '/' . implode('|', $this->pregOperators) . '/i',
3471
            $str,
3472
            $match
3473
        ) ? ($list ? $match[0] : $match[0][0]) : false;
3474
    }
3475

3476
    /**
3477
     * Returns the SQL string operator from where key
3478
     *
3479
     * @return false|list<string>
3480
     */
3481
    private function getOperatorFromWhereKey(string $whereKey)
3482
    {
3483
        $whereKey = trim($whereKey);
3484

3485
        $pregOperators = [
3486
            '\s*(?:<|>|!)?=',         // =, <=, >=, !=
3487
            '\s*<>?',                 // <, <>
3488
            '\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)
3496
            '\s+LIKE',                // LIKE
3497
            '\s+NOT LIKE',            // NOT LIKE
3498
        ];
3499

3500
        return preg_match_all(
3501
            '/' . implode('|', $pregOperators) . '/i',
3502
            $whereKey,
3503
            $match
3504
        ) ? $match[0] : false;
3505
    }
3506

3507
    /**
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.
3512
     *
3513
     * @param mixed $value
3514
     */
3515
    protected function setBind(string $key, $value = null, bool $escape = true): string
3516
    {
3517
        if (! array_key_exists($key, $this->binds)) {
3518
            $this->binds[$key] = [
3519
                $value,
3520
                $escape,
3521
            ];
3522

3523
            return $key;
3524
        }
3525

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

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

3532
        $this->binds[$key . '.' . $count] = [
3533
            $value,
3534
            $escape,
3535
        ];
3536

3537
        return $key . '.' . $count;
3538
    }
3539

3540
    /**
3541
     * Returns a clone of a Base Builder with reset query builder values.
3542
     *
3543
     * @return $this
3544
     *
3545
     * @deprecated
3546
     */
3547
    protected function cleanClone()
3548
    {
3549
        return (clone $this)->from([], true)->resetQuery();
3550
    }
3551

3552
    /**
3553
     * @param mixed $value
3554
     */
3555
    protected function isSubquery($value): bool
3556
    {
3557
        return $value instanceof BaseBuilder || $value instanceof Closure;
3558
    }
3559

3560
    /**
3561
     * @param BaseBuilder|Closure $builder
3562
     * @param bool                $wrapped Wrap the subquery in brackets
3563
     * @param string              $alias   Subquery alias
3564
     */
3565
    protected function buildSubquery($builder, bool $wrapped = false, string $alias = ''): string
3566
    {
3567
        if ($builder instanceof Closure) {
3568
            $builder($builder = $this->db->newQuery());
3569
        }
3570

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

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

3577
        if ($wrapped) {
3578
            $subquery = '(' . $subquery . ')';
3579
            $alias    = trim($alias);
3580

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

3586
        return $subquery;
3587
    }
3588
}
3589

Использование cookies

Мы используем файлы cookie в соответствии с Политикой конфиденциальности и Политикой использования cookies.

Нажимая кнопку «Принимаю», Вы даете АО «СберТех» согласие на обработку Ваших персональных данных в целях совершенствования нашего веб-сайта и Сервиса GitVerse, а также повышения удобства их использования.

Запретить использование cookies Вы можете самостоятельно в настройках Вашего браузера.