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