1 | <?php |
||
2 | |||
3 | /** |
||
4 | * This file is part of the miBadger package. |
||
5 | * |
||
6 | * @author Michael Webbers <[email protected]> |
||
7 | * @license http://opensource.org/licenses/Apache-2.0 Apache v2 License |
||
8 | */ |
||
9 | |||
10 | namespace miBadger\Query; |
||
11 | |||
12 | /** |
||
13 | * The query class. |
||
14 | * |
||
15 | * @since 1.0.0 |
||
16 | */ |
||
17 | class Query implements QueryInterface |
||
18 | { |
||
19 | /* @var \PDO The PDO. */ |
||
20 | private $pdo; |
||
21 | |||
22 | /* @var array The bindings. */ |
||
23 | private $bindings; |
||
24 | |||
25 | /* @var QueryBuilder The query builder. */ |
||
26 | private $queryBuilder; |
||
27 | |||
28 | /* @var QueryExpression The expression for the where clause */ |
||
29 | private $whereExpression; |
||
30 | |||
31 | /* @var QueryExpression The expression for the having clause */ |
||
32 | private $havingExpression; |
||
33 | |||
34 | 21 | /** |
|
35 | * Construct a query object with the given pdo and table. |
||
36 | 21 | * |
|
37 | 21 | * @param \PDO $pdo |
|
38 | 21 | * @param string $table |
|
39 | 21 | */ |
|
40 | public function __construct(\PDO $pdo, string $table) |
||
41 | { |
||
42 | $this->pdo = $pdo; |
||
43 | $this->bindings = []; |
||
44 | $this->whereExpression = null; |
||
45 | $this->havingExpression = null; |
||
46 | 21 | $this->queryBuilder = new QueryBuilder($table); |
|
47 | } |
||
48 | 21 | ||
49 | /** |
||
50 | * Returns a string representation of the query object. |
||
51 | * |
||
52 | * @return string a string representation of the query object. |
||
53 | */ |
||
54 | 17 | public function __toString() |
|
55 | { |
||
56 | 17 | return $this->toPreparedString(); |
|
57 | } |
||
58 | 17 | ||
59 | /** |
||
60 | * Binds the registered Where and Having expression clauses, |
||
61 | * before being executed (or printed) |
||
62 | */ |
||
63 | private function addLateBindings() |
||
64 | 1 | { |
|
65 | // Late binding of where statement |
||
66 | 1 | if ($this->whereExpression !== null) { |
|
67 | 1 | foreach ($this->whereExpression->getFlattenedConditions() as $cond) { |
|
68 | $cond->bind($this, 'where'); |
||
69 | 1 | } |
|
70 | } |
||
71 | |||
72 | // Late binding of having statement |
||
73 | if ($this->havingExpression !== null) { |
||
74 | foreach ($this->havingExpression->getFlattenedConditions() as $cond) { |
||
75 | 1 | $cond->bind($this, 'having'); |
|
76 | } |
||
77 | 1 | } |
|
78 | } |
||
79 | 1 | ||
80 | /** |
||
81 | * Removes the late-bound bindings for the Where/Having expression clauses, |
||
82 | * used to make debug strings possible |
||
83 | */ |
||
84 | private function clearLateBindings() |
||
85 | 1 | { |
|
86 | // Remove the binding from the QueryConditions and remove the bound values. |
||
87 | 1 | if ($this->whereExpression !== null) { |
|
88 | foreach ($this->whereExpression->getFlattenedConditions() as $cond) { |
||
89 | 1 | $cond->clearBinding(); |
|
90 | } |
||
91 | } |
||
92 | $this->removeBindings('where'); |
||
93 | |||
94 | if ($this->havingExpression !== null) { |
||
95 | 1 | foreach ($this->havingExpression->getFlattenedConditions() as $cond) { |
|
96 | $cond->clearBinding(); |
||
97 | 1 | } |
|
98 | } |
||
99 | 1 | $this->removeBindings('having'); |
|
100 | } |
||
101 | |||
102 | /** |
||
103 | * Returns the prepared SQL string, where binding substitutions have been applied |
||
104 | * Example: SELECT * FROM table WHERE name = :where1 |
||
105 | 1 | */ |
|
106 | public function toPreparedString(): string |
||
107 | 1 | { |
|
108 | $this->addLateBindings(); |
||
109 | 1 | return $this->queryBuilder->__toString(); |
|
110 | } |
||
111 | |||
112 | /** |
||
113 | * Returns the SQL string without prepared statements, useful for debugging or logging purposes |
||
114 | * Example: SELECT * FROM table WHERE name = John Doe |
||
115 | 1 | */ |
|
116 | public function toDebugString(): string |
||
117 | 1 | { |
|
118 | $this->clearLateBindings(); |
||
119 | 1 | return $this->queryBuilder->__toString(); |
|
120 | } |
||
121 | |||
122 | /** |
||
123 | * {@inheritdoc} |
||
124 | */ |
||
125 | 1 | public function select(Array $columns = ['*'], bool $quote = true) |
|
126 | { |
||
127 | 1 | if ($quote === true && $columns !== ['*']) { |
|
128 | $quotedColumns = []; |
||
129 | 1 | foreach ($columns as $column) { |
|
130 | $quotedColumns[] = '`' . $column . '`'; |
||
131 | } |
||
132 | $this->queryBuilder->select($quotedColumns); |
||
133 | } else { |
||
134 | $this->queryBuilder->select($columns); |
||
135 | 7 | } |
|
136 | |||
137 | 7 | return $this; |
|
138 | 1 | } |
|
139 | |||
140 | 6 | /** |
|
141 | * {@inheritdoc} |
||
142 | */ |
||
143 | 7 | public function insert(array $values) |
|
144 | { |
||
145 | $this->bindings['insert'] = []; |
||
146 | $this->queryBuilder->insert($this->setBindings('insert', $values)); |
||
147 | |||
148 | return $this; |
||
149 | 1 | } |
|
150 | |||
151 | 1 | /** |
|
152 | * {@inheritdoc} |
||
153 | 1 | */ |
|
154 | public function update(array $values) |
||
155 | { |
||
156 | $this->queryBuilder->update($this->setBindings('update', $values)); |
||
157 | |||
158 | return $this; |
||
159 | 1 | } |
|
160 | |||
161 | 1 | /** |
|
162 | * {@inheritdoc} |
||
163 | 1 | */ |
|
164 | public function delete() |
||
165 | { |
||
166 | $this->queryBuilder->delete(); |
||
167 | |||
168 | return $this; |
||
169 | 2 | } |
|
170 | |||
171 | 2 | /** |
|
172 | * {@inheritdoc} |
||
173 | 2 | */ |
|
174 | public function join(string $table, string $primary, string $operator, string $secondary) |
||
175 | { |
||
176 | $this->queryBuilder->join($table, $primary, $operator, $secondary); |
||
177 | |||
178 | return $this; |
||
179 | 1 | } |
|
180 | |||
181 | 1 | /** |
|
182 | * {@inheritdoc} |
||
183 | 1 | */ |
|
184 | public function leftJoin(string $table, string $primary, string $operator, string $secondary) |
||
185 | { |
||
186 | $this->queryBuilder->leftJoin($table, $primary, $operator, $secondary); |
||
187 | |||
188 | return $this; |
||
189 | } |
||
190 | |||
191 | 4 | /** |
|
192 | * {@inheritdoc} |
||
193 | 4 | */ |
|
194 | public function rightJoin(string $table, string $primary, string $operator, string $secondary) |
||
195 | 4 | { |
|
196 | 1 | $this->queryBuilder->rightJoin($table, $primary, $operator, $secondary); |
|
197 | 1 | ||
198 | return $this; |
||
199 | } |
||
200 | |||
201 | 4 | /** |
|
202 | * {@inheritdoc} |
||
203 | 4 | */ |
|
204 | public function crossJoin(string $table, string $primary, string $operator, string $secondary) |
||
205 | { |
||
206 | $this->queryBuilder->crossJoin($table, $primary, $operator, $secondary); |
||
207 | |||
208 | return $this; |
||
209 | } |
||
210 | |||
211 | /** |
||
212 | 1 | * {@inheritdoc} |
|
213 | */ |
||
214 | 1 | public function where(QueryExpression $exp) |
|
215 | { |
||
216 | 1 | $this->whereExpression = $exp; |
|
217 | 1 | ||
218 | 1 | if ($this->queryBuilder->where !== null) { |
|
219 | 1 | throw new QueryException('Can only call where on query once.'); |
|
220 | 1 | } |
|
221 | 1 | ||
222 | $this->queryBuilder->where($exp); |
||
223 | return $this; |
||
224 | 1 | } |
|
225 | |||
226 | public function having(QueryExpression $exp) |
||
227 | { |
||
228 | $this->havingExpression = $exp; |
||
229 | |||
230 | if ($this->queryBuilder->having !== null) { |
||
231 | throw new QueryException('Can only call having on query once.'); |
||
232 | } |
||
233 | |||
234 | 10 | $this->queryBuilder->having($exp); |
|
235 | return $this; |
||
236 | 10 | } |
|
237 | |||
238 | 10 | /** |
|
239 | * {@inheritdoc} |
||
240 | */ |
||
241 | public function groupBy(string $column) |
||
242 | { |
||
243 | $this->queryBuilder->groupBy($column); |
||
244 | |||
245 | return $this; |
||
246 | } |
||
247 | |||
248 | 3 | /** |
|
249 | * {@inheritdoc} |
||
250 | 3 | */ |
|
251 | public function orderBy(string $column, $order = null) |
||
252 | 3 | { |
|
253 | 3 | $this->queryBuilder->orderBy($column, $order); |
|
254 | |||
255 | return $this; |
||
256 | 3 | } |
|
257 | |||
258 | /** |
||
259 | * {@inheritdoc} |
||
260 | */ |
||
261 | public function limit($limit) |
||
262 | { |
||
263 | $this->queryBuilder->limit($this->setBinding('limit', (int) $limit)); |
||
264 | |||
265 | return $this; |
||
266 | 2 | } |
|
267 | |||
268 | 2 | /** |
|
269 | * {@inheritdoc} |
||
270 | */ |
||
271 | public function offset($offset) |
||
272 | { |
||
273 | $this->queryBuilder->offset($this->setBinding('offset', (int) $offset)); |
||
274 | |||
275 | return $this; |
||
276 | } |
||
277 | |||
278 | 2 | /** |
|
279 | * Returns the result of the executed prepared query. |
||
280 | 2 | * |
|
281 | * @return QueryResult the result of the executed prepared query. |
||
282 | */ |
||
283 | public function execute(): QueryResult |
||
284 | { |
||
285 | $pdoStatement = $this->pdo->prepare((string) $this); |
||
286 | |||
287 | foreach ($this->bindings as $clause => $predicate) { |
||
288 | foreach ($predicate as $key => $value) { |
||
289 | 4 | $pdoStatement->bindValue(sprintf(':%s%d', $clause, $key + 1), $value, $this->getPdoDataType($value)); |
|
290 | } |
||
291 | 4 | } |
|
292 | |||
293 | 4 | $pdoStatement->execute(); |
|
294 | |||
295 | $this->clearLateBindings(); |
||
296 | |||
297 | return new QueryResult($pdoStatement); |
||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
298 | } |
||
299 | |||
300 | /** |
||
301 | * Returns the data type of the given value. |
||
302 | * |
||
303 | * @param mixed $value |
||
304 | * @return int the data type of the given value. |
||
305 | */ |
||
306 | protected function getPdoDataType($value): int |
||
307 | { |
||
308 | $result = \PDO::PARAM_STR; |
||
309 | |||
310 | if (is_bool($value)) { |
||
311 | $result = \PDO::PARAM_BOOL; |
||
312 | } elseif (is_null($value)) { |
||
313 | $result = \PDO::PARAM_NULL; |
||
314 | } elseif (is_int($value)) { |
||
315 | $result = \PDO::PARAM_INT; |
||
316 | } |
||
317 | |||
318 | return $result; |
||
319 | } |
||
320 | |||
321 | /** |
||
322 | * Returns a binding for the given clause and value. |
||
323 | * |
||
324 | * @param string $clause |
||
325 | * @param string $value |
||
326 | * @return string a binding for the given clause and value. |
||
327 | */ |
||
328 | public function addBinding(string $clause, $value) |
||
329 | { |
||
330 | $clause = strtolower($clause); |
||
331 | $this->bindings[$clause][] = $value; |
||
332 | |||
333 | return sprintf(':%s%d', $clause, count($this->bindings[$clause])); |
||
334 | } |
||
335 | |||
336 | /** |
||
337 | * Returns bindings for the given clause and values. |
||
338 | * |
||
339 | * @param string $clause |
||
340 | * @param array $values |
||
341 | * @return array bindings for the given clause and values. |
||
342 | */ |
||
343 | public function addBindings(string $clause, array $values) |
||
344 | { |
||
345 | $result = []; |
||
346 | |||
347 | foreach ($values as $key => $value) { |
||
348 | $result[$key] = $this->addBinding($clause, $value); |
||
349 | } |
||
350 | |||
351 | return $result; |
||
352 | } |
||
353 | |||
354 | /** |
||
355 | * Returns a binding for the given clause and value. |
||
356 | * |
||
357 | * @param string $clause |
||
358 | * @param string $value |
||
359 | * @return string a binding for the given clause and value. |
||
360 | */ |
||
361 | private function setBinding(string $clause, $value) |
||
362 | { |
||
363 | return $this->removeBindings($clause)->addBinding($clause, $value); |
||
364 | } |
||
365 | |||
366 | /** |
||
367 | * Returns bindings for the given clause and values. |
||
368 | * |
||
369 | * @param string $clause |
||
370 | * @param array $values |
||
371 | * @return array bindings for the given clause and values. |
||
372 | */ |
||
373 | private function setBindings(string $clause, array $values) |
||
374 | { |
||
375 | return $this->removeBindings($clause)->addBindings($clause, $values); |
||
376 | } |
||
377 | |||
378 | /** |
||
379 | * Remove the bindings that are associated with the given clause. |
||
380 | * |
||
381 | * @param string $clause |
||
382 | * @return $this |
||
383 | */ |
||
384 | private function removeBindings(string $clause) |
||
385 | { |
||
386 | $this->bindings[$clause] = []; |
||
387 | |||
388 | return $this; |
||
389 | } |
||
390 | |||
391 | /** |
||
392 | * Creates a Greater than Query condition, equivalent to mysql > operator |
||
393 | * @param string $left the lhs of the condition. Unescaped |
||
394 | * @param mixed $right the rhs of the condition. Escaped |
||
395 | * @return QueryCondition the query condition |
||
396 | */ |
||
397 | public static function Greater($left, $right): QueryCondition |
||
398 | { |
||
399 | return new QueryCondition($left, '>', $right); |
||
400 | } |
||
401 | |||
402 | /** |
||
403 | * Creates a "Greater than or equal to" Query condition, equivalent to mysql >= operator |
||
404 | * @param string $left the lhs of the condition. Unescaped |
||
405 | * @param mixed $right the rhs of the condition. Escaped |
||
406 | * @return QueryCondition the query condition |
||
407 | */ |
||
408 | public static function GreaterOrEqual($left, $right): QueryCondition |
||
409 | { |
||
410 | return new QueryCondition($left, '>=', $right); |
||
411 | } |
||
412 | |||
413 | /** |
||
414 | * Creates a "Less than" Query condition, equivalent to mysql < operator |
||
415 | * @param string $left the lhs of the condition. Unescaped |
||
416 | * @param mixed $right the rhs of the condition. Escaped |
||
417 | * @return QueryCondition the query condition |
||
418 | */ |
||
419 | public static function Less($left, $right): QueryCondition |
||
420 | { |
||
421 | return new QueryCondition($left, '<', $right); |
||
422 | } |
||
423 | |||
424 | /** |
||
425 | * Creates a "Less than or equal to" Query condition, equivalent to mysql <= operator |
||
426 | * @param string $left the lhs of the condition. Unescaped |
||
427 | * @param mixed $right the rhs of the condition. Escaped |
||
428 | * @return QueryCondition the query condition |
||
429 | */ |
||
430 | public static function LessOrEqual($left, $right): QueryCondition |
||
431 | { |
||
432 | return new QueryCondition($left, '<=', $right); |
||
433 | } |
||
434 | |||
435 | /** |
||
436 | * Creates an "equal to" Query condition, equivalent to mysql = operator |
||
437 | * @param string $left the lhs of the condition. Unescaped |
||
438 | * @param mixed $right the rhs of the condition. Escaped |
||
439 | * @return QueryCondition the query condition |
||
440 | */ |
||
441 | public static function Equal($left, $right): QueryCondition |
||
442 | { |
||
443 | return new QueryCondition($left, '=', $right); |
||
444 | } |
||
445 | |||
446 | /** |
||
447 | * Creates a "Not equal to" Query condition, equivalent to mysql <> or != operators |
||
448 | * @param string $left the lhs of the condition. Unescaped |
||
449 | * @param mixed $right the rhs of the condition. Escaped |
||
450 | * @return QueryCondition the query condition |
||
451 | */ |
||
452 | public static function NotEqual($left, $right): QueryCondition |
||
453 | { |
||
454 | return new QueryCondition($left, '<>', $right); |
||
455 | } |
||
456 | |||
457 | /** |
||
458 | * Creates a "Not Like" Query condition, equivalent to mysql NOT LIKE operator |
||
459 | * @param string $left the lhs of the condition. Unescaped |
||
460 | * @param mixed $right the rhs of the condition. Escaped |
||
461 | * @return QueryCondition the query condition |
||
462 | */ |
||
463 | public static function NotLike($left, $right): QueryCondition |
||
464 | { |
||
465 | return new QueryCondition($left, 'NOT LIKE', $right); |
||
466 | } |
||
467 | |||
468 | /** |
||
469 | * Creates a "Like" Query condition, equivalent to mysql LIKE operator |
||
470 | * @param string $left the lhs of the condition. Unescaped |
||
471 | * @param mixed $right the rhs of the condition. Escaped |
||
472 | * @return QueryCondition the query condition |
||
473 | */ |
||
474 | public static function Like($left, $right): QueryCondition |
||
475 | { |
||
476 | return new QueryCondition($left, 'LIKE', $right); |
||
477 | } |
||
478 | |||
479 | /** |
||
480 | * Creates an "Is" Query condition, equivalent to mysql IS operator |
||
481 | * @param string $left the lhs of the condition. Unescaped |
||
482 | * @param mixed $right the rhs of the condition. Escaped |
||
483 | * @return QueryCondition the query condition |
||
484 | */ |
||
485 | public static function Is($left, $right): QueryCondition |
||
486 | { |
||
487 | return new QueryCondition($left, 'IS', $right); |
||
488 | } |
||
489 | |||
490 | /** |
||
491 | * Creates an "Is not" Query condition, equivalent to mysql IS NOT operator |
||
492 | * @param string $left the lhs of the condition. Unescaped |
||
493 | * @param string|array $right the rhs of the condition. Escaped |
||
494 | * @return QueryCondition the query condition |
||
495 | */ |
||
496 | public static function IsNot($left, $right): QueryCondition |
||
497 | { |
||
498 | return new QueryCondition($left, 'IS NOT', $right); |
||
499 | } |
||
500 | |||
501 | /** |
||
502 | * Creates a "Not in" Query condition, equivalent to mysql NOT IN operator |
||
503 | * @param string $needle the parameter that cannot be present in the haystack. Unescaped |
||
504 | * @param string|Array $haystack the values that can be searched through. Escaped |
||
505 | * @return QueryCondition the query condition |
||
506 | */ |
||
507 | public static function NotIn($needle, $haystack): QueryCondition |
||
508 | { |
||
509 | return new QueryCondition($needle, 'NOT IN', $haystack); |
||
510 | } |
||
511 | |||
512 | /** |
||
513 | * Creates a "In" Query condition, equivalent to mysql IN operator |
||
514 | * @param string $needle the parameter that has to be found. Unescaped |
||
515 | * @param string|Array $haystack the values that can be searched through. Escaped |
||
516 | * @return QueryCondition the query condition |
||
517 | */ |
||
518 | public static function In($needle, $haystack): QueryCondition |
||
519 | { |
||
520 | return new QueryCondition($needle, 'IN', $haystack); |
||
521 | } |
||
522 | |||
523 | /** |
||
524 | * Creates an "AND" predicate from a variable number of expressions |
||
525 | * @return QueryPredicate the predicate expression |
||
526 | */ |
||
527 | public static function And(QueryExpression $left, QueryExpression ...$others): ?QueryPredicate |
||
528 | { |
||
529 | return new QueryPredicate('AND', $left, ...$others); |
||
530 | } |
||
531 | |||
532 | /** |
||
533 | * Combines an array of QueryExpression clauses into an AND predicate |
||
534 | * @return miBadger\Query\QueryExpression|null Either null (if array contains no clauses), |
||
0 ignored issues
–
show
|
|||
535 | * the single clause in the input array, or a QueryPredicate combining the clauses |
||
536 | */ |
||
537 | public static function AndArray(Array $clauses) |
||
538 | { |
||
539 | if (count($clauses) == 0) { |
||
540 | return null; |
||
541 | } else if (count($clauses) == 1) |
||
542 | { |
||
543 | return $clauses[0]; |
||
544 | } else { |
||
545 | return new QueryPredicate('AND', $clauses[0], ...array_slice($clauses, 1)); |
||
546 | } |
||
547 | } |
||
548 | |||
549 | /** |
||
550 | * Creates an "OR" predicate from a variable number of expressions |
||
551 | * @return QueryPredicate the predicate expression |
||
552 | */ |
||
553 | public static function Or(QueryExpression $left, QueryExpression ...$others): QueryPredicate |
||
554 | { |
||
555 | return new QueryPredicate('OR', $left, ...$others); |
||
556 | } |
||
557 | |||
558 | /** |
||
559 | * Combines an array of QueryExpression clauses into an OR predicate |
||
560 | * @return miBadger\Query\QueryExpression|null Either null (if array contains no clauses), |
||
561 | * the single clause in the input array, or a QueryPredicate combining the clauses |
||
562 | */ |
||
563 | public static function OrArray(Array $clauses) |
||
564 | { |
||
565 | if (count($clauses) == 0) { |
||
566 | return null; |
||
567 | } else if (count($clauses) == 1) |
||
568 | { |
||
569 | return $clauses[0]; |
||
570 | } else { |
||
571 | return new QueryPredicate('OR', $clauses[0], ...array_slice($clauses, 1)); |
||
572 | } |
||
573 | } |
||
574 | |||
575 | /** |
||
576 | * Creates a "NOT" predicate negating an expression |
||
577 | * @param QueryExpression The condition to be negated |
||
0 ignored issues
–
show
The type
miBadger\Query\The was not found. Maybe you did not declare it correctly or list all dependencies?
The issue could also be caused by a filter entry in the build configuration.
If the path has been excluded in your configuration, e.g. filter:
dependency_paths: ["lib/*"]
For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths
Loading history...
|
|||
578 | * @return QueryPredicate the predicate expression |
||
579 | */ |
||
580 | public static function Not(QueryExpression $exp): QueryPredicate |
||
581 | { |
||
582 | return new QueryPredicate('NOT', $exp); |
||
583 | } |
||
584 | } |
||
585 |