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