1
|
|
|
<?php declare(strict_types=1); |
2
|
|
|
/** |
3
|
|
|
* Query |
4
|
|
|
* |
5
|
|
|
* SQL Query Builder / Database Abstraction Layer |
6
|
|
|
* |
7
|
|
|
* PHP version 7 |
8
|
|
|
* |
9
|
|
|
* @package Query |
10
|
|
|
* @author Timothy J. Warren <[email protected]> |
11
|
|
|
* @copyright 2012 - 2016 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\DriverInterface; |
20
|
|
|
|
21
|
|
|
/** |
22
|
|
|
* Convenience class for creating sql queries |
23
|
|
|
*/ |
24
|
|
|
class QueryBuilder extends AbstractQueryBuilder implements QueryBuilderInterface { |
25
|
|
|
|
26
|
|
|
/** |
27
|
|
|
* String class values to be reset |
28
|
|
|
* |
29
|
|
|
* @var array |
30
|
|
|
*/ |
31
|
|
|
private $stringVars = [ |
32
|
|
|
'selectString', |
33
|
|
|
'fromString', |
34
|
|
|
'setString', |
35
|
|
|
'orderString', |
36
|
|
|
'groupString', |
37
|
|
|
'limit', |
38
|
|
|
'offset', |
39
|
|
|
'explain', |
40
|
|
|
]; |
41
|
|
|
|
42
|
|
|
/** |
43
|
|
|
* Array class variables to be reset |
44
|
|
|
* |
45
|
|
|
* @var array |
46
|
|
|
*/ |
47
|
|
|
private $arrayVars = [ |
48
|
|
|
'setArrayKeys', |
49
|
|
|
'orderArray', |
50
|
|
|
'groupArray', |
51
|
|
|
'values', |
52
|
|
|
'whereValues', |
53
|
|
|
'queryMap', |
54
|
|
|
'havingMap' |
55
|
|
|
]; |
56
|
|
|
|
57
|
|
|
// -------------------------------------------------------------------------- |
58
|
|
|
// ! Methods |
59
|
|
|
// -------------------------------------------------------------------------- |
60
|
|
|
|
61
|
|
|
/** |
62
|
|
|
* Constructor |
63
|
|
|
* |
64
|
|
|
* @param DriverInterface $db |
65
|
|
|
* @param QueryParser $parser |
66
|
|
|
*/ |
67
|
|
|
public function __construct(DriverInterface $db, QueryParser $parser) |
68
|
|
|
{ |
69
|
|
|
// Inject driver and parser |
70
|
|
|
$this->db = $db; |
71
|
|
|
$this->parser = $parser; |
72
|
|
|
|
73
|
|
|
$this->queries['total_time'] = 0; |
74
|
|
|
|
75
|
|
|
// Alias driver sql and util classes |
76
|
|
|
$this->sql = $this->db->getSql(); |
77
|
|
|
$this->util = $this->db->getUtil(); |
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
/** |
81
|
|
|
* Destructor |
82
|
|
|
* @codeCoverageIgnore |
83
|
|
|
*/ |
84
|
|
|
public function __destruct() |
85
|
|
|
{ |
86
|
|
|
$this->db = NULL; |
87
|
|
|
} |
88
|
|
|
|
89
|
|
|
/** |
90
|
|
|
* Calls a function further down the inheritance chain |
91
|
|
|
* |
92
|
|
|
* @param string $name |
93
|
|
|
* @param array $params |
94
|
|
|
* @return mixed |
95
|
|
|
* @throws BadMethodCallException |
96
|
|
|
*/ |
97
|
|
|
public function __call(string $name, array $params) |
98
|
|
|
{ |
99
|
|
|
// Alias snake_case method calls |
100
|
|
|
$camelName = \to_camel_case($name); |
101
|
|
|
|
102
|
|
|
foreach([$this, $this->db] as $object) |
103
|
|
|
{ |
104
|
|
|
foreach([$name, $camelName] as $methodName) |
105
|
|
|
{ |
106
|
|
|
if (method_exists($object, $methodName)) |
107
|
|
|
{ |
108
|
|
|
return call_user_func_array([$object, $methodName], $params); |
109
|
|
|
} |
110
|
|
|
} |
111
|
|
|
|
112
|
|
|
} |
113
|
|
|
|
114
|
|
|
throw new BadMethodCallException('Method does not exist'); |
115
|
|
|
} |
116
|
|
|
|
117
|
|
|
// -------------------------------------------------------------------------- |
118
|
|
|
// ! Select Queries |
119
|
|
|
// -------------------------------------------------------------------------- |
120
|
|
|
|
121
|
|
|
/** |
122
|
|
|
* Specifies rows to select in a query |
123
|
|
|
* |
124
|
|
|
* @param string $fields |
125
|
|
|
* @return QueryBuilderInterface |
126
|
|
|
*/ |
127
|
|
|
public function select(string $fields): QueryBuilderInterface |
128
|
|
|
{ |
129
|
|
|
// Split fields by comma |
130
|
|
|
$fieldsArray = explode(',', $fields); |
131
|
|
|
$fieldsArray = array_map('mb_trim', $fieldsArray); |
132
|
|
|
|
133
|
|
|
// Split on 'As' |
134
|
|
|
foreach ($fieldsArray as $key => $field) |
135
|
|
|
{ |
136
|
|
|
if (stripos($field, 'as') !== FALSE) |
137
|
|
|
{ |
138
|
|
|
$fieldsArray[$key] = preg_split('` as `i', $field); |
139
|
|
|
$fieldsArray[$key] = array_map('mb_trim', $fieldsArray[$key]); |
140
|
|
|
} |
141
|
|
|
} |
142
|
|
|
|
143
|
|
|
// Quote the identifiers |
144
|
|
|
$safeArray = $this->db->quoteIdent($fieldsArray); |
145
|
|
|
|
146
|
|
|
unset($fieldsArray); |
147
|
|
|
|
148
|
|
|
// Join the strings back together |
149
|
|
|
for($i = 0, $c = count($safeArray); $i < $c; $i++) |
150
|
|
|
{ |
151
|
|
|
if (is_array($safeArray[$i])) |
152
|
|
|
{ |
153
|
|
|
$safeArray[$i] = implode(' AS ', $safeArray[$i]); |
154
|
|
|
} |
155
|
|
|
} |
156
|
|
|
|
157
|
|
|
$this->selectString .= implode(', ', $safeArray); |
158
|
|
|
|
159
|
|
|
unset($safeArray); |
160
|
|
|
|
161
|
|
|
return $this; |
162
|
|
|
} |
163
|
|
|
|
164
|
|
|
/** |
165
|
|
|
* Selects the maximum value of a field from a query |
166
|
|
|
* |
167
|
|
|
* @param string $field |
168
|
|
|
* @param string|bool $as |
169
|
|
|
* @return QueryBuilderInterface |
170
|
|
|
*/ |
171
|
|
|
public function selectMax(string $field, $as=FALSE): QueryBuilderInterface |
172
|
|
|
{ |
173
|
|
|
// Create the select string |
174
|
|
|
$this->selectString .= ' MAX'.$this->_select($field, $as); |
175
|
|
|
return $this; |
176
|
|
|
} |
177
|
|
|
|
178
|
|
|
/** |
179
|
|
|
* Selects the minimum value of a field from a query |
180
|
|
|
* |
181
|
|
|
* @param string $field |
182
|
|
|
* @param string|bool $as |
183
|
|
|
* @return QueryBuilderInterface |
184
|
|
|
*/ |
185
|
|
|
public function selectMin(string $field, $as=FALSE): QueryBuilderInterface |
186
|
|
|
{ |
187
|
|
|
// Create the select string |
188
|
|
|
$this->selectString .= ' MIN'.$this->_select($field, $as); |
189
|
|
|
return $this; |
190
|
|
|
} |
191
|
|
|
|
192
|
|
|
/** |
193
|
|
|
* Selects the average value of a field from a query |
194
|
|
|
* |
195
|
|
|
* @param string $field |
196
|
|
|
* @param string|bool $as |
197
|
|
|
* @return QueryBuilderInterface |
198
|
|
|
*/ |
199
|
|
|
public function selectAvg(string $field, $as=FALSE): QueryBuilderInterface |
200
|
|
|
{ |
201
|
|
|
// Create the select string |
202
|
|
|
$this->selectString .= ' AVG'.$this->_select($field, $as); |
203
|
|
|
return $this; |
204
|
|
|
} |
205
|
|
|
|
206
|
|
|
/** |
207
|
|
|
* Selects the sum of a field from a query |
208
|
|
|
* |
209
|
|
|
* @param string $field |
210
|
|
|
* @param string|bool $as |
211
|
|
|
* @return QueryBuilderInterface |
212
|
|
|
*/ |
213
|
|
|
public function selectSum(string $field, $as=FALSE): QueryBuilderInterface |
214
|
|
|
{ |
215
|
|
|
// Create the select string |
216
|
|
|
$this->selectString .= ' SUM'.$this->_select($field, $as); |
217
|
|
|
return $this; |
218
|
|
|
} |
219
|
|
|
|
220
|
|
|
/** |
221
|
|
|
* Adds the 'distinct' keyword to a query |
222
|
|
|
* |
223
|
|
|
* @return QueryBuilderInterface |
224
|
|
|
*/ |
225
|
|
|
public function distinct(): QueryBuilderInterface |
226
|
|
|
{ |
227
|
|
|
// Prepend the keyword to the select string |
228
|
|
|
$this->selectString = ' DISTINCT '.$this->selectString; |
229
|
|
|
return $this; |
230
|
|
|
} |
231
|
|
|
|
232
|
|
|
/** |
233
|
|
|
* Tell the database to give you the query plan instead of result set |
234
|
|
|
* |
235
|
|
|
* @return QueryBuilderInterface |
236
|
|
|
*/ |
237
|
|
|
public function explain(): QueryBuilderInterface |
238
|
|
|
{ |
239
|
|
|
$this->explain = TRUE; |
240
|
|
|
return $this; |
241
|
|
|
} |
242
|
|
|
|
243
|
|
|
/** |
244
|
|
|
* Specify the database table to select from |
245
|
|
|
* |
246
|
|
|
* @param string $tblname |
247
|
|
|
* @return QueryBuilderInterface |
248
|
|
|
*/ |
249
|
|
|
public function from($tblname): QueryBuilderInterface |
250
|
|
|
{ |
251
|
|
|
// Split identifiers on spaces |
252
|
|
|
$identArray = explode(' ', \mb_trim($tblname)); |
253
|
|
|
$identArray = array_map('\\mb_trim', $identArray); |
254
|
|
|
|
255
|
|
|
// Quote the identifiers |
256
|
|
|
$identArray[0] = $this->db->quoteTable($identArray[0]); |
257
|
|
|
$identArray = $this->db->quoteIdent($identArray); |
258
|
|
|
|
259
|
|
|
// Paste it back together |
260
|
|
|
$this->fromString = implode(' ', $identArray); |
261
|
|
|
|
262
|
|
|
return $this; |
263
|
|
|
} |
264
|
|
|
|
265
|
|
|
// -------------------------------------------------------------------------- |
266
|
|
|
// ! 'Like' methods |
267
|
|
|
// -------------------------------------------------------------------------- |
268
|
|
|
|
269
|
|
|
/** |
270
|
|
|
* Creates a Like clause in the sql statement |
271
|
|
|
* |
272
|
|
|
* @param string $field |
273
|
|
|
* @param mixed $val |
274
|
|
|
* @param string $pos |
275
|
|
|
* @return QueryBuilderInterface |
276
|
|
|
*/ |
277
|
|
|
public function like($field, $val, $pos='both'): QueryBuilderInterface |
278
|
|
|
{ |
279
|
|
|
return $this->_like($field, $val, $pos, 'LIKE', 'AND'); |
280
|
|
|
} |
281
|
|
|
|
282
|
|
|
/** |
283
|
|
|
* Generates an OR Like clause |
284
|
|
|
* |
285
|
|
|
* @param string $field |
286
|
|
|
* @param mixed $val |
287
|
|
|
* @param string $pos |
288
|
|
|
* @return QueryBuilderInterface |
289
|
|
|
*/ |
290
|
|
|
public function orLike($field, $val, $pos='both'): QueryBuilderInterface |
291
|
|
|
{ |
292
|
|
|
return $this->_like($field, $val, $pos, 'LIKE', 'OR'); |
293
|
|
|
} |
294
|
|
|
|
295
|
|
|
/** |
296
|
|
|
* Generates a NOT LIKE clause |
297
|
|
|
* |
298
|
|
|
* @param string $field |
299
|
|
|
* @param mixed $val |
300
|
|
|
* @param string $pos |
301
|
|
|
* @return QueryBuilderInterface |
302
|
|
|
*/ |
303
|
|
|
public function notLike($field, $val, $pos='both'): QueryBuilderInterface |
304
|
|
|
{ |
305
|
|
|
return $this->_like($field, $val, $pos, 'NOT LIKE', 'AND'); |
306
|
|
|
} |
307
|
|
|
|
308
|
|
|
/** |
309
|
|
|
* Generates a OR NOT LIKE clause |
310
|
|
|
* |
311
|
|
|
* @param string $field |
312
|
|
|
* @param mixed $val |
313
|
|
|
* @param string $pos |
314
|
|
|
* @return QueryBuilderInterface |
315
|
|
|
*/ |
316
|
|
|
public function orNotLike($field, $val, $pos='both'): QueryBuilderInterface |
317
|
|
|
{ |
318
|
|
|
return $this->_like($field, $val, $pos, 'NOT LIKE', 'OR'); |
319
|
|
|
} |
320
|
|
|
|
321
|
|
|
// -------------------------------------------------------------------------- |
322
|
|
|
// ! Having methods |
323
|
|
|
// -------------------------------------------------------------------------- |
324
|
|
|
|
325
|
|
|
/** |
326
|
|
|
* Generates a 'Having' clause |
327
|
|
|
* |
328
|
|
|
* @param mixed $key |
329
|
|
|
* @param mixed $val |
330
|
|
|
* @return QueryBuilderInterface |
331
|
|
|
*/ |
332
|
|
|
public function having($key, $val=[]): QueryBuilderInterface |
333
|
|
|
{ |
334
|
|
|
return $this->_having($key, $val, 'AND'); |
335
|
|
|
} |
336
|
|
|
|
337
|
|
|
/** |
338
|
|
|
* Generates a 'Having' clause prefixed with 'OR' |
339
|
|
|
* |
340
|
|
|
* @param mixed $key |
341
|
|
|
* @param mixed $val |
342
|
|
|
* @return QueryBuilderInterface |
343
|
|
|
*/ |
344
|
|
|
public function orHaving($key, $val=[]): QueryBuilderInterface |
345
|
|
|
{ |
346
|
|
|
return $this->_having($key, $val, 'OR'); |
347
|
|
|
} |
348
|
|
|
|
349
|
|
|
// -------------------------------------------------------------------------- |
350
|
|
|
// ! 'Where' methods |
351
|
|
|
// -------------------------------------------------------------------------- |
352
|
|
|
|
353
|
|
|
/** |
354
|
|
|
* Specify condition(s) in the where clause of a query |
355
|
|
|
* Note: this function works with key / value, or a |
356
|
|
|
* passed array with key / value pairs |
357
|
|
|
* |
358
|
|
|
* @param mixed $key |
359
|
|
|
* @param mixed $val |
360
|
|
|
* @param mixed $escape |
361
|
|
|
* @return QueryBuilderInterface |
362
|
|
|
*/ |
363
|
|
|
public function where($key, $val=[], $escape=NULL): QueryBuilderInterface |
364
|
|
|
{ |
365
|
|
|
return $this->_whereString($key, $val, 'AND'); |
366
|
|
|
} |
367
|
|
|
|
368
|
|
|
/** |
369
|
|
|
* Where clause prefixed with "OR" |
370
|
|
|
* |
371
|
|
|
* @param string $key |
372
|
|
|
* @param mixed $val |
373
|
|
|
* @return QueryBuilderInterface |
374
|
|
|
*/ |
375
|
|
|
public function orWhere($key, $val=[]): QueryBuilderInterface |
376
|
|
|
{ |
377
|
|
|
return $this->_whereString($key, $val, 'OR'); |
378
|
|
|
} |
379
|
|
|
|
380
|
|
|
/** |
381
|
|
|
* Where clause with 'IN' statement |
382
|
|
|
* |
383
|
|
|
* @param mixed $field |
384
|
|
|
* @param mixed $val |
385
|
|
|
* @return QueryBuilderInterface |
386
|
|
|
*/ |
387
|
|
|
public function whereIn($field, $val=[]): QueryBuilderInterface |
388
|
|
|
{ |
389
|
|
|
return $this->_whereIn($field, $val); |
390
|
|
|
} |
391
|
|
|
|
392
|
|
|
/** |
393
|
|
|
* Where in statement prefixed with "or" |
394
|
|
|
* |
395
|
|
|
* @param string $field |
396
|
|
|
* @param mixed $val |
397
|
|
|
* @return QueryBuilderInterface |
398
|
|
|
*/ |
399
|
|
|
public function orWhereIn($field, $val=[]): QueryBuilderInterface |
400
|
|
|
{ |
401
|
|
|
return $this->_whereIn($field, $val, 'IN', 'OR'); |
402
|
|
|
} |
403
|
|
|
|
404
|
|
|
/** |
405
|
|
|
* WHERE NOT IN (FOO) clause |
406
|
|
|
* |
407
|
|
|
* @param string $field |
408
|
|
|
* @param mixed $val |
409
|
|
|
* @return QueryBuilderInterface |
410
|
|
|
*/ |
411
|
|
|
public function whereNotIn($field, $val=[]): QueryBuilderInterface |
412
|
|
|
{ |
413
|
|
|
return $this->_whereIn($field, $val, 'NOT IN', 'AND'); |
414
|
|
|
} |
415
|
|
|
|
416
|
|
|
/** |
417
|
|
|
* OR WHERE NOT IN (FOO) clause |
418
|
|
|
* |
419
|
|
|
* @param string $field |
420
|
|
|
* @param mixed $val |
421
|
|
|
* @return QueryBuilderInterface |
422
|
|
|
*/ |
423
|
|
|
public function orWhereNotIn($field, $val=[]): QueryBuilderInterface |
424
|
|
|
{ |
425
|
|
|
return $this->_whereIn($field, $val, 'NOT IN', 'OR'); |
426
|
|
|
} |
427
|
|
|
|
428
|
|
|
// -------------------------------------------------------------------------- |
429
|
|
|
// ! Other Query Modifier methods |
430
|
|
|
// -------------------------------------------------------------------------- |
431
|
|
|
|
432
|
|
|
/** |
433
|
|
|
* Sets values for inserts / updates / deletes |
434
|
|
|
* |
435
|
|
|
* @param mixed $key |
436
|
|
|
* @param mixed $val |
437
|
|
|
* @return QueryBuilderInterface |
438
|
|
|
*/ |
439
|
|
|
public function set($key, $val = NULL): QueryBuilderInterface |
440
|
|
|
{ |
441
|
|
|
$this->_mixedSet($this->setArrayKeys, $key, $val, self::KEY); |
442
|
|
|
$this->_mixedSet($this->values, $key, $val, self::VALUE); |
443
|
|
|
|
444
|
|
|
// Use the keys of the array to make the insert/update string |
445
|
|
|
// Escape the field names |
446
|
|
|
$this->setArrayKeys = array_map([$this->db, '_quote'], $this->setArrayKeys); |
447
|
|
|
|
448
|
|
|
// Generate the "set" string |
449
|
|
|
$this->setString = implode('=?,', $this->setArrayKeys); |
450
|
|
|
$this->setString .= '=?'; |
451
|
|
|
|
452
|
|
|
return $this; |
453
|
|
|
} |
454
|
|
|
|
455
|
|
|
/** |
456
|
|
|
* Creates a join phrase in a compiled query |
457
|
|
|
* |
458
|
|
|
* @param string $table |
459
|
|
|
* @param string $condition |
460
|
|
|
* @param string $type |
461
|
|
|
* @return QueryBuilderInterface |
462
|
|
|
*/ |
463
|
|
|
public function join($table, $condition, $type=''): QueryBuilderInterface |
464
|
|
|
{ |
465
|
|
|
// Prefix and quote table name |
466
|
|
|
$table = explode(' ', mb_trim($table)); |
467
|
|
|
$table[0] = $this->db->quoteTable($table[0]); |
468
|
|
|
$table = $this->db->quoteIdent($table); |
469
|
|
|
$table = implode(' ', $table); |
470
|
|
|
|
471
|
|
|
// Parse out the join condition |
472
|
|
|
$parsedCondition = $this->parser->compileJoin($condition); |
473
|
|
|
$condition = $table . ' ON ' . $parsedCondition; |
474
|
|
|
|
475
|
|
|
$this->_appendMap("\n" . strtoupper($type) . ' JOIN ', $condition, 'join'); |
476
|
|
|
|
477
|
|
|
return $this; |
478
|
|
|
} |
479
|
|
|
|
480
|
|
|
/** |
481
|
|
|
* Group the results by the selected field(s) |
482
|
|
|
* |
483
|
|
|
* @param mixed $field |
484
|
|
|
* @return QueryBuilderInterface |
485
|
|
|
*/ |
486
|
|
|
public function groupBy($field): QueryBuilderInterface |
487
|
|
|
{ |
488
|
|
|
if ( ! is_scalar($field)) |
489
|
|
|
{ |
490
|
|
|
$newGroupArray = array_map([$this->db, 'quoteIdent'], $field); |
491
|
|
|
$this->groupArray = array_merge($this->groupArray, $newGroupArray); |
492
|
|
|
} |
493
|
|
|
else |
494
|
|
|
{ |
495
|
|
|
$this->groupArray[] = $this->db->quoteIdent($field); |
496
|
|
|
} |
497
|
|
|
|
498
|
|
|
$this->groupString = ' GROUP BY ' . implode(',', $this->groupArray); |
499
|
|
|
|
500
|
|
|
return $this; |
501
|
|
|
} |
502
|
|
|
|
503
|
|
|
/** |
504
|
|
|
* Order the results by the selected field(s) |
505
|
|
|
* |
506
|
|
|
* @param string $field |
507
|
|
|
* @param string $type |
508
|
|
|
* @return QueryBuilderInterface |
509
|
|
|
*/ |
510
|
|
|
public function orderBy($field, $type=""): QueryBuilderInterface |
511
|
|
|
{ |
512
|
|
|
// When ordering by random, do an ascending order if the driver |
513
|
|
|
// doesn't support random ordering |
514
|
|
|
if (stripos($type, 'rand') !== FALSE) |
515
|
|
|
{ |
516
|
|
|
$rand = $this->sql->random(); |
517
|
|
|
$type = $rand ?? 'ASC'; |
518
|
|
|
} |
519
|
|
|
|
520
|
|
|
// Set fields for later manipulation |
521
|
|
|
$field = $this->db->quoteIdent($field); |
522
|
|
|
$this->orderArray[$field] = $type; |
523
|
|
|
|
524
|
|
|
$orderClauses = []; |
525
|
|
|
|
526
|
|
|
// Flatten key/val pairs into an array of space-separated pairs |
527
|
|
|
foreach($this->orderArray as $k => $v) |
528
|
|
|
{ |
529
|
|
|
$orderClauses[] = $k . ' ' . strtoupper($v); |
530
|
|
|
} |
531
|
|
|
|
532
|
|
|
// Set the final string |
533
|
|
|
$this->orderString = ( ! isset($rand)) |
534
|
|
|
? "\nORDER BY ".implode(', ', $orderClauses) |
535
|
|
|
: "\nORDER BY".$rand; |
536
|
|
|
|
537
|
|
|
return $this; |
538
|
|
|
} |
539
|
|
|
|
540
|
|
|
/** |
541
|
|
|
* Set a limit on the current sql statement |
542
|
|
|
* |
543
|
|
|
* @param int $limit |
544
|
|
|
* @param int|bool $offset |
545
|
|
|
* @return QueryBuilderInterface |
546
|
|
|
*/ |
547
|
|
|
public function limit($limit, $offset=FALSE): QueryBuilderInterface |
548
|
|
|
{ |
549
|
|
|
$this->limit = (int) $limit; |
|
|
|
|
550
|
|
|
$this->offset = $offset; |
|
|
|
|
551
|
|
|
|
552
|
|
|
return $this; |
553
|
|
|
} |
554
|
|
|
|
555
|
|
|
// -------------------------------------------------------------------------- |
556
|
|
|
// ! Query Grouping Methods |
557
|
|
|
// -------------------------------------------------------------------------- |
558
|
|
|
|
559
|
|
|
/** |
560
|
|
|
* Adds a paren to the current query for query grouping |
561
|
|
|
* |
562
|
|
|
* @return QueryBuilderInterface |
563
|
|
|
*/ |
564
|
|
View Code Duplication |
public function groupStart(): QueryBuilderInterface |
|
|
|
|
565
|
|
|
{ |
566
|
|
|
$conj = (empty($this->queryMap)) ? ' WHERE ' : ' '; |
567
|
|
|
|
568
|
|
|
$this->_appendMap($conj, '(', 'group_start'); |
569
|
|
|
|
570
|
|
|
return $this; |
571
|
|
|
} |
572
|
|
|
|
573
|
|
|
/** |
574
|
|
|
* Adds a paren to the current query for query grouping, |
575
|
|
|
* prefixed with 'NOT' |
576
|
|
|
* |
577
|
|
|
* @return QueryBuilderInterface |
578
|
|
|
*/ |
579
|
|
View Code Duplication |
public function notGroupStart(): QueryBuilderInterface |
|
|
|
|
580
|
|
|
{ |
581
|
|
|
$conj = (empty($this->queryMap)) ? ' WHERE ' : ' AND '; |
582
|
|
|
|
583
|
|
|
$this->_appendMap($conj, ' NOT (', 'group_start'); |
584
|
|
|
|
585
|
|
|
return $this; |
586
|
|
|
} |
587
|
|
|
|
588
|
|
|
/** |
589
|
|
|
* Adds a paren to the current query for query grouping, |
590
|
|
|
* prefixed with 'OR' |
591
|
|
|
* |
592
|
|
|
* @return QueryBuilderInterface |
593
|
|
|
*/ |
594
|
|
|
public function orGroupStart(): QueryBuilderInterface |
595
|
|
|
{ |
596
|
|
|
$this->_appendMap('', ' OR (', 'group_start'); |
597
|
|
|
|
598
|
|
|
return $this; |
599
|
|
|
} |
600
|
|
|
|
601
|
|
|
/** |
602
|
|
|
* Adds a paren to the current query for query grouping, |
603
|
|
|
* prefixed with 'OR NOT' |
604
|
|
|
* |
605
|
|
|
* @return QueryBuilderInterface |
606
|
|
|
*/ |
607
|
|
|
public function orNotGroupStart(): QueryBuilderInterface |
608
|
|
|
{ |
609
|
|
|
$this->_appendMap('', ' OR NOT (', 'group_start'); |
610
|
|
|
|
611
|
|
|
return $this; |
612
|
|
|
} |
613
|
|
|
|
614
|
|
|
/** |
615
|
|
|
* Ends a query group |
616
|
|
|
* |
617
|
|
|
* @return QueryBuilderInterface |
618
|
|
|
*/ |
619
|
|
|
public function groupEnd(): QueryBuilderInterface |
620
|
|
|
{ |
621
|
|
|
$this->_appendMap('', ')', 'group_end'); |
622
|
|
|
|
623
|
|
|
return $this; |
624
|
|
|
} |
625
|
|
|
|
626
|
|
|
// -------------------------------------------------------------------------- |
627
|
|
|
// ! Query execution methods |
628
|
|
|
// -------------------------------------------------------------------------- |
629
|
|
|
|
630
|
|
|
/** |
631
|
|
|
* Select and retrieve all records from the current table, and/or |
632
|
|
|
* execute current compiled query |
633
|
|
|
* |
634
|
|
|
* @param string $table |
635
|
|
|
* @param int|bool $limit |
636
|
|
|
* @param int|bool $offset |
637
|
|
|
* @return PDOStatement |
638
|
|
|
*/ |
639
|
|
|
public function get($table='', $limit=FALSE, $offset=FALSE): PDOStatement |
640
|
|
|
{ |
641
|
|
|
// Set the table |
642
|
|
|
if ( ! empty($table)) |
643
|
|
|
{ |
644
|
|
|
$this->from($table); |
645
|
|
|
} |
646
|
|
|
|
647
|
|
|
// Set the limit, if it exists |
648
|
|
|
if (is_int($limit)) |
649
|
|
|
{ |
650
|
|
|
$this->limit($limit, $offset); |
651
|
|
|
} |
652
|
|
|
|
653
|
|
|
return $this->_run("get", $table); |
654
|
|
|
} |
655
|
|
|
|
656
|
|
|
/** |
657
|
|
|
* Convenience method for get() with a where clause |
658
|
|
|
* |
659
|
|
|
* @param string $table |
660
|
|
|
* @param array $where |
661
|
|
|
* @param int|bool $limit |
662
|
|
|
* @param int|bool $offset |
663
|
|
|
* @return PDOStatement |
664
|
|
|
*/ |
665
|
|
|
public function getWhere($table, $where=[], $limit=FALSE, $offset=FALSE): PDOStatement |
666
|
|
|
{ |
667
|
|
|
// Create the where clause |
668
|
|
|
$this->where($where); |
669
|
|
|
|
670
|
|
|
// Return the result |
671
|
|
|
return $this->get($table, $limit, $offset); |
672
|
|
|
} |
673
|
|
|
|
674
|
|
|
/** |
675
|
|
|
* Retrieve the number of rows in the selected table |
676
|
|
|
* |
677
|
|
|
* @param string $table |
678
|
|
|
* @return int |
679
|
|
|
*/ |
680
|
|
|
public function countAll($table): int |
681
|
|
|
{ |
682
|
|
|
$sql = 'SELECT * FROM '.$this->db->quoteTable($table); |
683
|
|
|
$res = $this->db->query($sql); |
|
|
|
|
684
|
|
|
return (int) count($res->fetchAll()); |
685
|
|
|
} |
686
|
|
|
|
687
|
|
|
/** |
688
|
|
|
* Retrieve the number of results for the generated query - used |
689
|
|
|
* in place of the get() method |
690
|
|
|
* |
691
|
|
|
* @param string $table |
692
|
|
|
* @param boolean $reset |
693
|
|
|
* @return int |
694
|
|
|
*/ |
695
|
|
|
public function countAllResults(string $table='', bool $reset = TRUE): int |
696
|
|
|
{ |
697
|
|
|
// Set the table |
698
|
|
|
if ( ! empty($table)) |
699
|
|
|
{ |
700
|
|
|
$this->from($table); |
701
|
|
|
} |
702
|
|
|
|
703
|
|
|
$result = $this->_run('get', $table, NULL, NULL, $reset); |
704
|
|
|
$rows = $result->fetchAll(); |
705
|
|
|
|
706
|
|
|
return (int) count($rows); |
707
|
|
|
} |
708
|
|
|
|
709
|
|
|
/** |
710
|
|
|
* Creates an insert clause, and executes it |
711
|
|
|
* |
712
|
|
|
* @param string $table |
713
|
|
|
* @param mixed $data |
714
|
|
|
* @return PDOStatement |
715
|
|
|
*/ |
716
|
|
|
public function insert($table, $data=[]): PDOStatement |
717
|
|
|
{ |
718
|
|
|
if ( ! empty($data)) |
719
|
|
|
{ |
720
|
|
|
$this->set($data); |
721
|
|
|
} |
722
|
|
|
|
723
|
|
|
return $this->_run("insert", $table); |
724
|
|
|
} |
725
|
|
|
|
726
|
|
|
/** |
727
|
|
|
* Creates and executes a batch insertion query |
728
|
|
|
* |
729
|
|
|
* @param string $table |
730
|
|
|
* @param array $data |
731
|
|
|
* @return PDOStatement |
732
|
|
|
*/ |
733
|
|
View Code Duplication |
public function insertBatch($table, $data=[]): PDOStatement |
|
|
|
|
734
|
|
|
{ |
735
|
|
|
// Get the generated values and sql string |
736
|
|
|
list($sql, $data) = $this->db->insertBatch($table, $data); |
737
|
|
|
|
738
|
|
|
return ( ! is_null($sql)) |
739
|
|
|
? $this->_run('', $table, $sql, $data) |
740
|
|
|
: NULL; |
741
|
|
|
} |
742
|
|
|
|
743
|
|
|
/** |
744
|
|
|
* Creates an update clause, and executes it |
745
|
|
|
* |
746
|
|
|
* @param string $table |
747
|
|
|
* @param mixed $data |
748
|
|
|
* @return PDOStatement |
749
|
|
|
*/ |
750
|
|
|
public function update($table, $data=[]): PDOStatement |
751
|
|
|
{ |
752
|
|
|
if ( ! empty($data)) |
753
|
|
|
{ |
754
|
|
|
$this->set($data); |
755
|
|
|
} |
756
|
|
|
|
757
|
|
|
return $this->_run("update", $table); |
758
|
|
|
} |
759
|
|
|
|
760
|
|
|
/** |
761
|
|
|
* Creates a batch update, and executes it. |
762
|
|
|
* Returns the number of affected rows |
763
|
|
|
* |
764
|
|
|
* @param string $table |
765
|
|
|
* @param array|object $data |
766
|
|
|
* @param string $where |
767
|
|
|
* @return int|null |
768
|
|
|
*/ |
769
|
|
View Code Duplication |
public function updateBatch($table, $data, $where) |
|
|
|
|
770
|
|
|
{ |
771
|
|
|
// Get the generated values and sql string |
772
|
|
|
list($sql, $data) = $this->db->updateBatch($table, $data, $where); |
773
|
|
|
|
774
|
|
|
return ( ! is_null($sql)) |
|
|
|
|
775
|
|
|
? $this->_run('', $table, $sql, $data) |
776
|
|
|
: NULL; |
777
|
|
|
} |
778
|
|
|
|
779
|
|
|
/** |
780
|
|
|
* Insertion with automatic overwrite, rather than attempted duplication |
781
|
|
|
* |
782
|
|
|
* @param string $table |
783
|
|
|
* @param array $data |
784
|
|
|
* @return \PDOStatement|null |
785
|
|
|
*/ |
786
|
|
|
public function replace($table, $data=[]) |
787
|
|
|
{ |
788
|
|
|
if ( ! empty($data)) |
789
|
|
|
{ |
790
|
|
|
$this->set($data); |
791
|
|
|
} |
792
|
|
|
|
793
|
|
|
return $this->_run("replace", $table); |
794
|
|
|
} |
795
|
|
|
|
796
|
|
|
/** |
797
|
|
|
* Deletes data from a table |
798
|
|
|
* |
799
|
|
|
* @param string $table |
800
|
|
|
* @param mixed $where |
801
|
|
|
* @return PDOStatement |
802
|
|
|
*/ |
803
|
|
|
public function delete($table, $where=''): PDOStatement |
804
|
|
|
{ |
805
|
|
|
// Set the where clause |
806
|
|
|
if ( ! empty($where)) |
807
|
|
|
{ |
808
|
|
|
$this->where($where); |
809
|
|
|
} |
810
|
|
|
|
811
|
|
|
return $this->_run("delete", $table); |
812
|
|
|
} |
813
|
|
|
|
814
|
|
|
// -------------------------------------------------------------------------- |
815
|
|
|
// ! SQL Returning Methods |
816
|
|
|
// -------------------------------------------------------------------------- |
817
|
|
|
|
818
|
|
|
/** |
819
|
|
|
* Returns the generated 'select' sql query |
820
|
|
|
* |
821
|
|
|
* @param string $table |
822
|
|
|
* @param bool $reset |
823
|
|
|
* @return string |
824
|
|
|
*/ |
825
|
|
|
public function getCompiledSelect(string $table='', bool $reset=TRUE): string |
826
|
|
|
{ |
827
|
|
|
// Set the table |
828
|
|
|
if ( ! empty($table)) |
829
|
|
|
{ |
830
|
|
|
$this->from($table); |
831
|
|
|
} |
832
|
|
|
|
833
|
|
|
return $this->_getCompile('select', $table, $reset); |
834
|
|
|
} |
835
|
|
|
|
836
|
|
|
/** |
837
|
|
|
* Returns the generated 'insert' sql query |
838
|
|
|
* |
839
|
|
|
* @param string $table |
840
|
|
|
* @param bool $reset |
841
|
|
|
* @return string |
842
|
|
|
*/ |
843
|
|
|
public function getCompiledInsert(string $table, bool $reset=TRUE): string |
844
|
|
|
{ |
845
|
|
|
return $this->_getCompile('insert', $table, $reset); |
846
|
|
|
} |
847
|
|
|
|
848
|
|
|
/** |
849
|
|
|
* Returns the generated 'update' sql query |
850
|
|
|
* |
851
|
|
|
* @param string $table |
852
|
|
|
* @param bool $reset |
853
|
|
|
* @return string |
854
|
|
|
*/ |
855
|
|
|
public function getCompiledUpdate(string $table='', bool $reset=TRUE): string |
856
|
|
|
{ |
857
|
|
|
return $this->_getCompile('update', $table, $reset); |
858
|
|
|
} |
859
|
|
|
|
860
|
|
|
/** |
861
|
|
|
* Returns the generated 'delete' sql query |
862
|
|
|
* |
863
|
|
|
* @param string $table |
864
|
|
|
* @param bool $reset |
865
|
|
|
* @return string |
866
|
|
|
*/ |
867
|
|
|
public function getCompiledDelete(string $table='', bool $reset=TRUE): string |
868
|
|
|
{ |
869
|
|
|
return $this->_getCompile('delete', $table, $reset); |
870
|
|
|
} |
871
|
|
|
|
872
|
|
|
// -------------------------------------------------------------------------- |
873
|
|
|
// ! Miscellaneous Methods |
874
|
|
|
// -------------------------------------------------------------------------- |
875
|
|
|
|
876
|
|
|
/** |
877
|
|
|
* Clear out the class variables, so the next query can be run |
878
|
|
|
* |
879
|
|
|
* @return void |
880
|
|
|
*/ |
881
|
|
|
public function resetQuery() |
882
|
|
|
{ |
883
|
|
|
// Reset strings and booleans |
884
|
|
|
foreach($this->stringVars as $var) |
885
|
|
|
{ |
886
|
|
|
$this->$var = NULL; |
887
|
|
|
} |
888
|
|
|
|
889
|
|
|
// Reset arrays |
890
|
|
|
foreach($this->arrayVars as $var) |
891
|
|
|
{ |
892
|
|
|
$this->$var = []; |
893
|
|
|
} |
894
|
|
|
} |
895
|
|
|
} |
896
|
|
|
// End of query_builder.php |
This check looks for assignments to scalar types that may be of the wrong type.
To ensure the code behaves as expected, it may be a good idea to add an explicit type cast.