Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
100.00% covered (success)
100.00%
144 / 144
100.00% covered (success)
100.00%
45 / 45
CRAP
100.00% covered (success)
100.00%
1 / 1
QueryBuilder
100.00% covered (success)
100.00%
144 / 144
100.00% covered (success)
100.00%
45 / 45
69
100.00% covered (success)
100.00%
1 / 1
 select
100.00% covered (success)
100.00%
13 / 13
100.00% covered (success)
100.00%
1 / 1
5
 selectMax
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 selectMin
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 selectAvg
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 selectSum
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 returning
100.00% covered (success)
100.00%
4 / 4
100.00% covered (success)
100.00%
1 / 1
2
 distinct
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 explain
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 table
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 from
100.00% covered (success)
100.00%
6 / 6
100.00% covered (success)
100.00%
1 / 1
1
 like
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 orLike
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 notLike
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 orNotLike
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 having
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 orHaving
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 where
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 orWhere
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 whereIn
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 orWhereIn
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 whereNotIn
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 orWhereNotIn
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 set
100.00% covered (success)
100.00%
12 / 12
100.00% covered (success)
100.00%
1 / 1
2
 join
100.00% covered (success)
100.00%
8 / 8
100.00% covered (success)
100.00%
1 / 1
1
 groupBy
100.00% covered (success)
100.00%
9 / 9
100.00% covered (success)
100.00%
1 / 1
2
 orderBy
100.00% covered (success)
100.00%
13 / 13
100.00% covered (success)
100.00%
1 / 1
4
 limit
100.00% covered (success)
100.00%
3 / 3
100.00% covered (success)
100.00%
1 / 1
1
 groupStart
100.00% covered (success)
100.00%
3 / 3
100.00% covered (success)
100.00%
1 / 1
2
 notGroupStart
100.00% covered (success)
100.00%
3 / 3
100.00% covered (success)
100.00%
1 / 1
2
 orGroupStart
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 orNotGroupStart
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 groupEnd
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 get
100.00% covered (success)
100.00%
5 / 5
100.00% covered (success)
100.00%
1 / 1
3
 getWhere
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 countAll
100.00% covered (success)
100.00%
3 / 3
100.00% covered (success)
100.00%
1 / 1
2
 countAllResults
100.00% covered (success)
100.00%
5 / 5
100.00% covered (success)
100.00%
1 / 1
2
 insert
100.00% covered (success)
100.00%
3 / 3
100.00% covered (success)
100.00%
1 / 1
2
 insertBatch
100.00% covered (success)
100.00%
4 / 4
100.00% covered (success)
100.00%
1 / 1
2
 update
100.00% covered (success)
100.00%
3 / 3
100.00% covered (success)
100.00%
1 / 1
2
 updateBatch
100.00% covered (success)
100.00%
5 / 5
100.00% covered (success)
100.00%
1 / 1
4
 delete
100.00% covered (success)
100.00%
3 / 3
100.00% covered (success)
100.00%
1 / 1
2
 getCompiledSelect
100.00% covered (success)
100.00%
3 / 3
100.00% covered (success)
100.00%
1 / 1
2
 getCompiledInsert
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 getCompiledUpdate
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 getCompiledDelete
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
1<?php declare(strict_types=1);
2/**
3 * Query
4 *
5 * SQL Query Builder / Database Abstraction Layer
6 *
7 * PHP version 8.1
8 *
9 * @package     Query
10 * @author      Timothy J. Warren <tim@timshomepage.net>
11 * @copyright   2012 - 2022 Timothy J. Warren
12 * @license     http://www.opensource.org/licenses/mit-license.html  MIT License
13 * @link        https://git.timshomepage.net/aviat/Query
14 * @version     4.0.0
15 */
16namespace Query;
17
18use function is_array;
19use function is_int;
20use function mb_trim;
21
22use PDOStatement;
23
24/**
25 * Convenience class for creating sql queries
26 */
27class QueryBuilder extends QueryBuilderBase implements QueryBuilderInterface {
28    // --------------------------------------------------------------------------
29    // ! Select Queries
30    // --------------------------------------------------------------------------
31    /**
32     * Specifies rows to select in a query
33     */
34    public function select(string $fields): self
35    {
36        // Split fields by comma
37        $fieldsArray = explode(',', $fields);
38        $fieldsArray = array_map('mb_trim', $fieldsArray);
39
40        // Split on 'As'
41        foreach ($fieldsArray as $key => $field)
42        {
43            if (stripos($field, 'as') !== FALSE)
44            {
45                $fieldsArray[$key] = preg_split('` as `i', (string) $field);
46                $fieldsArray[$key] = array_map('mb_trim', $fieldsArray[$key]);
47            }
48        }
49
50        // Quote the identifiers
51        $safeArray = $this->driver->quoteIdent($fieldsArray);
52
53        unset($fieldsArray);
54
55        // Join the strings back together
56        foreach ($safeArray as $i => $iValue)
57        {
58            if (is_array($iValue))
59            {
60                $safeArray[$i] = implode(' AS ', $iValue);
61            }
62        }
63
64        $this->state->appendSelectString(implode(', ', $safeArray));
65
66        return $this;
67    }
68
69    /**
70     * Selects the maximum value of a field from a query
71     *
72     * @param string|bool $as
73     */
74    public function selectMax(string $field, $as=FALSE): self
75    {
76        // Create the select string
77        $this->state->appendSelectString(' MAX'.$this->_select($field, $as));
78        return $this;
79    }
80
81    /**
82     * Selects the minimum value of a field from a query
83     *
84     * @param string|bool $as
85     */
86    public function selectMin(string $field, $as=FALSE): self
87    {
88        // Create the select string
89        $this->state->appendSelectString(' MIN'.$this->_select($field, $as));
90        return $this;
91    }
92
93    /**
94     * Selects the average value of a field from a query
95     *
96     * @param string|bool $as
97     */
98    public function selectAvg(string $field, $as=FALSE): self
99    {
100        // Create the select string
101        $this->state->appendSelectString(' AVG'.$this->_select($field, $as));
102        return $this;
103    }
104
105    /**
106     * Selects the sum of a field from a query
107     *
108     * @param string|bool $as
109     */
110    public function selectSum(string $field, $as=FALSE): self
111    {
112        // Create the select string
113        $this->state->appendSelectString(' SUM'.$this->_select($field, $as));
114        return $this;
115    }
116
117    /**
118     * Add a 'returning' clause to an insert,update, or delete query
119     *
120     * @return $this
121     */
122    public function returning(string $fields = ''): self
123    {
124        $this->returning = TRUE;
125
126        // Re-use the string select field for generating the returning type clause
127        if ($fields !== '')
128        {
129            return $this->select($fields);
130        }
131
132        return $this;
133    }
134
135    /**
136     * Adds the 'distinct' keyword to a query
137     */
138    public function distinct(): self
139    {
140        // Prepend the keyword to the select string
141        $this->state->setSelectString(' DISTINCT' . $this->state->getSelectString());
142        return $this;
143    }
144
145    /**
146     * Tell the database to give you the query plan instead of result set
147     */
148    public function explain(): self
149    {
150        $this->explain = TRUE;
151        return $this;
152    }
153
154    /**
155     * Specify the database table to select from
156     *
157     * Alias of `from` method to better match CodeIgniter 4
158     *
159     * @param string $tableName
160     */
161    public function table(string $tableName): self
162    {
163        return $this->from($tableName);
164    }
165
166    /**
167     * Specify the database table to select from
168     */
169    public function from(string $tableName): self
170    {
171        // Split identifiers on spaces
172        $identArray = explode(' ', mb_trim($tableName));
173        $identArray = array_map('mb_trim', $identArray);
174
175        // Quote the identifiers
176        $identArray[0] = $this->driver->quoteTable($identArray[0]);
177        $identArray = $this->driver->quoteIdent($identArray);
178
179        // Paste it back together
180        $this->state->setFromString(implode(' ', $identArray));
181
182        return $this;
183    }
184
185    // --------------------------------------------------------------------------
186    // ! 'Like' methods
187    // --------------------------------------------------------------------------
188    /**
189     * Creates a Like clause in the sql statement
190     */
191    public function like(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self
192    {
193        return $this->_like($field, $values, LikeType::parse($pos));
194    }
195
196    /**
197     * Generates an OR Like clause
198     */
199    public function orLike(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self
200    {
201        return $this->_like($field, $values, LikeType::parse($pos), 'LIKE', 'OR');
202    }
203
204    /**
205     * Generates a NOT LIKE clause
206     */
207    public function notLike(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self
208    {
209        return $this->_like($field, $values, LikeType::parse($pos), 'NOT LIKE');
210    }
211
212    /**
213     * Generates a OR NOT LIKE clause
214     */
215    public function orNotLike(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self
216    {
217        return $this->_like($field, $values, LikeType::parse($pos), 'NOT LIKE', 'OR');
218    }
219
220    // --------------------------------------------------------------------------
221    // ! Having methods
222    // --------------------------------------------------------------------------
223    /**
224     * Generates a 'Having' clause
225     */
226    public function having(mixed $key, mixed $values=[]): self
227    {
228        return $this->_having($key, $values);
229    }
230
231    /**
232     * Generates a 'Having' clause prefixed with 'OR'
233     */
234    public function orHaving(mixed $key, mixed $values=[]): self
235    {
236        return $this->_having($key, $values, 'OR');
237    }
238
239    // --------------------------------------------------------------------------
240    // ! 'Where' methods
241    // --------------------------------------------------------------------------
242    /**
243     * Specify condition(s) in the where clause of a query
244     * Note: this function works with key / value, or a
245     * passed array with key / value pairs
246     */
247    public function where(mixed $key, mixed $values=[]): self
248    {
249        return $this->_whereString($key, $values);
250    }
251
252    /**
253     * Where clause prefixed with "OR"
254     */
255    public function orWhere(mixed $key, mixed $values=[]): self
256    {
257        return $this->_whereString($key, $values, 'OR');
258    }
259
260    /**
261     * Where clause with 'IN' statement
262     */
263    public function whereIn(string $field, mixed $val=[]): self
264    {
265        return $this->_whereIn($field, $val);
266    }
267
268    /**
269     * Where in statement prefixed with "or"
270     */
271    public function orWhereIn(string $field, mixed $val=[]): self
272    {
273        return $this->_whereIn($field, $val, 'IN', 'OR');
274    }
275
276    /**
277     * WHERE NOT IN (FOO) clause
278     */
279    public function whereNotIn(string $field, mixed $val=[]): self
280    {
281        return $this->_whereIn($field, $val, 'NOT IN');
282    }
283
284    /**
285     * OR WHERE NOT IN (FOO) clause
286     */
287    public function orWhereNotIn(string $field, mixed $val=[]): self
288    {
289        return $this->_whereIn($field, $val, 'NOT IN', 'OR');
290    }
291
292    // --------------------------------------------------------------------------
293    // ! Other Query Modifier methods
294    // --------------------------------------------------------------------------
295    /**
296     * Sets values for inserts / updates / deletes
297     */
298    public function set(mixed $key, mixed $val = NULL): self
299    {
300        $pairs = is_scalar($key) ? [$key => $val] : $key;
301
302        $keys = array_keys($pairs);
303        $values = array_values($pairs);
304
305        $this->state->appendSetArrayKeys($keys);
306        $this->state->appendValues($values);
307
308        // Use the keys of the array to make the insert/update string
309        // Escape the field names
310        $this->state->setSetArrayKeys(
311            array_map([$this->driver, '_quote'], $this->state->getSetArrayKeys())
312        );
313
314        // Generate the "set" string
315        $setString = implode('=?,', $this->state->getSetArrayKeys());
316        $setString .= '=?';
317
318        $this->state->setSetString($setString);
319
320        return $this;
321    }
322
323    /**
324     * Creates a join phrase in a compiled query
325     */
326    public function join(string $table, string $condition, JoinType|string $type=JoinType::INNER): self
327    {
328        // Prefix and quote table name
329        $tableArr = explode(' ', mb_trim($table));
330        $tableArr[0] = $this->driver->quoteTable($tableArr[0]);
331        $tableArr = $this->driver->quoteIdent($tableArr);
332        $table = implode(' ', $tableArr);
333
334        // Parse out the join condition
335        $parsedCondition = $this->parser->compileJoin($condition);
336        $condition = $table . ' ON ' . $parsedCondition;
337
338        $this->state->appendMap("\n" . strtoupper(JoinType::parse($type)->value) . ' JOIN ', $condition, MapType::JOIN);
339
340        return $this;
341    }
342
343    /**
344     * Group the results by the selected field(s)
345     */
346    public function groupBy(mixed $field): self
347    {
348        if ( ! is_scalar($field))
349        {
350            $newGroupArray = array_merge(
351                $this->state->getGroupArray(),
352                array_map([$this->driver, 'quoteIdent'], $field)
353            );
354            $this->state->setGroupArray($newGroupArray);
355        }
356        else
357        {
358            $this->state->appendGroupArray($this->driver->quoteIdent($field));
359        }
360
361        $this->state->setGroupString(' GROUP BY ' . implode(',', $this->state->getGroupArray()));
362
363        return $this;
364    }
365
366    /**
367     * Order the results by the selected field(s)
368     */
369    public function orderBy(string $field, string $type=''): self
370    {
371        // When ordering by random, do an ascending order if the driver
372        // doesn't support random ordering
373        if (stripos($type, 'rand') !== FALSE)
374        {
375            $rand = $this->driver->getSql()->random();
376            $type = $rand ?? 'ASC';
377        }
378
379        // Set fields for later manipulation
380        $field = $this->driver->quoteIdent($field);
381        $this->state->setOrderArray($field, $type);
382
383        $orderClauses = [];
384
385        // Flatten key/val pairs into an array of space-separated pairs
386        foreach($this->state->getOrderArray() as $k => $v)
387        {
388            $orderClauses[] = $k . ' ' . strtoupper($v);
389        }
390
391        // Set the final string
392        $orderString =  isset($rand)
393            ? "\nORDER BY".$rand
394            : "\nORDER BY ".implode(', ', $orderClauses);
395
396        $this->state->setOrderString($orderString);
397
398        return $this;
399    }
400
401    /**
402     * Set a limit on the current sql statement
403     */
404    public function limit(int $limit, ?int $offset=NULL): self
405    {
406        $this->state->setLimit($limit);
407        $this->state->setOffset($offset);
408
409        return $this;
410    }
411
412    // --------------------------------------------------------------------------
413    // ! Query Grouping Methods
414    // --------------------------------------------------------------------------
415    /**
416     * Adds a paren to the current query for query grouping
417     */
418    public function groupStart(): self
419    {
420        $conj = empty($this->state->getQueryMap()) ? ' WHERE ' : ' ';
421
422        $this->state->appendMap($conj, '(', MapType::GROUP_START);
423
424        return $this;
425    }
426
427    /**
428     * Adds a paren to the current query for query grouping,
429     * prefixed with 'NOT'
430     */
431    public function notGroupStart(): self
432    {
433        $conj = empty($this->state->getQueryMap()) ? ' WHERE ' : ' AND ';
434
435        $this->state->appendMap($conj, ' NOT (', MapType::GROUP_START);
436
437        return $this;
438    }
439
440    /**
441     * Adds a paren to the current query for query grouping,
442     * prefixed with 'OR'
443     */
444    public function orGroupStart(): self
445    {
446        $this->state->appendMap('', ' OR (', MapType::GROUP_START);
447
448        return $this;
449    }
450
451    /**
452     * Adds a paren to the current query for query grouping,
453     * prefixed with 'OR NOT'
454     */
455    public function orNotGroupStart(): self
456    {
457        $this->state->appendMap('', ' OR NOT (', MapType::GROUP_START);
458
459        return $this;
460    }
461
462    /**
463     * Ends a query group
464     */
465    public function groupEnd(): self
466    {
467        $this->state->appendMap('', ')', MapType::GROUP_END);
468
469        return $this;
470    }
471
472    // --------------------------------------------------------------------------
473    // ! Query execution methods
474    // --------------------------------------------------------------------------
475    /**
476     * Select and retrieve all records from the current table, and/or
477     * execute current compiled query
478     */
479    public function get(string $table='', ?int $limit=NULL, ?int $offset=NULL): PDOStatement
480    {
481        // Set the table
482        if ( ! empty($table))
483        {
484            $this->from($table);
485        }
486
487        // Set the limit, if it exists
488        if (is_int($limit))
489        {
490            $this->limit($limit, $offset);
491        }
492
493        return $this->_run(QueryType::SELECT, $table);
494    }
495
496    /**
497     * Convenience method for get() with a where clause
498     *
499     * @param mixed $where
500     */
501    public function getWhere(string $table, $where=[], ?int $limit=NULL, ?int $offset=NULL): PDOStatement
502    {
503        // Create the where clause
504        $this->where($where);
505
506        // Return the result
507        return $this->get($table, $limit, $offset);
508    }
509
510    /**
511     * Retrieve the number of rows in the selected table
512     */
513    public function countAll(string $table): int
514    {
515        $sql = 'SELECT * FROM '.$this->driver->quoteTable($table);
516        $res = $this->driver->query($sql);
517        return (int) (is_countable($res->fetchAll()) ? count($res->fetchAll()) : 0);
518    }
519
520    /**
521     * Retrieve the number of results for the generated query - used
522     * in place of the get() method
523     */
524    public function countAllResults(string $table='', bool $reset = TRUE): int
525    {
526        // Set the table
527        if ( ! empty($table))
528        {
529            $this->from($table);
530        }
531
532        $result = $this->_run(QueryType::SELECT, $table, NULL, NULL, $reset);
533        $rows = $result->fetchAll();
534
535        return (int) count($rows);
536    }
537
538    /**
539     * Creates an insert clause, and executes it
540     */
541    public function insert(string $table, mixed $data=[]): PDOStatement
542    {
543        if ( ! empty($data))
544        {
545            $this->set($data);
546        }
547
548        return $this->_run(QueryType::INSERT, $table);
549    }
550
551    /**
552     * Creates and executes a batch insertion query
553     */
554    public function insertBatch(string $table, $data=[]): ?PDOStatement
555    {
556        // Get the generated values and sql string
557        [$sql, $data] = $this->driver->insertBatch($table, $data);
558
559        return $sql !== NULL
560            ? $this->_run(QueryType::INSERT_BATCH, $table, $sql, $data)
561            : NULL;
562    }
563
564    /**
565     * Creates an update clause, and executes it
566     */
567    public function update(string $table, mixed $data=[]): PDOStatement
568    {
569        if ( ! empty($data))
570        {
571            $this->set($data);
572        }
573
574        return $this->_run(QueryType::UPDATE, $table);
575    }
576
577    /**
578     * Creates a batch update, and executes it.
579     * Returns the number of affected rows
580     */
581    public function updateBatch(string $table, array $data, string $where): ?int
582    {
583        if (empty($table) || empty($data) || empty($where))
584        {
585            return NULL;
586        }
587
588        // Get the generated values and sql string
589        [$sql, $data, $affectedRows] = $this->driver->updateBatch($table, $data, $where);
590
591        $this->_run(QueryType::UPDATE_BATCH, $table, $sql, $data);
592        return $affectedRows;
593    }
594
595    /**
596     * Deletes data from a table
597     */
598    public function delete(string $table, mixed $where=''): PDOStatement
599    {
600        // Set the where clause
601        if ( ! empty($where))
602        {
603            $this->where($where);
604        }
605
606        return $this->_run(QueryType::DELETE, $table);
607    }
608
609    // --------------------------------------------------------------------------
610    // ! SQL Returning Methods
611    // --------------------------------------------------------------------------
612    /**
613     * Returns the generated 'select' sql query
614     */
615    public function getCompiledSelect(string $table='', bool $reset=TRUE): string
616    {
617        // Set the table
618        if ( ! empty($table))
619        {
620            $this->from($table);
621        }
622
623        return $this->_getCompile(QueryType::SELECT, $table, $reset);
624    }
625
626    /**
627     * Returns the generated 'insert' sql query
628     */
629    public function getCompiledInsert(string $table, bool $reset=TRUE): string
630    {
631        return $this->_getCompile(QueryType::INSERT, $table, $reset);
632    }
633
634    /**
635     * Returns the generated 'update' sql query
636     */
637    public function getCompiledUpdate(string $table='', bool $reset=TRUE): string
638    {
639        return $this->_getCompile(QueryType::UPDATE, $table, $reset);
640    }
641
642    /**
643     * Returns the generated 'delete' sql query
644     */
645    public function getCompiledDelete(string $table='', bool $reset=TRUE): string
646    {
647        return $this->_getCompile(QueryType::DELETE, $table, $reset);
648    }
649}