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