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