[TASK] Doctrine: Add support for aggregate SQL functions
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Database / Query / QueryBuilder.php
1 <?php
2 declare (strict_types = 1);
3 namespace TYPO3\CMS\Core\Database\Query;
4
5 /*
6 * This file is part of the TYPO3 CMS project.
7 *
8 * It is free software; you can redistribute it and/or modify it under
9 * the terms of the GNU General Public License, either version 2
10 * of the License, or any later version.
11 *
12 * For the full copyright and license information, please read the
13 * LICENSE.txt file that was distributed with this source code.
14 *
15 * The TYPO3 project - inspiring people to share!
16 */
17
18 use Doctrine\DBAL\Query\Expression\CompositeExpression;
19 use TYPO3\CMS\Core\Database\Connection;
20 use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
21 use TYPO3\CMS\Core\Utility\GeneralUtility;
22
23 /**
24 * Object oriented approach to building SQL queries.
25 *
26 * It's a facade to the Doctrine DBAL QueryBuilder that implements PHP7 type hinting and automatic
27 * quoting of table and column names.
28 *
29 * <code>
30 * $query->select('aField', 'anotherField')
31 * ->from('aTable')
32 * ->where($query->expr()->eq('aField', 1))
33 * ->andWhere($query->expr()->gte('anotherField',10'))
34 * ->execute()
35 * </code>
36 *
37 * Additional functionality included is support for COUNT() and TRUNCATE() statements.
38 */
39 class QueryBuilder
40 {
41 /**
42 * The DBAL Connection.
43 *
44 * @var Connection
45 */
46 protected $connection;
47
48 /**
49 * @var \Doctrine\DBAL\Query\QueryBuilder
50 */
51 protected $concreteQueryBuilder;
52
53 /**
54 * @var QueryContext
55 */
56 protected $queryContext;
57
58 /**
59 * Initializes a new QueryBuilder.
60 *
61 * @param Connection $connection The DBAL Connection.
62 * @param QueryContext $queryContext
63 * @param \Doctrine\DBAL\Query\QueryBuilder $concreteQueryBuilder
64 */
65 public function __construct(
66 Connection $connection,
67 QueryContext $queryContext = null,
68 \Doctrine\DBAL\Query\QueryBuilder $concreteQueryBuilder = null
69 ) {
70 $this->connection = $connection;
71
72 if ($queryContext === null) {
73 $queryContext = GeneralUtility::makeInstance(QueryContext::class);
74 }
75 $this->queryContext = $queryContext;
76
77 if ($concreteQueryBuilder === null) {
78 $concreteQueryBuilder = GeneralUtility::makeInstance(
79 \Doctrine\DBAL\Query\QueryBuilder::class,
80 $connection
81 );
82 }
83 $this->concreteQueryBuilder = $concreteQueryBuilder;
84 }
85
86 /**
87 * @return QueryContext
88 */
89 public function getQueryContext(): QueryContext
90 {
91 return $this->queryContext;
92 }
93
94 /**
95 * @param QueryContext $queryContext
96 */
97 public function setQueryContext(QueryContext $queryContext)
98 {
99 $this->queryContext = $queryContext;
100 }
101
102 /**
103 * Gets an ExpressionBuilder used for object-oriented construction of query expressions.
104 * This producer method is intended for convenient inline usage. Example:
105 *
106 * For more complex expression construction, consider storing the expression
107 * builder object in a local variable.
108 *
109 * @return ExpressionBuilder
110 */
111 public function expr(): ExpressionBuilder
112 {
113 return $this->connection->getExpressionBuilder();
114 }
115
116 /**
117 * Gets the type of the currently built query.
118 *
119 * @return int
120 * @internal
121 */
122 public function getType(): int
123 {
124 return $this->concreteQueryBuilder->getType();
125 }
126
127 /**
128 * Gets the associated DBAL Connection for this query builder.
129 *
130 * @return Connection
131 */
132 public function getConnection(): Connection
133 {
134 return $this->connection;
135 }
136
137 /**
138 * Gets the state of this query builder instance.
139 *
140 * @return int Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN.
141 * @internal
142 */
143 public function getState(): int
144 {
145 return $this->concreteQueryBuilder->getState();
146 }
147
148 /**
149 * Gets the concrete implementation of the query builder
150 *
151 * @return \Doctrine\DBAL\Query\QueryBuilder
152 * @internal
153 */
154 public function getConcreteQueryBuilder(): \Doctrine\DBAL\Query\QueryBuilder
155 {
156 return $this->concreteQueryBuilder;
157 }
158
159 /**
160 * Executes this query using the bound parameters and their types.
161 *
162 * @return \Doctrine\DBAL\Driver\Statement|int
163 */
164 public function execute()
165 {
166 if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
167 return $this->concreteQueryBuilder->execute();
168 }
169
170 // set additional query restrictions based on context & TCA config
171 $originalWhereConditions = $this->addAdditonalWhereConditions();
172
173 $result = $this->concreteQueryBuilder->execute();
174
175 // restore the original query conditions in case the user keeps
176 // on modifying the state.
177 $this->concreteQueryBuilder->add('where', $originalWhereConditions, false);
178
179 return $result;
180 }
181
182 /**
183 * Gets the complete SQL string formed by the current specifications of this QueryBuilder.
184 *
185 * If the statement is a SELECT TYPE query restrictions based on TCA settings will
186 * automatically be applied based on the current QuerySettings.
187 *
188 * @return string The SQL query string.
189 */
190 public function getSQL(): string
191 {
192 if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
193 return $this->concreteQueryBuilder->getSQL();
194 }
195
196 // set additional query restrictions based on context & TCA config
197 $originalWhereConditions = $this->addAdditonalWhereConditions();
198
199 $sql = $this->concreteQueryBuilder->getSQL();
200
201 // restore the original query conditions in case the user keeps
202 // on modifying the state.
203 $this->concreteQueryBuilder->add('where', $originalWhereConditions, false);
204
205 return $sql;
206 }
207
208 /**
209 * Sets a query parameter for the query being constructed.
210 *
211 * @param string|int $key The parameter position or name.
212 * @param mixed $value The parameter value.
213 * @param string|null $type One of the Connection::PARAM_* constants.
214 *
215 * @return QueryBuilder This QueryBuilder instance.
216 */
217 public function setParameter($key, $value, string $type = null): QueryBuilder
218 {
219 $this->concreteQueryBuilder->setParameter($key, $value, $type);
220
221 return $this;
222 }
223
224 /**
225 * Sets a collection of query parameters for the query being constructed.
226 *
227 * @param array $params The query parameters to set.
228 * @param array $types The query parameters types to set.
229 *
230 * @return QueryBuilder This QueryBuilder instance.
231 */
232 public function setParameters(array $params, array $types = []): QueryBuilder
233 {
234 $this->concreteQueryBuilder->setParameters($params, $types);
235
236 return $this;
237 }
238
239 /**
240 * Gets all defined query parameters for the query being constructed indexed by parameter index or name.
241 *
242 * @return array The currently defined query parameters indexed by parameter index or name.
243 */
244 public function getParameters(): array
245 {
246 return $this->concreteQueryBuilder->getParameters();
247 }
248
249 /**
250 * Gets a (previously set) query parameter of the query being constructed.
251 *
252 * @param string|int $key The key (index or name) of the bound parameter.
253 *
254 * @return mixed The value of the bound parameter.
255 */
256 public function getParameter($key)
257 {
258 return $this->concreteQueryBuilder->getParameter($key);
259 }
260
261 /**
262 * Gets all defined query parameter types for the query being constructed indexed by parameter index or name.
263 *
264 * @return array The currently defined query parameter types indexed by parameter index or name.
265 */
266 public function getParameterTypes(): array
267 {
268 return $this->concreteQueryBuilder->getParameterTypes();
269 }
270
271 /**
272 * Gets a (previously set) query parameter type of the query being constructed.
273 *
274 * @param string|int $key The key (index or name) of the bound parameter type.
275 *
276 * @return mixed The value of the bound parameter type.
277 */
278 public function getParameterType($key)
279 {
280 return $this->concreteQueryBuilder->getParameterType($key);
281 }
282
283 /**
284 * Sets the position of the first result to retrieve (the "offset").
285 *
286 * @param int $firstResult The first result to return.
287 *
288 * @return QueryBuilder This QueryBuilder instance.
289 */
290 public function setFirstResult(int $firstResult): QueryBuilder
291 {
292 $this->concreteQueryBuilder->setFirstResult($firstResult);
293
294 return $this;
295 }
296
297 /**
298 * Gets the position of the first result the query object was set to retrieve (the "offset").
299 * Returns NULL if {@link setFirstResult} was not applied to this QueryBuilder.
300 *
301 * @return int The position of the first result.
302 */
303 public function getFirstResult(): int
304 {
305 return $this->concreteQueryBuilder->getFirstResult();
306 }
307
308 /**
309 * Sets the maximum number of results to retrieve (the "limit").
310 *
311 * @param int $maxResults The maximum number of results to retrieve.
312 *
313 * @return QueryBuilder This QueryBuilder instance.
314 */
315 public function setMaxResults(int $maxResults): QueryBuilder
316 {
317 $this->concreteQueryBuilder->setMaxResults($maxResults);
318
319 return $this;
320 }
321
322 /**
323 * Gets the maximum number of results the query object was set to retrieve (the "limit").
324 * Returns 0 if setMaxResults was not applied to this query builder.
325 *
326 * @return int The maximum number of results.
327 */
328 public function getMaxResults(): int
329 {
330 return (int)$this->concreteQueryBuilder->getMaxResults();
331 }
332
333 /**
334 * Either appends to or replaces a single, generic query part.
335 *
336 * The available parts are: 'select', 'from', 'set', 'where',
337 * 'groupBy', 'having' and 'orderBy'.
338 *
339 * @param string $sqlPartName
340 * @param string $sqlPart
341 * @param bool $append
342 *
343 * @return QueryBuilder This QueryBuilder instance.
344 */
345 public function add(string $sqlPartName, string $sqlPart, bool $append = false): QueryBuilder
346 {
347 $this->concreteQueryBuilder->add($sqlPartName, $sqlPart, $append);
348
349 return $this;
350 }
351
352 /**
353 * Specifies the item that is to be counted in the query result.
354 * Replaces any previously specified selections, if any.
355 *
356 * @param string $item Will be quoted according to database platform automatically.
357 * @return QueryBuilder This QueryBuilder instance.
358 */
359 public function count(string $item): QueryBuilder
360 {
361 $countExpr = $this->getConnection()->getDatabasePlatform()->getCountExpression(
362 $item === '*' ? $item : $this->quoteIdentifier($item)
363 );
364 $this->concreteQueryBuilder->select($countExpr);
365
366 return $this;
367 }
368
369 /**
370 * Specifies items that are to be returned in the query result.
371 * Replaces any previously specified selections, if any.
372 *
373 * @param string[] $selects
374 * @return QueryBuilder This QueryBuilder instance.
375 */
376 public function select(string ...$selects): QueryBuilder
377 {
378 $this->concreteQueryBuilder->select(...$this->quoteIdentifiersForSelect($selects));
379
380 return $this;
381 }
382
383 /**
384 * Adds an item that is to be returned in the query result.
385 *
386 * @param string[] $selects The selection expression.
387 *
388 * @return QueryBuilder This QueryBuilder instance.
389 */
390 public function addSelect(string ...$selects): QueryBuilder
391 {
392 $this->concreteQueryBuilder->addSelect(...$this->quoteIdentifiersForSelect($selects));
393
394 return $this;
395 }
396
397 /**
398 * Specifies items that are to be returned in the query result.
399 * Replaces any previously specified selections, if any.
400 * This should only be used for literal SQL expressions as no
401 * quoting/escaping of any kind will be performed on the items.
402 *
403 * @param string[] $selects Literal SQL expressions to be selected. Warning: No quoting will be done!
404 * @return QueryBuilder This QueryBuilder instance.
405 */
406 public function selectLiteral(string ...$selects): QueryBuilder
407 {
408 $this->concreteQueryBuilder->select(...$selects);
409
410 return $this;
411 }
412
413 /**
414 * Adds an item that is to be returned in the query result. This should
415 * only be used for literal SQL expressions as no quoting/escaping of
416 * any kind will be performed on the items.
417 *
418 * @param string[] $selects Literal SQL expressions to be selected.
419 * @return QueryBuilder This QueryBuilder instance.
420 */
421 public function addSelectLiteral(string ...$selects): QueryBuilder
422 {
423 $this->concreteQueryBuilder->addSelect(...$selects);
424
425 return $this;
426 }
427
428 /**
429 * Turns the query being built into a bulk delete query that ranges over
430 * a certain table.
431 *
432 * @param string $delete The table whose rows are subject to the deletion.
433 * Will be quoted according to database platform automatically.
434 * @param string $alias The table alias used in the constructed query.
435 * Will be quoted according to database platform automatically.
436 *
437 * @return QueryBuilder This QueryBuilder instance.
438 */
439 public function delete(string $delete, string $alias = null): QueryBuilder
440 {
441 $this->concreteQueryBuilder->delete(
442 $this->quoteIdentifier($delete),
443 empty($alias) ? $alias : $this->quoteIdentifier($alias)
444 );
445
446 return $this;
447 }
448
449 /**
450 * Turns the query being built into a bulk update query that ranges over
451 * a certain table
452 *
453 * @param string $update The table whose rows are subject to the update.
454 * @param string $alias The table alias used in the constructed query.
455 *
456 * @return QueryBuilder This QueryBuilder instance.
457 */
458 public function update(string $update, string $alias = null): QueryBuilder
459 {
460 $this->concreteQueryBuilder->update(
461 $this->quoteIdentifier($update),
462 empty($alias) ? $alias : $this->quoteIdentifier($alias)
463 );
464
465 return $this;
466 }
467
468 /**
469 * Turns the query being built into an insert query that inserts into
470 * a certain table
471 *
472 * @param string $insert The table into which the rows should be inserted.
473 *
474 * @return QueryBuilder This QueryBuilder instance.
475 */
476 public function insert(string $insert): QueryBuilder
477 {
478 $this->concreteQueryBuilder->insert($this->quoteIdentifier($insert));
479
480 return $this;
481 }
482
483 /**
484 * Creates and adds a query root corresponding to the table identified by the
485 * given alias, forming a cartesian product with any existing query roots.
486 *
487 * @param string $from The table. Will be quoted according to database platform automatically.
488 * @param string $alias The alias of the table. Will be quoted according to database platform automatically.
489 *
490 * @return QueryBuilder This QueryBuilder instance.
491 */
492 public function from(string $from, string $alias = null): QueryBuilder
493 {
494 $this->concreteQueryBuilder->from(
495 $this->quoteIdentifier($from),
496 empty($alias) ? $alias : $this->quoteIdentifier($alias)
497 );
498
499 return $this;
500 }
501
502 /**
503 * Creates and adds a join to the query.
504 *
505 * @param string $fromAlias The alias that points to a from clause.
506 * @param string $join The table name to join.
507 * @param string $alias The alias of the join table.
508 * @param string $condition The condition for the join.
509 *
510 * @return QueryBuilder This QueryBuilder instance.
511 */
512 public function join(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
513 {
514 $this->concreteQueryBuilder->innerJoin(
515 $this->quoteIdentifier($fromAlias),
516 $this->quoteIdentifier($join),
517 $this->quoteIdentifier($alias),
518 $condition
519 );
520
521 return $this;
522 }
523
524 /**
525 * Creates and adds a join to the query.
526 *
527 * @param string $fromAlias The alias that points to a from clause.
528 * @param string $join The table name to join.
529 * @param string $alias The alias of the join table.
530 * @param string $condition The condition for the join.
531 *
532 * @return QueryBuilder This QueryBuilder instance.
533 */
534 public function innerJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
535 {
536 $this->concreteQueryBuilder->innerJoin(
537 $this->quoteIdentifier($fromAlias),
538 $this->quoteIdentifier($join),
539 $this->quoteIdentifier($alias),
540 $condition
541 );
542
543 return $this;
544 }
545
546 /**
547 * Creates and adds a left join to the query.
548 *
549 * @param string $fromAlias The alias that points to a from clause.
550 * @param string $join The table name to join.
551 * @param string $alias The alias of the join table.
552 * @param string $condition The condition for the join.
553 *
554 * @return QueryBuilder This QueryBuilder instance.
555 */
556 public function leftJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
557 {
558 $this->concreteQueryBuilder->leftJoin(
559 $this->quoteIdentifier($fromAlias),
560 $this->quoteIdentifier($join),
561 $this->quoteIdentifier($alias),
562 $condition
563 );
564
565 return $this;
566 }
567
568 /**
569 * Creates and adds a right join to the query.
570 *
571 * @param string $fromAlias The alias that points to a from clause.
572 * @param string $join The table name to join.
573 * @param string $alias The alias of the join table.
574 * @param string $condition The condition for the join.
575 *
576 * @return QueryBuilder This QueryBuilder instance.
577 */
578 public function rightJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
579 {
580 $this->concreteQueryBuilder->rightJoin(
581 $this->quoteIdentifier($fromAlias),
582 $this->quoteIdentifier($join),
583 $this->quoteIdentifier($alias),
584 $condition
585 );
586
587 return $this;
588 }
589
590 /**
591 * Sets a new value for a column in a bulk update query.
592 *
593 * @param string $key The column to set.
594 * @param string $value The value, expression, placeholder, etc.
595 * @param bool $createNamedParameter Automatically create a named parameter for the value
596 *
597 * @return QueryBuilder This QueryBuilder instance.
598 */
599 public function set(string $key, $value, bool $createNamedParameter = true): QueryBuilder
600 {
601 $this->concreteQueryBuilder->set(
602 $this->quoteIdentifier($key),
603 $createNamedParameter ? $this->createNamedParameter($value) : $value
604 );
605
606 return $this;
607 }
608
609 /**
610 * Specifies one or more restrictions to the query result.
611 * Replaces any previously specified restrictions, if any.
612 *
613 * @param mixed,... $predicates
614 * @return QueryBuilder This QueryBuilder instance.
615 */
616 public function where(...$predicates): QueryBuilder
617 {
618 $this->concreteQueryBuilder->where(...$predicates);
619
620 return $this;
621 }
622
623 /**
624 * Adds one or more restrictions to the query results, forming a logical
625 * conjunction with any previously specified restrictions.
626 *
627 * @param mixed,... $where The query restrictions.
628 *
629 * @return QueryBuilder This QueryBuilder instance.
630 *
631 * @see where()
632 */
633 public function andWhere(...$where): QueryBuilder
634 {
635 $this->concreteQueryBuilder->andWhere(...$where);
636
637 return $this;
638 }
639
640 /**
641 * Adds one or more restrictions to the query results, forming a logical
642 * disjunction with any previously specified restrictions.
643 *
644 * @param mixed,... $where The WHERE statement.
645 *
646 * @return QueryBuilder This QueryBuilder instance.
647 *
648 * @see where()
649 */
650 public function orWhere(...$where): QueryBuilder
651 {
652 $this->concreteQueryBuilder->orWhere(...$where);
653
654 return $this;
655 }
656
657 /**
658 * Specifies a grouping over the results of the query.
659 * Replaces any previously specified groupings, if any.
660 *
661 * @param mixed,... $groupBy The grouping expression.
662 *
663 * @return QueryBuilder This QueryBuilder instance.
664 */
665 public function groupBy(...$groupBy): QueryBuilder
666 {
667 $this->concreteQueryBuilder->groupBy(...$this->quoteIdentifiers($groupBy));
668
669 return $this;
670 }
671
672 /**
673 * Adds a grouping expression to the query.
674 *
675 * @param mixed,... $groupBy The grouping expression.
676 *
677 * @return QueryBuilder This QueryBuilder instance.
678 */
679 public function addGroupBy(...$groupBy): QueryBuilder
680 {
681 $this->concreteQueryBuilder->addGroupBy(...$this->quoteIdentifiers($groupBy));
682
683 return $this;
684 }
685
686 /**
687 * Sets a value for a column in an insert query.
688 *
689 * @param string $column The column into which the value should be inserted.
690 * @param string $value The value that should be inserted into the column.
691 * @param bool $createNamedParameter Automatically create a named parameter for the value
692 *
693 * @return QueryBuilder This QueryBuilder instance.
694 */
695 public function setValue(string $column, $value, bool $createNamedParameter = true): QueryBuilder
696 {
697 $this->concreteQueryBuilder->setValue(
698 $this->quoteIdentifier($column),
699 $createNamedParameter ? $this->createNamedParameter($value) : $value
700 );
701
702 return $this;
703 }
704
705 /**
706 * Specifies values for an insert query indexed by column names.
707 * Replaces any previous values, if any.
708 *
709 * @param array $values The values to specify for the insert query indexed by column names.
710 * @param bool $createNamedParameters Automatically create named parameters for all values
711 *
712 * @return QueryBuilder This QueryBuilder instance.
713 */
714 public function values(array $values, bool $createNamedParameters = true): QueryBuilder
715 {
716 if ($createNamedParameters === true) {
717 foreach ($values as &$value) {
718 $value = $this->createNamedParameter($value);
719 }
720 }
721
722 $this->concreteQueryBuilder->values($this->quoteColumnValuePairs($values));
723
724 return $this;
725 }
726
727 /**
728 * Specifies a restriction over the groups of the query.
729 * Replaces any previous having restrictions, if any.
730 *
731 * @param mixed,... $having The restriction over the groups.
732 *
733 * @return QueryBuilder This QueryBuilder instance.
734 */
735 public function having(...$having): QueryBuilder
736 {
737 $this->concreteQueryBuilder->having(...$having);
738 return $this;
739 }
740
741 /**
742 * Adds a restriction over the groups of the query, forming a logical
743 * conjunction with any existing having restrictions.
744 *
745 * @param mixed,... $having The restriction to append.
746 *
747 * @return QueryBuilder This QueryBuilder instance.
748 */
749 public function andHaving(...$having): QueryBuilder
750 {
751 $this->concreteQueryBuilder->andHaving(...$having);
752
753 return $this;
754 }
755
756 /**
757 * Adds a restriction over the groups of the query, forming a logical
758 * disjunction with any existing having restrictions.
759 *
760 * @param mixed,... $having The restriction to add.
761 *
762 * @return QueryBuilder This QueryBuilder instance.
763 */
764 public function orHaving(...$having): QueryBuilder
765 {
766 $this->concreteQueryBuilder->orHaving(...$having);
767
768 return $this;
769 }
770
771 /**
772 * Specifies an ordering for the query results.
773 * Replaces any previously specified orderings, if any.
774 *
775 * @param string $fieldName The fieldName to order by. Will be quoted according to database platform automatically.
776 * @param string $order The ordering direction. No automatic quoting/escaping.
777 *
778 * @return QueryBuilder This QueryBuilder instance.
779 */
780 public function orderBy(string $fieldName, string $order = null): QueryBuilder
781 {
782 $this->concreteQueryBuilder->orderBy($this->connection->quoteIdentifier($fieldName), $order);
783
784 return $this;
785 }
786
787 /**
788 * Adds an ordering to the query results.
789 *
790 * @param string $fieldName The fieldName to order by. Will be quoted according to database platform automatically.
791 * @param string $order The ordering direction.
792 *
793 * @return QueryBuilder This QueryBuilder instance.
794 */
795 public function addOrderBy(string $fieldName, string $order = null): QueryBuilder
796 {
797 $this->concreteQueryBuilder->addOrderBy($this->connection->quoteIdentifier($fieldName), $order);
798
799 return $this;
800 }
801
802 /**
803 * Gets a query part by its name.
804 *
805 * @param string $queryPartName
806 *
807 * @return mixed
808 */
809 public function getQueryPart(string $queryPartName)
810 {
811 return $this->concreteQueryBuilder->getQueryPart($queryPartName);
812 }
813
814 /**
815 * Gets all query parts.
816 *
817 * @return array
818 */
819 public function getQueryParts(): array
820 {
821 return $this->concreteQueryBuilder->getQueryParts();
822 }
823
824 /**
825 * Resets SQL parts.
826 *
827 * @param array|null $queryPartNames
828 *
829 * @return QueryBuilder This QueryBuilder instance.
830 */
831 public function resetQueryParts(array $queryPartNames = null): QueryBuilder
832 {
833 $this->concreteQueryBuilder->resetQueryParts($queryPartNames);
834
835 return $this;
836 }
837
838 /**
839 * Resets a single SQL part.
840 *
841 * @param string $queryPartName
842 *
843 * @return QueryBuilder This QueryBuilder instance.
844 */
845 public function resetQueryPart($queryPartName): QueryBuilder
846 {
847 $this->concreteQueryBuilder->resetQueryPart($queryPartName);
848
849 return $this;
850 }
851
852 /**
853 * Gets a string representation of this QueryBuilder which corresponds to
854 * the final SQL query being constructed.
855 *
856 * @return string The string representation of this QueryBuilder.
857 */
858 public function __toString(): string
859 {
860 return $this->getSQL();
861 }
862
863 /**
864 * Creates a new named parameter and bind the value $value to it.
865 *
866 * This method provides a shortcut for PDOStatement::bindValue
867 * when using prepared statements.
868 *
869 * The parameter $value specifies the value that you want to bind. If
870 * $placeholder is not provided bindValue() will automatically create a
871 * placeholder for you. An automatic placeholder will be of the name
872 * ':dcValue1', ':dcValue2' etc.
873 *
874 * @param mixed $value
875 * @param int $type
876 * @param string $placeHolder The name to bind with. The string must start with a colon ':'.
877 *
878 * @return string the placeholder name used.
879 */
880 public function createNamedParameter($value, int $type = \PDO::PARAM_STR, string $placeHolder = null): string
881 {
882 return $this->concreteQueryBuilder->createNamedParameter($value, $type, $placeHolder);
883 }
884
885 /**
886 * Creates a new positional parameter and bind the given value to it.
887 *
888 * Attention: If you are using positional parameters with the query builder you have
889 * to be very careful to bind all parameters in the order they appear in the SQL
890 * statement , otherwise they get bound in the wrong order which can lead to serious
891 * bugs in your code.
892 *
893 * @param mixed $value
894 * @param int $type
895 *
896 * @return string
897 */
898 public function createPositionalParameter($value, int $type = \PDO::PARAM_STR): string
899 {
900 return $this->concreteQueryBuilder->createPositionalParameter($value, $type);
901 }
902
903 /**
904 * Quotes a given input parameter.
905 *
906 * @param mixed $input The parameter to be quoted.
907 * @param string|null $type The type of the parameter.
908 *
909 * @return string The quoted parameter.
910 */
911 public function quote($input, string $type = null): string
912 {
913 return $this->getConnection()->quote($input, $type);
914 }
915
916 /**
917 * Quotes a string so it can be safely used as a table or column name, even if
918 * it is a reserved name.
919 *
920 * Delimiting style depends on the underlying database platform that is being used.
921 *
922 * @param string $identifier The name to be quoted.
923 *
924 * @return string The quoted name.
925 */
926 public function quoteIdentifier(string $identifier): string
927 {
928 return $this->getConnection()->quoteIdentifier($identifier);
929 }
930
931 /**
932 * Quotes an array of column names so it can be safely used, even if the name is a reserved name.
933 *
934 * Delimiting style depends on the underlying database platform that is being used.
935 *
936 * @param array $input
937 *
938 * @return array
939 */
940 public function quoteIdentifiers(array $input): array
941 {
942 return $this->getConnection()->quoteIdentifiers($input);
943 }
944
945 /**
946 * Quotes an array of column names so it can be safely used, even if the name is a reserved name.
947 * Takes into account the special case of the * placeholder that can only be used in SELECT type
948 * statements.
949 *
950 * Delimiting style depends on the underlying database platform that is being used.
951 *
952 * @param array $input
953 *
954 * @return array
955 * @throws \InvalidArgumentException
956 */
957 public function quoteIdentifiersForSelect(array $input): array
958 {
959 foreach ($input as &$select) {
960 list($fieldName, $alias, $suffix) = GeneralUtility::trimExplode(' AS ', $select, 3);
961 if (!empty($suffix)) {
962 throw new \InvalidArgumentException(
963 'QueryBuilder::quoteIdentifiersForSelect() could not parse the input "' . $input . '"',
964 1461170686
965 );
966 }
967
968 // The SQL * operator must not be quoted. As it can only occur either by itself
969 // or preceded by a tablename (tablename.*) check if the last character of a select
970 // expression is the * and quote only prepended table name. In all other cases the
971 // full expression is being quoted.
972 if (substr($fieldName, -2) === '.*') {
973 $select = $this->quoteIdentifier(substr($fieldName, 0, -2)) . '.*';
974 } elseif ($fieldName !== '*') {
975 $select = $this->quoteIdentifier($fieldName);
976 }
977
978 // Quote the alias for the current fieldName, if given
979 if (!empty($alias)) {
980 $select .= ' AS ' . $this->quoteIdentifier($alias);
981 }
982 }
983 return $input;
984 }
985
986 /**
987 * Quotes an associative array of column-value so the column names can be safely used, even
988 * if the name is a reserved name.
989 *
990 * Delimiting style depends on the underlying database platform that is being used.
991 *
992 * @param array $input
993 *
994 * @return array
995 */
996 public function quoteColumnValuePairs(array $input): array
997 {
998 return $this->getConnection()->quoteColumnValuePairs($input);
999 }
1000
1001 /**
1002 * Unquote a single identifier (no dot expansion). Used to unquote the table names
1003 * from the expressionBuilder so that the table can be found in the TCA definition.
1004 *
1005 * @param string $identifier The identifier / table name
1006 * @return string The unquoted table name / identifier
1007 */
1008 protected function unquoteSingleIdentifier(string $identifier): string
1009 {
1010 $quoteChar = $this->getConnection()
1011 ->getDatabasePlatform()
1012 ->getIdentifierQuoteCharacter();
1013
1014 $unquotedIdentifier = trim($identifier, $quoteChar);
1015
1016 return str_replace($quoteChar . $quoteChar, $quoteChar, $unquotedIdentifier);
1017 }
1018
1019 /**
1020 * Return all tables/aliases used in FROM or JOIN query parts from the query builder.
1021 *
1022 * The table names are automatically unquoted. This is a helper for to build the list
1023 * of queried tables for the QueryRestrictionBuilder.
1024 *
1025 * @return string[]
1026 */
1027 protected function getQueriedTables(): array
1028 {
1029 $queriedTables = [];
1030
1031 // Loop through all FROM tables
1032 foreach ($this->getQueryPart('from') as $from) {
1033 $tableName = $this->unquoteSingleIdentifier($from['table']);
1034 $tableAlias = isset($from['alias']) ? $this->unquoteSingleIdentifier($from['alias']) : null;
1035 $queriedTables[$tableName] = $tableAlias;
1036 }
1037
1038 // Loop through all JOIN tables
1039 foreach ($this->getQueryPart('join') as $fromTable => $joins) {
1040 foreach ($joins as $join) {
1041 $tableName = $this->unquoteSingleIdentifier($join['joinTable']);
1042 $tableAlias = isset($join['joinAlias']) ? $this->unquoteSingleIdentifier($join['joinAlias']) : null;
1043 $queriedTables[$tableName] = $tableAlias;
1044 }
1045 }
1046
1047 return $queriedTables;
1048 }
1049
1050 /**
1051 * Add the additional query conditions returned by the QueryRestrictionBuilder
1052 * to the current query and return the original set of conditions so that they
1053 * can be restored after the query has been built/executed.
1054 *
1055 * @return \Doctrine\DBAL\Query\Expression\CompositeExpression|mixed
1056 */
1057 protected function addAdditonalWhereConditions()
1058 {
1059 $queryRestrictionBuilder = GeneralUtility::makeInstance(
1060 QueryRestrictionBuilder::class,
1061 $this->getQueriedTables(),
1062 $this->expr(),
1063 $this->getQueryContext()
1064 );
1065
1066 $originalWhereConditions = $this->concreteQueryBuilder->getQueryPart('where');
1067 if ($originalWhereConditions instanceof CompositeExpression) {
1068 $originalWhereConditions = clone($originalWhereConditions);
1069 }
1070
1071 $additionalQueryRestrictions = $queryRestrictionBuilder->getVisibilityConstraints();
1072
1073 if ($additionalQueryRestrictions->count() !== 0) {
1074 // save the original query conditions so we can restore
1075 // them after the query has been built.
1076
1077 $this->concreteQueryBuilder->andWhere($additionalQueryRestrictions);
1078 }
1079
1080 return $originalWhereConditions;
1081 }
1082 }