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