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