1
|
|
|
<?php declare(strict_types=1); |
2
|
|
|
/** |
3
|
|
|
* Query |
4
|
|
|
* |
5
|
|
|
* SQL Query Builder / Database Abstraction Layer |
6
|
|
|
* |
7
|
|
|
* PHP version 7.1 |
8
|
|
|
* |
9
|
|
|
* @package Query |
10
|
|
|
* @author Timothy J. Warren <[email protected]> |
11
|
|
|
* @copyright 2012 - 2018 Timothy J. Warren |
12
|
|
|
* @license http://www.opensource.org/licenses/mit-license.html MIT License |
13
|
|
|
* @link https://git.timshomepage.net/aviat4ion/Query |
14
|
|
|
*/ |
15
|
|
|
namespace Query; |
16
|
|
|
|
17
|
|
|
use BadMethodCallException; |
18
|
|
|
use PDOStatement; |
19
|
|
|
use Query\Drivers\{ |
20
|
|
|
AbstractUtil, |
21
|
|
|
DriverInterface, |
22
|
|
|
SQLInterface |
23
|
|
|
}; |
24
|
|
|
|
25
|
|
|
/** |
26
|
|
|
* Convenience class for creating sql queries |
27
|
|
|
* @method query(mixed $sql): PDOStatement; |
28
|
|
|
*/ |
29
|
|
|
class QueryBuilder implements QueryBuilderInterface { |
30
|
|
|
|
31
|
|
|
// -------------------------------------------------------------------------- |
32
|
|
|
// ! Constants |
33
|
|
|
// -------------------------------------------------------------------------- |
34
|
|
|
|
35
|
|
|
const KEY = 0; |
36
|
|
|
const VALUE = 1; |
37
|
|
|
const BOTH = 2; |
38
|
|
|
|
39
|
|
|
// -------------------------------------------------------------------------- |
40
|
|
|
// ! SQL Clause Strings |
41
|
|
|
// -------------------------------------------------------------------------- |
42
|
|
|
|
43
|
|
|
/** |
44
|
|
|
* Compiled 'select' clause |
45
|
|
|
* @var string |
46
|
|
|
*/ |
47
|
|
|
protected $selectString = ''; |
48
|
|
|
|
49
|
|
|
/** |
50
|
|
|
* Compiled 'from' clause |
51
|
|
|
* @var string |
52
|
|
|
*/ |
53
|
|
|
protected $fromString = ''; |
54
|
|
|
|
55
|
|
|
/** |
56
|
|
|
* Compiled arguments for insert / update |
57
|
|
|
* @var string |
58
|
|
|
*/ |
59
|
|
|
protected $setString; |
60
|
|
|
|
61
|
|
|
/** |
62
|
|
|
* Order by clause |
63
|
|
|
* @var string |
64
|
|
|
*/ |
65
|
|
|
protected $orderString; |
66
|
|
|
|
67
|
|
|
/** |
68
|
|
|
* Group by clause |
69
|
|
|
* @var string |
70
|
|
|
*/ |
71
|
|
|
protected $groupString; |
72
|
|
|
|
73
|
|
|
// -------------------------------------------------------------------------- |
74
|
|
|
// ! SQL Clause Arrays |
75
|
|
|
// -------------------------------------------------------------------------- |
76
|
|
|
|
77
|
|
|
/** |
78
|
|
|
* Keys for insert/update statement |
79
|
|
|
* @var array |
80
|
|
|
*/ |
81
|
|
|
protected $setArrayKeys = []; |
82
|
|
|
|
83
|
|
|
/** |
84
|
|
|
* Key/val pairs for order by clause |
85
|
|
|
* @var array |
86
|
|
|
*/ |
87
|
|
|
protected $orderArray = []; |
88
|
|
|
|
89
|
|
|
/** |
90
|
|
|
* Key/val pairs for group by clause |
91
|
|
|
* @var array |
92
|
|
|
*/ |
93
|
|
|
protected $groupArray = []; |
94
|
|
|
|
95
|
|
|
// -------------------------------------------------------------------------- |
96
|
|
|
// ! Other Class vars |
97
|
|
|
// -------------------------------------------------------------------------- |
98
|
|
|
|
99
|
|
|
/** |
100
|
|
|
* Values to apply to prepared statements |
101
|
|
|
* @var array |
102
|
|
|
*/ |
103
|
|
|
protected $values = []; |
104
|
|
|
|
105
|
|
|
/** |
106
|
|
|
* Values to apply to where clauses in prepared statements |
107
|
|
|
* @var array |
108
|
|
|
*/ |
109
|
|
|
protected $whereValues = []; |
110
|
|
|
|
111
|
|
|
/** |
112
|
|
|
* Value for limit string |
113
|
|
|
* @var string |
114
|
|
|
*/ |
115
|
|
|
protected $limit; |
116
|
|
|
|
117
|
|
|
/** |
118
|
|
|
* Value for offset in limit string |
119
|
|
|
* @var integer |
120
|
|
|
*/ |
121
|
|
|
protected $offset; |
122
|
|
|
|
123
|
|
|
/** |
124
|
|
|
* Query component order mapping |
125
|
|
|
* for complex select queries |
126
|
|
|
* |
127
|
|
|
* Format: |
128
|
|
|
* array( |
129
|
|
|
* 'type' => 'where', |
130
|
|
|
* 'conjunction' => ' AND ', |
131
|
|
|
* 'string' => 'k=?' |
132
|
|
|
* ) |
133
|
|
|
* |
134
|
|
|
* @var array |
135
|
|
|
*/ |
136
|
|
|
protected $queryMap = []; |
137
|
|
|
|
138
|
|
|
/** |
139
|
|
|
* Map for having clause |
140
|
|
|
* @var array |
141
|
|
|
*/ |
142
|
|
|
protected $havingMap; |
143
|
|
|
|
144
|
|
|
/** |
145
|
|
|
* Convenience property for connection management |
146
|
|
|
* @var string |
147
|
|
|
*/ |
148
|
|
|
public $connName = ''; |
149
|
|
|
|
150
|
|
|
/** |
151
|
|
|
* List of queries executed |
152
|
|
|
* @var array |
153
|
|
|
*/ |
154
|
|
|
public $queries; |
155
|
|
|
|
156
|
|
|
/** |
157
|
|
|
* Whether to do only an explain on the query |
158
|
|
|
* @var boolean |
159
|
|
|
*/ |
160
|
|
|
protected $explain; |
161
|
|
|
|
162
|
|
|
/** |
163
|
|
|
* The current database driver |
164
|
|
|
* @var DriverInterface |
165
|
|
|
*/ |
166
|
|
|
public $driver; |
167
|
|
|
|
168
|
|
|
/** |
169
|
|
|
* Query parser class instance |
170
|
|
|
* @var QueryParser |
171
|
|
|
*/ |
172
|
|
|
protected $parser; |
173
|
|
|
|
174
|
|
|
/** |
175
|
|
|
* Alias to driver util class |
176
|
|
|
* @var AbstractUtil |
177
|
|
|
*/ |
178
|
|
|
protected $util; |
179
|
|
|
|
180
|
|
|
/** |
181
|
|
|
* Alias to driver sql class |
182
|
|
|
* @var SQLInterface |
183
|
|
|
*/ |
184
|
|
|
protected $sql; |
185
|
|
|
|
186
|
|
|
/** |
187
|
|
|
* String class values to be reset |
188
|
|
|
* |
189
|
|
|
* @var array |
190
|
|
|
*/ |
191
|
|
|
private $stringVars = [ |
192
|
|
|
'selectString', |
193
|
|
|
'fromString', |
194
|
|
|
'setString', |
195
|
|
|
'orderString', |
196
|
|
|
'groupString', |
197
|
|
|
'limit', |
198
|
|
|
'offset', |
199
|
|
|
'explain', |
200
|
|
|
]; |
201
|
|
|
|
202
|
|
|
/** |
203
|
|
|
* Array class variables to be reset |
204
|
|
|
* |
205
|
|
|
* @var array |
206
|
|
|
*/ |
207
|
|
|
private $arrayVars = [ |
208
|
|
|
'setArrayKeys', |
209
|
|
|
'orderArray', |
210
|
|
|
'groupArray', |
211
|
|
|
'values', |
212
|
|
|
'whereValues', |
213
|
|
|
'queryMap', |
214
|
|
|
'havingMap' |
215
|
|
|
]; |
216
|
|
|
|
217
|
|
|
// -------------------------------------------------------------------------- |
218
|
|
|
// ! Methods |
219
|
|
|
// -------------------------------------------------------------------------- |
220
|
|
|
|
221
|
|
|
/** |
222
|
|
|
* Constructor |
223
|
|
|
* |
224
|
|
|
* @param DriverInterface $driver |
225
|
|
|
* @param QueryParser $parser |
226
|
|
|
*/ |
227
|
|
|
public function __construct(DriverInterface $driver, QueryParser $parser) |
228
|
|
|
{ |
229
|
|
|
// Inject driver and parser |
230
|
|
|
$this->driver = $driver; |
|
|
|
|
231
|
|
|
$this->parser = $parser; |
232
|
|
|
|
233
|
|
|
$this->queries['total_time'] = 0; |
234
|
|
|
|
235
|
|
|
// Alias driver sql and util classes |
236
|
|
|
$this->sql = $this->driver->getSql(); |
|
|
|
|
237
|
|
|
$this->util = $this->driver->getUtil(); |
|
|
|
|
238
|
|
|
} |
239
|
|
|
|
240
|
|
|
/** |
241
|
|
|
* Destructor |
242
|
|
|
* @codeCoverageIgnore |
243
|
|
|
*/ |
244
|
|
|
public function __destruct() |
245
|
|
|
{ |
246
|
|
|
$this->driver = NULL; |
247
|
|
|
} |
248
|
|
|
|
249
|
|
|
/** |
250
|
|
|
* Calls a function further down the inheritance chain |
251
|
|
|
* |
252
|
|
|
* @param string $name |
253
|
|
|
* @param array $params |
254
|
|
|
* @return mixed |
255
|
|
|
* @throws BadMethodCallException |
256
|
|
|
*/ |
257
|
|
|
public function __call(string $name, array $params) |
258
|
|
|
{ |
259
|
|
|
// Alias snake_case method calls |
260
|
|
|
$camelName = \to_camel_case($name); |
261
|
|
|
|
262
|
|
|
foreach([$this, $this->driver] as $object) |
263
|
|
|
{ |
264
|
|
|
foreach([$name, $camelName] as $methodName) |
265
|
|
|
{ |
266
|
|
|
if (method_exists($object, $methodName)) |
267
|
|
|
{ |
268
|
|
|
return \call_user_func_array([$object, $methodName], $params); |
269
|
|
|
} |
270
|
|
|
} |
271
|
|
|
|
272
|
|
|
} |
273
|
|
|
|
274
|
|
|
throw new BadMethodCallException('Method does not exist'); |
275
|
|
|
} |
276
|
|
|
|
277
|
|
|
// -------------------------------------------------------------------------- |
278
|
|
|
// ! Driver setters |
279
|
|
|
// -------------------------------------------------------------------------- |
280
|
|
|
|
281
|
|
|
public function setDriver(DriverInterface $driver) |
|
|
|
|
282
|
|
|
{ |
283
|
|
|
|
284
|
|
|
} |
285
|
|
|
|
286
|
|
|
// -------------------------------------------------------------------------- |
287
|
|
|
// ! Select Queries |
288
|
|
|
// -------------------------------------------------------------------------- |
289
|
|
|
|
290
|
|
|
/** |
291
|
|
|
* Specifies rows to select in a query |
292
|
|
|
* |
293
|
|
|
* @param string $fields |
294
|
|
|
* @return QueryBuilderInterface |
295
|
|
|
*/ |
296
|
|
|
public function select(string $fields): QueryBuilderInterface |
297
|
|
|
{ |
298
|
|
|
// Split fields by comma |
299
|
|
|
$fieldsArray = explode(',', $fields); |
300
|
|
|
$fieldsArray = array_map('mb_trim', $fieldsArray); |
301
|
|
|
|
302
|
|
|
// Split on 'As' |
303
|
|
|
foreach ($fieldsArray as $key => $field) |
304
|
|
|
{ |
305
|
|
|
if (stripos($field, 'as') !== FALSE) |
306
|
|
|
{ |
307
|
|
|
$fieldsArray[$key] = preg_split('` as `i', $field); |
308
|
|
|
$fieldsArray[$key] = array_map('mb_trim', $fieldsArray[$key]); |
309
|
|
|
} |
310
|
|
|
} |
311
|
|
|
|
312
|
|
|
// Quote the identifiers |
313
|
|
|
$safeArray = $this->driver->quoteIdent($fieldsArray); |
314
|
|
|
|
315
|
|
|
unset($fieldsArray); |
316
|
|
|
|
317
|
|
|
// Join the strings back together |
318
|
|
|
for($i = 0, $c = count($safeArray); $i < $c; $i++) |
319
|
|
|
{ |
320
|
|
|
if (\is_array($safeArray[$i])) |
321
|
|
|
{ |
322
|
|
|
$safeArray[$i] = implode(' AS ', $safeArray[$i]); |
323
|
|
|
} |
324
|
|
|
} |
325
|
|
|
|
326
|
|
|
$this->selectString .= implode(', ', $safeArray); |
327
|
|
|
|
328
|
|
|
unset($safeArray); |
329
|
|
|
|
330
|
|
|
return $this; |
331
|
|
|
} |
332
|
|
|
|
333
|
|
|
/** |
334
|
|
|
* Selects the maximum value of a field from a query |
335
|
|
|
* |
336
|
|
|
* @param string $field |
337
|
|
|
* @param string|bool $as |
338
|
|
|
* @return QueryBuilderInterface |
339
|
|
|
*/ |
340
|
|
|
public function selectMax(string $field, $as=FALSE): QueryBuilderInterface |
341
|
|
|
{ |
342
|
|
|
// Create the select string |
343
|
|
|
$this->selectString .= ' MAX'.$this->_select($field, $as); |
344
|
|
|
return $this; |
345
|
|
|
} |
346
|
|
|
|
347
|
|
|
/** |
348
|
|
|
* Selects the minimum value of a field from a query |
349
|
|
|
* |
350
|
|
|
* @param string $field |
351
|
|
|
* @param string|bool $as |
352
|
|
|
* @return QueryBuilderInterface |
353
|
|
|
*/ |
354
|
|
|
public function selectMin(string $field, $as=FALSE): QueryBuilderInterface |
355
|
|
|
{ |
356
|
|
|
// Create the select string |
357
|
|
|
$this->selectString .= ' MIN'.$this->_select($field, $as); |
358
|
|
|
return $this; |
359
|
|
|
} |
360
|
|
|
|
361
|
|
|
/** |
362
|
|
|
* Selects the average value of a field from a query |
363
|
|
|
* |
364
|
|
|
* @param string $field |
365
|
|
|
* @param string|bool $as |
366
|
|
|
* @return QueryBuilderInterface |
367
|
|
|
*/ |
368
|
|
|
public function selectAvg(string $field, $as=FALSE): QueryBuilderInterface |
369
|
|
|
{ |
370
|
|
|
// Create the select string |
371
|
|
|
$this->selectString .= ' AVG'.$this->_select($field, $as); |
372
|
|
|
return $this; |
373
|
|
|
} |
374
|
|
|
|
375
|
|
|
/** |
376
|
|
|
* Selects the sum of a field from a query |
377
|
|
|
* |
378
|
|
|
* @param string $field |
379
|
|
|
* @param string|bool $as |
380
|
|
|
* @return QueryBuilderInterface |
381
|
|
|
*/ |
382
|
|
|
public function selectSum(string $field, $as=FALSE): QueryBuilderInterface |
383
|
|
|
{ |
384
|
|
|
// Create the select string |
385
|
|
|
$this->selectString .= ' SUM'.$this->_select($field, $as); |
386
|
|
|
return $this; |
387
|
|
|
} |
388
|
|
|
|
389
|
|
|
/** |
390
|
|
|
* Adds the 'distinct' keyword to a query |
391
|
|
|
* |
392
|
|
|
* @return QueryBuilderInterface |
393
|
|
|
*/ |
394
|
|
|
public function distinct(): QueryBuilderInterface |
395
|
|
|
{ |
396
|
|
|
// Prepend the keyword to the select string |
397
|
|
|
$this->selectString = ' DISTINCT '.$this->selectString; |
398
|
|
|
return $this; |
399
|
|
|
} |
400
|
|
|
|
401
|
|
|
/** |
402
|
|
|
* Tell the database to give you the query plan instead of result set |
403
|
|
|
* |
404
|
|
|
* @return QueryBuilderInterface |
405
|
|
|
*/ |
406
|
|
|
public function explain(): QueryBuilderInterface |
407
|
|
|
{ |
408
|
|
|
$this->explain = TRUE; |
409
|
|
|
return $this; |
410
|
|
|
} |
411
|
|
|
|
412
|
|
|
/** |
413
|
|
|
* Specify the database table to select from |
414
|
|
|
* |
415
|
|
|
* @param string $tblname |
416
|
|
|
* @return QueryBuilderInterface |
417
|
|
|
*/ |
418
|
|
|
public function from($tblname): QueryBuilderInterface |
419
|
|
|
{ |
420
|
|
|
// Split identifiers on spaces |
421
|
|
|
$identArray = explode(' ', \mb_trim($tblname)); |
422
|
|
|
$identArray = array_map('\\mb_trim', $identArray); |
423
|
|
|
|
424
|
|
|
// Quote the identifiers |
425
|
|
|
$identArray[0] = $this->driver->quoteTable($identArray[0]); |
426
|
|
|
$identArray = $this->driver->quoteIdent($identArray); |
427
|
|
|
|
428
|
|
|
// Paste it back together |
429
|
|
|
$this->fromString = implode(' ', $identArray); |
430
|
|
|
|
431
|
|
|
return $this; |
432
|
|
|
} |
433
|
|
|
|
434
|
|
|
// -------------------------------------------------------------------------- |
435
|
|
|
// ! 'Like' methods |
436
|
|
|
// -------------------------------------------------------------------------- |
437
|
|
|
|
438
|
|
|
/** |
439
|
|
|
* Creates a Like clause in the sql statement |
440
|
|
|
* |
441
|
|
|
* @param string $field |
442
|
|
|
* @param mixed $val |
443
|
|
|
* @param string $pos |
444
|
|
|
* @return QueryBuilderInterface |
445
|
|
|
*/ |
446
|
|
|
public function like($field, $val, $pos='both'): QueryBuilderInterface |
447
|
|
|
{ |
448
|
|
|
return $this->_like($field, $val, $pos, 'LIKE', 'AND'); |
449
|
|
|
} |
450
|
|
|
|
451
|
|
|
/** |
452
|
|
|
* Generates an OR Like clause |
453
|
|
|
* |
454
|
|
|
* @param string $field |
455
|
|
|
* @param mixed $val |
456
|
|
|
* @param string $pos |
457
|
|
|
* @return QueryBuilderInterface |
458
|
|
|
*/ |
459
|
|
|
public function orLike($field, $val, $pos='both'): QueryBuilderInterface |
460
|
|
|
{ |
461
|
|
|
return $this->_like($field, $val, $pos, 'LIKE', 'OR'); |
462
|
|
|
} |
463
|
|
|
|
464
|
|
|
/** |
465
|
|
|
* Generates a NOT LIKE clause |
466
|
|
|
* |
467
|
|
|
* @param string $field |
468
|
|
|
* @param mixed $val |
469
|
|
|
* @param string $pos |
470
|
|
|
* @return QueryBuilderInterface |
471
|
|
|
*/ |
472
|
|
|
public function notLike($field, $val, $pos='both'): QueryBuilderInterface |
473
|
|
|
{ |
474
|
|
|
return $this->_like($field, $val, $pos, 'NOT LIKE', 'AND'); |
475
|
|
|
} |
476
|
|
|
|
477
|
|
|
/** |
478
|
|
|
* Generates a OR NOT LIKE clause |
479
|
|
|
* |
480
|
|
|
* @param string $field |
481
|
|
|
* @param mixed $val |
482
|
|
|
* @param string $pos |
483
|
|
|
* @return QueryBuilderInterface |
484
|
|
|
*/ |
485
|
|
|
public function orNotLike($field, $val, $pos='both'): QueryBuilderInterface |
486
|
|
|
{ |
487
|
|
|
return $this->_like($field, $val, $pos, 'NOT LIKE', 'OR'); |
488
|
|
|
} |
489
|
|
|
|
490
|
|
|
// -------------------------------------------------------------------------- |
491
|
|
|
// ! Having methods |
492
|
|
|
// -------------------------------------------------------------------------- |
493
|
|
|
|
494
|
|
|
/** |
495
|
|
|
* Generates a 'Having' clause |
496
|
|
|
* |
497
|
|
|
* @param mixed $key |
498
|
|
|
* @param mixed $val |
499
|
|
|
* @return QueryBuilderInterface |
500
|
|
|
*/ |
501
|
|
|
public function having($key, $val=[]): QueryBuilderInterface |
502
|
|
|
{ |
503
|
|
|
return $this->_having($key, $val, 'AND'); |
504
|
|
|
} |
505
|
|
|
|
506
|
|
|
/** |
507
|
|
|
* Generates a 'Having' clause prefixed with 'OR' |
508
|
|
|
* |
509
|
|
|
* @param mixed $key |
510
|
|
|
* @param mixed $val |
511
|
|
|
* @return QueryBuilderInterface |
512
|
|
|
*/ |
513
|
|
|
public function orHaving($key, $val=[]): QueryBuilderInterface |
514
|
|
|
{ |
515
|
|
|
return $this->_having($key, $val, 'OR'); |
516
|
|
|
} |
517
|
|
|
|
518
|
|
|
// -------------------------------------------------------------------------- |
519
|
|
|
// ! 'Where' methods |
520
|
|
|
// -------------------------------------------------------------------------- |
521
|
|
|
|
522
|
|
|
/** |
523
|
|
|
* Specify condition(s) in the where clause of a query |
524
|
|
|
* Note: this function works with key / value, or a |
525
|
|
|
* passed array with key / value pairs |
526
|
|
|
* |
527
|
|
|
* @param mixed $key |
528
|
|
|
* @param mixed $val |
529
|
|
|
* @param mixed $escape |
530
|
|
|
* @return QueryBuilderInterface |
531
|
|
|
*/ |
532
|
|
|
public function where($key, $val=[], $escape=NULL): QueryBuilderInterface |
533
|
|
|
{ |
534
|
|
|
return $this->_whereString($key, $val, 'AND'); |
535
|
|
|
} |
536
|
|
|
|
537
|
|
|
/** |
538
|
|
|
* Where clause prefixed with "OR" |
539
|
|
|
* |
540
|
|
|
* @param string $key |
541
|
|
|
* @param mixed $val |
542
|
|
|
* @return QueryBuilderInterface |
543
|
|
|
*/ |
544
|
|
|
public function orWhere($key, $val=[]): QueryBuilderInterface |
545
|
|
|
{ |
546
|
|
|
return $this->_whereString($key, $val, 'OR'); |
547
|
|
|
} |
548
|
|
|
|
549
|
|
|
/** |
550
|
|
|
* Where clause with 'IN' statement |
551
|
|
|
* |
552
|
|
|
* @param mixed $field |
553
|
|
|
* @param mixed $val |
554
|
|
|
* @return QueryBuilderInterface |
555
|
|
|
*/ |
556
|
|
|
public function whereIn($field, $val=[]): QueryBuilderInterface |
557
|
|
|
{ |
558
|
|
|
return $this->_whereIn($field, $val); |
559
|
|
|
} |
560
|
|
|
|
561
|
|
|
/** |
562
|
|
|
* Where in statement prefixed with "or" |
563
|
|
|
* |
564
|
|
|
* @param string $field |
565
|
|
|
* @param mixed $val |
566
|
|
|
* @return QueryBuilderInterface |
567
|
|
|
*/ |
568
|
|
|
public function orWhereIn($field, $val=[]): QueryBuilderInterface |
569
|
|
|
{ |
570
|
|
|
return $this->_whereIn($field, $val, 'IN', 'OR'); |
571
|
|
|
} |
572
|
|
|
|
573
|
|
|
/** |
574
|
|
|
* WHERE NOT IN (FOO) clause |
575
|
|
|
* |
576
|
|
|
* @param string $field |
577
|
|
|
* @param mixed $val |
578
|
|
|
* @return QueryBuilderInterface |
579
|
|
|
*/ |
580
|
|
|
public function whereNotIn($field, $val=[]): QueryBuilderInterface |
581
|
|
|
{ |
582
|
|
|
return $this->_whereIn($field, $val, 'NOT IN', 'AND'); |
583
|
|
|
} |
584
|
|
|
|
585
|
|
|
/** |
586
|
|
|
* OR WHERE NOT IN (FOO) clause |
587
|
|
|
* |
588
|
|
|
* @param string $field |
589
|
|
|
* @param mixed $val |
590
|
|
|
* @return QueryBuilderInterface |
591
|
|
|
*/ |
592
|
|
|
public function orWhereNotIn($field, $val=[]): QueryBuilderInterface |
593
|
|
|
{ |
594
|
|
|
return $this->_whereIn($field, $val, 'NOT IN', 'OR'); |
595
|
|
|
} |
596
|
|
|
|
597
|
|
|
// -------------------------------------------------------------------------- |
598
|
|
|
// ! Other Query Modifier methods |
599
|
|
|
// -------------------------------------------------------------------------- |
600
|
|
|
|
601
|
|
|
/** |
602
|
|
|
* Sets values for inserts / updates / deletes |
603
|
|
|
* |
604
|
|
|
* @param mixed $key |
605
|
|
|
* @param mixed $val |
606
|
|
|
* @return QueryBuilderInterface |
607
|
|
|
*/ |
608
|
|
|
public function set($key, $val = NULL): QueryBuilderInterface |
609
|
|
|
{ |
610
|
|
|
$this->_mixedSet($this->setArrayKeys, $key, $val, self::KEY); |
611
|
|
|
$this->_mixedSet($this->values, $key, $val, self::VALUE); |
612
|
|
|
|
613
|
|
|
// Use the keys of the array to make the insert/update string |
614
|
|
|
// Escape the field names |
615
|
|
|
$this->setArrayKeys = array_map([$this->driver, '_quote'], $this->setArrayKeys); |
616
|
|
|
|
617
|
|
|
// Generate the "set" string |
618
|
|
|
$this->setString = implode('=?,', $this->setArrayKeys); |
619
|
|
|
$this->setString .= '=?'; |
620
|
|
|
|
621
|
|
|
return $this; |
622
|
|
|
} |
623
|
|
|
|
624
|
|
|
/** |
625
|
|
|
* Creates a join phrase in a compiled query |
626
|
|
|
* |
627
|
|
|
* @param string $table |
628
|
|
|
* @param string $condition |
629
|
|
|
* @param string $type |
630
|
|
|
* @return QueryBuilderInterface |
631
|
|
|
*/ |
632
|
|
|
public function join($table, $condition, $type=''): QueryBuilderInterface |
633
|
|
|
{ |
634
|
|
|
// Prefix and quote table name |
635
|
|
|
$table = explode(' ', mb_trim($table)); |
636
|
|
|
$table[0] = $this->driver->quoteTable($table[0]); |
637
|
|
|
$table = $this->driver->quoteIdent($table); |
638
|
|
|
$table = implode(' ', $table); |
639
|
|
|
|
640
|
|
|
// Parse out the join condition |
641
|
|
|
$parsedCondition = $this->parser->compileJoin($condition); |
642
|
|
|
$condition = $table . ' ON ' . $parsedCondition; |
643
|
|
|
|
644
|
|
|
$this->_appendMap("\n" . strtoupper($type) . ' JOIN ', $condition, 'join'); |
645
|
|
|
|
646
|
|
|
return $this; |
647
|
|
|
} |
648
|
|
|
|
649
|
|
|
/** |
650
|
|
|
* Group the results by the selected field(s) |
651
|
|
|
* |
652
|
|
|
* @param mixed $field |
653
|
|
|
* @return QueryBuilderInterface |
654
|
|
|
*/ |
655
|
|
|
public function groupBy($field): QueryBuilderInterface |
656
|
|
|
{ |
657
|
|
|
if ( ! is_scalar($field)) |
658
|
|
|
{ |
659
|
|
|
$newGroupArray = array_map([$this->driver, 'quoteIdent'], $field); |
660
|
|
|
$this->groupArray = array_merge($this->groupArray, $newGroupArray); |
661
|
|
|
} |
662
|
|
|
else |
663
|
|
|
{ |
664
|
|
|
$this->groupArray[] = $this->driver->quoteIdent($field); |
665
|
|
|
} |
666
|
|
|
|
667
|
|
|
$this->groupString = ' GROUP BY ' . implode(',', $this->groupArray); |
668
|
|
|
|
669
|
|
|
return $this; |
670
|
|
|
} |
671
|
|
|
|
672
|
|
|
/** |
673
|
|
|
* Order the results by the selected field(s) |
674
|
|
|
* |
675
|
|
|
* @param string $field |
676
|
|
|
* @param string $type |
677
|
|
|
* @return QueryBuilderInterface |
678
|
|
|
*/ |
679
|
|
|
public function orderBy($field, $type=''): QueryBuilderInterface |
680
|
|
|
{ |
681
|
|
|
// When ordering by random, do an ascending order if the driver |
682
|
|
|
// doesn't support random ordering |
683
|
|
|
if (stripos($type, 'rand') !== FALSE) |
684
|
|
|
{ |
685
|
|
|
$rand = $this->sql->random(); |
686
|
|
|
$type = $rand ?? 'ASC'; |
687
|
|
|
} |
688
|
|
|
|
689
|
|
|
// Set fields for later manipulation |
690
|
|
|
$field = $this->driver->quoteIdent($field); |
691
|
|
|
$this->orderArray[$field] = $type; |
692
|
|
|
|
693
|
|
|
$orderClauses = []; |
694
|
|
|
|
695
|
|
|
// Flatten key/val pairs into an array of space-separated pairs |
696
|
|
|
foreach($this->orderArray as $k => $v) |
697
|
|
|
{ |
698
|
|
|
$orderClauses[] = $k . ' ' . strtoupper($v); |
699
|
|
|
} |
700
|
|
|
|
701
|
|
|
// Set the final string |
702
|
|
|
$this->orderString = ( ! isset($rand)) |
703
|
|
|
? "\nORDER BY ".implode(', ', $orderClauses) |
704
|
|
|
: "\nORDER BY".$rand; |
705
|
|
|
|
706
|
|
|
return $this; |
707
|
|
|
} |
708
|
|
|
|
709
|
|
|
/** |
710
|
|
|
* Set a limit on the current sql statement |
711
|
|
|
* |
712
|
|
|
* @param int $limit |
713
|
|
|
* @param int|bool $offset |
714
|
|
|
* @return QueryBuilderInterface |
715
|
|
|
*/ |
716
|
|
|
public function limit($limit, $offset=FALSE): QueryBuilderInterface |
717
|
|
|
{ |
718
|
|
|
$this->limit = (int) $limit; |
|
|
|
|
719
|
|
|
$this->offset = $offset; |
|
|
|
|
720
|
|
|
|
721
|
|
|
return $this; |
722
|
|
|
} |
723
|
|
|
|
724
|
|
|
// -------------------------------------------------------------------------- |
725
|
|
|
// ! Query Grouping Methods |
726
|
|
|
// -------------------------------------------------------------------------- |
727
|
|
|
|
728
|
|
|
/** |
729
|
|
|
* Adds a paren to the current query for query grouping |
730
|
|
|
* |
731
|
|
|
* @return QueryBuilderInterface |
732
|
|
|
*/ |
733
|
|
View Code Duplication |
public function groupStart(): QueryBuilderInterface |
|
|
|
|
734
|
|
|
{ |
735
|
|
|
$conj = empty($this->queryMap) ? ' WHERE ' : ' '; |
736
|
|
|
|
737
|
|
|
$this->_appendMap($conj, '(', 'group_start'); |
738
|
|
|
|
739
|
|
|
return $this; |
740
|
|
|
} |
741
|
|
|
|
742
|
|
|
/** |
743
|
|
|
* Adds a paren to the current query for query grouping, |
744
|
|
|
* prefixed with 'NOT' |
745
|
|
|
* |
746
|
|
|
* @return QueryBuilderInterface |
747
|
|
|
*/ |
748
|
|
View Code Duplication |
public function notGroupStart(): QueryBuilderInterface |
|
|
|
|
749
|
|
|
{ |
750
|
|
|
$conj = empty($this->queryMap) ? ' WHERE ' : ' AND '; |
751
|
|
|
|
752
|
|
|
$this->_appendMap($conj, ' NOT (', 'group_start'); |
753
|
|
|
|
754
|
|
|
return $this; |
755
|
|
|
} |
756
|
|
|
|
757
|
|
|
/** |
758
|
|
|
* Adds a paren to the current query for query grouping, |
759
|
|
|
* prefixed with 'OR' |
760
|
|
|
* |
761
|
|
|
* @return QueryBuilderInterface |
762
|
|
|
*/ |
763
|
|
|
public function orGroupStart(): QueryBuilderInterface |
764
|
|
|
{ |
765
|
|
|
$this->_appendMap('', ' OR (', 'group_start'); |
766
|
|
|
|
767
|
|
|
return $this; |
768
|
|
|
} |
769
|
|
|
|
770
|
|
|
/** |
771
|
|
|
* Adds a paren to the current query for query grouping, |
772
|
|
|
* prefixed with 'OR NOT' |
773
|
|
|
* |
774
|
|
|
* @return QueryBuilderInterface |
775
|
|
|
*/ |
776
|
|
|
public function orNotGroupStart(): QueryBuilderInterface |
777
|
|
|
{ |
778
|
|
|
$this->_appendMap('', ' OR NOT (', 'group_start'); |
779
|
|
|
|
780
|
|
|
return $this; |
781
|
|
|
} |
782
|
|
|
|
783
|
|
|
/** |
784
|
|
|
* Ends a query group |
785
|
|
|
* |
786
|
|
|
* @return QueryBuilderInterface |
787
|
|
|
*/ |
788
|
|
|
public function groupEnd(): QueryBuilderInterface |
789
|
|
|
{ |
790
|
|
|
$this->_appendMap('', ')', 'group_end'); |
791
|
|
|
|
792
|
|
|
return $this; |
793
|
|
|
} |
794
|
|
|
|
795
|
|
|
// -------------------------------------------------------------------------- |
796
|
|
|
// ! Query execution methods |
797
|
|
|
// -------------------------------------------------------------------------- |
798
|
|
|
|
799
|
|
|
/** |
800
|
|
|
* Select and retrieve all records from the current table, and/or |
801
|
|
|
* execute current compiled query |
802
|
|
|
* |
803
|
|
|
* @param string $table |
804
|
|
|
* @param int|bool $limit |
805
|
|
|
* @param int|bool $offset |
806
|
|
|
* @return PDOStatement |
807
|
|
|
*/ |
808
|
|
|
public function get($table='', $limit=FALSE, $offset=FALSE): PDOStatement |
809
|
|
|
{ |
810
|
|
|
// Set the table |
811
|
|
|
if ( ! empty($table)) |
812
|
|
|
{ |
813
|
|
|
$this->from($table); |
814
|
|
|
} |
815
|
|
|
|
816
|
|
|
// Set the limit, if it exists |
817
|
|
|
if (\is_int($limit)) |
818
|
|
|
{ |
819
|
|
|
$this->limit($limit, $offset); |
820
|
|
|
} |
821
|
|
|
|
822
|
|
|
return $this->_run('get', $table); |
823
|
|
|
} |
824
|
|
|
|
825
|
|
|
/** |
826
|
|
|
* Convenience method for get() with a where clause |
827
|
|
|
* |
828
|
|
|
* @param string $table |
829
|
|
|
* @param array $where |
830
|
|
|
* @param int|bool $limit |
831
|
|
|
* @param int|bool $offset |
832
|
|
|
* @return PDOStatement |
833
|
|
|
*/ |
834
|
|
|
public function getWhere($table, $where=[], $limit=FALSE, $offset=FALSE): PDOStatement |
835
|
|
|
{ |
836
|
|
|
// Create the where clause |
837
|
|
|
$this->where($where); |
838
|
|
|
|
839
|
|
|
// Return the result |
840
|
|
|
return $this->get($table, $limit, $offset); |
841
|
|
|
} |
842
|
|
|
|
843
|
|
|
/** |
844
|
|
|
* Retrieve the number of rows in the selected table |
845
|
|
|
* |
846
|
|
|
* @param string $table |
847
|
|
|
* @return int |
848
|
|
|
*/ |
849
|
|
|
public function countAll($table): int |
850
|
|
|
{ |
851
|
|
|
$sql = 'SELECT * FROM '.$this->driver->quoteTable($table); |
852
|
|
|
$res = $this->driver->query($sql); |
853
|
|
|
return (int) count($res->fetchAll()); |
854
|
|
|
} |
855
|
|
|
|
856
|
|
|
/** |
857
|
|
|
* Retrieve the number of results for the generated query - used |
858
|
|
|
* in place of the get() method |
859
|
|
|
* |
860
|
|
|
* @param string $table |
861
|
|
|
* @param boolean $reset |
862
|
|
|
* @return int |
863
|
|
|
*/ |
864
|
|
|
public function countAllResults(string $table='', bool $reset = TRUE): int |
865
|
|
|
{ |
866
|
|
|
// Set the table |
867
|
|
|
if ( ! empty($table)) |
868
|
|
|
{ |
869
|
|
|
$this->from($table); |
870
|
|
|
} |
871
|
|
|
|
872
|
|
|
$result = $this->_run('get', $table, NULL, NULL, $reset); |
873
|
|
|
$rows = $result->fetchAll(); |
874
|
|
|
|
875
|
|
|
return (int) count($rows); |
876
|
|
|
} |
877
|
|
|
|
878
|
|
|
/** |
879
|
|
|
* Creates an insert clause, and executes it |
880
|
|
|
* |
881
|
|
|
* @param string $table |
882
|
|
|
* @param mixed $data |
883
|
|
|
* @return PDOStatement |
884
|
|
|
*/ |
885
|
|
|
public function insert($table, $data=[]): PDOStatement |
886
|
|
|
{ |
887
|
|
|
if ( ! empty($data)) |
888
|
|
|
{ |
889
|
|
|
$this->set($data); |
890
|
|
|
} |
891
|
|
|
|
892
|
|
|
return $this->_run('insert', $table); |
893
|
|
|
} |
894
|
|
|
|
895
|
|
|
/** |
896
|
|
|
* Creates and executes a batch insertion query |
897
|
|
|
* |
898
|
|
|
* @param string $table |
899
|
|
|
* @param array $data |
900
|
|
|
* @return PDOStatement |
901
|
|
|
*/ |
902
|
|
View Code Duplication |
public function insertBatch($table, $data=[]): PDOStatement |
|
|
|
|
903
|
|
|
{ |
904
|
|
|
// Get the generated values and sql string |
905
|
|
|
list($sql, $data) = $this->driver->insertBatch($table, $data); |
906
|
|
|
|
907
|
|
|
return ( ! is_null($sql)) |
908
|
|
|
? $this->_run('', $table, $sql, $data) |
909
|
|
|
: NULL; |
910
|
|
|
} |
911
|
|
|
|
912
|
|
|
/** |
913
|
|
|
* Creates an update clause, and executes it |
914
|
|
|
* |
915
|
|
|
* @param string $table |
916
|
|
|
* @param mixed $data |
917
|
|
|
* @return PDOStatement |
918
|
|
|
*/ |
919
|
|
|
public function update($table, $data=[]): PDOStatement |
920
|
|
|
{ |
921
|
|
|
if ( ! empty($data)) |
922
|
|
|
{ |
923
|
|
|
$this->set($data); |
924
|
|
|
} |
925
|
|
|
|
926
|
|
|
return $this->_run('update', $table); |
927
|
|
|
} |
928
|
|
|
|
929
|
|
|
/** |
930
|
|
|
* Creates a batch update, and executes it. |
931
|
|
|
* Returns the number of affected rows |
932
|
|
|
* |
933
|
|
|
* @param string $table |
934
|
|
|
* @param array|object $data |
935
|
|
|
* @param string $where |
936
|
|
|
* @return int|null |
937
|
|
|
*/ |
938
|
|
View Code Duplication |
public function updateBatch($table, $data, $where) |
|
|
|
|
939
|
|
|
{ |
940
|
|
|
// Get the generated values and sql string |
941
|
|
|
list($sql, $data) = $this->driver->updateBatch($table, $data, $where); |
942
|
|
|
|
943
|
|
|
return ( ! is_null($sql)) |
|
|
|
|
944
|
|
|
? $this->_run('', $table, $sql, $data) |
945
|
|
|
: NULL; |
946
|
|
|
} |
947
|
|
|
|
948
|
|
|
/** |
949
|
|
|
* Insertion with automatic overwrite, rather than attempted duplication |
950
|
|
|
* |
951
|
|
|
* @param string $table |
952
|
|
|
* @param array $data |
953
|
|
|
* @return \PDOStatement|null |
954
|
|
|
*/ |
955
|
|
|
public function replace($table, $data=[]) |
956
|
|
|
{ |
957
|
|
|
if ( ! empty($data)) |
958
|
|
|
{ |
959
|
|
|
$this->set($data); |
960
|
|
|
} |
961
|
|
|
|
962
|
|
|
return $this->_run('replace', $table); |
963
|
|
|
} |
964
|
|
|
|
965
|
|
|
/** |
966
|
|
|
* Deletes data from a table |
967
|
|
|
* |
968
|
|
|
* @param string $table |
969
|
|
|
* @param mixed $where |
970
|
|
|
* @return PDOStatement |
971
|
|
|
*/ |
972
|
|
|
public function delete($table, $where=''): PDOStatement |
973
|
|
|
{ |
974
|
|
|
// Set the where clause |
975
|
|
|
if ( ! empty($where)) |
976
|
|
|
{ |
977
|
|
|
$this->where($where); |
978
|
|
|
} |
979
|
|
|
|
980
|
|
|
return $this->_run('delete', $table); |
981
|
|
|
} |
982
|
|
|
|
983
|
|
|
// -------------------------------------------------------------------------- |
984
|
|
|
// ! SQL Returning Methods |
985
|
|
|
// -------------------------------------------------------------------------- |
986
|
|
|
|
987
|
|
|
/** |
988
|
|
|
* Returns the generated 'select' sql query |
989
|
|
|
* |
990
|
|
|
* @param string $table |
991
|
|
|
* @param bool $reset |
992
|
|
|
* @return string |
993
|
|
|
*/ |
994
|
|
|
public function getCompiledSelect(string $table='', bool $reset=TRUE): string |
995
|
|
|
{ |
996
|
|
|
// Set the table |
997
|
|
|
if ( ! empty($table)) |
998
|
|
|
{ |
999
|
|
|
$this->from($table); |
1000
|
|
|
} |
1001
|
|
|
|
1002
|
|
|
return $this->_getCompile('select', $table, $reset); |
1003
|
|
|
} |
1004
|
|
|
|
1005
|
|
|
/** |
1006
|
|
|
* Returns the generated 'insert' sql query |
1007
|
|
|
* |
1008
|
|
|
* @param string $table |
1009
|
|
|
* @param bool $reset |
1010
|
|
|
* @return string |
1011
|
|
|
*/ |
1012
|
|
|
public function getCompiledInsert(string $table, bool $reset=TRUE): string |
1013
|
|
|
{ |
1014
|
|
|
return $this->_getCompile('insert', $table, $reset); |
1015
|
|
|
} |
1016
|
|
|
|
1017
|
|
|
/** |
1018
|
|
|
* Returns the generated 'update' sql query |
1019
|
|
|
* |
1020
|
|
|
* @param string $table |
1021
|
|
|
* @param bool $reset |
1022
|
|
|
* @return string |
1023
|
|
|
*/ |
1024
|
|
|
public function getCompiledUpdate(string $table='', bool $reset=TRUE): string |
1025
|
|
|
{ |
1026
|
|
|
return $this->_getCompile('update', $table, $reset); |
1027
|
|
|
} |
1028
|
|
|
|
1029
|
|
|
/** |
1030
|
|
|
* Returns the generated 'delete' sql query |
1031
|
|
|
* |
1032
|
|
|
* @param string $table |
1033
|
|
|
* @param bool $reset |
1034
|
|
|
* @return string |
1035
|
|
|
*/ |
1036
|
|
|
public function getCompiledDelete(string $table='', bool $reset=TRUE): string |
1037
|
|
|
{ |
1038
|
|
|
return $this->_getCompile('delete', $table, $reset); |
1039
|
|
|
} |
1040
|
|
|
|
1041
|
|
|
// -------------------------------------------------------------------------- |
1042
|
|
|
// ! Miscellaneous Methods |
1043
|
|
|
// -------------------------------------------------------------------------- |
1044
|
|
|
|
1045
|
|
|
/** |
1046
|
|
|
* Clear out the class variables, so the next query can be run |
1047
|
|
|
* |
1048
|
|
|
* @return void |
1049
|
|
|
*/ |
1050
|
|
|
public function resetQuery(): void |
1051
|
|
|
{ |
1052
|
|
|
// Reset strings and booleans |
1053
|
|
|
foreach($this->stringVars as $var) |
1054
|
|
|
{ |
1055
|
|
|
$this->$var = NULL; |
1056
|
|
|
} |
1057
|
|
|
|
1058
|
|
|
// Reset arrays |
1059
|
|
|
foreach($this->arrayVars as $var) |
1060
|
|
|
{ |
1061
|
|
|
$this->$var = []; |
1062
|
|
|
} |
1063
|
|
|
} |
1064
|
|
|
|
1065
|
|
|
/** |
1066
|
|
|
* Set values in the class, with either an array or key value pair |
1067
|
|
|
* |
1068
|
|
|
* @param array $var |
1069
|
|
|
* @param mixed $key |
1070
|
|
|
* @param mixed $val |
1071
|
|
|
* @param int $valType |
1072
|
|
|
* @return array |
1073
|
|
|
*/ |
1074
|
|
|
protected function _mixedSet(array &$var, $key, $val=NULL, int $valType=self::BOTH): array |
1075
|
|
|
{ |
1076
|
|
|
$arg = (is_scalar($key) && is_scalar($val)) |
1077
|
|
|
? [$key => $val] |
1078
|
|
|
: $key; |
1079
|
|
|
|
1080
|
|
|
foreach($arg as $k => $v) |
1081
|
|
|
{ |
1082
|
|
|
if (\in_array($valType, [self::KEY, self::VALUE], TRUE)) |
1083
|
|
|
{ |
1084
|
|
|
$var[] = ($valType === self::KEY) |
1085
|
|
|
? $k |
1086
|
|
|
: $v; |
1087
|
|
|
} |
1088
|
|
|
else |
1089
|
|
|
{ |
1090
|
|
|
$var[$k] = $v; |
1091
|
|
|
} |
1092
|
|
|
} |
1093
|
|
|
|
1094
|
|
|
return $var; |
1095
|
|
|
} |
1096
|
|
|
|
1097
|
|
|
/** |
1098
|
|
|
* Method to simplify select_ methods |
1099
|
|
|
* |
1100
|
|
|
* @param string $field |
1101
|
|
|
* @param string|bool $as |
1102
|
|
|
* @return string |
1103
|
|
|
*/ |
1104
|
|
|
protected function _select(string $field, $as = FALSE): string |
1105
|
|
|
{ |
1106
|
|
|
// Escape the identifiers |
1107
|
|
|
$field = $this->driver->quoteIdent($field); |
1108
|
|
|
|
1109
|
|
|
if ( ! \is_string($as)) |
1110
|
|
|
{ |
1111
|
|
|
return $field; |
1112
|
|
|
} |
1113
|
|
|
|
1114
|
|
|
$as = $this->driver->quoteIdent($as); |
1115
|
|
|
return "({$field}) AS {$as} "; |
1116
|
|
|
} |
1117
|
|
|
|
1118
|
|
|
/** |
1119
|
|
|
* Helper function for returning sql strings |
1120
|
|
|
* |
1121
|
|
|
* @param string $type |
1122
|
|
|
* @param string $table |
1123
|
|
|
* @param bool $reset |
1124
|
|
|
* @return string |
1125
|
|
|
*/ |
1126
|
|
|
protected function _getCompile(string $type, string $table, bool $reset): string |
1127
|
|
|
{ |
1128
|
|
|
$sql = $this->_compile($type, $table); |
1129
|
|
|
|
1130
|
|
|
// Reset the query builder for the next query |
1131
|
|
|
if ($reset) |
1132
|
|
|
{ |
1133
|
|
|
$this->resetQuery(); |
1134
|
|
|
} |
1135
|
|
|
|
1136
|
|
|
return $sql; |
1137
|
|
|
} |
1138
|
|
|
|
1139
|
|
|
/** |
1140
|
|
|
* Simplify 'like' methods |
1141
|
|
|
* |
1142
|
|
|
* @param string $field |
1143
|
|
|
* @param mixed $val |
1144
|
|
|
* @param string $pos |
1145
|
|
|
* @param string $like |
1146
|
|
|
* @param string $conj |
1147
|
|
|
* @return self |
1148
|
|
|
*/ |
1149
|
|
|
protected function _like(string $field, $val, string $pos, string $like='LIKE', string $conj='AND'): self |
1150
|
|
|
{ |
1151
|
|
|
$field = $this->driver->quoteIdent($field); |
1152
|
|
|
|
1153
|
|
|
// Add the like string into the order map |
1154
|
|
|
$like = $field. " {$like} ?"; |
1155
|
|
|
|
1156
|
|
|
if ($pos === 'before') |
1157
|
|
|
{ |
1158
|
|
|
$val = "%{$val}"; |
1159
|
|
|
} |
1160
|
|
|
elseif ($pos === 'after') |
1161
|
|
|
{ |
1162
|
|
|
$val = "{$val}%"; |
1163
|
|
|
} |
1164
|
|
|
else |
1165
|
|
|
{ |
1166
|
|
|
$val = "%{$val}%"; |
1167
|
|
|
} |
1168
|
|
|
|
1169
|
|
|
$conj = empty($this->queryMap) ? ' WHERE ' : " {$conj} "; |
1170
|
|
|
$this->_appendMap($conj, $like, 'like'); |
1171
|
|
|
|
1172
|
|
|
// Add to the values array |
1173
|
|
|
$this->whereValues[] = $val; |
1174
|
|
|
|
1175
|
|
|
return $this; |
1176
|
|
|
} |
1177
|
|
|
|
1178
|
|
|
/** |
1179
|
|
|
* Simplify building having clauses |
1180
|
|
|
* |
1181
|
|
|
* @param mixed $key |
1182
|
|
|
* @param mixed $values |
1183
|
|
|
* @param string $conj |
1184
|
|
|
* @return self |
1185
|
|
|
*/ |
1186
|
|
|
protected function _having($key, $values=[], string $conj='AND'): self |
1187
|
|
|
{ |
1188
|
|
|
$where = $this->_where($key, $values); |
1189
|
|
|
|
1190
|
|
|
// Create key/value placeholders |
1191
|
|
|
foreach($where as $f => $val) |
1192
|
|
|
{ |
1193
|
|
|
// Split each key by spaces, in case there |
1194
|
|
|
// is an operator such as >, <, !=, etc. |
1195
|
|
|
$fArray = explode(' ', trim($f)); |
1196
|
|
|
|
1197
|
|
|
$item = $this->driver->quoteIdent($fArray[0]); |
1198
|
|
|
|
1199
|
|
|
// Simple key value, or an operator |
1200
|
|
|
$item .= (count($fArray) === 1) ? '=?' : " {$fArray[1]} ?"; |
1201
|
|
|
|
1202
|
|
|
// Put in the having map |
1203
|
|
|
$this->havingMap[] = [ |
1204
|
|
|
'conjunction' => ( ! empty($this->havingMap)) ? " {$conj} " : ' HAVING ', |
1205
|
|
|
'string' => $item |
1206
|
|
|
]; |
1207
|
|
|
} |
1208
|
|
|
|
1209
|
|
|
return $this; |
1210
|
|
|
} |
1211
|
|
|
|
1212
|
|
|
/** |
1213
|
|
|
* Do all the redundant stuff for where/having type methods |
1214
|
|
|
* |
1215
|
|
|
* @param mixed $key |
1216
|
|
|
* @param mixed $val |
1217
|
|
|
* @return array |
1218
|
|
|
*/ |
1219
|
|
|
protected function _where($key, $val=[]): array |
1220
|
|
|
{ |
1221
|
|
|
$where = []; |
1222
|
|
|
$this->_mixedSet($where, $key, $val); |
1223
|
|
|
$this->_mixedSet($this->whereValues, $key, $val, self::VALUE); |
1224
|
|
|
return $where; |
1225
|
|
|
} |
1226
|
|
|
|
1227
|
|
|
/** |
1228
|
|
|
* Simplify generating where string |
1229
|
|
|
* |
1230
|
|
|
* @param mixed $key |
1231
|
|
|
* @param mixed $values |
1232
|
|
|
* @param string $defaultConj |
1233
|
|
|
* @return self |
1234
|
|
|
*/ |
1235
|
|
|
protected function _whereString($key, $values=[], string $defaultConj='AND'): self |
1236
|
|
|
{ |
1237
|
|
|
// Create key/value placeholders |
1238
|
|
|
foreach($this->_where($key, $values) as $f => $val) |
1239
|
|
|
{ |
1240
|
|
|
// Split each key by spaces, in case there |
1241
|
|
|
// is an operator such as >, <, !=, etc. |
1242
|
|
|
$fArray = explode(' ', trim($f)); |
1243
|
|
|
|
1244
|
|
|
$item = $this->driver->quoteIdent($fArray[0]); |
1245
|
|
|
|
1246
|
|
|
// Simple key value, or an operator |
1247
|
|
|
$item .= (count($fArray) === 1) ? '=?' : " {$fArray[1]} ?"; |
1248
|
|
|
$lastItem = end($this->queryMap); |
1249
|
|
|
|
1250
|
|
|
// Determine the correct conjunction |
1251
|
|
|
$conjunctionList = array_column($this->queryMap, 'conjunction'); |
1252
|
|
|
if (empty($this->queryMap) || ( ! regex_in_array($conjunctionList, "/^ ?\n?WHERE/i"))) |
1253
|
|
|
{ |
1254
|
|
|
$conj = "\nWHERE "; |
1255
|
|
|
} |
1256
|
|
|
elseif ($lastItem['type'] === 'group_start') |
1257
|
|
|
{ |
1258
|
|
|
$conj = ''; |
1259
|
|
|
} |
1260
|
|
|
else |
1261
|
|
|
{ |
1262
|
|
|
$conj = " {$defaultConj} "; |
1263
|
|
|
} |
1264
|
|
|
|
1265
|
|
|
$this->_appendMap($conj, $item, 'where'); |
1266
|
|
|
} |
1267
|
|
|
|
1268
|
|
|
return $this; |
1269
|
|
|
} |
1270
|
|
|
|
1271
|
|
|
/** |
1272
|
|
|
* Simplify where_in methods |
1273
|
|
|
* |
1274
|
|
|
* @param mixed $key |
1275
|
|
|
* @param mixed $val |
1276
|
|
|
* @param string $in - The (not) in fragment |
1277
|
|
|
* @param string $conj - The where in conjunction |
1278
|
|
|
* @return self |
1279
|
|
|
*/ |
1280
|
|
|
protected function _whereIn($key, $val=[], string $in='IN', string $conj='AND'): self |
1281
|
|
|
{ |
1282
|
|
|
$key = $this->driver->quoteIdent($key); |
1283
|
|
|
$params = array_fill(0, count($val), '?'); |
1284
|
|
|
|
1285
|
|
|
foreach($val as $v) |
1286
|
|
|
{ |
1287
|
|
|
$this->whereValues[] = $v; |
1288
|
|
|
} |
1289
|
|
|
|
1290
|
|
|
$conjunction = ( ! empty($this->queryMap)) ? " {$conj} " : ' WHERE '; |
1291
|
|
|
$str = $key . " {$in} (".implode(',', $params).') '; |
1292
|
|
|
|
1293
|
|
|
$this->_appendMap($conjunction, $str, 'where_in'); |
1294
|
|
|
|
1295
|
|
|
return $this; |
1296
|
|
|
} |
1297
|
|
|
|
1298
|
|
|
/** |
1299
|
|
|
* Executes the compiled query |
1300
|
|
|
* |
1301
|
|
|
* @param string $type |
1302
|
|
|
* @param string $table |
1303
|
|
|
* @param string $sql |
1304
|
|
|
* @param array|null $vals |
1305
|
|
|
* @param boolean $reset |
1306
|
|
|
* @return PDOStatement |
1307
|
|
|
*/ |
1308
|
|
|
protected function _run(string $type, string $table, $sql=NULL, $vals=NULL, bool $reset=TRUE): PDOStatement |
1309
|
|
|
{ |
1310
|
|
|
if ($sql === NULL) |
1311
|
|
|
{ |
1312
|
|
|
$sql = $this->_compile($type, $table); |
1313
|
|
|
} |
1314
|
|
|
|
1315
|
|
|
if ($vals === NULL) |
1316
|
|
|
{ |
1317
|
|
|
$vals = array_merge($this->values, (array) $this->whereValues); |
1318
|
|
|
} |
1319
|
|
|
|
1320
|
|
|
$startTime = microtime(TRUE); |
1321
|
|
|
|
1322
|
|
|
$res = empty($vals) |
1323
|
|
|
? $this->driver->query($sql) |
1324
|
|
|
: $this->driver->prepareExecute($sql, $vals); |
1325
|
|
|
|
1326
|
|
|
$endTime = microtime(TRUE); |
1327
|
|
|
$totalTime = number_format($endTime - $startTime, 5); |
1328
|
|
|
|
1329
|
|
|
// Add this query to the list of executed queries |
1330
|
|
|
$this->_appendQuery($vals, $sql, (int) $totalTime); |
1331
|
|
|
|
1332
|
|
|
// Reset class state for next query |
1333
|
|
|
if ($reset) |
1334
|
|
|
{ |
1335
|
|
|
$this->resetQuery(); |
1336
|
|
|
} |
1337
|
|
|
|
1338
|
|
|
return $res; |
1339
|
|
|
} |
1340
|
|
|
|
1341
|
|
|
/** |
1342
|
|
|
* Add an additional set of mapping pairs to a internal map |
1343
|
|
|
* |
1344
|
|
|
* @param string $conjunction |
1345
|
|
|
* @param string $string |
1346
|
|
|
* @param string $type |
1347
|
|
|
* @return void |
1348
|
|
|
*/ |
1349
|
|
|
protected function _appendMap(string $conjunction = '', string $string = '', string $type = '') |
1350
|
|
|
{ |
1351
|
|
|
$this->queryMap[] = [ |
1352
|
|
|
'type' => $type, |
1353
|
|
|
'conjunction' => $conjunction, |
1354
|
|
|
'string' => $string |
1355
|
|
|
]; |
1356
|
|
|
} |
1357
|
|
|
|
1358
|
|
|
/** |
1359
|
|
|
* Convert the prepared statement into readable sql |
1360
|
|
|
* |
1361
|
|
|
* @param array $vals |
1362
|
|
|
* @param string $sql |
1363
|
|
|
* @param int $totalTime |
1364
|
|
|
* @return void |
1365
|
|
|
*/ |
1366
|
|
|
protected function _appendQuery($vals, string $sql, int $totalTime) |
1367
|
|
|
{ |
1368
|
|
|
$evals = \is_array($vals) ? $vals : []; |
1369
|
|
|
$esql = str_replace('?', "%s", $sql); |
1370
|
|
|
|
1371
|
|
|
// Quote string values |
1372
|
|
|
foreach($evals as &$v) |
1373
|
|
|
{ |
1374
|
|
|
$v = ( ! is_numeric($v)) |
1375
|
|
|
? htmlentities($this->driver->quote($v), ENT_NOQUOTES, 'utf-8') |
1376
|
|
|
: $v; |
1377
|
|
|
} |
1378
|
|
|
|
1379
|
|
|
// Add the query onto the array of values to pass |
1380
|
|
|
// as arguments to sprintf |
1381
|
|
|
array_unshift($evals, $esql); |
1382
|
|
|
|
1383
|
|
|
// Add the interpreted query to the list of executed queries |
1384
|
|
|
$this->queries[] = [ |
1385
|
|
|
'time' => $totalTime, |
1386
|
|
|
'sql' => sprintf(...$evals) |
1387
|
|
|
]; |
1388
|
|
|
|
1389
|
|
|
$this->queries['total_time'] += $totalTime; |
1390
|
|
|
|
1391
|
|
|
// Set the last query to get rowcounts properly |
1392
|
|
|
$this->driver->setLastQuery($sql); |
1393
|
|
|
} |
1394
|
|
|
|
1395
|
|
|
/** |
1396
|
|
|
* Sub-method for generating sql strings |
1397
|
|
|
* |
1398
|
|
|
* @param string $type |
1399
|
|
|
* @param string $table |
1400
|
|
|
* @return string |
1401
|
|
|
*/ |
1402
|
|
|
protected function _compileType(string $type='', string $table=''): string |
1403
|
|
|
{ |
1404
|
|
|
switch($type) |
1405
|
|
|
{ |
1406
|
|
|
case 'insert': |
1407
|
|
|
$paramCount = count($this->setArrayKeys); |
1408
|
|
|
$params = array_fill(0, $paramCount, '?'); |
1409
|
|
|
$sql = "INSERT INTO {$table} (" |
1410
|
|
|
. implode(',', $this->setArrayKeys) |
1411
|
|
|
. ")\nVALUES (".implode(',', $params).')'; |
1412
|
|
|
break; |
1413
|
|
|
|
1414
|
|
|
case 'update': |
1415
|
|
|
$sql = "UPDATE {$table}\nSET {$this->setString}"; |
1416
|
|
|
break; |
1417
|
|
|
|
1418
|
|
|
case 'replace': |
1419
|
|
|
// @TODO implement |
1420
|
|
|
$sql = ''; |
1421
|
|
|
break; |
1422
|
|
|
|
1423
|
|
|
case 'delete': |
1424
|
|
|
$sql = "DELETE FROM {$table}"; |
1425
|
|
|
break; |
1426
|
|
|
|
1427
|
|
|
// Get queries |
1428
|
|
|
default: |
1429
|
|
|
$sql = "SELECT * \nFROM {$this->fromString}"; |
1430
|
|
|
|
1431
|
|
|
// Set the select string |
1432
|
|
|
if ( ! empty($this->selectString)) |
1433
|
|
|
{ |
1434
|
|
|
// Replace the star with the selected fields |
1435
|
|
|
$sql = str_replace('*', $this->selectString, $sql); |
1436
|
|
|
} |
1437
|
|
|
break; |
1438
|
|
|
} |
1439
|
|
|
|
1440
|
|
|
return $sql; |
1441
|
|
|
} |
1442
|
|
|
|
1443
|
|
|
/** |
1444
|
|
|
* String together the sql statements for sending to the db |
1445
|
|
|
* |
1446
|
|
|
* @param string $type |
1447
|
|
|
* @param string $table |
1448
|
|
|
* @return string |
1449
|
|
|
*/ |
1450
|
|
|
protected function _compile(string $type='', string $table=''): string |
1451
|
|
|
{ |
1452
|
|
|
// Get the base clause for the query |
1453
|
|
|
$sql = $this->_compileType($type, $this->driver->quoteTable($table)); |
1454
|
|
|
|
1455
|
|
|
$clauses = [ |
1456
|
|
|
'queryMap', |
1457
|
|
|
'groupString', |
1458
|
|
|
'orderString', |
1459
|
|
|
'havingMap', |
1460
|
|
|
]; |
1461
|
|
|
|
1462
|
|
|
// Set each type of subclause |
1463
|
|
|
foreach($clauses as $clause) |
1464
|
|
|
{ |
1465
|
|
|
$param = $this->$clause; |
1466
|
|
|
if (\is_array($param)) |
1467
|
|
|
{ |
1468
|
|
|
foreach($param as $q) |
1469
|
|
|
{ |
1470
|
|
|
$sql .= $q['conjunction'] . $q['string']; |
1471
|
|
|
} |
1472
|
|
|
} |
1473
|
|
|
else |
1474
|
|
|
{ |
1475
|
|
|
$sql .= $param; |
1476
|
|
|
} |
1477
|
|
|
} |
1478
|
|
|
|
1479
|
|
|
// Set the limit via the class variables |
1480
|
|
|
if (is_numeric($this->limit)) |
1481
|
|
|
{ |
1482
|
|
|
$sql = $this->sql->limit($sql, $this->limit, $this->offset); |
1483
|
|
|
} |
1484
|
|
|
|
1485
|
|
|
// See if the query plan, rather than the |
1486
|
|
|
// query data should be returned |
1487
|
|
|
if ($this->explain === TRUE) |
1488
|
|
|
{ |
1489
|
|
|
$sql = $this->sql->explain($sql); |
1490
|
|
|
} |
1491
|
|
|
|
1492
|
|
|
return $sql; |
1493
|
|
|
} |
1494
|
|
|
} |
1495
|
|
|
|
Our type inference engine has found an assignment to a property that is incompatible with the declared type of that property.
Either this assignment is in error or the assigned type should be added to the documentation/type hint for that property..