1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Pixie\QueryBuilder; |
4
|
|
|
|
5
|
|
|
use wpdb; |
6
|
|
|
use Closure; |
7
|
|
|
use Throwable; |
8
|
|
|
use Pixie\Binding; |
9
|
|
|
use Pixie\Exception; |
10
|
|
|
use Pixie\Connection; |
11
|
|
|
|
12
|
|
|
use Pixie\QueryBuilder\Raw; |
13
|
|
|
|
14
|
|
|
use Pixie\Hydration\Hydrator; |
15
|
|
|
use Pixie\QueryBuilder\JoinBuilder; |
16
|
|
|
use Pixie\QueryBuilder\QueryObject; |
17
|
|
|
use Pixie\QueryBuilder\Transaction; |
18
|
|
|
use Pixie\QueryBuilder\WPDBAdapter; |
19
|
|
|
use function mb_strlen; |
20
|
|
|
|
21
|
|
|
class QueryBuilderHandler |
22
|
|
|
{ |
23
|
|
|
/** |
24
|
|
|
* @var \Viocon\Container |
25
|
|
|
*/ |
26
|
|
|
protected $container; |
27
|
|
|
|
28
|
|
|
/** |
29
|
|
|
* @var Connection |
30
|
|
|
*/ |
31
|
|
|
protected $connection; |
32
|
|
|
|
33
|
|
|
/** |
34
|
|
|
* @var array<string, mixed[]|mixed> |
35
|
|
|
*/ |
36
|
|
|
protected $statements = []; |
37
|
|
|
|
38
|
|
|
/** |
39
|
|
|
* @var wpdb |
40
|
|
|
*/ |
41
|
|
|
protected $dbInstance; |
42
|
|
|
|
43
|
|
|
/** |
44
|
|
|
* @var string|string[]|null |
45
|
|
|
*/ |
46
|
|
|
protected $sqlStatement = null; |
47
|
|
|
|
48
|
|
|
/** |
49
|
|
|
* @var string|null |
50
|
|
|
*/ |
51
|
|
|
protected $tablePrefix = null; |
52
|
|
|
|
53
|
|
|
/** |
54
|
|
|
* @var WPDBAdapter |
55
|
|
|
*/ |
56
|
|
|
protected $adapterInstance; |
57
|
|
|
|
58
|
|
|
/** |
59
|
|
|
* The mode to return results as. |
60
|
|
|
* Accepts WPDB constants or class names. |
61
|
|
|
* |
62
|
|
|
* @var string |
63
|
|
|
*/ |
64
|
|
|
protected $fetchMode; |
65
|
|
|
|
66
|
|
|
/** |
67
|
|
|
* Custom args used to construct models for hydrator |
68
|
|
|
* |
69
|
|
|
* @var array<int, mixed>|null |
70
|
|
|
*/ |
71
|
|
|
protected $hydratorConstructorArgs; |
72
|
|
|
|
73
|
|
|
/** |
74
|
|
|
* @param \Pixie\Connection|null $connection |
75
|
|
|
* @param string $fetchMode |
76
|
|
|
* @param mixed[] $hydratorConstructorArgs |
77
|
|
|
* |
78
|
|
|
* @throws Exception if no connection passed and not previously established |
79
|
|
|
*/ |
80
|
|
|
final public function __construct( |
81
|
|
|
Connection $connection = null, |
82
|
|
|
string $fetchMode = \OBJECT, |
83
|
|
|
?array $hydratorConstructorArgs = null |
84
|
|
|
) { |
85
|
|
|
if (is_null($connection)) { |
86
|
|
|
// throws if connection not already established. |
87
|
|
|
$connection = Connection::getStoredConnection(); |
88
|
|
|
} |
89
|
|
|
|
90
|
|
|
// Set all dependencies from connection. |
91
|
|
|
$this->connection = $connection; |
92
|
|
|
$this->container = $this->connection->getContainer(); |
93
|
|
|
$this->dbInstance = $this->connection->getDbInstance(); |
94
|
|
|
$this->setAdapterConfig($this->connection->getAdapterConfig()); |
95
|
|
|
|
96
|
|
|
// Set up optional hydration details. |
97
|
|
|
$this->setFetchMode($fetchMode); |
98
|
|
|
$this->hydratorConstructorArgs = $hydratorConstructorArgs; |
99
|
|
|
|
100
|
|
|
// Query builder adapter instance |
101
|
|
|
$this->adapterInstance = $this->container->build( |
102
|
|
|
WPDBAdapter::class, |
103
|
|
|
[$this->connection] |
104
|
|
|
); |
105
|
|
|
} |
106
|
|
|
|
107
|
|
|
/** |
108
|
|
|
* Sets the config for WPDB |
109
|
|
|
* |
110
|
|
|
* @param array<string, mixed> $adapterConfig |
111
|
|
|
* |
112
|
|
|
* @return void |
113
|
|
|
*/ |
114
|
|
|
protected function setAdapterConfig(array $adapterConfig): void |
115
|
|
|
{ |
116
|
|
|
if (isset($adapterConfig['prefix'])) { |
117
|
|
|
$this->tablePrefix = $adapterConfig['prefix']; |
118
|
|
|
} |
119
|
|
|
} |
120
|
|
|
|
121
|
|
|
/** |
122
|
|
|
* Set the fetch mode |
123
|
|
|
* |
124
|
|
|
* @param string $mode |
125
|
|
|
* @param array<int, mixed>|null $constructorArgs |
126
|
|
|
* |
127
|
|
|
* @return static |
128
|
|
|
*/ |
129
|
|
|
public function setFetchMode(string $mode, ?array $constructorArgs = null): self |
130
|
|
|
{ |
131
|
|
|
$this->fetchMode = $mode; |
132
|
|
|
$this->hydratorConstructorArgs = $constructorArgs; |
133
|
|
|
|
134
|
|
|
return $this; |
135
|
|
|
} |
136
|
|
|
|
137
|
|
|
/** |
138
|
|
|
* @param Connection|null $connection |
139
|
|
|
* |
140
|
|
|
* @return static |
141
|
|
|
* |
142
|
|
|
* @throws Exception |
143
|
|
|
*/ |
144
|
|
|
public function newQuery(Connection $connection = null): self |
145
|
|
|
{ |
146
|
|
|
if (is_null($connection)) { |
147
|
|
|
$connection = $this->connection; |
148
|
|
|
} |
149
|
|
|
|
150
|
|
|
$newQuery = $this->constructCurrentBuilderClass($connection); |
151
|
|
|
$newQuery->setFetchMode($this->getFetchMode(), $this->hydratorConstructorArgs); |
152
|
|
|
|
153
|
|
|
return $newQuery; |
154
|
|
|
} |
155
|
|
|
|
156
|
|
|
/** |
157
|
|
|
* Returns a new instance of the current, with the passed connection. |
158
|
|
|
* |
159
|
|
|
* @param \Pixie\Connection $connection |
160
|
|
|
* |
161
|
|
|
* @return static |
162
|
|
|
*/ |
163
|
|
|
protected function constructCurrentBuilderClass(Connection $connection): self |
164
|
|
|
{ |
165
|
|
|
return new static($connection); |
166
|
|
|
} |
167
|
|
|
|
168
|
|
|
/** |
169
|
|
|
* Interpolates a query |
170
|
|
|
* |
171
|
|
|
* @param string $query |
172
|
|
|
* @param array<mixed> $bindings |
173
|
|
|
* @return string |
174
|
|
|
*/ |
175
|
|
|
public function interpolateQuery(string $query, array $bindings = []): string |
176
|
|
|
{ |
177
|
|
|
return $this->adapterInstance->interpolateQuery($query, $bindings); |
178
|
|
|
} |
179
|
|
|
|
180
|
|
|
/** |
181
|
|
|
* @param string $sql |
182
|
|
|
* @param array<int,mixed> $bindings |
183
|
|
|
* |
184
|
|
|
* @return static |
185
|
|
|
*/ |
186
|
|
|
public function query($sql, $bindings = []): self |
187
|
|
|
{ |
188
|
|
|
list($this->sqlStatement) = $this->statement($sql, $bindings); |
189
|
|
|
|
190
|
|
|
return $this; |
191
|
|
|
} |
192
|
|
|
|
193
|
|
|
/** |
194
|
|
|
* @param string $sql |
195
|
|
|
* @param array<int,mixed> $bindings |
196
|
|
|
* |
197
|
|
|
* @return array{0:string, 1:float} |
198
|
|
|
*/ |
199
|
|
|
public function statement(string $sql, $bindings = []): array |
200
|
|
|
{ |
201
|
|
|
$start = microtime(true); |
202
|
|
|
$sqlStatement = empty($bindings) ? $sql : $this->interpolateQuery($sql, $bindings); |
203
|
|
|
|
204
|
|
|
if (!is_string($sqlStatement)) { |
|
|
|
|
205
|
|
|
throw new Exception('Could not interpolate query', 1); |
206
|
|
|
} |
207
|
|
|
|
208
|
|
|
return [$sqlStatement, microtime(true) - $start]; |
209
|
|
|
} |
210
|
|
|
|
211
|
|
|
/** |
212
|
|
|
* Get all rows |
213
|
|
|
* |
214
|
|
|
* @return array<mixed,mixed>|null |
215
|
|
|
* |
216
|
|
|
* @throws Exception |
217
|
|
|
*/ |
218
|
|
|
public function get() |
219
|
|
|
{ |
220
|
|
|
$eventResult = $this->fireEvents('before-select'); |
221
|
|
|
if (!is_null($eventResult)) { |
222
|
|
|
return $eventResult; |
223
|
|
|
} |
224
|
|
|
$executionTime = 0; |
225
|
|
|
if (is_null($this->sqlStatement)) { |
226
|
|
|
$queryObject = $this->getQuery('select'); |
227
|
|
|
$statement = $this->statement( |
228
|
|
|
$queryObject->getSql(), |
229
|
|
|
$queryObject->getBindings() |
230
|
|
|
); |
231
|
|
|
|
232
|
|
|
$this->sqlStatement = $statement[0]; |
233
|
|
|
$executionTime = $statement[1]; |
234
|
|
|
} |
235
|
|
|
|
236
|
|
|
$start = microtime(true); |
237
|
|
|
$result = $this->dbInstance()->get_results( |
238
|
|
|
is_array($this->sqlStatement) ? (end($this->sqlStatement) ?: '') : $this->sqlStatement, |
239
|
|
|
// If we are using the hydrator, return as OBJECT and let the hydrator map the correct model. |
240
|
|
|
$this->useHydrator() ? OBJECT : $this->getFetchMode() |
241
|
|
|
); |
242
|
|
|
$executionTime += microtime(true) - $start; |
243
|
|
|
$this->sqlStatement = null; |
244
|
|
|
|
245
|
|
|
// Ensure we have an array of results. |
246
|
|
|
if (!is_array($result) && null !== $result) { |
247
|
|
|
$result = [$result]; |
248
|
|
|
} |
249
|
|
|
|
250
|
|
|
// Maybe hydrate the results. |
251
|
|
|
if (null !== $result && $this->useHydrator()) { |
252
|
|
|
$result = $this->getHydrator()->fromMany($result); |
253
|
|
|
} |
254
|
|
|
|
255
|
|
|
$this->fireEvents('after-select', $result, $executionTime); |
256
|
|
|
|
257
|
|
|
return $result; |
258
|
|
|
} |
259
|
|
|
|
260
|
|
|
/** |
261
|
|
|
* Returns a populated instance of the Hydrator. |
262
|
|
|
* |
263
|
|
|
* @return Hydrator |
264
|
|
|
*/ |
265
|
|
|
protected function getHydrator(): Hydrator /* @phpstan-ignore-line */ |
266
|
|
|
{ |
267
|
|
|
$hydrator = new Hydrator($this->getFetchMode(), $this->hydratorConstructorArgs ?? []); /* @phpstan-ignore-line */ |
268
|
|
|
|
269
|
|
|
return $hydrator; |
270
|
|
|
} |
271
|
|
|
|
272
|
|
|
/** |
273
|
|
|
* Checks if the results should be mapped via the hydrator |
274
|
|
|
* |
275
|
|
|
* @return bool |
276
|
|
|
*/ |
277
|
|
|
protected function useHydrator(): bool |
278
|
|
|
{ |
279
|
|
|
return !in_array($this->getFetchMode(), [\ARRAY_A, \ARRAY_N, \OBJECT, \OBJECT_K]); |
280
|
|
|
} |
281
|
|
|
|
282
|
|
|
/** |
283
|
|
|
* Find all matching a simple where condition. |
284
|
|
|
* |
285
|
|
|
* Shortcut of ->where('key','=','value')->limit(1)->get(); |
286
|
|
|
* |
287
|
|
|
* @return \stdClass\array<mixed,mixed>|object|null Can return any object using hydrator |
|
|
|
|
288
|
|
|
*/ |
289
|
|
|
public function first() |
290
|
|
|
{ |
291
|
|
|
$this->limit(1); |
292
|
|
|
$result = $this->get(); |
293
|
|
|
|
294
|
|
|
return empty($result) ? null : $result[0]; |
295
|
|
|
} |
296
|
|
|
|
297
|
|
|
/** |
298
|
|
|
* Find all matching a simple where condition. |
299
|
|
|
* |
300
|
|
|
* Shortcut of ->where('key','=','value')->get(); |
301
|
|
|
* |
302
|
|
|
* @param string $fieldName |
303
|
|
|
* @param mixed $value |
304
|
|
|
* |
305
|
|
|
* @return array<mixed,mixed>|null Can return any object using hydrator |
306
|
|
|
*/ |
307
|
|
|
public function findAll($fieldName, $value) |
308
|
|
|
{ |
309
|
|
|
$this->where($fieldName, '=', $value); |
310
|
|
|
|
311
|
|
|
return $this->get(); |
312
|
|
|
} |
313
|
|
|
|
314
|
|
|
/** |
315
|
|
|
* @param string $fieldName |
316
|
|
|
* @param mixed $value |
317
|
|
|
* |
318
|
|
|
* @return \stdClass\array<mixed,mixed>|object|null Can return any object using hydrator |
319
|
|
|
*/ |
320
|
|
|
public function find($value, $fieldName = 'id') |
321
|
|
|
{ |
322
|
|
|
$this->where($fieldName, '=', $value); |
323
|
|
|
|
324
|
|
|
return $this->first(); |
325
|
|
|
} |
326
|
|
|
|
327
|
|
|
/** |
328
|
|
|
* @param string $fieldName |
329
|
|
|
* @param mixed $value |
330
|
|
|
* |
331
|
|
|
* @return \stdClass\array<mixed,mixed>|object Can return any object using hydrator |
332
|
|
|
* @throws Exception If fails to find |
333
|
|
|
*/ |
334
|
|
|
public function findOrFail($value, $fieldName = 'id') |
335
|
|
|
{ |
336
|
|
|
$result = $this->find($value, $fieldName); |
337
|
|
|
if (null === $result) { |
338
|
|
|
throw new Exception("Failed to find {$fieldName}={$value}", 1); |
339
|
|
|
} |
340
|
|
|
return $result; |
341
|
|
|
} |
342
|
|
|
|
343
|
|
|
/** |
344
|
|
|
* Used to handle all aggregation method. |
345
|
|
|
* |
346
|
|
|
* @see Taken from the pecee-pixie library - https://github.com/skipperbent/pecee-pixie/ |
347
|
|
|
* |
348
|
|
|
* @param string $type |
349
|
|
|
* @param string $field |
350
|
|
|
* |
351
|
|
|
* @return float |
352
|
|
|
*/ |
353
|
|
|
protected function aggregate(string $type, string $field = '*'): float |
354
|
|
|
{ |
355
|
|
|
// Verify that field exists |
356
|
|
|
if ('*' !== $field && true === isset($this->statements['selects']) && false === \in_array($field, $this->statements['selects'], true)) { |
357
|
|
|
throw new \Exception(sprintf('Failed %s query - the column %s hasn\'t been selected in the query.', $type, $field)); |
358
|
|
|
} |
359
|
|
|
|
360
|
|
|
if (false === isset($this->statements['tables'])) { |
361
|
|
|
throw new Exception('No table selected'); |
362
|
|
|
} |
363
|
|
|
|
364
|
|
|
$count = $this |
365
|
|
|
->table($this->subQuery($this, 'count')) |
366
|
|
|
->select([$this->raw(sprintf('%s(%s) AS field', strtoupper($type), $field))]) |
367
|
|
|
->first(); |
368
|
|
|
|
369
|
|
|
return true === isset($count->field) ? (float)$count->field : 0; |
370
|
|
|
} |
371
|
|
|
|
372
|
|
|
/** |
373
|
|
|
* Get count of all the rows for the current query |
374
|
|
|
* |
375
|
|
|
* @see Taken from the pecee-pixie library - https://github.com/skipperbent/pecee-pixie/ |
376
|
|
|
* |
377
|
|
|
* @param string $field |
378
|
|
|
* |
379
|
|
|
* @return int |
380
|
|
|
* |
381
|
|
|
* @throws Exception |
382
|
|
|
*/ |
383
|
|
|
public function count(string $field = '*'): int |
384
|
|
|
{ |
385
|
|
|
return (int)$this->aggregate('count', $field); |
386
|
|
|
} |
387
|
|
|
|
388
|
|
|
/** |
389
|
|
|
* Get the sum for a field in the current query |
390
|
|
|
* |
391
|
|
|
* @see Taken from the pecee-pixie library - https://github.com/skipperbent/pecee-pixie/ |
392
|
|
|
* |
393
|
|
|
* @param string $field |
394
|
|
|
* |
395
|
|
|
* @return float |
396
|
|
|
* |
397
|
|
|
* @throws Exception |
398
|
|
|
*/ |
399
|
|
|
public function sum(string $field): float |
400
|
|
|
{ |
401
|
|
|
return $this->aggregate('sum', $field); |
402
|
|
|
} |
403
|
|
|
|
404
|
|
|
/** |
405
|
|
|
* Get the average for a field in the current query |
406
|
|
|
* |
407
|
|
|
* @see Taken from the pecee-pixie library - https://github.com/skipperbent/pecee-pixie/ |
408
|
|
|
* |
409
|
|
|
* @param string $field |
410
|
|
|
* |
411
|
|
|
* @return float |
412
|
|
|
* |
413
|
|
|
* @throws Exception |
414
|
|
|
*/ |
415
|
|
|
public function average(string $field): float |
416
|
|
|
{ |
417
|
|
|
return $this->aggregate('avg', $field); |
418
|
|
|
} |
419
|
|
|
|
420
|
|
|
/** |
421
|
|
|
* Get the minimum for a field in the current query |
422
|
|
|
* |
423
|
|
|
* @see Taken from the pecee-pixie library - https://github.com/skipperbent/pecee-pixie/ |
424
|
|
|
* |
425
|
|
|
* @param string $field |
426
|
|
|
* |
427
|
|
|
* @return float |
428
|
|
|
* |
429
|
|
|
* @throws Exception |
430
|
|
|
*/ |
431
|
|
|
public function min(string $field): float |
432
|
|
|
{ |
433
|
|
|
return $this->aggregate('min', $field); |
434
|
|
|
} |
435
|
|
|
|
436
|
|
|
/** |
437
|
|
|
* Get the maximum for a field in the current query |
438
|
|
|
* |
439
|
|
|
* @see Taken from the pecee-pixie library - https://github.com/skipperbent/pecee-pixie/ |
440
|
|
|
* |
441
|
|
|
* @param string $field |
442
|
|
|
* |
443
|
|
|
* @return float |
444
|
|
|
* |
445
|
|
|
* @throws Exception |
446
|
|
|
*/ |
447
|
|
|
public function max(string $field): float |
448
|
|
|
{ |
449
|
|
|
return $this->aggregate('max', $field); |
450
|
|
|
} |
451
|
|
|
|
452
|
|
|
/** |
453
|
|
|
* @param string $type |
454
|
|
|
* @param bool|array<mixed, mixed> $dataToBePassed |
455
|
|
|
* |
456
|
|
|
* @return mixed |
457
|
|
|
* |
458
|
|
|
* @throws Exception |
459
|
|
|
*/ |
460
|
|
|
public function getQuery(string $type = 'select', $dataToBePassed = []) |
461
|
|
|
{ |
462
|
|
|
$allowedTypes = ['select', 'insert', 'insertignore', 'replace', 'delete', 'update', 'criteriaonly']; |
463
|
|
|
if (!in_array(strtolower($type), $allowedTypes)) { |
464
|
|
|
throw new Exception($type . ' is not a known type.', 2); |
465
|
|
|
} |
466
|
|
|
|
467
|
|
|
$queryArr = $this->adapterInstance->$type($this->statements, $dataToBePassed); |
468
|
|
|
|
469
|
|
|
return $this->container->build( |
470
|
|
|
QueryObject::class, |
471
|
|
|
[$queryArr['sql'], $queryArr['bindings'], $this->dbInstance] |
472
|
|
|
); |
473
|
|
|
} |
474
|
|
|
|
475
|
|
|
/** |
476
|
|
|
* @param QueryBuilderHandler $queryBuilder |
477
|
|
|
* @param string|null $alias |
478
|
|
|
* |
479
|
|
|
* @return Raw |
480
|
|
|
*/ |
481
|
|
|
public function subQuery(QueryBuilderHandler $queryBuilder, ?string $alias = null) |
482
|
|
|
{ |
483
|
|
|
$sql = '(' . $queryBuilder->getQuery()->getRawSql() . ')'; |
484
|
|
|
if (is_string($alias) && 0 !== mb_strlen($alias)) { |
485
|
|
|
$sql = $sql . ' as ' . $alias; |
486
|
|
|
} |
487
|
|
|
|
488
|
|
|
return $queryBuilder->raw($sql); |
489
|
|
|
} |
490
|
|
|
|
491
|
|
|
/** |
492
|
|
|
* Handles the various insert operations based on the type. |
493
|
|
|
* |
494
|
|
|
* @param array<int|string, mixed|mixed[]> $data |
495
|
|
|
* @param string $type |
496
|
|
|
* |
497
|
|
|
* @return int|int[]|mixed|null can return a single row id, array of row ids, null (for failed) or any other value short circuited from event |
498
|
|
|
*/ |
499
|
|
|
private function doInsert(array $data, string $type) |
500
|
|
|
{ |
501
|
|
|
$eventResult = $this->fireEvents('before-insert'); |
502
|
|
|
if (!is_null($eventResult)) { |
503
|
|
|
return $eventResult; |
504
|
|
|
} |
505
|
|
|
|
506
|
|
|
// If first value is not an array () not a batch insert) |
507
|
|
|
if (!is_array(current($data))) { |
508
|
|
|
$queryObject = $this->getQuery($type, $data); |
509
|
|
|
|
510
|
|
|
list($preparedQuery, $executionTime) = $this->statement($queryObject->getSql(), $queryObject->getBindings()); |
511
|
|
|
$this->dbInstance->get_results($preparedQuery); |
512
|
|
|
|
513
|
|
|
// Check we have a result. |
514
|
|
|
$return = 1 === $this->dbInstance->rows_affected ? $this->dbInstance->insert_id : null; |
515
|
|
|
} else { |
516
|
|
|
// Its a batch insert |
517
|
|
|
$return = []; |
518
|
|
|
$executionTime = 0; |
519
|
|
|
foreach ($data as $subData) { |
520
|
|
|
$queryObject = $this->getQuery($type, $subData); |
521
|
|
|
|
522
|
|
|
list($preparedQuery, $time) = $this->statement($queryObject->getSql(), $queryObject->getBindings()); |
523
|
|
|
$this->dbInstance->get_results($preparedQuery); |
524
|
|
|
$executionTime += $time; |
525
|
|
|
|
526
|
|
|
if (1 === $this->dbInstance->rows_affected) { |
527
|
|
|
$return[] = $this->dbInstance->insert_id; |
528
|
|
|
} |
529
|
|
|
} |
530
|
|
|
} |
531
|
|
|
|
532
|
|
|
$this->fireEvents('after-insert', $return, $executionTime); |
533
|
|
|
|
534
|
|
|
return $return; |
535
|
|
|
} |
536
|
|
|
|
537
|
|
|
/** |
538
|
|
|
* @param array<int|string, mixed|mixed[]> $data either key=>value array for single or array of arrays for bulk |
539
|
|
|
* |
540
|
|
|
* @return int|int[]|mixed|null can return a single row id, array of row ids, null (for failed) or any other value short circuited from event |
541
|
|
|
*/ |
542
|
|
|
public function insert($data) |
543
|
|
|
{ |
544
|
|
|
return $this->doInsert($data, 'insert'); |
545
|
|
|
} |
546
|
|
|
|
547
|
|
|
/** |
548
|
|
|
* |
549
|
|
|
* @param array<int|string, mixed|mixed[]> $data either key=>value array for single or array of arrays for bulk |
550
|
|
|
* |
551
|
|
|
* @return int|int[]|mixed|null can return a single row id, array of row ids, null (for failed) or any other value short circuited from event |
552
|
|
|
*/ |
553
|
|
|
public function insertIgnore($data) |
554
|
|
|
{ |
555
|
|
|
return $this->doInsert($data, 'insertignore'); |
556
|
|
|
} |
557
|
|
|
|
558
|
|
|
/** |
559
|
|
|
* |
560
|
|
|
* @param array<int|string, mixed|mixed[]> $data either key=>value array for single or array of arrays for bulk |
561
|
|
|
* |
562
|
|
|
* @return int|int[]|mixed|null can return a single row id, array of row ids, null (for failed) or any other value short circuited from event |
563
|
|
|
*/ |
564
|
|
|
public function replace($data) |
565
|
|
|
{ |
566
|
|
|
return $this->doInsert($data, 'replace'); |
567
|
|
|
} |
568
|
|
|
|
569
|
|
|
/** |
570
|
|
|
* @param array<string, mixed> $data |
571
|
|
|
* |
572
|
|
|
* @return int|null |
573
|
|
|
*/ |
574
|
|
|
public function update($data) |
575
|
|
|
{ |
576
|
|
|
$eventResult = $this->fireEvents('before-update'); |
577
|
|
|
if (!is_null($eventResult)) { |
578
|
|
|
return $eventResult; |
579
|
|
|
} |
580
|
|
|
$queryObject = $this->getQuery('update', $data); |
581
|
|
|
list($preparedQuery, $executionTime) = $this->statement($queryObject->getSql(), $queryObject->getBindings()); |
582
|
|
|
|
583
|
|
|
$this->dbInstance()->get_results($preparedQuery); |
584
|
|
|
$this->fireEvents('after-update', $queryObject, $executionTime); |
585
|
|
|
|
586
|
|
|
return 0 !== $this->dbInstance()->rows_affected |
587
|
|
|
? $this->dbInstance()->rows_affected |
588
|
|
|
: null; |
589
|
|
|
} |
590
|
|
|
|
591
|
|
|
/** |
592
|
|
|
* @param array<string, mixed> $data |
593
|
|
|
* |
594
|
|
|
* @return int|null will return row id for insert and bool for success/fail on update |
595
|
|
|
*/ |
596
|
|
|
public function updateOrInsert($data) |
597
|
|
|
{ |
598
|
|
|
if ($this->first()) { |
599
|
|
|
return $this->update($data); |
600
|
|
|
} |
601
|
|
|
|
602
|
|
|
return $this->insert($data); |
|
|
|
|
603
|
|
|
} |
604
|
|
|
|
605
|
|
|
/** |
606
|
|
|
* @param array<string, mixed> $data |
607
|
|
|
* |
608
|
|
|
* @return static |
609
|
|
|
*/ |
610
|
|
|
public function onDuplicateKeyUpdate($data) |
611
|
|
|
{ |
612
|
|
|
$this->addStatement('onduplicate', $data); |
613
|
|
|
|
614
|
|
|
return $this; |
615
|
|
|
} |
616
|
|
|
|
617
|
|
|
/** |
618
|
|
|
* @return int number of rows effected |
619
|
|
|
*/ |
620
|
|
|
public function delete(): int |
621
|
|
|
{ |
622
|
|
|
$eventResult = $this->fireEvents('before-delete'); |
623
|
|
|
if (!is_null($eventResult)) { |
624
|
|
|
return $eventResult; |
625
|
|
|
} |
626
|
|
|
|
627
|
|
|
$queryObject = $this->getQuery('delete'); |
628
|
|
|
|
629
|
|
|
list($preparedQuery, $executionTime) = $this->statement($queryObject->getSql(), $queryObject->getBindings()); |
630
|
|
|
$this->dbInstance()->get_results($preparedQuery); |
631
|
|
|
$this->fireEvents('after-delete', $queryObject, $executionTime); |
632
|
|
|
|
633
|
|
|
return $this->dbInstance()->rows_affected; |
634
|
|
|
} |
635
|
|
|
|
636
|
|
|
/** |
637
|
|
|
* @param string|Raw ...$tables Single table or array of tables |
638
|
|
|
* |
639
|
|
|
* @return static |
640
|
|
|
* |
641
|
|
|
* @throws Exception |
642
|
|
|
*/ |
643
|
|
|
public function table(...$tables): QueryBuilderHandler |
644
|
|
|
{ |
645
|
|
|
$instance = $this->constructCurrentBuilderClass($this->connection); |
646
|
|
|
$this->setFetchMode($this->getFetchMode(), $this->hydratorConstructorArgs); |
647
|
|
|
$tables = $this->addTablePrefix($tables, false); |
648
|
|
|
$instance->addStatement('tables', $tables); |
649
|
|
|
|
650
|
|
|
return $instance; |
651
|
|
|
} |
652
|
|
|
|
653
|
|
|
/** |
654
|
|
|
* @param string|Raw ...$tables Single table or array of tables |
655
|
|
|
* |
656
|
|
|
* @return static |
657
|
|
|
*/ |
658
|
|
|
public function from(...$tables): self |
659
|
|
|
{ |
660
|
|
|
$tables = $this->addTablePrefix($tables, false); |
661
|
|
|
$this->addStatement('tables', $tables); |
662
|
|
|
|
663
|
|
|
return $this; |
664
|
|
|
} |
665
|
|
|
|
666
|
|
|
/** |
667
|
|
|
* @param string|string[]|Raw[]|array<string, string> $fields |
668
|
|
|
* |
669
|
|
|
* @return static |
670
|
|
|
*/ |
671
|
|
|
public function select($fields): self |
672
|
|
|
{ |
673
|
|
|
if (!is_array($fields)) { |
674
|
|
|
$fields = func_get_args(); |
675
|
|
|
} |
676
|
|
|
|
677
|
|
|
foreach ($fields as $field => $alias) { |
678
|
|
|
// If we have a JSON expression |
679
|
|
|
if ($this->isJsonExpression($field)) { |
680
|
|
|
// Add using JSON select. |
681
|
|
|
$this->castToJsonSelect($field, $alias); |
682
|
|
|
unset($fields[$field]); |
683
|
|
|
continue; |
684
|
|
|
} |
685
|
|
|
|
686
|
|
|
// If no alias passed, but field is for JSON. thrown an exception. |
687
|
|
|
if (is_numeric($field) && $this->isJsonExpression($alias)) { |
688
|
|
|
throw new Exception("An alias must be used if you wish to select from JSON Object", 1); |
689
|
|
|
} |
690
|
|
|
|
691
|
|
|
// Treat each array as a single table, to retain order added |
692
|
|
|
$field = is_numeric($field) |
693
|
|
|
? $field = $alias // If single colum |
|
|
|
|
694
|
|
|
: $field = [$field => $alias]; // Has alias |
695
|
|
|
|
696
|
|
|
$field = $this->addTablePrefix($field); |
697
|
|
|
$this->addStatement('selects', $field); |
698
|
|
|
} |
699
|
|
|
|
700
|
|
|
|
701
|
|
|
|
702
|
|
|
return $this; |
703
|
|
|
} |
704
|
|
|
|
705
|
|
|
/** |
706
|
|
|
* Checks if the passed expression is for JSON |
707
|
|
|
* this->denotes->json |
708
|
|
|
* |
709
|
|
|
* @param string $expression |
710
|
|
|
* @return bool |
711
|
|
|
*/ |
712
|
|
|
protected function isJsonExpression(string $expression): bool |
713
|
|
|
{ |
714
|
|
|
return 2 <= count(explode('->', $expression)); |
715
|
|
|
} |
716
|
|
|
|
717
|
|
|
/** |
718
|
|
|
* Casts a select to JSON based on -> in column name. |
719
|
|
|
* |
720
|
|
|
* @param string $keys |
721
|
|
|
* @param string|null $alias |
722
|
|
|
* @return self |
723
|
|
|
*/ |
724
|
|
|
public function castToJsonSelect(string $keys, ?string $alias): self |
725
|
|
|
{ |
726
|
|
|
$parts = explode('->', $keys); |
727
|
|
|
$field = $parts[0]; |
728
|
|
|
unset($parts[0]); |
729
|
|
|
return $this->selectJson($field, $parts, $alias); |
730
|
|
|
} |
731
|
|
|
|
732
|
|
|
/** |
733
|
|
|
* @param string|string[]|Raw[]|array<string, string> $fields |
734
|
|
|
* |
735
|
|
|
* @return static |
736
|
|
|
*/ |
737
|
|
|
public function selectDistinct($fields) |
738
|
|
|
{ |
739
|
|
|
$this->select($fields); |
740
|
|
|
$this->addStatement('distinct', true); |
741
|
|
|
|
742
|
|
|
return $this; |
743
|
|
|
} |
744
|
|
|
|
745
|
|
|
/** |
746
|
|
|
* @param string|string[] $field either the single field or an array of fields |
747
|
|
|
* |
748
|
|
|
* @return static |
749
|
|
|
*/ |
750
|
|
|
public function groupBy($field): self |
751
|
|
|
{ |
752
|
|
|
$field = $this->addTablePrefix($field); |
753
|
|
|
$this->addStatement('groupBys', $field); |
754
|
|
|
|
755
|
|
|
return $this; |
756
|
|
|
} |
757
|
|
|
|
758
|
|
|
/** |
759
|
|
|
* @param string|array<string|Raw, mixed> $fields |
760
|
|
|
* @param string $defaultDirection |
761
|
|
|
* |
762
|
|
|
* @return static |
763
|
|
|
*/ |
764
|
|
|
public function orderBy($fields, string $defaultDirection = 'ASC'): self |
765
|
|
|
{ |
766
|
|
|
if (!is_array($fields)) { |
767
|
|
|
$fields = [$fields]; |
768
|
|
|
} |
769
|
|
|
|
770
|
|
|
foreach ($fields as $key => $value) { |
771
|
|
|
$field = $key; |
772
|
|
|
$type = $value; |
773
|
|
|
if (is_int($key)) { |
774
|
|
|
$field = $value; |
775
|
|
|
$type = $defaultDirection; |
776
|
|
|
} |
777
|
|
|
if (!$field instanceof Raw) { |
778
|
|
|
$field = $this->addTablePrefix($field); |
779
|
|
|
} |
780
|
|
|
$this->statements['orderBys'][] = compact('field', 'type'); |
781
|
|
|
} |
782
|
|
|
|
783
|
|
|
return $this; |
784
|
|
|
} |
785
|
|
|
|
786
|
|
|
/** |
787
|
|
|
* @param int $limit |
788
|
|
|
* |
789
|
|
|
* @return static |
790
|
|
|
*/ |
791
|
|
|
public function limit(int $limit): self |
792
|
|
|
{ |
793
|
|
|
$this->statements['limit'] = $limit; |
794
|
|
|
|
795
|
|
|
return $this; |
796
|
|
|
} |
797
|
|
|
|
798
|
|
|
/** |
799
|
|
|
* @param int $offset |
800
|
|
|
* |
801
|
|
|
* @return static |
802
|
|
|
*/ |
803
|
|
|
public function offset(int $offset): self |
804
|
|
|
{ |
805
|
|
|
$this->statements['offset'] = $offset; |
806
|
|
|
|
807
|
|
|
return $this; |
808
|
|
|
} |
809
|
|
|
|
810
|
|
|
/** |
811
|
|
|
* @param string|string[]|Raw|Raw[] $key |
812
|
|
|
* @param string $operator |
813
|
|
|
* @param mixed $value |
814
|
|
|
* @param string $joiner |
815
|
|
|
* |
816
|
|
|
* @return static |
817
|
|
|
*/ |
818
|
|
|
public function having($key, string $operator, $value, string $joiner = 'AND') |
819
|
|
|
{ |
820
|
|
|
$key = $this->addTablePrefix($key); |
821
|
|
|
$this->statements['havings'][] = compact('key', 'operator', 'value', 'joiner'); |
822
|
|
|
|
823
|
|
|
return $this; |
824
|
|
|
} |
825
|
|
|
|
826
|
|
|
/** |
827
|
|
|
* @param string|string[]|Raw|Raw[] $key |
828
|
|
|
* @param string $operator |
829
|
|
|
* @param mixed $value |
830
|
|
|
* |
831
|
|
|
* @return static |
832
|
|
|
*/ |
833
|
|
|
public function orHaving($key, $operator, $value) |
834
|
|
|
{ |
835
|
|
|
return $this->having($key, $operator, $value, 'OR'); |
836
|
|
|
} |
837
|
|
|
|
838
|
|
|
/** |
839
|
|
|
* @param string|Raw $key |
840
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
841
|
|
|
* @param mixed|null $value |
842
|
|
|
* |
843
|
|
|
* @return static |
844
|
|
|
*/ |
845
|
|
|
public function where($key, $operator = null, $value = null): self |
846
|
|
|
{ |
847
|
|
|
// If two params are given then assume operator is = |
848
|
|
|
if (2 === func_num_args()) { |
849
|
|
|
$value = $operator; |
850
|
|
|
$operator = '='; |
851
|
|
|
} |
852
|
|
|
|
853
|
|
|
return $this->whereHandler($key, $operator, $value); |
854
|
|
|
} |
855
|
|
|
|
856
|
|
|
/** |
857
|
|
|
* @param string|Raw $key |
858
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
859
|
|
|
* @param mixed|null $value |
860
|
|
|
* |
861
|
|
|
* @return static |
862
|
|
|
*/ |
863
|
|
|
public function orWhere($key, $operator = null, $value = null): self |
864
|
|
|
{ |
865
|
|
|
// If two params are given then assume operator is = |
866
|
|
|
if (2 === func_num_args()) { |
867
|
|
|
$value = $operator; |
868
|
|
|
$operator = '='; |
869
|
|
|
} |
870
|
|
|
|
871
|
|
|
return $this->whereHandler($key, $operator, $value, 'OR'); |
872
|
|
|
} |
873
|
|
|
|
874
|
|
|
/** |
875
|
|
|
* @param string|Raw $key |
876
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
877
|
|
|
* @param mixed|null $value |
878
|
|
|
* |
879
|
|
|
* @return static |
880
|
|
|
*/ |
881
|
|
|
public function whereNot($key, $operator = null, $value = null): self |
882
|
|
|
{ |
883
|
|
|
// If two params are given then assume operator is = |
884
|
|
|
if (2 === func_num_args()) { |
885
|
|
|
$value = $operator; |
886
|
|
|
$operator = '='; |
887
|
|
|
} |
888
|
|
|
|
889
|
|
|
return $this->whereHandler($key, $operator, $value, 'AND NOT'); |
890
|
|
|
} |
891
|
|
|
|
892
|
|
|
/** |
893
|
|
|
* @param string|Raw $key |
894
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
895
|
|
|
* @param mixed|null $value |
896
|
|
|
* |
897
|
|
|
* @return static |
898
|
|
|
*/ |
899
|
|
|
public function orWhereNot($key, $operator = null, $value = null) |
900
|
|
|
{ |
901
|
|
|
// If two params are given then assume operator is = |
902
|
|
|
if (2 === func_num_args()) { |
903
|
|
|
$value = $operator; |
904
|
|
|
$operator = '='; |
905
|
|
|
} |
906
|
|
|
|
907
|
|
|
return $this->whereHandler($key, $operator, $value, 'OR NOT'); |
908
|
|
|
} |
909
|
|
|
|
910
|
|
|
/** |
911
|
|
|
* @param string|Raw $key |
912
|
|
|
* @param mixed[]|string|Raw $values |
913
|
|
|
* |
914
|
|
|
* @return static |
915
|
|
|
*/ |
916
|
|
|
public function whereIn($key, $values): self |
917
|
|
|
{ |
918
|
|
|
return $this->whereHandler($key, 'IN', $values, 'AND'); |
919
|
|
|
} |
920
|
|
|
|
921
|
|
|
/** |
922
|
|
|
* @param string|Raw $key |
923
|
|
|
* @param mixed[]|string|Raw $values |
924
|
|
|
* |
925
|
|
|
* @return static |
926
|
|
|
*/ |
927
|
|
|
public function whereNotIn($key, $values): self |
928
|
|
|
{ |
929
|
|
|
return $this->whereHandler($key, 'NOT IN', $values, 'AND'); |
930
|
|
|
} |
931
|
|
|
|
932
|
|
|
/** |
933
|
|
|
* @param string|Raw $key |
934
|
|
|
* @param mixed[]|string|Raw $values |
935
|
|
|
* |
936
|
|
|
* @return static |
937
|
|
|
*/ |
938
|
|
|
public function orWhereIn($key, $values): self |
939
|
|
|
{ |
940
|
|
|
return $this->whereHandler($key, 'IN', $values, 'OR'); |
941
|
|
|
} |
942
|
|
|
|
943
|
|
|
/** |
944
|
|
|
* @param string|Raw $key |
945
|
|
|
* @param mixed[]|string|Raw $values |
946
|
|
|
* |
947
|
|
|
* @return static |
948
|
|
|
*/ |
949
|
|
|
public function orWhereNotIn($key, $values): self |
950
|
|
|
{ |
951
|
|
|
return $this->whereHandler($key, 'NOT IN', $values, 'OR'); |
952
|
|
|
} |
953
|
|
|
|
954
|
|
|
/** |
955
|
|
|
* @param string|Raw $key |
956
|
|
|
* @param mixed $valueFrom |
957
|
|
|
* @param mixed $valueTo |
958
|
|
|
* |
959
|
|
|
* @return static |
960
|
|
|
*/ |
961
|
|
|
public function whereBetween($key, $valueFrom, $valueTo): self |
962
|
|
|
{ |
963
|
|
|
return $this->whereHandler($key, 'BETWEEN', [$valueFrom, $valueTo], 'AND'); |
964
|
|
|
} |
965
|
|
|
|
966
|
|
|
/** |
967
|
|
|
* @param string|Raw $key |
968
|
|
|
* @param mixed $valueFrom |
969
|
|
|
* @param mixed $valueTo |
970
|
|
|
* |
971
|
|
|
* @return static |
972
|
|
|
*/ |
973
|
|
|
public function orWhereBetween($key, $valueFrom, $valueTo): self |
974
|
|
|
{ |
975
|
|
|
return $this->whereHandler($key, 'BETWEEN', [$valueFrom, $valueTo], 'OR'); |
976
|
|
|
} |
977
|
|
|
|
978
|
|
|
/** |
979
|
|
|
* Handles all function call based where conditions |
980
|
|
|
* |
981
|
|
|
* @param string|Raw $key |
982
|
|
|
* @param string $function |
983
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
984
|
|
|
* @param mixed|null $value |
985
|
|
|
* @return static |
986
|
|
|
*/ |
987
|
|
|
protected function whereFunctionCallHandler($key, $function, $operator, $value): self |
988
|
|
|
{ |
989
|
|
|
$key = \sprintf('%s(%s)', $function, $this->addTablePrefix($key)); |
|
|
|
|
990
|
|
|
return $this->where($key, $operator, $value); |
991
|
|
|
} |
992
|
|
|
|
993
|
|
|
/** |
994
|
|
|
* @param string|Raw $key |
995
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
996
|
|
|
* @param mixed|null $value |
997
|
|
|
* @return self |
998
|
|
|
*/ |
999
|
|
|
public function whereMonth($key, $operator = null, $value = null): self |
1000
|
|
|
{ |
1001
|
|
|
// If two params are given then assume operator is = |
1002
|
|
|
if (2 === func_num_args()) { |
1003
|
|
|
$value = $operator; |
1004
|
|
|
$operator = '='; |
1005
|
|
|
} |
1006
|
|
|
return $this->whereFunctionCallHandler($key, 'MONTH', $operator, $value); |
1007
|
|
|
} |
1008
|
|
|
|
1009
|
|
|
/** |
1010
|
|
|
* @param string|Raw $key |
1011
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
1012
|
|
|
* @param mixed|null $value |
1013
|
|
|
* @return self |
1014
|
|
|
*/ |
1015
|
|
|
public function whereDay($key, $operator = null, $value = null): self |
1016
|
|
|
{ |
1017
|
|
|
// If two params are given then assume operator is = |
1018
|
|
|
if (2 === func_num_args()) { |
1019
|
|
|
$value = $operator; |
1020
|
|
|
$operator = '='; |
1021
|
|
|
} |
1022
|
|
|
return $this->whereFunctionCallHandler($key, 'DAY', $operator, $value); |
1023
|
|
|
} |
1024
|
|
|
|
1025
|
|
|
/** |
1026
|
|
|
* @param string|Raw $key |
1027
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
1028
|
|
|
* @param mixed|null $value |
1029
|
|
|
* @return self |
1030
|
|
|
*/ |
1031
|
|
|
public function whereYear($key, $operator = null, $value = null): self |
1032
|
|
|
{ |
1033
|
|
|
// If two params are given then assume operator is = |
1034
|
|
|
if (2 === func_num_args()) { |
1035
|
|
|
$value = $operator; |
1036
|
|
|
$operator = '='; |
1037
|
|
|
} |
1038
|
|
|
return $this->whereFunctionCallHandler($key, 'YEAR', $operator, $value); |
1039
|
|
|
} |
1040
|
|
|
|
1041
|
|
|
/** |
1042
|
|
|
* @param string|Raw $key |
1043
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
1044
|
|
|
* @param mixed|null $value |
1045
|
|
|
* @return self |
1046
|
|
|
*/ |
1047
|
|
|
public function whereDate($key, $operator = null, $value = null): self |
1048
|
|
|
{ |
1049
|
|
|
// If two params are given then assume operator is = |
1050
|
|
|
if (2 === func_num_args()) { |
1051
|
|
|
$value = $operator; |
1052
|
|
|
$operator = '='; |
1053
|
|
|
} |
1054
|
|
|
return $this->whereFunctionCallHandler($key, 'DATE', $operator, $value); |
1055
|
|
|
} |
1056
|
|
|
|
1057
|
|
|
/** |
1058
|
|
|
* @param string|Raw $key |
1059
|
|
|
* |
1060
|
|
|
* @return static |
1061
|
|
|
*/ |
1062
|
|
|
public function whereNull($key): self |
1063
|
|
|
{ |
1064
|
|
|
return $this->whereNullHandler($key); |
1065
|
|
|
} |
1066
|
|
|
|
1067
|
|
|
/** |
1068
|
|
|
* @param string|Raw $key |
1069
|
|
|
* |
1070
|
|
|
* @return static |
1071
|
|
|
*/ |
1072
|
|
|
public function whereNotNull($key): self |
1073
|
|
|
{ |
1074
|
|
|
return $this->whereNullHandler($key, 'NOT'); |
1075
|
|
|
} |
1076
|
|
|
|
1077
|
|
|
/** |
1078
|
|
|
* @param string|Raw $key |
1079
|
|
|
* |
1080
|
|
|
* @return static |
1081
|
|
|
*/ |
1082
|
|
|
public function orWhereNull($key): self |
1083
|
|
|
{ |
1084
|
|
|
return $this->whereNullHandler($key, '', 'or'); |
1085
|
|
|
} |
1086
|
|
|
|
1087
|
|
|
/** |
1088
|
|
|
* @param string|Raw $key |
1089
|
|
|
* |
1090
|
|
|
* @return static |
1091
|
|
|
*/ |
1092
|
|
|
public function orWhereNotNull($key): self |
1093
|
|
|
{ |
1094
|
|
|
return $this->whereNullHandler($key, 'NOT', 'or'); |
1095
|
|
|
} |
1096
|
|
|
|
1097
|
|
|
/** |
1098
|
|
|
* @param string|Raw $key |
1099
|
|
|
* @param string $prefix |
1100
|
|
|
* @param string $operator |
1101
|
|
|
* |
1102
|
|
|
* @return static |
1103
|
|
|
*/ |
1104
|
|
|
protected function whereNullHandler($key, string $prefix = '', $operator = ''): self |
1105
|
|
|
{ |
1106
|
|
|
$prefix = 0 === mb_strlen($prefix) ? '' : " {$prefix}"; |
1107
|
|
|
|
1108
|
|
|
if ($key instanceof Raw) { |
1109
|
|
|
$key = $this->adapterInstance->parseRaw($key); |
1110
|
|
|
} |
1111
|
|
|
|
1112
|
|
|
$key = $this->adapterInstance->wrapSanitizer($this->addTablePrefix($key)); |
|
|
|
|
1113
|
|
|
if ($key instanceof Closure) { |
1114
|
|
|
throw new Exception('Key used for whereNull condition must be a string or raw exrpession.', 1); |
1115
|
|
|
} |
1116
|
|
|
|
1117
|
|
|
return $this->{$operator . 'Where'}($this->raw("{$key} IS{$prefix} NULL")); |
1118
|
|
|
} |
1119
|
|
|
|
1120
|
|
|
/** |
1121
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
1122
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
1123
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
1124
|
|
|
* @param mixed|null $value |
1125
|
|
|
* @return static |
1126
|
|
|
*/ |
1127
|
|
|
public function whereJson($key, $jsonKey, $operator = null, $value = null): self |
1128
|
|
|
{ |
1129
|
|
|
// If two params are given then assume operator is = |
1130
|
|
|
if (3 === func_num_args()) { |
1131
|
|
|
$value = $operator; |
1132
|
|
|
$operator = '='; |
1133
|
|
|
} |
1134
|
|
|
|
1135
|
|
|
// Handle potential raw values. |
1136
|
|
|
if ($key instanceof Raw) { |
1137
|
|
|
$key = $this->adapterInstance->parseRaw($key); |
1138
|
|
|
} |
1139
|
|
|
if ($jsonKey instanceof Raw) { |
1140
|
|
|
$jsonKey = $this->adapterInstance->parseRaw($jsonKey); |
1141
|
|
|
} |
1142
|
|
|
|
1143
|
|
|
// If deeply nested jsonKey. |
1144
|
|
|
if (is_array($jsonKey)) { |
1145
|
|
|
$jsonKey = \implode('.', $jsonKey); |
1146
|
|
|
} |
1147
|
|
|
|
1148
|
|
|
// Add any possible prefixes to the key |
1149
|
|
|
$key = $this->addTablePrefix($key, true); |
1150
|
|
|
|
1151
|
|
|
return $this->where( |
1152
|
|
|
new Raw("JSON_UNQUOTE(JSON_EXTRACT({$key}, \"$.{$jsonKey}\"))"), |
1153
|
|
|
$operator, |
1154
|
|
|
$value |
1155
|
|
|
); |
1156
|
|
|
} |
1157
|
|
|
|
1158
|
|
|
/** |
1159
|
|
|
* @param string|Raw $table |
1160
|
|
|
* @param string|Raw|Closure $key |
1161
|
|
|
* @param string|null $operator |
1162
|
|
|
* @param mixed $value |
1163
|
|
|
* @param string $type |
1164
|
|
|
* |
1165
|
|
|
* @return static |
1166
|
|
|
*/ |
1167
|
|
|
public function join($table, $key, ?string $operator = null, $value = null, $type = 'inner') |
1168
|
|
|
{ |
1169
|
|
|
if (!$key instanceof Closure) { |
1170
|
|
|
$key = function ($joinBuilder) use ($key, $operator, $value) { |
1171
|
|
|
$joinBuilder->on($key, $operator, $value); |
1172
|
|
|
}; |
1173
|
|
|
} |
1174
|
|
|
|
1175
|
|
|
// Build a new JoinBuilder class, keep it by reference so any changes made |
1176
|
|
|
// in the closure should reflect here |
1177
|
|
|
$joinBuilder = $this->container->build(JoinBuilder::class, [$this->connection]); |
1178
|
|
|
$joinBuilder = &$joinBuilder; |
1179
|
|
|
// Call the closure with our new joinBuilder object |
1180
|
|
|
$key($joinBuilder); |
1181
|
|
|
$table = $this->addTablePrefix($table, false); |
1182
|
|
|
// Get the criteria only query from the joinBuilder object |
1183
|
|
|
$this->statements['joins'][] = compact('type', 'table', 'joinBuilder'); |
1184
|
|
|
return $this; |
1185
|
|
|
} |
1186
|
|
|
|
1187
|
|
|
/** |
1188
|
|
|
* Runs a transaction |
1189
|
|
|
* |
1190
|
|
|
* @param \Closure(Transaction):void $callback |
1191
|
|
|
* |
1192
|
|
|
* @return static |
1193
|
|
|
*/ |
1194
|
|
|
public function transaction(Closure $callback): self |
1195
|
|
|
{ |
1196
|
|
|
try { |
1197
|
|
|
// Begin the transaction |
1198
|
|
|
$this->dbInstance->query('START TRANSACTION'); |
1199
|
|
|
|
1200
|
|
|
// Get the Transaction class |
1201
|
|
|
$transaction = $this->container->build(Transaction::class, [$this->connection]); |
1202
|
|
|
|
1203
|
|
|
$this->handleTransactionCall($callback, $transaction); |
1204
|
|
|
|
1205
|
|
|
// If no errors have been thrown or the transaction wasn't completed within |
1206
|
|
|
$this->dbInstance->query('COMMIT'); |
1207
|
|
|
|
1208
|
|
|
return $this; |
1209
|
|
|
} catch (TransactionHaltException $e) { |
1210
|
|
|
// Commit or rollback behavior has been handled in the closure, so exit |
1211
|
|
|
return $this; |
1212
|
|
|
} catch (\Exception $e) { |
1213
|
|
|
// something happened, rollback changes |
1214
|
|
|
$this->dbInstance->query('ROLLBACK'); |
1215
|
|
|
|
1216
|
|
|
return $this; |
1217
|
|
|
} |
1218
|
|
|
} |
1219
|
|
|
|
1220
|
|
|
/** |
1221
|
|
|
* Handles the transaction call. |
1222
|
|
|
* |
1223
|
|
|
* Catches any WP Errors (printed) |
1224
|
|
|
* |
1225
|
|
|
* @param Closure $callback |
1226
|
|
|
* @param Transaction $transaction |
1227
|
|
|
* |
1228
|
|
|
* @return void |
1229
|
|
|
* |
1230
|
|
|
* @throws Exception |
1231
|
|
|
*/ |
1232
|
|
|
protected function handleTransactionCall(Closure $callback, Transaction $transaction): void |
1233
|
|
|
{ |
1234
|
|
|
try { |
1235
|
|
|
ob_start(); |
1236
|
|
|
$callback($transaction); |
1237
|
|
|
$output = ob_get_clean() ?: ''; |
1238
|
|
|
} catch (Throwable $th) { |
1239
|
|
|
ob_end_clean(); |
1240
|
|
|
throw $th; |
1241
|
|
|
} |
1242
|
|
|
|
1243
|
|
|
// If we caught an error, throw an exception. |
1244
|
|
|
if (0 !== mb_strlen($output)) { |
1245
|
|
|
throw new Exception($output); |
1246
|
|
|
} |
1247
|
|
|
} |
1248
|
|
|
|
1249
|
|
|
/** |
1250
|
|
|
* @param string|Raw $table |
1251
|
|
|
* @param string|Raw|Closure $key |
1252
|
|
|
* @param string|null $operator |
1253
|
|
|
* @param mixed $value |
1254
|
|
|
* |
1255
|
|
|
* @return static |
1256
|
|
|
*/ |
1257
|
|
|
public function leftJoin($table, $key, $operator = null, $value = null) |
1258
|
|
|
{ |
1259
|
|
|
return $this->join($table, $key, $operator, $value, 'left'); |
1260
|
|
|
} |
1261
|
|
|
|
1262
|
|
|
/** |
1263
|
|
|
* @param string|Raw $table |
1264
|
|
|
* @param string|Raw|Closure $key |
1265
|
|
|
* @param string|null $operator |
1266
|
|
|
* @param mixed $value |
1267
|
|
|
* |
1268
|
|
|
* @return static |
1269
|
|
|
*/ |
1270
|
|
|
public function rightJoin($table, $key, $operator = null, $value = null) |
1271
|
|
|
{ |
1272
|
|
|
return $this->join($table, $key, $operator, $value, 'right'); |
1273
|
|
|
} |
1274
|
|
|
|
1275
|
|
|
/** |
1276
|
|
|
* @param string|Raw $table |
1277
|
|
|
* @param string|Raw|Closure $key |
1278
|
|
|
* @param string|null $operator |
1279
|
|
|
* @param mixed $value |
1280
|
|
|
* |
1281
|
|
|
* @return static |
1282
|
|
|
*/ |
1283
|
|
|
public function innerJoin($table, $key, $operator = null, $value = null) |
1284
|
|
|
{ |
1285
|
|
|
return $this->join($table, $key, $operator, $value, 'inner'); |
1286
|
|
|
} |
1287
|
|
|
|
1288
|
|
|
/** |
1289
|
|
|
* @param string|Raw $table |
1290
|
|
|
* @param string|Raw|Closure $key |
1291
|
|
|
* @param string|null $operator |
1292
|
|
|
* @param mixed $value |
1293
|
|
|
* |
1294
|
|
|
* @return static |
1295
|
|
|
*/ |
1296
|
|
|
public function crossJoin($table, $key, $operator = null, $value = null) |
1297
|
|
|
{ |
1298
|
|
|
return $this->join($table, $key, $operator, $value, 'cross'); |
1299
|
|
|
} |
1300
|
|
|
|
1301
|
|
|
/** |
1302
|
|
|
* @param string|Raw $table |
1303
|
|
|
* @param string|Raw|Closure $key |
1304
|
|
|
* @param string|null $operator |
1305
|
|
|
* @param mixed $value |
1306
|
|
|
* |
1307
|
|
|
* @return static |
1308
|
|
|
*/ |
1309
|
|
|
public function outerJoin($table, $key, $operator = null, $value = null) |
1310
|
|
|
{ |
1311
|
|
|
return $this->join($table, $key, $operator, $value, 'outer'); |
1312
|
|
|
} |
1313
|
|
|
|
1314
|
|
|
/** |
1315
|
|
|
* Shortcut to join 2 tables on the same key name with equals |
1316
|
|
|
* |
1317
|
|
|
* @param string $table |
1318
|
|
|
* @param string $key |
1319
|
|
|
* @param string $type |
1320
|
|
|
* @return self |
1321
|
|
|
* @throws Exception If base table is set as more than 1 or 0 |
1322
|
|
|
*/ |
1323
|
|
|
public function joinUsing(string $table, string $key, string $type = 'INNER'): self |
1324
|
|
|
{ |
1325
|
|
|
if (!array_key_exists('tables', $this->statements) || count($this->statements['tables']) !== 1) { |
1326
|
|
|
throw new Exception("JoinUsing can only be used with a single table set as the base of the query", 1); |
1327
|
|
|
} |
1328
|
|
|
$baseTable = end($this->statements['tables']); |
1329
|
|
|
|
1330
|
|
|
$remoteKey = $table = $this->addTablePrefix("{$table}.{$key}", true); |
|
|
|
|
1331
|
|
|
$localKey = $table = $this->addTablePrefix("{$baseTable}.{$key}", true); |
1332
|
|
|
return $this->join($table, $remoteKey, '=', $localKey, $type); |
|
|
|
|
1333
|
|
|
} |
1334
|
|
|
|
1335
|
|
|
/** |
1336
|
|
|
* Add a raw query |
1337
|
|
|
* |
1338
|
|
|
* @param string|Raw $value |
1339
|
|
|
* @param mixed|mixed[] $bindings |
1340
|
|
|
* |
1341
|
|
|
* @return Raw |
1342
|
|
|
*/ |
1343
|
|
|
public function raw($value, $bindings = []): Raw |
1344
|
|
|
{ |
1345
|
|
|
return new Raw($value, $bindings); |
1346
|
|
|
} |
1347
|
|
|
|
1348
|
|
|
/** |
1349
|
|
|
* Return wpdb instance |
1350
|
|
|
* |
1351
|
|
|
* @return wpdb |
1352
|
|
|
*/ |
1353
|
|
|
public function dbInstance(): wpdb |
1354
|
|
|
{ |
1355
|
|
|
return $this->dbInstance; |
1356
|
|
|
} |
1357
|
|
|
|
1358
|
|
|
/** |
1359
|
|
|
* @param Connection $connection |
1360
|
|
|
* |
1361
|
|
|
* @return static |
1362
|
|
|
*/ |
1363
|
|
|
public function setConnection(Connection $connection): self |
1364
|
|
|
{ |
1365
|
|
|
$this->connection = $connection; |
1366
|
|
|
|
1367
|
|
|
return $this; |
1368
|
|
|
} |
1369
|
|
|
|
1370
|
|
|
/** |
1371
|
|
|
* @return Connection |
1372
|
|
|
*/ |
1373
|
|
|
public function getConnection() |
1374
|
|
|
{ |
1375
|
|
|
return $this->connection; |
1376
|
|
|
} |
1377
|
|
|
|
1378
|
|
|
/** |
1379
|
|
|
* @param string|Raw|Closure $key |
1380
|
|
|
* @param string|null $operator |
1381
|
|
|
* @param mixed|null $value |
1382
|
|
|
* @param string $joiner |
1383
|
|
|
* |
1384
|
|
|
* @return static |
1385
|
|
|
*/ |
1386
|
|
|
protected function whereHandler($key, $operator = null, $value = null, $joiner = 'AND') |
1387
|
|
|
{ |
1388
|
|
|
if ($key instanceof Raw) { |
1389
|
|
|
$key = $this->adapterInstance->parseRaw($key); |
1390
|
|
|
} |
1391
|
|
|
$key = $this->addTablePrefix($key); |
1392
|
|
|
$this->statements['wheres'][] = compact('key', 'operator', 'value', 'joiner'); |
1393
|
|
|
return $this; |
1394
|
|
|
} |
1395
|
|
|
|
1396
|
|
|
/** |
1397
|
|
|
* Add table prefix (if given) on given string. |
1398
|
|
|
* |
1399
|
|
|
* @param array<string|int, string|int|float|bool|Raw|Closure>|string|int|float|bool|Raw|Closure $values |
1400
|
|
|
* @param bool $tableFieldMix If we have mixes of field and table names with a "." |
1401
|
|
|
* |
1402
|
|
|
* @return mixed|mixed[] |
1403
|
|
|
*/ |
1404
|
|
|
public function addTablePrefix($values, bool $tableFieldMix = true) |
1405
|
|
|
{ |
1406
|
|
|
if (is_null($this->tablePrefix)) { |
1407
|
|
|
return $values; |
1408
|
|
|
} |
1409
|
|
|
|
1410
|
|
|
// $value will be an array and we will add prefix to all table names |
1411
|
|
|
|
1412
|
|
|
// If supplied value is not an array then make it one |
1413
|
|
|
$single = false; |
1414
|
|
|
if (!is_array($values)) { |
1415
|
|
|
$values = [$values]; |
1416
|
|
|
// We had single value, so should return a single value |
1417
|
|
|
$single = true; |
1418
|
|
|
} |
1419
|
|
|
|
1420
|
|
|
$return = []; |
1421
|
|
|
|
1422
|
|
|
foreach ($values as $key => $value) { |
1423
|
|
|
// It's a raw query, just add it to our return array and continue next |
1424
|
|
|
if ($value instanceof Raw || $value instanceof Closure) { |
1425
|
|
|
$return[$key] = $value; |
1426
|
|
|
continue; |
1427
|
|
|
} |
1428
|
|
|
|
1429
|
|
|
// If key is not integer, it is likely a alias mapping, |
1430
|
|
|
// so we need to change prefix target |
1431
|
|
|
$target = &$value; |
1432
|
|
|
if (!is_int($key)) { |
1433
|
|
|
$target = &$key; |
1434
|
|
|
} |
1435
|
|
|
|
1436
|
|
|
// Do prefix if the target is an expression or function. |
1437
|
|
|
if ( |
1438
|
|
|
!$tableFieldMix |
1439
|
|
|
|| ( |
1440
|
|
|
is_string($target) // Must be a string |
1441
|
|
|
&& (bool) preg_match('/^[A-Za-z0-9_.]+$/', $target) // Can only contain letters, numbers, underscore and full stops |
1442
|
|
|
&& 1 === \substr_count($target, '.') // Contains a single full stop ONLY. |
1443
|
|
|
) |
1444
|
|
|
) { |
1445
|
|
|
$target = $this->tablePrefix . $target; |
1446
|
|
|
} |
1447
|
|
|
|
1448
|
|
|
$return[$key] = $value; |
1449
|
|
|
} |
1450
|
|
|
|
1451
|
|
|
// If we had single value then we should return a single value (end value of the array) |
1452
|
|
|
return true === $single ? end($return) : $return; |
1453
|
|
|
} |
1454
|
|
|
|
1455
|
|
|
/** |
1456
|
|
|
* @param string $key |
1457
|
|
|
* @param mixed|mixed[]|bool $value |
1458
|
|
|
* |
1459
|
|
|
* @return void |
1460
|
|
|
*/ |
1461
|
|
|
protected function addStatement($key, $value) |
1462
|
|
|
{ |
1463
|
|
|
if (!is_array($value)) { |
1464
|
|
|
$value = [$value]; |
1465
|
|
|
} |
1466
|
|
|
|
1467
|
|
|
if (!array_key_exists($key, $this->statements)) { |
1468
|
|
|
$this->statements[$key] = $value; |
1469
|
|
|
} else { |
1470
|
|
|
$this->statements[$key] = array_merge($this->statements[$key], $value); |
1471
|
|
|
} |
1472
|
|
|
} |
1473
|
|
|
|
1474
|
|
|
/** |
1475
|
|
|
* @param string $event |
1476
|
|
|
* @param string|Raw $table |
1477
|
|
|
* |
1478
|
|
|
* @return callable|null |
1479
|
|
|
*/ |
1480
|
|
|
public function getEvent(string $event, $table = ':any'): ?callable |
1481
|
|
|
{ |
1482
|
|
|
return $this->connection->getEventHandler()->getEvent($event, $table); |
1483
|
|
|
} |
1484
|
|
|
|
1485
|
|
|
/** |
1486
|
|
|
* @param string $event |
1487
|
|
|
* @param string|Raw $table |
1488
|
|
|
* @param Closure $action |
1489
|
|
|
* |
1490
|
|
|
* @return void |
1491
|
|
|
*/ |
1492
|
|
|
public function registerEvent($event, $table, Closure $action): void |
1493
|
|
|
{ |
1494
|
|
|
$table = $table ?: ':any'; |
1495
|
|
|
|
1496
|
|
|
if (':any' != $table) { |
1497
|
|
|
$table = $this->addTablePrefix($table, false); |
1498
|
|
|
} |
1499
|
|
|
|
1500
|
|
|
$this->connection->getEventHandler()->registerEvent($event, $table, $action); |
|
|
|
|
1501
|
|
|
} |
1502
|
|
|
|
1503
|
|
|
/** |
1504
|
|
|
* @param string $event |
1505
|
|
|
* @param string|Raw $table |
1506
|
|
|
* |
1507
|
|
|
* @return void |
1508
|
|
|
*/ |
1509
|
|
|
public function removeEvent(string $event, $table = ':any') |
1510
|
|
|
{ |
1511
|
|
|
if (':any' != $table) { |
1512
|
|
|
$table = $this->addTablePrefix($table, false); |
1513
|
|
|
} |
1514
|
|
|
|
1515
|
|
|
$this->connection->getEventHandler()->removeEvent($event, $table); |
|
|
|
|
1516
|
|
|
} |
1517
|
|
|
|
1518
|
|
|
/** |
1519
|
|
|
* @param string $event |
1520
|
|
|
* |
1521
|
|
|
* @return mixed |
1522
|
|
|
*/ |
1523
|
|
|
public function fireEvents(string $event) |
1524
|
|
|
{ |
1525
|
|
|
$params = func_get_args(); // @todo Replace this with an easier to read alteratnive |
1526
|
|
|
array_unshift($params, $this); |
1527
|
|
|
|
1528
|
|
|
return call_user_func_array([$this->connection->getEventHandler(), 'fireEvents'], $params); |
1529
|
|
|
} |
1530
|
|
|
|
1531
|
|
|
/** |
1532
|
|
|
* @return array<string, mixed[]> |
1533
|
|
|
*/ |
1534
|
|
|
public function getStatements() |
1535
|
|
|
{ |
1536
|
|
|
return $this->statements; |
1537
|
|
|
} |
1538
|
|
|
|
1539
|
|
|
/** |
1540
|
|
|
* @return string will return WPDB Fetch mode |
1541
|
|
|
*/ |
1542
|
|
|
public function getFetchMode() |
1543
|
|
|
{ |
1544
|
|
|
return null !== $this->fetchMode |
1545
|
|
|
? $this->fetchMode |
1546
|
|
|
: \OBJECT; |
1547
|
|
|
} |
1548
|
|
|
|
1549
|
|
|
// JSON |
1550
|
|
|
|
1551
|
|
|
/** |
1552
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
1553
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
1554
|
|
|
* @param string|null $alias The alias used to define the value in results, if not defined will use json_{$jsonKey} |
1555
|
|
|
* @return static |
1556
|
|
|
*/ |
1557
|
|
|
public function selectJson($key, $jsonKey, ?string $alias = null): self |
1558
|
|
|
{ |
1559
|
|
|
// Handle potential raw values. |
1560
|
|
|
if ($key instanceof Raw) { |
1561
|
|
|
$key = $this->adapterInstance->parseRaw($key); |
1562
|
|
|
} |
1563
|
|
|
if ($jsonKey instanceof Raw) { |
1564
|
|
|
$jsonKey = $this->adapterInstance->parseRaw($jsonKey); |
1565
|
|
|
} |
1566
|
|
|
|
1567
|
|
|
// If deeply nested jsonKey. |
1568
|
|
|
if (is_array($jsonKey)) { |
1569
|
|
|
$jsonKey = \implode('.', $jsonKey); |
1570
|
|
|
} |
1571
|
|
|
|
1572
|
|
|
// Add any possible prefixes to the key |
1573
|
|
|
$key = $this->addTablePrefix($key, true); |
1574
|
|
|
|
1575
|
|
|
$alias = null === $alias ? "json_{$jsonKey}" : $alias; |
1576
|
|
|
return $this->select(new Raw("JSON_UNQUOTE(JSON_EXTRACT({$key}, \"$.{$jsonKey}\")) as {$alias}")); |
1577
|
|
|
} |
1578
|
|
|
} |
1579
|
|
|
// 'JSON_EXTRACT(json, "$.id") as jsonID' |
1580
|
|
|
|