576ec2c3b7372a53646c4e37575a21c7466b138f
[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 *
374 * @param string[] $selects
375 * @return QueryBuilder This QueryBuilder instance.
376 */
377 public function select(string ...$selects): QueryBuilder
378 {
379 $this->concreteQueryBuilder->select(...$this->quoteIdentifiersForSelect($selects));
380
381 return $this;
382 }
383
384 /**
385 * Adds an item that is to be returned in the query result.
386 *
387 * @param string[] $selects The selection expression.
388 *
389 * @return QueryBuilder This QueryBuilder instance.
390 */
391 public function addSelect(string ...$selects): QueryBuilder
392 {
393 $this->concreteQueryBuilder->addSelect(...$this->quoteIdentifiersForSelect($selects));
394
395 return $this;
396 }
397
398 /**
399 * Turns the query being built into a bulk delete query that ranges over
400 * a certain table.
401 *
402 * @param string $delete The table whose rows are subject to the deletion.
403 * Will be quoted according to database platform automatically.
404 * @param string $alias The table alias used in the constructed query.
405 * Will be quoted according to database platform automatically.
406 *
407 * @return QueryBuilder This QueryBuilder instance.
408 */
409 public function delete(string $delete, string $alias = null): QueryBuilder
410 {
411 $this->concreteQueryBuilder->delete(
412 $this->quoteIdentifier($delete),
413 empty($alias) ? $alias : $this->quoteIdentifier($alias)
414 );
415
416 return $this;
417 }
418
419 /**
420 * Turns the query being built into a bulk update query that ranges over
421 * a certain table
422 *
423 * @param string $update The table whose rows are subject to the update.
424 * @param string $alias The table alias used in the constructed query.
425 *
426 * @return QueryBuilder This QueryBuilder instance.
427 */
428 public function update(string $update, string $alias = null): QueryBuilder
429 {
430 $this->concreteQueryBuilder->update(
431 $this->quoteIdentifier($update),
432 empty($alias) ? $alias : $this->quoteIdentifier($alias)
433 );
434
435 return $this;
436 }
437
438 /**
439 * Turns the query being built into an insert query that inserts into
440 * a certain table
441 *
442 * @param string $insert The table into which the rows should be inserted.
443 *
444 * @return QueryBuilder This QueryBuilder instance.
445 */
446 public function insert(string $insert): QueryBuilder
447 {
448 $this->concreteQueryBuilder->insert($this->quoteIdentifier($insert));
449
450 return $this;
451 }
452
453 /**
454 * Creates and adds a query root corresponding to the table identified by the
455 * given alias, forming a cartesian product with any existing query roots.
456 *
457 * @param string $from The table. Will be quoted according to database platform automatically.
458 * @param string $alias The alias of the table. Will be quoted according to database platform automatically.
459 *
460 * @return QueryBuilder This QueryBuilder instance.
461 */
462 public function from(string $from, string $alias = null): QueryBuilder
463 {
464 $this->concreteQueryBuilder->from(
465 $this->quoteIdentifier($from),
466 empty($alias) ? $alias : $this->quoteIdentifier($alias)
467 );
468
469 return $this;
470 }
471
472 /**
473 * Creates and adds a join to the query.
474 *
475 * @param string $fromAlias The alias that points to a from clause.
476 * @param string $join The table name to join.
477 * @param string $alias The alias of the join table.
478 * @param string $condition The condition for the join.
479 *
480 * @return QueryBuilder This QueryBuilder instance.
481 */
482 public function join(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
483 {
484 $this->concreteQueryBuilder->innerJoin(
485 $this->quoteIdentifier($fromAlias),
486 $this->quoteIdentifier($join),
487 $this->quoteIdentifier($alias),
488 $condition
489 );
490
491 return $this;
492 }
493
494 /**
495 * Creates and adds a join to the query.
496 *
497 * @param string $fromAlias The alias that points to a from clause.
498 * @param string $join The table name to join.
499 * @param string $alias The alias of the join table.
500 * @param string $condition The condition for the join.
501 *
502 * @return QueryBuilder This QueryBuilder instance.
503 */
504 public function innerJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
505 {
506 $this->concreteQueryBuilder->innerJoin(
507 $this->quoteIdentifier($fromAlias),
508 $this->quoteIdentifier($join),
509 $this->quoteIdentifier($alias),
510 $condition
511 );
512
513 return $this;
514 }
515
516 /**
517 * Creates and adds a left 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 leftJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
527 {
528 $this->concreteQueryBuilder->leftJoin(
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 right 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 rightJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
549 {
550 $this->concreteQueryBuilder->rightJoin(
551 $this->quoteIdentifier($fromAlias),
552 $this->quoteIdentifier($join),
553 $this->quoteIdentifier($alias),
554 $condition
555 );
556
557 return $this;
558 }
559
560 /**
561 * Sets a new value for a column in a bulk update query.
562 *
563 * @param string $key The column to set.
564 * @param string $value The value, expression, placeholder, etc.
565 * @param bool $createNamedParameter Automatically create a named parameter for the value
566 *
567 * @return QueryBuilder This QueryBuilder instance.
568 */
569 public function set(string $key, $value, bool $createNamedParameter = true): QueryBuilder
570 {
571 $this->concreteQueryBuilder->set(
572 $this->quoteIdentifier($key),
573 $createNamedParameter ? $this->createNamedParameter($value) : $value
574 );
575
576 return $this;
577 }
578
579 /**
580 * Specifies one or more restrictions to the query result.
581 * Replaces any previously specified restrictions, if any.
582 *
583 * @param mixed,... $predicates
584 * @return QueryBuilder This QueryBuilder instance.
585 */
586 public function where(...$predicates): QueryBuilder
587 {
588 $this->concreteQueryBuilder->where(...$predicates);
589
590 return $this;
591 }
592
593 /**
594 * Adds one or more restrictions to the query results, forming a logical
595 * conjunction with any previously specified restrictions.
596 *
597 * @param mixed,... $where The query restrictions.
598 *
599 * @return QueryBuilder This QueryBuilder instance.
600 *
601 * @see where()
602 */
603 public function andWhere(...$where): QueryBuilder
604 {
605 $this->concreteQueryBuilder->andWhere(...$where);
606
607 return $this;
608 }
609
610 /**
611 * Adds one or more restrictions to the query results, forming a logical
612 * disjunction with any previously specified restrictions.
613 *
614 * @param mixed,... $where The WHERE statement.
615 *
616 * @return QueryBuilder This QueryBuilder instance.
617 *
618 * @see where()
619 */
620 public function orWhere(...$where): QueryBuilder
621 {
622 $this->concreteQueryBuilder->orWhere(...$where);
623
624 return $this;
625 }
626
627 /**
628 * Specifies a grouping over the results of the query.
629 * Replaces any previously specified groupings, if any.
630 *
631 * @param mixed,... $groupBy The grouping expression.
632 *
633 * @return QueryBuilder This QueryBuilder instance.
634 */
635 public function groupBy(...$groupBy): QueryBuilder
636 {
637 $this->concreteQueryBuilder->groupBy(...$this->quoteIdentifiers($groupBy));
638
639 return $this;
640 }
641
642 /**
643 * Adds a grouping expression to the query.
644 *
645 * @param mixed,... $groupBy The grouping expression.
646 *
647 * @return QueryBuilder This QueryBuilder instance.
648 */
649 public function addGroupBy(...$groupBy): QueryBuilder
650 {
651 $this->concreteQueryBuilder->addGroupBy(...$this->quoteIdentifiers($groupBy));
652
653 return $this;
654 }
655
656 /**
657 * Sets a value for a column in an insert query.
658 *
659 * @param string $column The column into which the value should be inserted.
660 * @param string $value The value that should be inserted into the column.
661 * @param bool $createNamedParameter Automatically create a named parameter for the value
662 *
663 * @return QueryBuilder This QueryBuilder instance.
664 */
665 public function setValue(string $column, $value, bool $createNamedParameter = true): QueryBuilder
666 {
667
668 $this->concreteQueryBuilder->setValue(
669 $this->quoteIdentifier($column),
670 $createNamedParameter ? $this->createNamedParameter($value) : $value
671 );
672
673 return $this;
674 }
675
676 /**
677 * Specifies values for an insert query indexed by column names.
678 * Replaces any previous values, if any.
679 *
680 * @param array $values The values to specify for the insert query indexed by column names.
681 * @param bool $createNamedParameters Automatically create named parameters for all values
682 *
683 * @return QueryBuilder This QueryBuilder instance.
684 */
685 public function values(array $values, bool $createNamedParameters = true): QueryBuilder
686 {
687 if ($createNamedParameters === true) {
688 foreach ($values as &$value) {
689 $value = $this->createNamedParameter($value);
690 }
691 }
692
693 $this->concreteQueryBuilder->values($this->quoteColumnValuePairs($values));
694
695 return $this;
696 }
697
698 /**
699 * Specifies a restriction over the groups of the query.
700 * Replaces any previous having restrictions, if any.
701 *
702 * @param mixed,... $having The restriction over the groups.
703 *
704 * @return QueryBuilder This QueryBuilder instance.
705 */
706 public function having(...$having): QueryBuilder
707 {
708 $this->concreteQueryBuilder->having(...$having);
709 return $this;
710 }
711
712 /**
713 * Adds a restriction over the groups of the query, forming a logical
714 * conjunction with any existing having restrictions.
715 *
716 * @param mixed,... $having The restriction to append.
717 *
718 * @return QueryBuilder This QueryBuilder instance.
719 */
720 public function andHaving(...$having): QueryBuilder
721 {
722 $this->concreteQueryBuilder->andHaving(...$having);
723
724 return $this;
725 }
726
727 /**
728 * Adds a restriction over the groups of the query, forming a logical
729 * disjunction with any existing having restrictions.
730 *
731 * @param mixed,... $having The restriction to add.
732 *
733 * @return QueryBuilder This QueryBuilder instance.
734 */
735 public function orHaving(...$having): QueryBuilder
736 {
737 $this->concreteQueryBuilder->orHaving(...$having);
738
739 return $this;
740 }
741
742 /**
743 * Specifies an ordering for the query results.
744 * Replaces any previously specified orderings, if any.
745 *
746 * @param string $fieldName The fieldName to order by. Will be quoted according to database platform automatically.
747 * @param string $order The ordering direction. No automatic quoting/escaping.
748 *
749 * @return QueryBuilder This QueryBuilder instance.
750 */
751 public function orderBy(string $fieldName, string $order = null): QueryBuilder
752 {
753 $this->concreteQueryBuilder->orderBy($this->connection->quoteIdentifier($fieldName), $order);
754
755 return $this;
756 }
757
758 /**
759 * Adds an ordering to the query results.
760 *
761 * @param string $fieldName The fieldName to order by. Will be quoted according to database platform automatically.
762 * @param string $order The ordering direction.
763 *
764 * @return QueryBuilder This QueryBuilder instance.
765 */
766 public function addOrderBy(string $fieldName, string $order = null): QueryBuilder
767 {
768 $this->concreteQueryBuilder->addOrderBy($this->connection->quoteIdentifier($fieldName), $order);
769
770 return $this;
771 }
772
773 /**
774 * Gets a query part by its name.
775 *
776 * @param string $queryPartName
777 *
778 * @return mixed
779 */
780 public function getQueryPart(string $queryPartName)
781 {
782 return $this->concreteQueryBuilder->getQueryPart($queryPartName);
783 }
784
785 /**
786 * Gets all query parts.
787 *
788 * @return array
789 */
790 public function getQueryParts(): array
791 {
792 return $this->concreteQueryBuilder->getQueryParts();
793 }
794
795 /**
796 * Resets SQL parts.
797 *
798 * @param array|null $queryPartNames
799 *
800 * @return QueryBuilder This QueryBuilder instance.
801 */
802 public function resetQueryParts(array $queryPartNames = null): QueryBuilder
803 {
804 $this->concreteQueryBuilder->resetQueryParts($queryPartNames);
805
806 return $this;
807 }
808
809 /**
810 * Resets a single SQL part.
811 *
812 * @param string $queryPartName
813 *
814 * @return QueryBuilder This QueryBuilder instance.
815 */
816 public function resetQueryPart($queryPartName): QueryBuilder
817 {
818 $this->concreteQueryBuilder->resetQueryPart($queryPartName);
819
820 return $this;
821 }
822
823 /**
824 * Gets a string representation of this QueryBuilder which corresponds to
825 * the final SQL query being constructed.
826 *
827 * @return string The string representation of this QueryBuilder.
828 */
829 public function __toString(): string
830 {
831 return $this->getSQL();
832 }
833
834 /**
835 * Creates a new named parameter and bind the value $value to it.
836 *
837 * This method provides a shortcut for PDOStatement::bindValue
838 * when using prepared statements.
839 *
840 * The parameter $value specifies the value that you want to bind. If
841 * $placeholder is not provided bindValue() will automatically create a
842 * placeholder for you. An automatic placeholder will be of the name
843 * ':dcValue1', ':dcValue2' etc.
844 *
845 * @param mixed $value
846 * @param int $type
847 * @param string $placeHolder The name to bind with. The string must start with a colon ':'.
848 *
849 * @return string the placeholder name used.
850 */
851 public function createNamedParameter($value, int $type = \PDO::PARAM_STR, string $placeHolder = null): string
852 {
853 return $this->concreteQueryBuilder->createNamedParameter($value, $type, $placeHolder);
854 }
855
856 /**
857 * Creates a new positional parameter and bind the given value to it.
858 *
859 * Attention: If you are using positional parameters with the query builder you have
860 * to be very careful to bind all parameters in the order they appear in the SQL
861 * statement , otherwise they get bound in the wrong order which can lead to serious
862 * bugs in your code.
863 *
864 * @param mixed $value
865 * @param int $type
866 *
867 * @return string
868 */
869 public function createPositionalParameter($value, int $type = \PDO::PARAM_STR): string
870 {
871 return $this->concreteQueryBuilder->createPositionalParameter($value, $type);
872 }
873
874 /**
875 * Quotes a given input parameter.
876 *
877 * @param mixed $input The parameter to be quoted.
878 * @param string|null $type The type of the parameter.
879 *
880 * @return string The quoted parameter.
881 */
882 public function quote($input, string $type = null): string
883 {
884 return $this->getConnection()->quote($input, $type);
885 }
886
887 /**
888 * Quotes a string so it can be safely used as a table or column name, even if
889 * it is a reserved name.
890 *
891 * Delimiting style depends on the underlying database platform that is being used.
892 *
893 * @param string $identifier The name to be quoted.
894 *
895 * @return string The quoted name.
896 */
897 public function quoteIdentifier(string $identifier): string
898 {
899 return $this->getConnection()->quoteIdentifier($identifier);
900 }
901
902 /**
903 * Quotes an array of column names so it can be safely used, even if the name is a reserved name.
904 *
905 * Delimiting style depends on the underlying database platform that is being used.
906 *
907 * @param array $input
908 *
909 * @return array
910 */
911 public function quoteIdentifiers(array $input): array
912 {
913 return $this->getConnection()->quoteIdentifiers($input);
914 }
915
916 /**
917 * Quotes an array of column names so it can be safely used, even if the name is a reserved name.
918 * Takes into account the special case of the * placeholder that can only be used in SELECT type
919 * statements.
920 *
921 * Delimiting style depends on the underlying database platform that is being used.
922 *
923 * @param array $input
924 *
925 * @return array
926 * @throws \InvalidArgumentException
927 */
928 public function quoteIdentifiersForSelect(array $input): array
929 {
930 foreach ($input as &$select) {
931 list($fieldName, $alias, $suffix) = GeneralUtility::trimExplode(' AS ', $select, 3);
932 if (!empty($suffix)) {
933 throw new \InvalidArgumentException(
934 'QueryBuilder::quoteIdentifiersForSelect() could not parse the input "' . $input . '"',
935 1461170686
936 );
937 }
938
939 // The SQL * operator must not be quoted. As it can only occur either by itself
940 // or preceded by a tablename (tablename.*) check if the last character of a select
941 // expression is the * and quote only prepended table name. In all other cases the
942 // full expression is being quoted.
943 if (substr($fieldName, -2) === '.*') {
944 $select = $this->quoteIdentifier(substr($fieldName, 0, -2)) . '.*';
945 } elseif ($fieldName !== '*') {
946 $select = $this->quoteIdentifier($fieldName);
947 }
948
949 // Quote the alias for the current fieldName, if given
950 if (!empty($alias)) {
951 $select .= ' AS ' . $this->quoteIdentifier($alias);
952 }
953 }
954 return $input;
955 }
956
957 /**
958 * Quotes an associative array of column-value so the column names can be safely used, even
959 * if the name is a reserved name.
960 *
961 * Delimiting style depends on the underlying database platform that is being used.
962 *
963 * @param array $input
964 *
965 * @return array
966 */
967 public function quoteColumnValuePairs(array $input): array
968 {
969 return $this->getConnection()->quoteColumnValuePairs($input);
970 }
971
972 /**
973 * Unquote a single identifier (no dot expansion). Used to unquote the table names
974 * from the expressionBuilder so that the table can be found in the TCA definition.
975 *
976 * @param string $identifier The identifier / table name
977 * @return string The unquoted table name / identifier
978 */
979 protected function unquoteSingleIdentifier(string $identifier): string
980 {
981 $quoteChar = $this->getConnection()
982 ->getDatabasePlatform()
983 ->getIdentifierQuoteCharacter();
984
985 $unquotedIdentifier = trim($identifier, $quoteChar);
986
987 return str_replace($quoteChar . $quoteChar, $quoteChar, $unquotedIdentifier);
988 }
989
990 /**
991 * Return all tables/aliases used in FROM or JOIN query parts from the query builder.
992 *
993 * The table names are automatically unquoted. This is a helper for to build the list
994 * of queried tables for the QueryRestrictionBuilder.
995 *
996 * @return string[]
997 */
998 protected function getQueriedTables(): array
999 {
1000 $queriedTables = [];
1001
1002 // Loop through all FROM tables
1003 foreach ($this->getQueryPart('from') as $from) {
1004 $tableName = $this->unquoteSingleIdentifier($from['table']);
1005 $tableAlias = isset($from['alias']) ? $this->unquoteSingleIdentifier($from['alias']) : null;
1006 $queriedTables[$tableName] = $tableAlias;
1007 }
1008
1009 // Loop through all JOIN tables
1010 foreach ($this->getQueryPart('join') as $fromTable => $joins) {
1011 foreach ($joins as $join) {
1012 $tableName = $this->unquoteSingleIdentifier($join['joinTable']);
1013 $tableAlias = isset($join['joinAlias']) ? $this->unquoteSingleIdentifier($join['joinAlias']) : null;
1014 $queriedTables[$tableName] = $tableAlias;
1015 }
1016 }
1017
1018 return $queriedTables;
1019 }
1020
1021 /**
1022 * Add the additional query conditions returned by the QueryRestrictionBuilder
1023 * to the current query and return the original set of conditions so that they
1024 * can be restored after the query has been built/executed.
1025 *
1026 * @return \Doctrine\DBAL\Query\Expression\CompositeExpression|mixed
1027 */
1028 protected function addAdditonalWhereConditions()
1029 {
1030 $queryRestrictionBuilder = GeneralUtility::makeInstance(
1031 QueryRestrictionBuilder::class,
1032 $this->getQueriedTables(),
1033 $this->expr(),
1034 $this->getQueryContext()
1035 );
1036
1037 $originalWhereConditions = $this->concreteQueryBuilder->getQueryPart('where');
1038 if ($originalWhereConditions instanceof CompositeExpression) {
1039 $originalWhereConditions = clone($originalWhereConditions);
1040 }
1041
1042 $additionalQueryRestrictions = $queryRestrictionBuilder->getVisibilityConstraints();
1043
1044 if ($additionalQueryRestrictions->count() !== 0) {
1045 // save the original query conditions so we can restore
1046 // them after the query has been built.
1047
1048 $this->concreteQueryBuilder->andWhere($additionalQueryRestrictions);
1049 }
1050
1051 return $originalWhereConditions;
1052 }
1053 }