[!!!][TASK] Extbase: Remove preparsing of queries
[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 * Parses the query and returns the SQL statement parts.
91 *
92 * @param QueryInterface $query The query
93 * @return array The SQL statement parts
94 */
95 public function parseQuery(QueryInterface $query)
96 {
97 $this->tablePropertyMap = array();
98 $sql = array();
99 $sql['keywords'] = array();
100 $sql['tables'] = array();
101 $sql['unions'] = array();
102 $sql['fields'] = array();
103 $sql['where'] = array();
104 $sql['additionalWhereClause'] = array();
105 $sql['orderings'] = array();
106 $sql['limit'] = ((int)$query->getLimit() ?: null);
107 $sql['offset'] = ((int)$query->getOffset() ?: null);
108 $sql['tableAliasMap'] = array();
109 $source = $query->getSource();
110 $this->parseSource($source, $sql);
111 $this->parseConstraint($query->getConstraint(), $source, $sql);
112 $this->parseOrderings($query->getOrderings(), $source, $sql);
113
114 foreach ($sql['tableAliasMap'] as $tableAlias => $tableName) {
115 $additionalWhereClause = $this->getAdditionalWhereClause($query->getQuerySettings(), $tableName, $tableAlias);
116 if ($additionalWhereClause !== '') {
117 $additionalWhereClause = $this->addNullConditionToStatementIfRequired($sql, $additionalWhereClause, $tableAlias);
118 $sql['additionalWhereClause'][] = $additionalWhereClause;
119 }
120 }
121
122 foreach ($sql['tableAliasMap'] as $tableAlias => $tableName) {
123 $statement = $this->getVisibilityConstraintStatement($query->getQuerySettings(), $tableName, $tableAlias);
124 if ($statement !== '') {
125 $statement = $this->addNullConditionToStatementIfRequired($sql, $statement, $tableAlias);
126 $sql['additionalWhereClause'][] = $statement;
127 }
128 }
129
130 return $sql;
131 }
132
133 /**
134 * If the given table alias is used in a UNION statement it is required to
135 * add an additional condition that allows the fields of the joined table
136 * to be NULL. Otherwise the condition would be too strict and filter out
137 * records that are actually valid.
138 *
139 * @param array $sql The current SQL query parts.
140 * @param string $statement The SQL statement to which the NULL condition should be added.
141 * @param string $tableAlias The table alias used in the SQL statement.
142 * @return string The statement including the NULL condition or the original statement.
143 */
144 protected function addNullConditionToStatementIfRequired(array $sql, $statement, $tableAlias)
145 {
146 if (isset($sql['unions'][$tableAlias])) {
147 $statement = '((' . $statement . ') OR ' . $tableAlias . '.uid' . ' IS NULL)';
148 }
149
150 return $statement;
151 }
152
153 /**
154 * Transforms a Query Source into SQL and parameter arrays
155 *
156 * @param Qom\SourceInterface $source The source
157 * @param array &$sql
158 * @return void
159 */
160 protected function parseSource(Qom\SourceInterface $source, array &$sql)
161 {
162 if ($source instanceof Qom\SelectorInterface) {
163 $className = $source->getNodeTypeName();
164 $tableName = $this->dataMapper->getDataMap($className)->getTableName();
165 $this->addRecordTypeConstraint($className, $sql);
166 $tableName = $this->getUniqueAlias($sql, $tableName);
167 $sql['fields'][$tableName] = $tableName . '.*';
168 $sql['tables'][$tableName] = $tableName;
169 } elseif ($source instanceof Qom\JoinInterface) {
170 $this->parseJoin($source, $sql);
171 }
172 }
173
174 /**
175 * Transforms a constraint into SQL and parameter arrays
176 *
177 * @param Qom\ConstraintInterface $constraint The constraint
178 * @param Qom\SourceInterface $source The source
179 * @param array &$sql The query parts
180 * @return void
181 */
182 protected function parseConstraint(Qom\ConstraintInterface $constraint = null, Qom\SourceInterface $source, array &$sql)
183 {
184 if ($constraint instanceof Qom\AndInterface) {
185 $sql['where'][] = '(';
186 $this->parseConstraint($constraint->getConstraint1(), $source, $sql);
187 $sql['where'][] = ' AND ';
188 $this->parseConstraint($constraint->getConstraint2(), $source, $sql);
189 $sql['where'][] = ')';
190 } elseif ($constraint instanceof Qom\OrInterface) {
191 $sql['where'][] = '(';
192 $this->parseConstraint($constraint->getConstraint1(), $source, $sql);
193 $sql['where'][] = ' OR ';
194 $this->parseConstraint($constraint->getConstraint2(), $source, $sql);
195 $sql['where'][] = ')';
196 } elseif ($constraint instanceof Qom\NotInterface) {
197 $sql['where'][] = 'NOT (';
198 $this->parseConstraint($constraint->getConstraint(), $source, $sql);
199 $sql['where'][] = ')';
200 } elseif ($constraint instanceof Qom\ComparisonInterface) {
201 $this->parseComparison($constraint, $source, $sql);
202 }
203 }
204
205 /**
206 * Transforms orderings into SQL.
207 *
208 * @param array $orderings An array of orderings (Qom\Ordering)
209 * @param Qom\SourceInterface $source The source
210 * @param array &$sql The query parts
211 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnsupportedOrderException
212 * @return void
213 */
214 protected function parseOrderings(array $orderings, Qom\SourceInterface $source, array &$sql)
215 {
216 foreach ($orderings as $propertyName => $order) {
217 switch ($order) {
218 case QueryInterface::ORDER_ASCENDING:
219 $order = 'ASC';
220 break;
221 case QueryInterface::ORDER_DESCENDING:
222 $order = 'DESC';
223 break;
224 default:
225 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnsupportedOrderException('Unsupported order encountered.', 1242816074);
226 }
227 $className = '';
228 $tableName = '';
229 if ($source instanceof Qom\SelectorInterface) {
230 $className = $source->getNodeTypeName();
231 $tableName = $this->dataMapper->convertClassNameToTableName($className);
232 $fullPropertyPath = '';
233 while (strpos($propertyName, '.') !== false) {
234 $this->addUnionStatement($className, $tableName, $propertyName, $sql, $fullPropertyPath);
235 }
236 } elseif ($source instanceof Qom\JoinInterface) {
237 $tableName = $source->getLeft()->getSelectorName();
238 }
239 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
240 if ($tableName !== '') {
241 $sql['orderings'][] = $tableName . '.' . $columnName . ' ' . $order;
242 } else {
243 $sql['orderings'][] = $columnName . ' ' . $order;
244 }
245 }
246 }
247
248 /**
249 * Parse a Comparison into SQL and parameter arrays.
250 *
251 * @param Qom\ComparisonInterface $comparison The comparison to parse
252 * @param Qom\SourceInterface $source The source
253 * @param array &$sql SQL query parts to add to
254 * @throws \RuntimeException
255 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\RepositoryException
256 * @return void
257 */
258 protected function parseComparison(Qom\ComparisonInterface $comparison, Qom\SourceInterface $source, array &$sql)
259 {
260 $operator = $comparison->getOperator();
261 $operand2 = $comparison->getOperand2();
262 if ($operator === QueryInterface::OPERATOR_IN) {
263 $hasValue = false;
264 foreach ($operand2 as $value) {
265 if ($this->dataMapper->getPlainValue($value) !== null) {
266 $hasValue = true;
267 break;
268 }
269 }
270 if ($hasValue === false) {
271 $sql['where'][] = '1<>1';
272 } else {
273 $this->parseDynamicOperand($comparison, $source, $sql);
274 }
275 } elseif ($operator === QueryInterface::OPERATOR_CONTAINS) {
276 if ($operand2 === null) {
277 $sql['where'][] = '1<>1';
278 } else {
279 $value = $this->dataMapper->getPlainValue($operand2);
280 if (!$source instanceof Qom\SelectorInterface) {
281 throw new \RuntimeException('Source is not of type "SelectorInterface"', 1395362539);
282 }
283 $className = $source->getNodeTypeName();
284 $tableName = $this->dataMapper->convertClassNameToTableName($className);
285 $operand1 = $comparison->getOperand1();
286 $propertyName = $operand1->getPropertyName();
287 $fullPropertyPath = '';
288 while (strpos($propertyName, '.') !== false) {
289 $this->addUnionStatement($className, $tableName, $propertyName, $sql, $fullPropertyPath);
290 }
291 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
292 $dataMap = $this->dataMapper->getDataMap($className);
293 $columnMap = $dataMap->getColumnMap($propertyName);
294 $typeOfRelation = $columnMap instanceof ColumnMap ? $columnMap->getTypeOfRelation() : null;
295 if ($typeOfRelation === ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY) {
296 $relationTableName = $columnMap->getRelationTableName();
297 $additionalWhereForMatchFields = $this->getAdditionalMatchFieldsStatement($columnMap, $relationTableName, $relationTableName);
298 $sql['where'][] = $tableName . '.uid IN (SELECT ' . $columnMap->getParentKeyFieldName() . ' FROM ' . $relationTableName . ' WHERE ' . $columnMap->getChildKeyFieldName() . '=' . $this->databaseHandle->fullQuoteStr($value, $relationTableName) . $additionalWhereForMatchFields . ')';
299 } elseif ($typeOfRelation === ColumnMap::RELATION_HAS_MANY) {
300 $parentKeyFieldName = $columnMap->getParentKeyFieldName();
301 if (isset($parentKeyFieldName)) {
302 $childTableName = $columnMap->getChildTableName();
303 $sql['where'][] = $tableName . '.uid=(SELECT ' . $childTableName . '.' . $parentKeyFieldName . ' FROM ' . $childTableName . ' WHERE ' . $childTableName . '.uid=' . $this->databaseHandle->fullQuoteStr($value, $childTableName) . ')';
304 } else {
305 $sql['where'][] = 'FIND_IN_SET(' . $this->databaseHandle->fullQuoteStr($value, $tableName) . ', ' . $tableName . '.' . $columnName . ')';
306 }
307 } else {
308 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\RepositoryException('Unsupported or non-existing property name "' . $propertyName . '" used in relation matching.', 1327065745);
309 }
310 }
311 } else {
312 $this->parseDynamicOperand($comparison, $source, $sql);
313 }
314 }
315
316 /**
317 * Parse a DynamicOperand into SQL and parameter arrays.
318 *
319 * @param Qom\ComparisonInterface $comparison
320 * @param Qom\SourceInterface $source The source
321 * @param array &$sql The query parts
322 * @return void
323 */
324 protected function parseDynamicOperand(Qom\ComparisonInterface $comparison, Qom\SourceInterface $source, array &$sql)
325 {
326 // workaround to find a suitable tablename
327 $tableName = reset($sql['tables']) ?: 'foo';
328 $operator = $this->resolveOperator($comparison->getOperator());
329 $operand = $comparison->getOperand1();
330 $operand2 = $comparison->getOperand2();
331
332 $constraintSQL = $this->parseOperand($operand, $source, $sql) . ' ' . $operator . ' ';
333 if ($comparison->getOperator() === QueryInterface::OPERATOR_IN) {
334 $constraintSQL .= '(';
335 $values = [];
336 foreach ($operand2 as $value) {
337 $values[] = $this->databaseHandle->fullQuoteStr($this->dataMapper->getPlainValue($value), $tableName);
338 }
339 $constraintSQL .= implode(',', $values);
340 $constraintSQL .= ')';
341 } else {
342 if ($operand2 === null) {
343 $constraintSQL .= $this->dataMapper->getPlainValue($operand2);
344 } else {
345 $constraintSQL .= $this->databaseHandle->fullQuoteStr($this->dataMapper->getPlainValue($operand2), $tableName);
346 }
347 }
348
349 $sql['where'][] = $constraintSQL;
350 }
351
352 /**
353 * @param Qom\DynamicOperandInterface $operand
354 * @param Qom\SourceInterface $source The source
355 * @param array &$sql The query parts
356 * @return string
357 * @throws \InvalidArgumentException
358 */
359 protected function parseOperand(Qom\DynamicOperandInterface $operand, Qom\SourceInterface $source, array &$sql)
360 {
361 if ($operand instanceof Qom\LowerCaseInterface) {
362 $constraintSQL = 'LOWER(' . $this->parseOperand($operand->getOperand(), $source, $sql) . ')';
363 } elseif ($operand instanceof Qom\UpperCaseInterface) {
364 $constraintSQL = 'UPPER(' . $this->parseOperand($operand->getOperand(), $source, $sql) . ')';
365 } elseif ($operand instanceof Qom\PropertyValueInterface) {
366 $propertyName = $operand->getPropertyName();
367 $className = '';
368 if ($source instanceof Qom\SelectorInterface) {
369 // @todo Only necessary to differ from Join
370 $className = $source->getNodeTypeName();
371 $tableName = $this->dataMapper->convertClassNameToTableName($className);
372 $fullPropertyPath = '';
373 while (strpos($propertyName, '.') !== false) {
374 $this->addUnionStatement($className, $tableName, $propertyName, $sql, $fullPropertyPath);
375 }
376 } elseif ($source instanceof Qom\JoinInterface) {
377 $tableName = $source->getJoinCondition()->getSelector1Name();
378 }
379 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
380 $constraintSQL = (!empty($tableName) ? $tableName . '.' : '') . $columnName;
381 } else {
382 throw new \InvalidArgumentException('Given operand has invalid type "' . get_class($operand) . '".', 1395710211);
383 }
384 return $constraintSQL;
385 }
386
387 /**
388 * Add a constraint to ensure that the record type of the returned tuples is matching the data type of the repository.
389 *
390 * @param string $className The class name
391 * @param array &$sql The query parts
392 * @return void
393 */
394 protected function addRecordTypeConstraint($className, &$sql)
395 {
396 if ($className !== null) {
397 $dataMap = $this->dataMapper->getDataMap($className);
398 if ($dataMap->getRecordTypeColumnName() !== null) {
399 $recordTypes = array();
400 if ($dataMap->getRecordType() !== null) {
401 $recordTypes[] = $dataMap->getRecordType();
402 }
403 foreach ($dataMap->getSubclasses() as $subclassName) {
404 $subclassDataMap = $this->dataMapper->getDataMap($subclassName);
405 if ($subclassDataMap->getRecordType() !== null) {
406 $recordTypes[] = $subclassDataMap->getRecordType();
407 }
408 }
409 if (!empty($recordTypes)) {
410 $recordTypeStatements = array();
411 foreach ($recordTypes as $recordType) {
412 $tableName = $dataMap->getTableName();
413 $recordTypeStatements[] = $tableName . '.' . $dataMap->getRecordTypeColumnName() . '=' . $this->databaseHandle->fullQuoteStr($recordType, $tableName);
414 }
415 $sql['additionalWhereClause'][] = '(' . implode(' OR ', $recordTypeStatements) . ')';
416 }
417 }
418 }
419 }
420
421 /**
422 * Builds a condition for filtering records by the configured match field,
423 * e.g. MM_match_fields, foreign_match_fields or foreign_table_field.
424 *
425 * @param ColumnMap $columnMap The column man for which the condition should be build.
426 * @param string $childTableName The real name of the child record table.
427 * @param string $childTableAlias The alias of the child record table used in the query.
428 * @param string $parentTable The real name of the parent table (used for building the foreign_table_field condition).
429 * @return string The match field conditions or an empty string.
430 */
431 protected function getAdditionalMatchFieldsStatement($columnMap, $childTableName, $childTableAlias, $parentTable = null)
432 {
433 $additionalWhereForMatchFields = '';
434
435 $relationTableMatchFields = $columnMap->getRelationTableMatchFields();
436 if (is_array($relationTableMatchFields) && !empty($relationTableMatchFields)) {
437 $additionalWhere = array();
438 foreach ($relationTableMatchFields as $fieldName => $value) {
439 $additionalWhere[] = $childTableAlias . '.' . $fieldName . ' = ' . $this->databaseHandle->fullQuoteStr($value, $childTableName);
440 }
441 $additionalWhereForMatchFields .= ' AND ' . implode(' AND ', $additionalWhere);
442 }
443
444 if (isset($parentTable)) {
445 $parentTableFieldName = $columnMap->getParentTableFieldName();
446 if (isset($parentTableFieldName) && $parentTableFieldName !== '') {
447 $additionalWhereForMatchFields .= ' AND ' . $childTableAlias . '.' . $parentTableFieldName . ' = ' . $this->databaseHandle->fullQuoteStr($parentTable, $childTableAlias);
448 }
449 }
450
451 return $additionalWhereForMatchFields;
452 }
453
454 /**
455 * Adds additional WHERE statements according to the query settings.
456 *
457 * @param QuerySettingsInterface $querySettings The TYPO3 CMS specific query settings
458 * @param string $tableName The table name to add the additional where clause for
459 * @param string $tableAlias The table alias used in the query.
460 * @return string
461 */
462 protected function getAdditionalWhereClause(QuerySettingsInterface $querySettings, $tableName, $tableAlias = null)
463 {
464 $sysLanguageStatement = '';
465 if ($querySettings->getRespectSysLanguage()) {
466 $sysLanguageStatement = $this->getSysLanguageStatement($tableName, $tableAlias, $querySettings);
467 }
468
469 $pageIdStatement = '';
470 if ($querySettings->getRespectStoragePage()) {
471 $pageIdStatement = $this->getPageIdStatement($tableName, $tableAlias, $querySettings->getStoragePageIds());
472 }
473
474 if ($sysLanguageStatement !== '' && $pageIdStatement !== '') {
475 $whereClause = $sysLanguageStatement . ' AND ' . $pageIdStatement;
476 } elseif ($sysLanguageStatement !== '') {
477 $whereClause = $sysLanguageStatement;
478 } else {
479 $whereClause = $pageIdStatement;
480 }
481
482 return $whereClause;
483 }
484
485 /**
486 * Adds enableFields and deletedClause to the query if necessary
487 *
488 * @param QuerySettingsInterface $querySettings
489 * @param string $tableName The database table name
490 * @param string $tableAlias
491 * @return string
492 */
493 protected function getVisibilityConstraintStatement(QuerySettingsInterface $querySettings, $tableName, $tableAlias)
494 {
495 $statement = '';
496 if (is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
497 $ignoreEnableFields = $querySettings->getIgnoreEnableFields();
498 $enableFieldsToBeIgnored = $querySettings->getEnableFieldsToBeIgnored();
499 $includeDeleted = $querySettings->getIncludeDeleted();
500 if ($this->environmentService->isEnvironmentInFrontendMode()) {
501 $statement .= $this->getFrontendConstraintStatement($tableName, $ignoreEnableFields, $enableFieldsToBeIgnored, $includeDeleted);
502 } else {
503 // TYPO3_MODE === 'BE'
504 $statement .= $this->getBackendConstraintStatement($tableName, $ignoreEnableFields, $includeDeleted);
505 }
506 if (!empty($statement)) {
507 $statement = $this->replaceTableNameWithAlias($statement, $tableName, $tableAlias);
508 $statement = strtolower(substr($statement, 1, 3)) === 'and' ? substr($statement, 5) : $statement;
509 }
510 }
511 return $statement;
512 }
513
514 /**
515 * Returns constraint statement for frontend context
516 *
517 * @param string $tableName
518 * @param bool $ignoreEnableFields A flag indicating whether the enable fields should be ignored
519 * @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.
520 * @param bool $includeDeleted A flag indicating whether deleted records should be included
521 * @return string
522 * @throws InconsistentQuerySettingsException
523 */
524 protected function getFrontendConstraintStatement($tableName, $ignoreEnableFields, array $enableFieldsToBeIgnored = array(), $includeDeleted)
525 {
526 $statement = '';
527 if ($ignoreEnableFields && !$includeDeleted) {
528 if (!empty($enableFieldsToBeIgnored)) {
529 // array_combine() is necessary because of the way \TYPO3\CMS\Frontend\Page\PageRepository::enableFields() is implemented
530 $statement .= $this->getPageRepository()->enableFields($tableName, -1, array_combine($enableFieldsToBeIgnored, $enableFieldsToBeIgnored));
531 } else {
532 $statement .= $this->getPageRepository()->deleteClause($tableName);
533 }
534 } elseif (!$ignoreEnableFields && !$includeDeleted) {
535 $statement .= $this->getPageRepository()->enableFields($tableName);
536 } elseif (!$ignoreEnableFields && $includeDeleted) {
537 throw new InconsistentQuerySettingsException('Query setting "ignoreEnableFields=FALSE" can not be used together with "includeDeleted=TRUE" in frontend context.', 1460975922);
538 }
539 return $statement;
540 }
541
542 /**
543 * Returns constraint statement for backend context
544 *
545 * @param string $tableName
546 * @param bool $ignoreEnableFields A flag indicating whether the enable fields should be ignored
547 * @param bool $includeDeleted A flag indicating whether deleted records should be included
548 * @return string
549 */
550 protected function getBackendConstraintStatement($tableName, $ignoreEnableFields, $includeDeleted)
551 {
552 $statement = '';
553 if (!$ignoreEnableFields) {
554 $statement .= BackendUtility::BEenableFields($tableName);
555 }
556 if (!$includeDeleted) {
557 $statement .= BackendUtility::deleteClause($tableName);
558 }
559 return $statement;
560 }
561
562 /**
563 * Builds the language field statement
564 *
565 * @param string $tableName The database table name
566 * @param string $tableAlias The table alias used in the query.
567 * @param QuerySettingsInterface $querySettings The TYPO3 CMS specific query settings
568 * @return string
569 */
570 protected function getSysLanguageStatement($tableName, $tableAlias, $querySettings)
571 {
572 $sysLanguageStatement = '';
573 if (is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
574 if (!empty($GLOBALS['TCA'][$tableName]['ctrl']['languageField'])) {
575 // Select all entries for the current language
576 $additionalWhereClause = $tableAlias . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . ' IN (' . (int)$querySettings->getLanguageUid() . ',-1)';
577 // If any language is set -> get those entries which are not translated yet
578 // They will be removed by \TYPO3\CMS\Frontend\Page\PageRepository::getRecordOverlay if not matching overlay mode
579 if (isset($GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'])
580 && $querySettings->getLanguageUid() > 0
581 ) {
582 $mode = $querySettings->getLanguageMode();
583 if ($mode === 'strict') {
584 $additionalWhereClause = $tableAlias . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=-1' .
585 ' OR (' . $tableAlias . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . ' = ' . (int)$querySettings->getLanguageUid() .
586 ' AND ' . $tableAlias . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '=0' .
587 ') OR (' . $tableAlias . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=0' .
588 ' AND ' . $tableAlias . '.uid IN (SELECT ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] .
589 ' FROM ' . $tableName .
590 ' WHERE ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '>0' .
591 ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=' . (int)$querySettings->getLanguageUid();
592 } else {
593 $additionalWhereClause .= ' OR (' . $tableAlias . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=0' .
594 ' AND ' . $tableAlias . '.uid NOT IN (SELECT ' . $tableAlias . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] .
595 ' FROM ' . $tableName .
596 ' WHERE ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '>0' .
597 ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=' . (int)$querySettings->getLanguageUid();
598 }
599
600 // Add delete clause to ensure all entries are loaded
601 if (isset($GLOBALS['TCA'][$tableName]['ctrl']['delete'])) {
602 $additionalWhereClause .= ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['delete'] . '=0';
603 }
604 $additionalWhereClause .= '))';
605 }
606 $sysLanguageStatement = '(' . $additionalWhereClause . ')';
607 }
608 }
609 return $sysLanguageStatement;
610 }
611
612 /**
613 * Builds the page ID checking statement
614 *
615 * @param string $tableName The database table name
616 * @param string $tableAlias The table alias used in the query.
617 * @param array $storagePageIds list of storage page ids
618 * @throws InconsistentQuerySettingsException
619 * @return string
620 */
621 protected function getPageIdStatement($tableName, $tableAlias, array $storagePageIds)
622 {
623 if (!is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
624 return '';
625 }
626
627 $rootLevel = (int)$GLOBALS['TCA'][$tableName]['ctrl']['rootLevel'];
628 switch ($rootLevel) {
629 // Only in pid 0
630 case 1:
631 return $tableAlias . '.pid = 0';
632 // Pid 0 and pagetree
633 case -1:
634 if (empty($storagePageIds)) {
635 return $tableAlias . '.pid = 0';
636 }
637 $storagePageIds[] = 0;
638 break;
639 // Only pagetree or not set
640 case 0:
641 if (empty($storagePageIds)) {
642 throw new InconsistentQuerySettingsException('Missing storage page ids.', 1365779762);
643 }
644 break;
645 // Invalid configuration
646 default:
647 return '';
648 }
649 $storagePageIds = array_map('intval', $storagePageIds);
650
651 return $tableAlias . '.pid IN (' . implode(',', $storagePageIds) . ')';
652 }
653
654 /**
655 * Transforms a Join into SQL and parameter arrays
656 *
657 * @param Qom\JoinInterface $join The join
658 * @param array &$sql The query parts
659 * @return void
660 */
661 protected function parseJoin(Qom\JoinInterface $join, array &$sql)
662 {
663 $leftSource = $join->getLeft();
664 $leftClassName = $leftSource->getNodeTypeName();
665 $leftTableName = $leftSource->getSelectorName();
666 $this->addRecordTypeConstraint($leftClassName, $sql);
667 $rightSource = $join->getRight();
668 if ($rightSource instanceof Qom\JoinInterface) {
669 $left = $rightSource->getLeft();
670 $rightClassName = $left->getNodeTypeName();
671 $rightTableName = $left->getSelectorName();
672 } else {
673 $rightClassName = $rightSource->getNodeTypeName();
674 $rightTableName = $rightSource->getSelectorName();
675 $sql['fields'][$leftTableName] = $rightTableName . '.*';
676 }
677 $this->addRecordTypeConstraint($rightClassName, $sql);
678 $leftTableName = $this->getUniqueAlias($sql, $leftTableName);
679 $sql['tables'][$leftTableName] = $leftTableName;
680 $rightTableName = $this->getUniqueAlias($sql, $rightTableName);
681 $sql['unions'][$rightTableName] = 'LEFT JOIN ' . $rightTableName;
682 $joinCondition = $join->getJoinCondition();
683 if ($joinCondition instanceof Qom\EquiJoinCondition) {
684 $column1Name = $this->dataMapper->convertPropertyNameToColumnName($joinCondition->getProperty1Name(), $leftClassName);
685 $column2Name = $this->dataMapper->convertPropertyNameToColumnName($joinCondition->getProperty2Name(), $rightClassName);
686 $sql['unions'][$rightTableName] .= ' ON ' . $leftTableName . '.' . $column1Name . ' = ' . $rightTableName . '.' . $column2Name;
687 }
688 if ($rightSource instanceof Qom\JoinInterface) {
689 $this->parseJoin($rightSource, $sql);
690 }
691 }
692
693 /**
694 * Generates a unique alias for the given table and the given property path.
695 * The property path will be mapped to the generated alias in the tablePropertyMap.
696 *
697 * @param array $sql The SQL satement parts, will be filled with the tableAliasMap.
698 * @param string $tableName The name of the table for which the alias should be generated.
699 * @param string $fullPropertyPath The full property path that is related to the given table.
700 * @return string The generated table alias.
701 */
702 protected function getUniqueAlias(array &$sql, $tableName, $fullPropertyPath = null)
703 {
704 if (isset($fullPropertyPath) && isset($this->tablePropertyMap[$fullPropertyPath])) {
705 return $this->tablePropertyMap[$fullPropertyPath];
706 }
707
708 $alias = $tableName;
709 $i = 0;
710 while (isset($sql['tableAliasMap'][$alias])) {
711 $alias = $tableName . $i;
712 $i++;
713 }
714
715 $sql['tableAliasMap'][$alias] = $tableName;
716
717 if (isset($fullPropertyPath)) {
718 $this->tablePropertyMap[$fullPropertyPath] = $alias;
719 }
720
721 return $alias;
722 }
723
724 /**
725 * adds a union statement to the query, mostly for tables referenced in the where condition.
726 * The property for which the union statement is generated will be appended.
727 *
728 * @param string &$className The name of the parent class, will be set to the child class after processing.
729 * @param string &$tableName The name of the parent table, will be set to the table alias that is used in the union statement.
730 * @param array &$propertyPath The remaining property path, will be cut of by one part during the process.
731 * @param array &$sql The SQL statement parts, will be filled with the union statements.
732 * @param string $fullPropertyPath The full path the the current property, will be used to make table names unique.
733 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception
734 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InvalidRelationConfigurationException
735 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\MissingColumnMapException
736 */
737 protected function addUnionStatement(&$className, &$tableName, &$propertyPath, array &$sql, &$fullPropertyPath)
738 {
739 $explodedPropertyPath = explode('.', $propertyPath, 2);
740 $propertyName = $explodedPropertyPath[0];
741 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
742 $realTableName = $this->dataMapper->convertClassNameToTableName($className);
743 $tableName = isset($this->tablePropertyMap[$fullPropertyPath]) ? $this->tablePropertyMap[$fullPropertyPath] : $realTableName;
744 $columnMap = $this->dataMapper->getDataMap($className)->getColumnMap($propertyName);
745
746 if ($columnMap === null) {
747 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\MissingColumnMapException('The ColumnMap for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1355142232);
748 }
749
750 $parentKeyFieldName = $columnMap->getParentKeyFieldName();
751 $childTableName = $columnMap->getChildTableName();
752
753 if ($childTableName === null) {
754 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InvalidRelationConfigurationException('The relation information for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1353170925);
755 }
756
757 $fullPropertyPath .= ($fullPropertyPath === '') ? $propertyName : '.' . $propertyName;
758 $childTableAlias = $this->getUniqueAlias($sql, $childTableName, $fullPropertyPath);
759
760 // If there is already exists a union with the current identifier we do not need to build it again and exit early.
761 if (isset($sql['unions'][$childTableAlias])) {
762 $propertyPath = $explodedPropertyPath[1];
763 $tableName = $childTableAlias;
764 $className = $this->dataMapper->getType($className, $propertyName);
765 return;
766 }
767
768 if ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_ONE) {
769 if (isset($parentKeyFieldName)) {
770 $sql['unions'][$childTableAlias] = 'LEFT JOIN ' . $childTableName . ' AS ' . $childTableAlias . ' ON ' . $tableName . '.uid=' . $childTableAlias . '.' . $parentKeyFieldName;
771 } else {
772 $sql['unions'][$childTableAlias] = 'LEFT JOIN ' . $childTableName . ' AS ' . $childTableAlias . ' ON ' . $tableName . '.' . $columnName . '=' . $childTableAlias . '.uid';
773 }
774 $sql['unions'][$childTableAlias] .= $this->getAdditionalMatchFieldsStatement($columnMap, $childTableName, $childTableAlias, $realTableName);
775 } elseif ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_MANY) {
776 if (isset($parentKeyFieldName)) {
777 $sql['unions'][$childTableAlias] = 'LEFT JOIN ' . $childTableName . ' AS ' . $childTableAlias . ' ON ' . $tableName . '.uid=' . $childTableAlias . '.' . $parentKeyFieldName;
778 } else {
779 $onStatement = '(FIND_IN_SET(' . $childTableAlias . '.uid, ' . $tableName . '.' . $columnName . '))';
780 $sql['unions'][$childTableAlias] = 'LEFT JOIN ' . $childTableName . ' AS ' . $childTableAlias . ' ON ' . $onStatement;
781 }
782 $sql['unions'][$childTableAlias] .= $this->getAdditionalMatchFieldsStatement($columnMap, $childTableName, $childTableAlias, $realTableName);
783 } elseif ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY) {
784 $relationTableName = $columnMap->getRelationTableName();
785 $relationTableAlias = $relationTableAlias = $this->getUniqueAlias($sql, $relationTableName, $fullPropertyPath . '_mm');
786 $sql['unions'][$relationTableAlias] = 'LEFT JOIN ' . $relationTableName . ' AS ' . $relationTableAlias . ' ON ' . $tableName . '.uid=' . $relationTableAlias . '.' . $columnMap->getParentKeyFieldName();
787 $sql['unions'][$childTableAlias] = 'LEFT JOIN ' . $childTableName . ' AS ' . $childTableAlias . ' ON ' . $relationTableAlias . '.' . $columnMap->getChildKeyFieldName() . '=' . $childTableAlias . '.uid';
788 $sql['unions'][$childTableAlias] .= $this->getAdditionalMatchFieldsStatement($columnMap, $relationTableName, $relationTableAlias, $realTableName);
789 } else {
790 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception('Could not determine type of relation.', 1252502725);
791 }
792 // @todo check if there is another solution for this
793 $sql['keywords']['distinct'] = 'DISTINCT';
794 $propertyPath = $explodedPropertyPath[1];
795 $tableName = $childTableAlias;
796 $className = $this->dataMapper->getType($className, $propertyName);
797 }
798
799 /**
800 * If the table name does not match the table alias all occurrences of
801 * "tableName." are replaced with "tableAlias." in the given SQL statement.
802 *
803 * @param string $statement The SQL statement in which the values are replaced.
804 * @param string $tableName The table name that is replaced.
805 * @param string $tableAlias The table alias that replaced the table name.
806 * @return string The modified SQL statement.
807 */
808 protected function replaceTableNameWithAlias($statement, $tableName, $tableAlias)
809 {
810 if ($tableAlias !== $tableName) {
811 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($tableName);
812 $quotedTableName = $connection->quoteIdentifier($tableName);
813 $quotedTableAliase = $connection->quoteIdentifier($tableAlias);
814 $statement = str_replace(
815 [$tableName . '.', $quotedTableName . '.'],
816 [$tableAlias . '.', $quotedTableAliase . '.'],
817 $statement
818 );
819 }
820
821 return $statement;
822 }
823
824 /**
825 * Returns the SQL operator for the given JCR operator type.
826 *
827 * @param string $operator One of the JCR_OPERATOR_* constants
828 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception
829 * @return string an SQL operator
830 */
831 protected function resolveOperator($operator)
832 {
833 switch ($operator) {
834 case QueryInterface::OPERATOR_IN:
835 $operator = 'IN';
836 break;
837 case QueryInterface::OPERATOR_EQUAL_TO:
838 $operator = '=';
839 break;
840 case QueryInterface::OPERATOR_EQUAL_TO_NULL:
841 $operator = 'IS';
842 break;
843 case QueryInterface::OPERATOR_NOT_EQUAL_TO:
844 $operator = '!=';
845 break;
846 case QueryInterface::OPERATOR_NOT_EQUAL_TO_NULL:
847 $operator = 'IS NOT';
848 break;
849 case QueryInterface::OPERATOR_LESS_THAN:
850 $operator = '<';
851 break;
852 case QueryInterface::OPERATOR_LESS_THAN_OR_EQUAL_TO:
853 $operator = '<=';
854 break;
855 case QueryInterface::OPERATOR_GREATER_THAN:
856 $operator = '>';
857 break;
858 case QueryInterface::OPERATOR_GREATER_THAN_OR_EQUAL_TO:
859 $operator = '>=';
860 break;
861 case QueryInterface::OPERATOR_LIKE:
862 $operator = 'LIKE';
863 break;
864 default:
865 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception('Unsupported operator encountered.', 1242816073);
866 }
867 return $operator;
868 }
869
870 /**
871 * @return \TYPO3\CMS\Frontend\Page\PageRepository
872 */
873 protected function getPageRepository()
874 {
875 if (!$this->pageRepository instanceof \TYPO3\CMS\Frontend\Page\PageRepository) {
876 if ($this->environmentService->isEnvironmentInFrontendMode() && is_object($GLOBALS['TSFE'])) {
877 $this->pageRepository = $GLOBALS['TSFE']->sys_page;
878 } else {
879 $this->pageRepository = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance(\TYPO3\CMS\Frontend\Page\PageRepository::class);
880 }
881 }
882
883 return $this->pageRepository;
884 }
885 }