[CLEANUP] Add class-imports in Extbase Persistence classes
[Packages/TYPO3.CMS.git] / typo3 / sysext / extbase / Classes / Persistence / Generic / Storage / Typo3DbQueryParser.php
1 <?php
2 namespace TYPO3\CMS\Extbase\Persistence\Generic\Storage;
3
4 /*
5 * This file is part of the TYPO3 CMS project.
6 *
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
10 *
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
13 *
14 * The TYPO3 project - inspiring people to share!
15 */
16
17 use TYPO3\CMS\Backend\Utility\BackendUtility;
18 use TYPO3\CMS\Core\Database\Connection;
19 use TYPO3\CMS\Core\Database\ConnectionPool;
20 use TYPO3\CMS\Core\Database\Query\Expression\CompositeExpression;
21 use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
22 use TYPO3\CMS\Core\Database\Query\QueryBuilder;
23 use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
24 use TYPO3\CMS\Core\Utility\GeneralUtility;
25 use TYPO3\CMS\Extbase\Persistence\Generic\Exception;
26 use TYPO3\CMS\Extbase\Persistence\Generic\Exception\InconsistentQuerySettingsException;
27 use TYPO3\CMS\Extbase\Persistence\Generic\Exception\InvalidRelationConfigurationException;
28 use TYPO3\CMS\Extbase\Persistence\Generic\Exception\MissingColumnMapException;
29 use TYPO3\CMS\Extbase\Persistence\Generic\Exception\RepositoryException;
30 use TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnsupportedOrderException;
31 use TYPO3\CMS\Extbase\Persistence\Generic\Mapper\ColumnMap;
32 use TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper;
33 use TYPO3\CMS\Extbase\Persistence\Generic\Qom;
34 use TYPO3\CMS\Extbase\Persistence\Generic\QuerySettingsInterface;
35 use TYPO3\CMS\Extbase\Persistence\Generic\Storage\Exception\BadConstraintException;
36 use TYPO3\CMS\Extbase\Persistence\QueryInterface;
37 use TYPO3\CMS\Extbase\Service\EnvironmentService;
38 use TYPO3\CMS\Frontend\Controller\TypoScriptFrontendController;
39 use TYPO3\CMS\Frontend\Page\PageRepository;
40
41 /**
42 * QueryParser, converting the qom to string representation
43 */
44 class Typo3DbQueryParser
45 {
46 /**
47 * @var DataMapper
48 */
49 protected $dataMapper;
50
51 /**
52 * The TYPO3 page repository. Used for language and workspace overlay
53 *
54 * @var PageRepository
55 */
56 protected $pageRepository;
57
58 /**
59 * @var EnvironmentService
60 */
61 protected $environmentService;
62
63 /**
64 * Instance of the Doctrine query builder
65 *
66 * @var QueryBuilder
67 */
68 protected $queryBuilder;
69
70 /**
71 * Maps domain model properties to their corresponding table aliases that are used in the query, e.g.:
72 *
73 * 'property1' => 'tableName',
74 * 'property1.property2' => 'tableName1',
75 *
76 * @var array
77 */
78 protected $tablePropertyMap = [];
79
80 /**
81 * Maps tablenames to their aliases to be used in where clauses etc.
82 * Mainly used for joins on the same table etc.
83 *
84 * @var array
85 */
86 protected $tableAliasMap = [];
87
88 /**
89 * Stores all tables used in for SQL joins
90 *
91 * @var array
92 */
93 protected $unionTableAliasCache = [];
94
95 /**
96 * @var string
97 */
98 protected $tableName = '';
99
100 /**
101 * @var bool
102 */
103 protected $suggestDistinctQuery = false;
104
105 /**
106 * @param DataMapper $dataMapper
107 */
108 public function injectDataMapper(DataMapper $dataMapper)
109 {
110 $this->dataMapper = $dataMapper;
111 }
112
113 /**
114 * @param EnvironmentService $environmentService
115 */
116 public function injectEnvironmentService(EnvironmentService $environmentService)
117 {
118 $this->environmentService = $environmentService;
119 }
120
121 /**
122 * Whether using a distinct query is suggested.
123 * This information is defined during parsing of the current query
124 * for RELATION_HAS_MANY & RELATION_HAS_AND_BELONGS_TO_MANY relations.
125 *
126 * @return bool
127 */
128 public function isDistinctQuerySuggested(): bool
129 {
130 return $this->suggestDistinctQuery;
131 }
132
133 /**
134 * Returns a ready to be executed QueryBuilder object, based on the query
135 *
136 * @param QueryInterface $query
137 * @return QueryBuilder
138 */
139 public function convertQueryToDoctrineQueryBuilder(QueryInterface $query)
140 {
141 // Reset all properties
142 $this->tablePropertyMap = [];
143 $this->tableAliasMap = [];
144 $this->unionTableAliasCache = [];
145 $this->tableName = '';
146
147 if ($query->getStatement() && $query->getStatement()->getStatement() instanceof QueryBuilder) {
148 $this->queryBuilder = clone $query->getStatement()->getStatement();
149 return $this->queryBuilder;
150 }
151 // Find the right table name
152 $source = $query->getSource();
153 $this->initializeQueryBuilder($source);
154
155 $constraint = $query->getConstraint();
156 if ($constraint instanceof Qom\ConstraintInterface) {
157 $wherePredicates = $this->parseConstraint($constraint, $source);
158 if (!empty($wherePredicates)) {
159 $this->queryBuilder->andWhere($wherePredicates);
160 }
161 }
162
163 $this->parseOrderings($query->getOrderings(), $source);
164 $this->addTypo3Constraints($query);
165
166 return $this->queryBuilder;
167 }
168
169 /**
170 * Creates the queryBuilder object whether it is a regular select or a JOIN
171 *
172 * @param Qom\SourceInterface $source The source
173 */
174 protected function initializeQueryBuilder(Qom\SourceInterface $source)
175 {
176 if ($source instanceof Qom\SelectorInterface) {
177 $className = $source->getNodeTypeName();
178 $tableName = $this->dataMapper->getDataMap($className)->getTableName();
179 $this->tableName = $tableName;
180
181 $this->queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
182 ->getQueryBuilderForTable($tableName);
183
184 $this->queryBuilder
185 ->getRestrictions()
186 ->removeAll();
187
188 $tableAlias = $this->getUniqueAlias($tableName);
189
190 $this->queryBuilder
191 ->select($tableAlias . '.*')
192 ->from($tableName, $tableAlias);
193
194 $this->addRecordTypeConstraint($className);
195 } elseif ($source instanceof Qom\JoinInterface) {
196 $leftSource = $source->getLeft();
197 $leftTableName = $leftSource->getSelectorName();
198
199 $this->queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
200 ->getQueryBuilderForTable($leftTableName);
201 $leftTableAlias = $this->getUniqueAlias($leftTableName);
202 $this->queryBuilder
203 ->select($leftTableAlias . '.*')
204 ->from($leftTableName, $leftTableAlias);
205 $this->parseJoin($source, $leftTableAlias);
206 }
207 }
208
209 /**
210 * Transforms a constraint into SQL and parameter arrays
211 *
212 * @param Qom\ConstraintInterface $constraint The constraint
213 * @param Qom\SourceInterface $source The source
214 * @return CompositeExpression|string
215 * @throws \RuntimeException
216 */
217 protected function parseConstraint(Qom\ConstraintInterface $constraint, Qom\SourceInterface $source)
218 {
219 if ($constraint instanceof Qom\AndInterface) {
220 $constraint1 = $constraint->getConstraint1();
221 $constraint2 = $constraint->getConstraint2();
222 if (($constraint1 instanceof Qom\ConstraintInterface)
223 && ($constraint2 instanceof Qom\ConstraintInterface)
224 ) {
225 return $this->queryBuilder->expr()->andX(
226 $this->parseConstraint($constraint1, $source),
227 $this->parseConstraint($constraint2, $source)
228 );
229 }
230 return '';
231 }
232 if ($constraint instanceof Qom\OrInterface) {
233 $constraint1 = $constraint->getConstraint1();
234 $constraint2 = $constraint->getConstraint2();
235 if (($constraint1 instanceof Qom\ConstraintInterface)
236 && ($constraint2 instanceof Qom\ConstraintInterface)
237 ) {
238 return $this->queryBuilder->expr()->orX(
239 $this->parseConstraint($constraint->getConstraint1(), $source),
240 $this->parseConstraint($constraint->getConstraint2(), $source)
241 );
242 }
243 return '';
244 }
245 if ($constraint instanceof Qom\NotInterface) {
246 return ' NOT(' . $this->parseConstraint($constraint->getConstraint(), $source) . ')';
247 }
248 if ($constraint instanceof Qom\ComparisonInterface) {
249 return $this->parseComparison($constraint, $source);
250 }
251 throw new \RuntimeException('not implemented', 1476199898);
252 }
253
254 /**
255 * Transforms orderings into SQL.
256 *
257 * @param array $orderings An array of orderings (Qom\Ordering)
258 * @param Qom\SourceInterface $source The source
259 * @throws UnsupportedOrderException
260 */
261 protected function parseOrderings(array $orderings, Qom\SourceInterface $source)
262 {
263 foreach ($orderings as $propertyName => $order) {
264 if ($order !== QueryInterface::ORDER_ASCENDING && $order !== QueryInterface::ORDER_DESCENDING) {
265 throw new UnsupportedOrderException('Unsupported order encountered.', 1242816074);
266 }
267 $className = null;
268 $tableName = '';
269 if ($source instanceof Qom\SelectorInterface) {
270 $className = $source->getNodeTypeName();
271 $tableName = $this->dataMapper->convertClassNameToTableName($className);
272 $fullPropertyPath = '';
273 while (strpos($propertyName, '.') !== false) {
274 $this->addUnionStatement($className, $tableName, $propertyName, $fullPropertyPath);
275 }
276 } elseif ($source instanceof Qom\JoinInterface) {
277 $tableName = $source->getLeft()->getSelectorName();
278 }
279 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
280 if ($tableName !== '') {
281 $this->queryBuilder->addOrderBy($tableName . '.' . $columnName, $order);
282 } else {
283 $this->queryBuilder->addOrderBy($columnName, $order);
284 }
285 }
286 }
287
288 /**
289 * add TYPO3 Constraints for all tables to the queryBuilder
290 *
291 * @param QueryInterface $query
292 */
293 protected function addTypo3Constraints(QueryInterface $query)
294 {
295 foreach ($this->tableAliasMap as $tableAlias => $tableName) {
296 $additionalWhereClauses = $this->getAdditionalWhereClause($query->getQuerySettings(), $tableName, $tableAlias);
297 $statement = $this->getVisibilityConstraintStatement($query->getQuerySettings(), $tableName, $tableAlias);
298 if ($statement !== '') {
299 $additionalWhereClauses[] = $statement;
300 }
301 if (!empty($additionalWhereClauses)) {
302 if (in_array($tableAlias, $this->unionTableAliasCache, true)) {
303 $this->queryBuilder->andWhere(
304 $this->queryBuilder->expr()->orX(
305 $this->queryBuilder->expr()->andX(...$additionalWhereClauses),
306 $this->queryBuilder->expr()->isNull($tableAlias . '.uid')
307 )
308 );
309 } else {
310 $this->queryBuilder->andWhere(...$additionalWhereClauses);
311 }
312 }
313 }
314 }
315
316 /**
317 * Parse a Comparison into SQL and parameter arrays.
318 *
319 * @param Qom\ComparisonInterface $comparison The comparison to parse
320 * @param Qom\SourceInterface $source The source
321 * @return string
322 * @throws \RuntimeException
323 * @throws RepositoryException
324 * @throws BadConstraintException
325 */
326 protected function parseComparison(Qom\ComparisonInterface $comparison, Qom\SourceInterface $source)
327 {
328 if ($comparison->getOperator() === QueryInterface::OPERATOR_CONTAINS) {
329 if ($comparison->getOperand2() === null) {
330 throw new BadConstraintException('The value for the CONTAINS operator must not be null.', 1484828468);
331 }
332 $value = $this->dataMapper->getPlainValue($comparison->getOperand2());
333 if (!$source instanceof Qom\SelectorInterface) {
334 throw new \RuntimeException('Source is not of type "SelectorInterface"', 1395362539);
335 }
336 $className = $source->getNodeTypeName();
337 $tableName = $this->dataMapper->convertClassNameToTableName($className);
338 $operand1 = $comparison->getOperand1();
339 $propertyName = $operand1->getPropertyName();
340 $fullPropertyPath = '';
341 while (strpos($propertyName, '.') !== false) {
342 $this->addUnionStatement($className, $tableName, $propertyName, $fullPropertyPath);
343 }
344 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
345 $dataMap = $this->dataMapper->getDataMap($className);
346 $columnMap = $dataMap->getColumnMap($propertyName);
347 $typeOfRelation = $columnMap instanceof ColumnMap ? $columnMap->getTypeOfRelation() : null;
348 if ($typeOfRelation === ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY) {
349 $relationTableName = $columnMap->getRelationTableName();
350 $queryBuilderForSubselect = $this->queryBuilder->getConnection()->createQueryBuilder();
351 $queryBuilderForSubselect
352 ->select($columnMap->getParentKeyFieldName())
353 ->from($relationTableName)
354 ->where(
355 $queryBuilderForSubselect->expr()->eq(
356 $columnMap->getChildKeyFieldName(),
357 $this->queryBuilder->createNamedParameter($value)
358 )
359 );
360 $additionalWhereForMatchFields = $this->getAdditionalMatchFieldsStatement($queryBuilderForSubselect->expr(), $columnMap, $relationTableName, $relationTableName);
361 if ($additionalWhereForMatchFields) {
362 $queryBuilderForSubselect->andWhere($additionalWhereForMatchFields);
363 }
364
365 return $this->queryBuilder->expr()->comparison(
366 $this->queryBuilder->quoteIdentifier($tableName . '.uid'),
367 'IN',
368 '(' . $queryBuilderForSubselect->getSQL() . ')'
369 );
370 }
371 if ($typeOfRelation === ColumnMap::RELATION_HAS_MANY) {
372 $parentKeyFieldName = $columnMap->getParentKeyFieldName();
373 if (isset($parentKeyFieldName)) {
374 $childTableName = $columnMap->getChildTableName();
375
376 // Build the SQL statement of the subselect
377 $queryBuilderForSubselect = $this->queryBuilder->getConnection()->createQueryBuilder();
378 $queryBuilderForSubselect
379 ->select($parentKeyFieldName)
380 ->from($childTableName)
381 ->where(
382 $queryBuilderForSubselect->expr()->eq(
383 'uid',
384 (int)$value
385 )
386 );
387
388 // Add it to the main query
389 return $this->queryBuilder->expr()->eq(
390 $tableName . '.uid',
391 '(' . $queryBuilderForSubselect->getSQL() . ')'
392 );
393 }
394 return $this->queryBuilder->expr()->inSet(
395 $tableName . '.' . $columnName,
396 $this->queryBuilder->quote($value)
397 );
398 }
399 throw new RepositoryException('Unsupported or non-existing property name "' . $propertyName . '" used in relation matching.', 1327065745);
400 }
401 return $this->parseDynamicOperand($comparison, $source);
402 }
403
404 /**
405 * Parse a DynamicOperand into SQL and parameter arrays.
406 *
407 * @param Qom\ComparisonInterface $comparison
408 * @param Qom\SourceInterface $source The source
409 * @return string
410 * @throws Exception
411 * @throws BadConstraintException
412 */
413 protected function parseDynamicOperand(Qom\ComparisonInterface $comparison, Qom\SourceInterface $source)
414 {
415 $value = $comparison->getOperand2();
416 $fieldName = $this->parseOperand($comparison->getOperand1(), $source);
417 $expr = null;
418 $exprBuilder = $this->queryBuilder->expr();
419 switch ($comparison->getOperator()) {
420 case QueryInterface::OPERATOR_IN:
421 $hasValue = false;
422 $plainValues = [];
423 foreach ($value as $singleValue) {
424 $plainValue = $this->dataMapper->getPlainValue($singleValue);
425 if ($plainValue !== null) {
426 $hasValue = true;
427 $plainValues[] = $this->createTypedNamedParameter($singleValue);
428 }
429 }
430 if (!$hasValue) {
431 throw new BadConstraintException(
432 'The IN operator needs a non-empty value list to compare against. ' .
433 'The given value list is empty.',
434 1484828466
435 );
436 }
437 $expr = $exprBuilder->comparison($fieldName, 'IN', '(' . implode(', ', $plainValues) . ')');
438 break;
439 case QueryInterface::OPERATOR_EQUAL_TO:
440 if ($value === null) {
441 $expr = $fieldName . ' IS NULL';
442 } else {
443 $placeHolder = $this->createTypedNamedParameter($value);
444 $expr = $exprBuilder->comparison($fieldName, $exprBuilder::EQ, $placeHolder);
445 }
446 break;
447 case QueryInterface::OPERATOR_EQUAL_TO_NULL:
448 $expr = $fieldName . ' IS NULL';
449 break;
450 case QueryInterface::OPERATOR_NOT_EQUAL_TO:
451 if ($value === null) {
452 $expr = $fieldName . ' IS NOT NULL';
453 } else {
454 $placeHolder = $this->createTypedNamedParameter($value);
455 $expr = $exprBuilder->comparison($fieldName, $exprBuilder::NEQ, $placeHolder);
456 }
457 break;
458 case QueryInterface::OPERATOR_NOT_EQUAL_TO_NULL:
459 $expr = $fieldName . ' IS NOT NULL';
460 break;
461 case QueryInterface::OPERATOR_LESS_THAN:
462 $placeHolder = $this->createTypedNamedParameter($value);
463 $expr = $exprBuilder->comparison($fieldName, $exprBuilder::LT, $placeHolder);
464 break;
465 case QueryInterface::OPERATOR_LESS_THAN_OR_EQUAL_TO:
466 $placeHolder = $this->createTypedNamedParameter($value);
467 $expr = $exprBuilder->comparison($fieldName, $exprBuilder::LTE, $placeHolder);
468 break;
469 case QueryInterface::OPERATOR_GREATER_THAN:
470 $placeHolder = $this->createTypedNamedParameter($value);
471 $expr = $exprBuilder->comparison($fieldName, $exprBuilder::GT, $placeHolder);
472 break;
473 case QueryInterface::OPERATOR_GREATER_THAN_OR_EQUAL_TO:
474 $placeHolder = $this->createTypedNamedParameter($value);
475 $expr = $exprBuilder->comparison($fieldName, $exprBuilder::GTE, $placeHolder);
476 break;
477 case QueryInterface::OPERATOR_LIKE:
478 $placeHolder = $this->createTypedNamedParameter($value, \PDO::PARAM_STR);
479 $expr = $exprBuilder->comparison($fieldName, 'LIKE', $placeHolder);
480 break;
481 default:
482 throw new Exception(
483 'Unsupported operator encountered.',
484 1242816073
485 );
486 }
487 return $expr;
488 }
489
490 /**
491 * Maps plain value of operand to PDO types to help Doctrine and/or the database driver process the value
492 * correctly when building the query.
493 *
494 * @param mixed $value The parameter value
495 * @return int
496 * @throws \InvalidArgumentException
497 */
498 protected function getParameterType($value): int
499 {
500 $parameterType = gettype($value);
501 switch ($parameterType) {
502 case 'integer':
503 return \PDO::PARAM_INT;
504 case 'string':
505 return \PDO::PARAM_STR;
506 default:
507 throw new \InvalidArgumentException(
508 'Unsupported parameter type encountered. Expected integer or string, ' . $parameterType . ' given.',
509 1494878863
510 );
511 }
512 }
513
514 /**
515 * Create a named parameter for the QueryBuilder and guess the parameter type based on the
516 * output of DataMapper::getPlainValue(). The type of the named parameter can be forced to
517 * one of the \PDO::PARAM_* types by specifying the $forceType argument.
518 *
519 * @param mixed $value The input value that should be sent to the database
520 * @param int|null $forceType The \PDO::PARAM_* type that should be forced
521 * @return string The placeholder string to be used in the query
522 * @see \TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper::getPlainValue()
523 */
524 protected function createTypedNamedParameter($value, int $forceType = null): string
525 {
526 $plainValue = $this->dataMapper->getPlainValue($value);
527 $parameterType = $forceType ?? $this->getParameterType($plainValue);
528 $placeholder = $this->queryBuilder->createNamedParameter($plainValue, $parameterType);
529
530 return $placeholder;
531 }
532
533 /**
534 * @param Qom\DynamicOperandInterface $operand
535 * @param Qom\SourceInterface $source The source
536 * @return string
537 * @throws \InvalidArgumentException
538 */
539 protected function parseOperand(Qom\DynamicOperandInterface $operand, Qom\SourceInterface $source)
540 {
541 if ($operand instanceof Qom\LowerCaseInterface) {
542 $constraintSQL = 'LOWER(' . $this->parseOperand($operand->getOperand(), $source) . ')';
543 } elseif ($operand instanceof Qom\UpperCaseInterface) {
544 $constraintSQL = 'UPPER(' . $this->parseOperand($operand->getOperand(), $source) . ')';
545 } elseif ($operand instanceof Qom\PropertyValueInterface) {
546 $propertyName = $operand->getPropertyName();
547 $className = '';
548 if ($source instanceof Qom\SelectorInterface) {
549 $className = $source->getNodeTypeName();
550 $tableName = $this->dataMapper->convertClassNameToTableName($className);
551 $fullPropertyPath = '';
552 while (strpos($propertyName, '.') !== false) {
553 $this->addUnionStatement($className, $tableName, $propertyName, $fullPropertyPath);
554 }
555 } elseif ($source instanceof Qom\JoinInterface) {
556 $tableName = $source->getJoinCondition()->getSelector1Name();
557 }
558 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
559 $constraintSQL = (!empty($tableName) ? $tableName . '.' : '') . $columnName;
560 $constraintSQL = $this->queryBuilder->getConnection()->quoteIdentifier($constraintSQL);
561 } else {
562 throw new \InvalidArgumentException('Given operand has invalid type "' . get_class($operand) . '".', 1395710211);
563 }
564 return $constraintSQL;
565 }
566
567 /**
568 * Add a constraint to ensure that the record type of the returned tuples is matching the data type of the repository.
569 *
570 * @param string $className The class name
571 */
572 protected function addRecordTypeConstraint($className)
573 {
574 if ($className !== null) {
575 $dataMap = $this->dataMapper->getDataMap($className);
576 if ($dataMap->getRecordTypeColumnName() !== null) {
577 $recordTypes = [];
578 if ($dataMap->getRecordType() !== null) {
579 $recordTypes[] = $dataMap->getRecordType();
580 }
581 foreach ($dataMap->getSubclasses() as $subclassName) {
582 $subclassDataMap = $this->dataMapper->getDataMap($subclassName);
583 if ($subclassDataMap->getRecordType() !== null) {
584 $recordTypes[] = $subclassDataMap->getRecordType();
585 }
586 }
587 if (!empty($recordTypes)) {
588 $recordTypeStatements = [];
589 foreach ($recordTypes as $recordType) {
590 $tableName = $dataMap->getTableName();
591 $recordTypeStatements[] = $this->queryBuilder->expr()->eq(
592 $tableName . '.' . $dataMap->getRecordTypeColumnName(),
593 $this->queryBuilder->createNamedParameter($recordType)
594 );
595 }
596 $this->queryBuilder->andWhere(
597 $this->queryBuilder->expr()->orX(...$recordTypeStatements)
598 );
599 }
600 }
601 }
602 }
603
604 /**
605 * Builds a condition for filtering records by the configured match field,
606 * e.g. MM_match_fields, foreign_match_fields or foreign_table_field.
607 *
608 * @param ExpressionBuilder $exprBuilder
609 * @param ColumnMap $columnMap The column man for which the condition should be build.
610 * @param string $childTableAlias The alias of the child record table used in the query.
611 * @param string $parentTable The real name of the parent table (used for building the foreign_table_field condition).
612 * @return string The match field conditions or an empty string.
613 */
614 protected function getAdditionalMatchFieldsStatement($exprBuilder, $columnMap, $childTableAlias, $parentTable = null)
615 {
616 $additionalWhereForMatchFields = [];
617 $relationTableMatchFields = $columnMap->getRelationTableMatchFields();
618 if (is_array($relationTableMatchFields) && !empty($relationTableMatchFields)) {
619 foreach ($relationTableMatchFields as $fieldName => $value) {
620 $additionalWhereForMatchFields[] = $exprBuilder->eq($childTableAlias . '.' . $fieldName, $this->queryBuilder->createNamedParameter($value));
621 }
622 }
623
624 if (isset($parentTable)) {
625 $parentTableFieldName = $columnMap->getParentTableFieldName();
626 if (!empty($parentTableFieldName)) {
627 $additionalWhereForMatchFields[] = $exprBuilder->eq($childTableAlias . '.' . $parentTableFieldName, $this->queryBuilder->createNamedParameter($parentTable));
628 }
629 }
630
631 if (!empty($additionalWhereForMatchFields)) {
632 return $exprBuilder->andX(...$additionalWhereForMatchFields);
633 }
634 return '';
635 }
636
637 /**
638 * Adds additional WHERE statements according to the query settings.
639 *
640 * @param QuerySettingsInterface $querySettings The TYPO3 CMS specific query settings
641 * @param string $tableName The table name to add the additional where clause for
642 * @param string $tableAlias The table alias used in the query.
643 * @return array
644 */
645 protected function getAdditionalWhereClause(QuerySettingsInterface $querySettings, $tableName, $tableAlias = null)
646 {
647 $whereClause = [];
648 if ($querySettings->getRespectSysLanguage()) {
649 $systemLanguageStatement = $this->getSysLanguageStatement($tableName, $tableAlias, $querySettings);
650 if (!empty($systemLanguageStatement)) {
651 $whereClause[] = $systemLanguageStatement;
652 }
653 }
654
655 if ($querySettings->getRespectStoragePage()) {
656 $pageIdStatement = $this->getPageIdStatement($tableName, $tableAlias, $querySettings->getStoragePageIds());
657 if (!empty($pageIdStatement)) {
658 $whereClause[] = $pageIdStatement;
659 }
660 }
661
662 return $whereClause;
663 }
664
665 /**
666 * Adds enableFields and deletedClause to the query if necessary
667 *
668 * @param QuerySettingsInterface $querySettings
669 * @param string $tableName The database table name
670 * @param string $tableAlias
671 * @return string
672 */
673 protected function getVisibilityConstraintStatement(QuerySettingsInterface $querySettings, $tableName, $tableAlias)
674 {
675 $statement = '';
676 if (is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
677 $ignoreEnableFields = $querySettings->getIgnoreEnableFields();
678 $enableFieldsToBeIgnored = $querySettings->getEnableFieldsToBeIgnored();
679 $includeDeleted = $querySettings->getIncludeDeleted();
680 if ($this->environmentService->isEnvironmentInFrontendMode()) {
681 $statement .= $this->getFrontendConstraintStatement($tableName, $ignoreEnableFields, $enableFieldsToBeIgnored, $includeDeleted);
682 } else {
683 // TYPO3_MODE === 'BE'
684 $statement .= $this->getBackendConstraintStatement($tableName, $ignoreEnableFields, $includeDeleted);
685 }
686 if (!empty($statement)) {
687 $statement = $this->replaceTableNameWithAlias($statement, $tableName, $tableAlias);
688 $statement = strtolower(substr($statement, 1, 3)) === 'and' ? substr($statement, 5) : $statement;
689 }
690 }
691 return $statement;
692 }
693
694 /**
695 * Returns constraint statement for frontend context
696 *
697 * @param string $tableName
698 * @param bool $ignoreEnableFields A flag indicating whether the enable fields should be ignored
699 * @param array $enableFieldsToBeIgnored If $ignoreEnableFields is true, this array specifies enable fields to be ignored. If it is NULL or an empty array (default) all enable fields are ignored.
700 * @param bool $includeDeleted A flag indicating whether deleted records should be included
701 * @return string
702 * @throws InconsistentQuerySettingsException
703 */
704 protected function getFrontendConstraintStatement($tableName, $ignoreEnableFields, array $enableFieldsToBeIgnored = [], $includeDeleted)
705 {
706 $statement = '';
707 if ($ignoreEnableFields && !$includeDeleted) {
708 if (!empty($enableFieldsToBeIgnored)) {
709 // array_combine() is necessary because of the way \TYPO3\CMS\Frontend\Page\PageRepository::enableFields() is implemented
710 $statement .= $this->getPageRepository()->enableFields($tableName, -1, array_combine($enableFieldsToBeIgnored, $enableFieldsToBeIgnored));
711 } else {
712 $statement .= $this->getPageRepository()->deleteClause($tableName);
713 }
714 } elseif (!$ignoreEnableFields && !$includeDeleted) {
715 $statement .= $this->getPageRepository()->enableFields($tableName);
716 } elseif (!$ignoreEnableFields && $includeDeleted) {
717 throw new InconsistentQuerySettingsException('Query setting "ignoreEnableFields=FALSE" can not be used together with "includeDeleted=TRUE" in frontend context.', 1460975922);
718 }
719 return $statement;
720 }
721
722 /**
723 * Returns constraint statement for backend context
724 *
725 * @param string $tableName
726 * @param bool $ignoreEnableFields A flag indicating whether the enable fields should be ignored
727 * @param bool $includeDeleted A flag indicating whether deleted records should be included
728 * @return string
729 */
730 protected function getBackendConstraintStatement($tableName, $ignoreEnableFields, $includeDeleted)
731 {
732 $statement = '';
733 if (!$ignoreEnableFields) {
734 $statement .= BackendUtility::BEenableFields($tableName);
735 }
736 if (!$includeDeleted) {
737 $statement .= BackendUtility::deleteClause($tableName);
738 }
739 return $statement;
740 }
741
742 /**
743 * Builds the language field statement
744 *
745 * @param string $tableName The database table name
746 * @param string $tableAlias The table alias used in the query.
747 * @param QuerySettingsInterface $querySettings The TYPO3 CMS specific query settings
748 * @return string
749 */
750 protected function getSysLanguageStatement($tableName, $tableAlias, $querySettings)
751 {
752 if (is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
753 if (!empty($GLOBALS['TCA'][$tableName]['ctrl']['languageField'])) {
754 // Select all entries for the current language
755 // If any language is set -> get those entries which are not translated yet
756 // They will be removed by \TYPO3\CMS\Frontend\Page\PageRepository::getRecordOverlay if not matching overlay mode
757 $languageField = $GLOBALS['TCA'][$tableName]['ctrl']['languageField'];
758
759 if (isset($GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'])
760 && $querySettings->getLanguageUid() > 0
761 ) {
762 $mode = $querySettings->getLanguageMode();
763
764 if ($mode === 'strict') {
765 $queryBuilderForSubselect = $this->queryBuilder->getConnection()->createQueryBuilder();
766 $queryBuilderForSubselect->getRestrictions()->removeAll()->add(new DeletedRestriction());
767 $queryBuilderForSubselect
768 ->select($tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'])
769 ->from($tableName)
770 ->where(
771 $queryBuilderForSubselect->expr()->andX(
772 $queryBuilderForSubselect->expr()->gt($tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'], 0),
773 $queryBuilderForSubselect->expr()->eq($tableName . '.' . $languageField, (int)$querySettings->getLanguageUid())
774 )
775 );
776 return $this->queryBuilder->expr()->orX(
777 $this->queryBuilder->expr()->eq($tableAlias . '.' . $languageField, -1),
778 $this->queryBuilder->expr()->andX(
779 $this->queryBuilder->expr()->eq($tableAlias . '.' . $languageField, (int)$querySettings->getLanguageUid()),
780 $this->queryBuilder->expr()->eq($tableAlias . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'], 0)
781 ),
782 $this->queryBuilder->expr()->andX(
783 $this->queryBuilder->expr()->eq($tableAlias . '.' . $languageField, 0),
784 $this->queryBuilder->expr()->in(
785 $tableAlias . '.uid',
786 $queryBuilderForSubselect->getSQL()
787
788 )
789 )
790 );
791 }
792 $queryBuilderForSubselect = $this->queryBuilder->getConnection()->createQueryBuilder();
793 $queryBuilderForSubselect->getRestrictions()->removeAll()->add(new DeletedRestriction());
794 $queryBuilderForSubselect
795 ->select($tableAlias . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'])
796 ->from($tableName)
797 ->where(
798 $queryBuilderForSubselect->expr()->andX(
799 $queryBuilderForSubselect->expr()->gt($tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'], 0),
800 $queryBuilderForSubselect->expr()->eq($tableName . '.' . $languageField, (int)$querySettings->getLanguageUid())
801 )
802 );
803 return $this->queryBuilder->expr()->orX(
804 $this->queryBuilder->expr()->in($tableAlias . '.' . $languageField, [(int)$querySettings->getLanguageUid(), -1]),
805 $this->queryBuilder->expr()->andX(
806 $this->queryBuilder->expr()->eq($tableAlias . '.' . $languageField, 0),
807 $this->queryBuilder->expr()->notIn(
808 $tableAlias . '.uid',
809 $queryBuilderForSubselect->getSQL()
810
811 )
812 )
813 );
814 }
815 return $this->queryBuilder->expr()->in(
816 $tableAlias . '.' . $languageField,
817 [(int)$querySettings->getLanguageUid(), -1]
818 );
819 }
820 }
821 return '';
822 }
823
824 /**
825 * Builds the page ID checking statement
826 *
827 * @param string $tableName The database table name
828 * @param string $tableAlias The table alias used in the query.
829 * @param array $storagePageIds list of storage page ids
830 * @return string
831 * @throws InconsistentQuerySettingsException
832 */
833 protected function getPageIdStatement($tableName, $tableAlias, array $storagePageIds)
834 {
835 if (!is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
836 return '';
837 }
838
839 $rootLevel = (int)$GLOBALS['TCA'][$tableName]['ctrl']['rootLevel'];
840 switch ($rootLevel) {
841 // Only in pid 0
842 case 1:
843 $storagePageIds = [0];
844 break;
845 // Pid 0 and pagetree
846 case -1:
847 if (empty($storagePageIds)) {
848 $storagePageIds = [0];
849 } else {
850 $storagePageIds[] = 0;
851 }
852 break;
853 // Only pagetree or not set
854 case 0:
855 if (empty($storagePageIds)) {
856 throw new InconsistentQuerySettingsException('Missing storage page ids.', 1365779762);
857 }
858 break;
859 // Invalid configuration
860 default:
861 return '';
862 }
863 $storagePageIds = array_map('intval', $storagePageIds);
864 if (count($storagePageIds) === 1) {
865 return $this->queryBuilder->expr()->eq($tableAlias . '.pid', reset($storagePageIds));
866 }
867 return $this->queryBuilder->expr()->in($tableAlias . '.pid', $storagePageIds);
868 }
869
870 /**
871 * Transforms a Join into SQL and parameter arrays
872 *
873 * @param Qom\JoinInterface $join The join
874 * @param string $leftTableAlias The alias from the table to main
875 */
876 protected function parseJoin(Qom\JoinInterface $join, $leftTableAlias)
877 {
878 $leftSource = $join->getLeft();
879 $leftClassName = $leftSource->getNodeTypeName();
880 $this->addRecordTypeConstraint($leftClassName);
881 $rightSource = $join->getRight();
882 if ($rightSource instanceof Qom\JoinInterface) {
883 $left = $rightSource->getLeft();
884 $rightClassName = $left->getNodeTypeName();
885 $rightTableName = $left->getSelectorName();
886 } else {
887 $rightClassName = $rightSource->getNodeTypeName();
888 $rightTableName = $rightSource->getSelectorName();
889 $this->queryBuilder->addSelect($rightTableName . '.*');
890 }
891 $this->addRecordTypeConstraint($rightClassName);
892 $rightTableAlias = $this->getUniqueAlias($rightTableName);
893 $joinCondition = $join->getJoinCondition();
894 $joinConditionExpression = null;
895 $this->unionTableAliasCache[] = $rightTableAlias;
896 if ($joinCondition instanceof Qom\EquiJoinCondition) {
897 $column1Name = $this->dataMapper->convertPropertyNameToColumnName($joinCondition->getProperty1Name(), $leftClassName);
898 $column2Name = $this->dataMapper->convertPropertyNameToColumnName($joinCondition->getProperty2Name(), $rightClassName);
899
900 $joinConditionExpression = $this->queryBuilder->expr()->eq(
901 $leftTableAlias . '.' . $column1Name,
902 $this->queryBuilder->quoteIdentifier($rightTableAlias . '.' . $column2Name)
903 );
904 }
905 $this->queryBuilder->leftJoin($leftTableAlias, $rightTableName, $rightTableAlias, $joinConditionExpression);
906 if ($rightSource instanceof Qom\JoinInterface) {
907 $this->parseJoin($rightSource, $rightTableAlias);
908 }
909 }
910
911 /**
912 * Generates a unique alias for the given table and the given property path.
913 * The property path will be mapped to the generated alias in the tablePropertyMap.
914 *
915 * @param string $tableName The name of the table for which the alias should be generated.
916 * @param string $fullPropertyPath The full property path that is related to the given table.
917 * @return string The generated table alias.
918 */
919 protected function getUniqueAlias($tableName, $fullPropertyPath = null)
920 {
921 if (isset($fullPropertyPath) && isset($this->tablePropertyMap[$fullPropertyPath])) {
922 return $this->tablePropertyMap[$fullPropertyPath];
923 }
924
925 $alias = $tableName;
926 $i = 0;
927 while (isset($this->tableAliasMap[$alias])) {
928 $alias = $tableName . $i;
929 $i++;
930 }
931
932 $this->tableAliasMap[$alias] = $tableName;
933
934 if (isset($fullPropertyPath)) {
935 $this->tablePropertyMap[$fullPropertyPath] = $alias;
936 }
937
938 return $alias;
939 }
940
941 /**
942 * adds a union statement to the query, mostly for tables referenced in the where condition.
943 * The property for which the union statement is generated will be appended.
944 *
945 * @param string &$className The name of the parent class, will be set to the child class after processing.
946 * @param string &$tableName The name of the parent table, will be set to the table alias that is used in the union statement.
947 * @param string &$propertyPath The remaining property path, will be cut of by one part during the process.
948 * @param string $fullPropertyPath The full path the the current property, will be used to make table names unique.
949 * @throws Exception
950 * @throws InvalidRelationConfigurationException
951 * @throws MissingColumnMapException
952 */
953 protected function addUnionStatement(&$className, &$tableName, &$propertyPath, &$fullPropertyPath)
954 {
955 $explodedPropertyPath = explode('.', $propertyPath, 2);
956 $propertyName = $explodedPropertyPath[0];
957 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
958 $realTableName = $this->dataMapper->convertClassNameToTableName($className);
959 $tableName = isset($this->tablePropertyMap[$fullPropertyPath]) ? $this->tablePropertyMap[$fullPropertyPath] : $realTableName;
960 $columnMap = $this->dataMapper->getDataMap($className)->getColumnMap($propertyName);
961
962 if ($columnMap === null) {
963 throw new MissingColumnMapException('The ColumnMap for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1355142232);
964 }
965
966 $parentKeyFieldName = $columnMap->getParentKeyFieldName();
967 $childTableName = $columnMap->getChildTableName();
968
969 if ($childTableName === null) {
970 throw new InvalidRelationConfigurationException('The relation information for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1353170925);
971 }
972
973 $fullPropertyPath .= ($fullPropertyPath === '') ? $propertyName : '.' . $propertyName;
974 $childTableAlias = $this->getUniqueAlias($childTableName, $fullPropertyPath);
975
976 // If there is already a union with the current identifier we do not need to build it again and exit early.
977 if (in_array($childTableAlias, $this->unionTableAliasCache, true)) {
978 $propertyPath = $explodedPropertyPath[1];
979 $tableName = $childTableAlias;
980 $className = $this->dataMapper->getType($className, $propertyName);
981 return;
982 }
983
984 if ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_ONE) {
985 if (isset($parentKeyFieldName)) {
986 // @todo: no test for this part yet
987 $joinConditionExpression = $this->queryBuilder->expr()->eq(
988 $tableName . '.uid',
989 $this->queryBuilder->quoteIdentifier($childTableAlias . '.' . $parentKeyFieldName)
990 );
991 } else {
992 $joinConditionExpression = $this->queryBuilder->expr()->eq(
993 $tableName . '.' . $columnName,
994 $this->queryBuilder->quoteIdentifier($childTableAlias . '.uid')
995 );
996 }
997 $this->queryBuilder->leftJoin($tableName, $childTableName, $childTableAlias, $joinConditionExpression);
998 $this->unionTableAliasCache[] = $childTableAlias;
999 $this->queryBuilder->andWhere(
1000 $this->getAdditionalMatchFieldsStatement($this->queryBuilder->expr(), $columnMap, $childTableAlias, $realTableName)
1001 );
1002 } elseif ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_MANY) {
1003 // @todo: no tests for this part yet
1004 if (isset($parentKeyFieldName)) {
1005 $joinConditionExpression = $this->queryBuilder->expr()->eq(
1006 $tableName . '.uid',
1007 $this->queryBuilder->quoteIdentifier($childTableAlias . '.' . $parentKeyFieldName)
1008 );
1009 } else {
1010 $joinConditionExpression = $this->queryBuilder->expr()->inSet(
1011 $tableName . '.' . $columnName,
1012 $this->queryBuilder->quoteIdentifier($childTableAlias . '.uid'),
1013 true
1014 );
1015 }
1016 $this->queryBuilder->leftJoin($tableName, $childTableName, $childTableAlias, $joinConditionExpression);
1017 $this->unionTableAliasCache[] = $childTableAlias;
1018 $this->queryBuilder->andWhere(
1019 $this->getAdditionalMatchFieldsStatement($this->queryBuilder->expr(), $columnMap, $childTableAlias, $realTableName)
1020 );
1021 $this->suggestDistinctQuery = true;
1022 } elseif ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY) {
1023 $relationTableName = $columnMap->getRelationTableName();
1024 $relationTableAlias = $relationTableAlias = $this->getUniqueAlias($relationTableName, $fullPropertyPath . '_mm');
1025
1026 $joinConditionExpression = $this->queryBuilder->expr()->andX(
1027 $this->queryBuilder->expr()->eq(
1028 $tableName . '.uid',
1029 $this->queryBuilder->quoteIdentifier(
1030 $relationTableAlias . '.' . $columnMap->getParentKeyFieldName()
1031 )
1032 ),
1033 $this->getAdditionalMatchFieldsStatement($this->queryBuilder->expr(), $columnMap, $relationTableAlias, $realTableName)
1034 );
1035 $this->queryBuilder->leftJoin($tableName, $relationTableName, $relationTableAlias, $joinConditionExpression);
1036 $joinConditionExpression = $this->queryBuilder->expr()->eq(
1037 $relationTableAlias . '.' . $columnMap->getChildKeyFieldName(),
1038 $this->queryBuilder->quoteIdentifier($childTableAlias . '.uid')
1039 );
1040 $this->queryBuilder->leftJoin($relationTableAlias, $childTableName, $childTableAlias, $joinConditionExpression);
1041 $this->unionTableAliasCache[] = $childTableAlias;
1042 $this->suggestDistinctQuery = true;
1043 } else {
1044 throw new Exception('Could not determine type of relation.', 1252502725);
1045 }
1046 $propertyPath = $explodedPropertyPath[1];
1047 $tableName = $childTableAlias;
1048 $className = $this->dataMapper->getType($className, $propertyName);
1049 }
1050
1051 /**
1052 * If the table name does not match the table alias all occurrences of
1053 * "tableName." are replaced with "tableAlias." in the given SQL statement.
1054 *
1055 * @param string $statement The SQL statement in which the values are replaced.
1056 * @param string $tableName The table name that is replaced.
1057 * @param string $tableAlias The table alias that replaced the table name.
1058 * @return string The modified SQL statement.
1059 */
1060 protected function replaceTableNameWithAlias($statement, $tableName, $tableAlias)
1061 {
1062 if ($tableAlias !== $tableName) {
1063 /** @var Connection $connection */
1064 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($tableName);
1065 $quotedTableName = $connection->quoteIdentifier($tableName);
1066 $quotedTableAlias = $connection->quoteIdentifier($tableAlias);
1067 $statement = str_replace(
1068 [$tableName . '.', $quotedTableName . '.'],
1069 [$tableAlias . '.', $quotedTableAlias . '.'],
1070 $statement
1071 );
1072 }
1073
1074 return $statement;
1075 }
1076
1077 /**
1078 * @return PageRepository
1079 */
1080 protected function getPageRepository()
1081 {
1082 if (!$this->pageRepository instanceof PageRepository) {
1083 if ($this->environmentService->isEnvironmentInFrontendMode() && is_object($this->getTSFE())) {
1084 $this->pageRepository = $this->getTSFE()->sys_page;
1085 } else {
1086 $this->pageRepository = GeneralUtility::makeInstance(PageRepository::class);
1087 }
1088 }
1089
1090 return $this->pageRepository;
1091 }
1092
1093 /**
1094 * @return TypoScriptFrontendController|null
1095 */
1096 protected function getTSFE()
1097 {
1098 return $GLOBALS['TSFE'] ?? null;
1099 }
1100 }