TYPO3CMS  8
 All Classes Namespaces Files Functions Variables Pages
QueryBuilder.php
Go to the documentation of this file.
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;
24 
42 {
48  protected $connection;
49 
54 
59 
67  public function __construct(
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 
80  public function getRestrictions()
81  {
83  }
84 
89  {
90  $this->restrictionContainer = $restrictionContainer;
91  }
92 
96  public function resetRestrictions()
97  {
98  $this->restrictionContainer = GeneralUtility::makeInstance(DefaultRestrictionContainer::class);
99  }
100 
110  public function expr(): ExpressionBuilder
111  {
112  return $this->connection->getExpressionBuilder();
113  }
114 
121  public function getType(): int
122  {
123  return $this->concreteQueryBuilder->getType();
124  }
125 
131  public function getConnection(): Connection
132  {
133  return $this->connection;
134  }
135 
142  public function getState(): int
143  {
144  return $this->concreteQueryBuilder->getState();
145  }
146 
153  public function getConcreteQueryBuilder(): \Doctrine\DBAL\Query\QueryBuilder
154  {
156  }
157 
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 
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 
216  public function setParameter($key, $value, string $type = null): QueryBuilder
217  {
218  $this->concreteQueryBuilder->setParameter($key, $value, $type);
219 
220  return $this;
221  }
222 
231  public function setParameters(array $params, array $types = []): QueryBuilder
232  {
233  $this->concreteQueryBuilder->setParameters($params, $types);
234 
235  return $this;
236  }
237 
243  public function getParameters(): array
244  {
245  return $this->concreteQueryBuilder->getParameters();
246  }
247 
255  public function getParameter($key)
256  {
257  return $this->concreteQueryBuilder->getParameter($key);
258  }
259 
265  public function getParameterTypes(): array
266  {
267  return $this->concreteQueryBuilder->getParameterTypes();
268  }
269 
277  public function getParameterType($key)
278  {
279  return $this->concreteQueryBuilder->getParameterType($key);
280  }
281 
289  public function setFirstResult(int $firstResult): QueryBuilder
290  {
291  $this->concreteQueryBuilder->setFirstResult($firstResult);
292 
293  return $this;
294  }
295 
302  public function getFirstResult(): int
303  {
304  return (int)$this->concreteQueryBuilder->getFirstResult();
305  }
306 
314  public function setMaxResults(int $maxResults): QueryBuilder
315  {
316  $this->concreteQueryBuilder->setMaxResults($maxResults);
317 
318  return $this;
319  }
320 
327  public function getMaxResults(): int
328  {
329  return (int)$this->concreteQueryBuilder->getMaxResults();
330  }
331 
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 
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 
375  public function select(string ...$selects): QueryBuilder
376  {
377  $this->concreteQueryBuilder->select(...$this->quoteIdentifiersForSelect($selects));
378 
379  return $this;
380  }
381 
389  public function addSelect(string ...$selects): QueryBuilder
390  {
391  $this->concreteQueryBuilder->addSelect(...$this->quoteIdentifiersForSelect($selects));
392 
393  return $this;
394  }
395 
405  public function selectLiteral(string ...$selects): QueryBuilder
406  {
407  $this->concreteQueryBuilder->select(...$selects);
408 
409  return $this;
410  }
411 
420  public function addSelectLiteral(string ...$selects): QueryBuilder
421  {
422  $this->concreteQueryBuilder->addSelect(...$selects);
423 
424  return $this;
425  }
426 
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 
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 
475  public function insert(string $insert): QueryBuilder
476  {
477  $this->concreteQueryBuilder->insert($this->quoteIdentifier($insert));
478 
479  return $this;
480  }
481 
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 
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 
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 
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 
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 
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 
615  public function where(...$predicates): QueryBuilder
616  {
617  $this->concreteQueryBuilder->where(...$predicates);
618 
619  return $this;
620  }
621 
632  public function andWhere(...$where): QueryBuilder
633  {
634  $this->concreteQueryBuilder->andWhere(...$where);
635 
636  return $this;
637  }
638 
649  public function orWhere(...$where): QueryBuilder
650  {
651  $this->concreteQueryBuilder->orWhere(...$where);
652 
653  return $this;
654  }
655 
664  public function groupBy(...$groupBy): QueryBuilder
665  {
666  $this->concreteQueryBuilder->groupBy(...$this->quoteIdentifiers($groupBy));
667 
668  return $this;
669  }
670 
678  public function addGroupBy(...$groupBy): QueryBuilder
679  {
680  $this->concreteQueryBuilder->addGroupBy(...$this->quoteIdentifiers($groupBy));
681 
682  return $this;
683  }
684 
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 
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 
734  public function having(...$having): QueryBuilder
735  {
736  $this->concreteQueryBuilder->having(...$having);
737  return $this;
738  }
739 
748  public function andHaving(...$having): QueryBuilder
749  {
750  $this->concreteQueryBuilder->andHaving(...$having);
751 
752  return $this;
753  }
754 
763  public function orHaving(...$having): QueryBuilder
764  {
765  $this->concreteQueryBuilder->orHaving(...$having);
766 
767  return $this;
768  }
769 
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 
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 
808  public function getQueryPart(string $queryPartName)
809  {
810  return $this->concreteQueryBuilder->getQueryPart($queryPartName);
811  }
812 
818  public function getQueryParts(): array
819  {
820  return $this->concreteQueryBuilder->getQueryParts();
821  }
822 
830  public function resetQueryParts(array $queryPartNames = null): QueryBuilder
831  {
832  $this->concreteQueryBuilder->resetQueryParts($queryPartNames);
833 
834  return $this;
835  }
836 
844  public function resetQueryPart($queryPartName): QueryBuilder
845  {
846  $this->concreteQueryBuilder->resetQueryPart($queryPartName);
847 
848  return $this;
849  }
850 
857  public function __toString(): string
858  {
859  return $this->getSQL();
860  }
861 
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 
897  public function createPositionalParameter($value, int $type = \PDO::PARAM_STR): string
898  {
899  return $this->concreteQueryBuilder->createPositionalParameter($value, $type);
900  }
901 
909  public function escapeLikeWildcards(string $value): string
910  {
911  return addcslashes($value, '_%');
912  }
913 
922  public function quote($input, string $type = null): string
923  {
924  return $this->getConnection()->quote($input, $type);
925  }
926 
937  public function quoteIdentifier(string $identifier): string
938  {
939  return $this->getConnection()->quoteIdentifier($identifier);
940  }
941 
951  public function quoteIdentifiers(array $input): array
952  {
953  return $this->getConnection()->quoteIdentifiers($input);
954  }
955 
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 
1007  public function quoteColumnValuePairs(array $input): array
1008  {
1009  return $this->getConnection()->quoteColumnValuePairs($input);
1010  }
1011 
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 
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 
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 }
update(string $update, string $alias=null)
setRestrictions(QueryRestrictionContainerInterface $restrictionContainer)
createPositionalParameter($value, int $type=\PDO::PARAM_STR)
addOrderBy(string $fieldName, string $order=null)
innerJoin(string $fromAlias, string $join, string $alias, string $condition=null)
static trimExplode($delim, $string, $removeEmptyValues=false, $limit=0)
leftJoin(string $fromAlias, string $join, string $alias, string $condition=null)
rightJoin(string $fromAlias, string $join, string $alias, string $condition=null)
setParameter($key, $value, string $type=null)
delete(string $delete, string $alias=null)
join(string $fromAlias, string $join, string $alias, string $condition=null)
orderBy(string $fieldName, string $order=null)
add(string $sqlPartName, string $sqlPart, bool $append=false)
from(string $from, string $alias=null)
resetQueryParts(array $queryPartNames=null)
createNamedParameter($value, int $type=\PDO::PARAM_STR, string $placeHolder=null)
values(array $values, bool $createNamedParameters=true)
setValue(string $column, $value, bool $createNamedParameter=true)
static makeInstance($className,...$constructorArguments)
__construct(Connection $connection, QueryRestrictionContainerInterface $restrictionContainer=null,\Doctrine\DBAL\Query\QueryBuilder $concreteQueryBuilder=null)
setParameters(array $params, array $types=[])
set(string $key, $value, bool $createNamedParameter=true)