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