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