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