[BUGFIX] FOLLOWUP Queries with NULL don't work
[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 * Copyright notice
6 *
7 * (c) 2014 Extbase Team (http://forge.typo3.org/projects/typo3v4-mvc)
8 * Extbase is a backport of TYPO3 Flow. All credits go to the TYPO3 Flow team.
9 * All rights reserved
10 *
11 * This script is part of the TYPO3 project. The TYPO3 project is
12 * free software; you can redistribute it and/or modify
13 * it under the terms of the GNU General Public License as published by
14 * the Free Software Foundation; either version 2 of the License, or
15 * (at your option) any later version.
16 *
17 * The GNU General Public License can be found at
18 * http://www.gnu.org/copyleft/gpl.html.
19 * A copy is found in the text file GPL.txt and important notices to the license
20 * from the author is found in LICENSE.txt distributed with these scripts.
21 *
22 *
23 * This script is distributed in the hope that it will be useful,
24 * but WITHOUT ANY WARRANTY; without even the implied warranty of
25 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
26 * GNU General Public License for more details.
27 *
28 * This copyright notice MUST APPEAR in all copies of the script!
29 ***************************************************************/
30
31 use TYPO3\CMS\Backend\Utility\BackendUtility;
32 use TYPO3\CMS\Extbase\Utility\TypeHandlingUtility;
33
34 /**
35 * QueryParser, converting the qom to string representation
36 */
37 class Typo3DbQueryParser {
38
39 /**
40 * The TYPO3 database object
41 *
42 * @var \TYPO3\CMS\Core\Database\DatabaseConnection
43 */
44 protected $databaseHandle;
45
46 /**
47 * @var \TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper
48 * @inject
49 */
50 protected $dataMapper;
51
52 /**
53 * The TYPO3 page repository. Used for language and workspace overlay
54 *
55 * @var \TYPO3\CMS\Frontend\Page\PageRepository
56 */
57 protected $pageRepository;
58
59 /**
60 * @var \TYPO3\CMS\Core\Cache\CacheManager
61 * @inject
62 */
63 protected $cacheManager;
64
65 /**
66 * @var \TYPO3\CMS\Core\Cache\Frontend\VariableFrontend
67 */
68 protected $tableColumnCache;
69
70 /**
71 * @var \TYPO3\CMS\Extbase\Service\EnvironmentService
72 * @inject
73 */
74 protected $environmentService;
75
76 /**
77 * Constructor. takes the database handle from $GLOBALS['TYPO3_DB']
78 */
79 public function __construct() {
80 $this->databaseHandle = $GLOBALS['TYPO3_DB'];
81 }
82
83 /**
84 * Lifecycle method
85 *
86 * @return void
87 */
88 public function initializeObject() {
89 $this->tableColumnCache = $this->cacheManager->getCache('extbase_typo3dbbackend_tablecolumns');
90 }
91
92 /**
93 * Preparses the query and returns the query's hash and the parameters
94 *
95 * @param \TYPO3\CMS\Extbase\Persistence\QueryInterface $query The query
96 * @return array the hash and the parameters
97 */
98 public function preparseQuery(\TYPO3\CMS\Extbase\Persistence\QueryInterface $query) {
99 list($parameters, $operators) = $this->preparseComparison($query->getConstraint());
100 $hashPartials = array(
101 $query->getQuerySettings(),
102 $query->getSource(),
103 array_keys($parameters),
104 $operators,
105 $query->getOrderings(),
106 );
107 $hash = md5(serialize($hashPartials));
108
109 return array($hash, $parameters);
110 }
111
112 /**
113 * Walks through the qom's constraints and extracts the properties and values.
114 *
115 * In the qom the query structure and values are glued together. This walks through the
116 * qom and only extracts the parts necessary for generating the hash and filling the
117 * statement. It leaves out the actual statement generation, as it is the most time
118 * consuming.
119 *
120 * @param object $comparison The constraint. Could be And-, Or-, Not- or ComparisonInterface
121 * @param string $qomPath current position of the child in the qom
122 * @return array Array of parameters and operators
123 * @throws \Exception
124 */
125 protected function preparseComparison($comparison, $qomPath = '') {
126 $parameters = array();
127 $operators = array();
128 $objectsToParse = array();
129
130 if ($comparison instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\AndInterface) {
131 $delimiter = 'AND';
132 $objectsToParse = array($comparison->getConstraint1(), $comparison->getConstraint2());
133 } elseif ($comparison instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\OrInterface) {
134 $delimiter = 'OR';
135 $objectsToParse = array($comparison->getConstraint1(), $comparison->getConstraint2());
136 } elseif ($comparison instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\NotInterface) {
137 $delimiter = 'NOT';
138 $objectsToParse = array($comparison->getConstraint());
139 } elseif ($comparison instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\ComparisonInterface) {
140 $parameterIdentifier = $this->normalizeParameterIdentifier($qomPath . $comparison->getOperand1()->getPropertyName());
141 $comparison->setParameterIdentifier($parameterIdentifier);
142 $operator = $comparison->getOperator();
143 $operand2 = $comparison->getOperand2();
144 if ($operator === \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_IN) {
145 $items = array();
146 foreach ($operand2 as $value) {
147 $value = $this->getPlainValue($value);
148 if ($value !== NULL) {
149 $items[] = $value;
150 }
151 }
152 $parameters[$parameterIdentifier] = $items;
153 } else {
154 $parameters[$parameterIdentifier] = $operand2;
155 }
156 $operators[] = $operator;
157 } elseif (!is_object($comparison)) {
158 $parameters = array(array(), $comparison);
159 return array($parameters, $operators);
160 } else {
161 throw new \Exception('Can not hash Query Component "' . get_class($comparison) . '".', 1392840462);
162 }
163
164 $childObjectIterator = 0;
165 foreach ($objectsToParse as $objectToParse) {
166 list($preparsedParameters, $preparsedOperators) = $this->preparseComparison($objectToParse, $qomPath . $delimiter . $childObjectIterator++);
167 if (!empty($preparsedParameters)) {
168 $parameters = array_merge($parameters, $preparsedParameters);
169 }
170 if (!empty($preparsedOperators)) {
171 $operators = array_merge($operators, $preparsedOperators);
172 }
173 }
174
175 return array($parameters, $operators);
176 }
177
178 /**
179 * normalizes the parameter's identifier
180 *
181 * @param string $identifier
182 * @return string
183 * @todo come on, clean up that method!
184 */
185 public function normalizeParameterIdentifier($identifier) {
186 return ':' . preg_replace('/[^A-Za-z0-9]/', '', $identifier);
187 }
188
189 /**
190 * Parses the query and returns the SQL statement parts.
191 *
192 * @param \TYPO3\CMS\Extbase\Persistence\QueryInterface $query The query
193 * @return array The SQL statement parts
194 */
195 public function parseQuery(\TYPO3\CMS\Extbase\Persistence\QueryInterface $query) {
196 $sql = array();
197 $sql['keywords'] = array();
198 $sql['tables'] = array();
199 $sql['unions'] = array();
200 $sql['fields'] = array();
201 $sql['where'] = array();
202 $sql['additionalWhereClause'] = array();
203 $sql['orderings'] = array();
204 $sql['limit'] = ((int)$query->getLimit() ?: NULL);
205 $sql['offset'] = ((int)$query->getOffset() ?: NULL);
206 $source = $query->getSource();
207 $this->parseSource($source, $sql);
208 $this->parseConstraint($query->getConstraint(), $source, $sql);
209 $this->parseOrderings($query->getOrderings(), $source, $sql);
210
211 $tableNames = array_unique(array_keys($sql['tables'] + $sql['unions']));
212 foreach ($tableNames as $tableName) {
213 if (is_string($tableName) && !empty($tableName)) {
214 $this->addAdditionalWhereClause($query->getQuerySettings(), $tableName, $sql);
215 }
216 }
217
218 return $sql;
219 }
220
221 /**
222 * Transforms a Query Source into SQL and parameter arrays
223 *
224 * @param \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source The source
225 * @param array &$sql
226 * @return void
227 */
228 protected function parseSource(\TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source, array &$sql) {
229 if ($source instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SelectorInterface) {
230 $className = $source->getNodeTypeName();
231 $tableName = $this->dataMapper->getDataMap($className)->getTableName();
232 $this->addRecordTypeConstraint($className, $sql);
233 $sql['fields'][$tableName] = $tableName . '.*';
234 $sql['tables'][$tableName] = $tableName;
235 } elseif ($source instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\JoinInterface) {
236 $this->parseJoin($source, $sql);
237 }
238 }
239
240 /**
241 * Transforms a constraint into SQL and parameter arrays
242 *
243 * @param \TYPO3\CMS\Extbase\Persistence\Generic\Qom\ConstraintInterface $constraint The constraint
244 * @param \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source The source
245 * @param array &$sql The query parts
246 * @return void
247 */
248 protected function parseConstraint(\TYPO3\CMS\Extbase\Persistence\Generic\Qom\ConstraintInterface $constraint = NULL, \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source, array &$sql) {
249 if ($constraint instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\AndInterface) {
250 $sql['where'][] = '(';
251 $this->parseConstraint($constraint->getConstraint1(), $source, $sql);
252 $sql['where'][] = ' AND ';
253 $this->parseConstraint($constraint->getConstraint2(), $source, $sql);
254 $sql['where'][] = ')';
255 } elseif ($constraint instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\OrInterface) {
256 $sql['where'][] = '(';
257 $this->parseConstraint($constraint->getConstraint1(), $source, $sql);
258 $sql['where'][] = ' OR ';
259 $this->parseConstraint($constraint->getConstraint2(), $source, $sql);
260 $sql['where'][] = ')';
261 } elseif ($constraint instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\NotInterface) {
262 $sql['where'][] = 'NOT (';
263 $this->parseConstraint($constraint->getConstraint(), $source, $sql);
264 $sql['where'][] = ')';
265 } elseif ($constraint instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\ComparisonInterface) {
266 $this->parseComparison($constraint, $source, $sql);
267 }
268 }
269
270 /**
271 * Transforms orderings into SQL.
272 *
273 * @param array $orderings An array of orderings (Tx_Extbase_Persistence_QOM_Ordering)
274 * @param \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source The source
275 * @param array &$sql The query parts
276 * @throws \RuntimeException
277 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnsupportedOrderException
278 * @return void
279 */
280 protected function parseOrderings(array $orderings, \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source, array &$sql) {
281 foreach ($orderings as $propertyName => $order) {
282 switch ($order) {
283 case \TYPO3\CMS\Extbase\Persistence\Generic\Qom\QueryObjectModelConstantsInterface::JCR_ORDER_ASCENDING:
284
285 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING:
286 $order = 'ASC';
287 break;
288 case \TYPO3\CMS\Extbase\Persistence\Generic\Qom\QueryObjectModelConstantsInterface::JCR_ORDER_DESCENDING:
289
290 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING:
291 $order = 'DESC';
292 break;
293 default:
294 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnsupportedOrderException('Unsupported order encountered.', 1242816074);
295 }
296 $className = '';
297 $tableName = '';
298 if ($source instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SelectorInterface) {
299 $className = $source->getNodeTypeName();
300 $tableName = $this->dataMapper->convertClassNameToTableName($className);
301 while (strpos($propertyName, '.') !== FALSE) {
302 $this->addUnionStatement($className, $tableName, $propertyName, $sql);
303 }
304 } elseif ($source instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\JoinInterface) {
305 $tableName = $source->getLeft()->getSelectorName();
306 }
307 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
308 if ($tableName !== '') {
309 $sql['orderings'][] = $tableName . '.' . $columnName . ' ' . $order;
310 } else {
311 $sql['orderings'][] = $columnName . ' ' . $order;
312 }
313 }
314 }
315
316 /**
317 * Parse a Comparison into SQL and parameter arrays.
318 *
319 * @param \TYPO3\CMS\Extbase\Persistence\Generic\Qom\ComparisonInterface $comparison The comparison to parse
320 * @param \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source The source
321 * @param array &$sql SQL query parts to add to
322 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\RepositoryException
323 * @return void
324 */
325 protected function parseComparison(\TYPO3\CMS\Extbase\Persistence\Generic\Qom\ComparisonInterface $comparison, \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source, array &$sql) {
326 $parameterIdentifier = $this->normalizeParameterIdentifier($comparison->getParameterIdentifier());
327 $operand1 = $comparison->getOperand1();
328 $operator = $comparison->getOperator();
329 $operand2 = $comparison->getOperand2();
330 if ($operator === \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_IN) {
331 $items = array();
332 $hasValue = FALSE;
333 foreach ($operand2 as $value) {
334 $value = $this->getPlainValue($value);
335 if ($value !== NULL) {
336 $parameters[] = $value;
337 $hasValue = TRUE;
338 }
339 }
340 if ($hasValue === FALSE) {
341 $sql['where'][] = '1<>1';
342 } else {
343 $this->parseDynamicOperand($comparison, $operator, $source, $sql, NULL);
344 }
345 } elseif ($operator === \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_CONTAINS) {
346 if ($operand2 === NULL) {
347 $sql['where'][] = '1<>1';
348 } else {
349 $className = $source->getNodeTypeName();
350 $tableName = $this->dataMapper->convertClassNameToTableName($className);
351 $propertyName = $operand1->getPropertyName();
352 while (strpos($propertyName, '.') !== FALSE) {
353 $this->addUnionStatement($className, $tableName, $propertyName, $sql);
354 }
355 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
356 $dataMap = $this->dataMapper->getDataMap($className);
357 $columnMap = $dataMap->getColumnMap($propertyName);
358 $typeOfRelation = $columnMap instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Mapper\ColumnMap ? $columnMap->getTypeOfRelation() : NULL;
359 if ($typeOfRelation === \TYPO3\CMS\Extbase\Persistence\Generic\Mapper\ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY) {
360 $relationTableName = $columnMap->getRelationTableName();
361 $sql['where'][] = $tableName . '.uid IN (SELECT ' . $columnMap->getParentKeyFieldName() . ' FROM ' . $relationTableName . ' WHERE ' . $columnMap->getChildKeyFieldName() . '=' . $parameterIdentifier . ')';
362 } elseif ($typeOfRelation === \TYPO3\CMS\Extbase\Persistence\Generic\Mapper\ColumnMap::RELATION_HAS_MANY) {
363 $parentKeyFieldName = $columnMap->getParentKeyFieldName();
364 if (isset($parentKeyFieldName)) {
365 $childTableName = $columnMap->getChildTableName();
366 $sql['where'][] = $tableName . '.uid=(SELECT ' . $childTableName . '.' . $parentKeyFieldName . ' FROM ' . $childTableName . ' WHERE ' . $childTableName . '.uid=' . $parameterIdentifier . ')';
367 } else {
368 $sql['where'][] = 'FIND_IN_SET(' . $parameterIdentifier . ', ' . $tableName . '.' . $columnName . ')';
369 }
370 } else {
371 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\RepositoryException('Unsupported or non-existing property name "' . $propertyName . '" used in relation matching.', 1327065745);
372 }
373 }
374 } else {
375 $this->parseDynamicOperand($comparison, $operator, $source, $sql);
376 }
377 }
378
379 /**
380 * Parse a DynamicOperand into SQL and parameter arrays.
381 *
382 * @param \TYPO3\CMS\Extbase\Persistence\Generic\Qom\ComparisonInterface $comparison
383 * @param string $operator One of the JCR_OPERATOR_* constants
384 * @param \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source The source
385 * @param array &$sql The query parts
386 * @param string $valueFunction an optional SQL function to apply to the operand value
387 * @return void
388 */
389 protected function parseDynamicOperand(\TYPO3\CMS\Extbase\Persistence\Generic\Qom\ComparisonInterface $comparison, $operator, \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source, array &$sql, $valueFunction = NULL) {
390 $operand = $comparison->getOperand1();
391 if ($operand instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\LowerCaseInterface) {
392 $this->parseDynamicOperand($operand->getOperand(), $operator, $source, $sql, 'LOWER');
393 } elseif ($operand instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\UpperCaseInterface) {
394 $this->parseDynamicOperand($operand->getOperand(), $operator, $source, $sql, 'UPPER');
395 } elseif ($operand instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\PropertyValueInterface) {
396 $propertyName = $operand->getPropertyName();
397 if ($source instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SelectorInterface) {
398 // FIXME Only necessary to differ from Join
399 $className = $source->getNodeTypeName();
400 $tableName = $this->dataMapper->convertClassNameToTableName($className);
401 while (strpos($propertyName, '.') !== FALSE) {
402 $this->addUnionStatement($className, $tableName, $propertyName, $sql);
403 }
404 } elseif ($source instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\JoinInterface) {
405 $tableName = $source->getJoinCondition()->getSelector1Name();
406 }
407 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
408 $operator = $this->resolveOperator($operator);
409 $constraintSQL = '';
410 if ($valueFunction === NULL) {
411 $constraintSQL .= (!empty($tableName) ? $tableName . '.' : '') . $columnName . ' ' . $operator . ' ';
412 } else {
413 $constraintSQL .= $valueFunction . '(' . (!empty($tableName) ? $tableName . '.' : '') . $columnName . ') ' . $operator . ' ';
414 }
415
416 if ($operator === 'LIKE' || $operator === 'IN') {
417 $constraintSQL .= '(' . $this->normalizeParameterIdentifier($comparison->getParameterIdentifier()) . ')';
418 } else {
419 $constraintSQL .= $this->normalizeParameterIdentifier($comparison->getParameterIdentifier());
420 }
421
422 $sql['where'][] = $constraintSQL;
423 }
424 }
425
426 /**
427 * Add a constraint to ensure that the record type of the returned tuples is matching the data type of the repository.
428 *
429 * @param string $className The class name
430 * @param array &$sql The query parts
431 * @return void
432 */
433 protected function addRecordTypeConstraint($className, &$sql) {
434 if ($className !== NULL) {
435 $dataMap = $this->dataMapper->getDataMap($className);
436 if ($dataMap->getRecordTypeColumnName() !== NULL) {
437 $recordTypes = array();
438 if ($dataMap->getRecordType() !== NULL) {
439 $recordTypes[] = $dataMap->getRecordType();
440 }
441 foreach ($dataMap->getSubclasses() as $subclassName) {
442 $subclassDataMap = $this->dataMapper->getDataMap($subclassName);
443 if ($subclassDataMap->getRecordType() !== NULL) {
444 $recordTypes[] = $subclassDataMap->getRecordType();
445 }
446 }
447 if (!empty($recordTypes)) {
448 $recordTypeStatements = array();
449 foreach ($recordTypes as $recordType) {
450 $tableName = $dataMap->getTableName();
451 $recordTypeStatements[] = $tableName . '.' . $dataMap->getRecordTypeColumnName() . '=' . $this->databaseHandle->fullQuoteStr($recordType, $tableName);
452 }
453 $sql['additionalWhereClause'][] = '(' . implode(' OR ', $recordTypeStatements) . ')';
454 }
455 }
456 }
457 }
458
459 /**
460 * Adds additional WHERE statements according to the query settings.
461 *
462 * @param \TYPO3\CMS\Extbase\Persistence\Generic\QuerySettingsInterface $querySettings The TYPO3 CMS specific query settings
463 * @param string $tableName The table name to add the additional where clause for
464 * @param string &$sql
465 * @return void
466 */
467 protected function addAdditionalWhereClause(\TYPO3\CMS\Extbase\Persistence\Generic\QuerySettingsInterface $querySettings, $tableName, &$sql) {
468 $this->addVisibilityConstraintStatement($querySettings, $tableName, $sql);
469 if ($querySettings->getRespectSysLanguage()) {
470 $this->addSysLanguageStatement($tableName, $sql, $querySettings);
471 }
472 if ($querySettings->getRespectStoragePage()) {
473 $this->addPageIdStatement($tableName, $sql, $querySettings->getStoragePageIds());
474 }
475 }
476
477 /**
478 * Adds enableFields and deletedClause to the query if necessary
479 *
480 * @param \TYPO3\CMS\Extbase\Persistence\Generic\QuerySettingsInterface $querySettings
481 * @param string $tableName The database table name
482 * @param array &$sql The query parts
483 * @return void
484 */
485 protected function addVisibilityConstraintStatement(\TYPO3\CMS\Extbase\Persistence\Generic\QuerySettingsInterface $querySettings, $tableName, array &$sql) {
486 $statement = '';
487 if (is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
488 $ignoreEnableFields = $querySettings->getIgnoreEnableFields();
489 $enableFieldsToBeIgnored = $querySettings->getEnableFieldsToBeIgnored();
490 $includeDeleted = $querySettings->getIncludeDeleted();
491 if ($this->environmentService->isEnvironmentInFrontendMode()) {
492 $statement .= $this->getFrontendConstraintStatement($tableName, $ignoreEnableFields, $enableFieldsToBeIgnored, $includeDeleted);
493 } else {
494 // TYPO3_MODE === 'BE'
495 $statement .= $this->getBackendConstraintStatement($tableName, $ignoreEnableFields, $includeDeleted);
496 }
497 if (!empty($statement)) {
498 $statement = strtolower(substr($statement, 1, 3)) === 'and' ? substr($statement, 5) : $statement;
499 $sql['additionalWhereClause'][] = $statement;
500 }
501 }
502 }
503
504 /**
505 * Returns constraint statement for frontend context
506 *
507 * @param string $tableName
508 * @param bool $ignoreEnableFields A flag indicating whether the enable fields should be ignored
509 * @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.
510 * @param bool $includeDeleted A flag indicating whether deleted records should be included
511 * @return string
512 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InconsistentQuerySettingsException
513 */
514 protected function getFrontendConstraintStatement($tableName, $ignoreEnableFields, array $enableFieldsToBeIgnored = array(), $includeDeleted) {
515 $statement = '';
516 if ($ignoreEnableFields && !$includeDeleted) {
517 if (count($enableFieldsToBeIgnored)) {
518 // array_combine() is necessary because of the way \TYPO3\CMS\Frontend\Page\PageRepository::enableFields() is implemented
519 $statement .= $this->getPageRepository()->enableFields($tableName, -1, array_combine($enableFieldsToBeIgnored, $enableFieldsToBeIgnored));
520 } else {
521 $statement .= $this->getPageRepository()->deleteClause($tableName);
522 }
523 } elseif (!$ignoreEnableFields && !$includeDeleted) {
524 $statement .= $this->getPageRepository()->enableFields($tableName);
525 } elseif (!$ignoreEnableFields && $includeDeleted) {
526 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InconsistentQuerySettingsException('Query setting "ignoreEnableFields=FALSE" can not be used together with "includeDeleted=TRUE" in frontend context.', 1327678173);
527 }
528 return $statement;
529 }
530
531 /**
532 * Returns constraint statement for backend context
533 *
534 * @param string $tableName
535 * @param bool $ignoreEnableFields A flag indicating whether the enable fields should be ignored
536 * @param bool $includeDeleted A flag indicating whether deleted records should be included
537 * @return string
538 */
539 protected function getBackendConstraintStatement($tableName, $ignoreEnableFields, $includeDeleted) {
540 $statement = '';
541 if (!$ignoreEnableFields) {
542 $statement .= BackendUtility::BEenableFields($tableName);
543 }
544 if (!$includeDeleted) {
545 $statement .= BackendUtility::deleteClause($tableName);
546 }
547 return $statement;
548 }
549
550 /**
551 * Builds the language field statement
552 *
553 * @param string $tableName The database table name
554 * @param array &$sql The query parts
555 * @param \TYPO3\CMS\Extbase\Persistence\Generic\QuerySettingsInterface $querySettings The TYPO3 CMS specific query settings
556 * @return void
557 */
558 protected function addSysLanguageStatement($tableName, array &$sql, $querySettings) {
559 if (is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
560 if (!empty($GLOBALS['TCA'][$tableName]['ctrl']['languageField'])) {
561 // Select all entries for the current language
562 $additionalWhereClause = $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . ' IN (' . (int)$querySettings->getLanguageUid() . ',-1)';
563 // If any language is set -> get those entries which are not translated yet
564 // They will be removed by t3lib_page::getRecordOverlay if not matching overlay mode
565 if (isset($GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'])
566 && $querySettings->getLanguageUid() > 0
567 ) {
568 $additionalWhereClause .= ' OR (' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=0' .
569 ' AND ' . $tableName . '.uid NOT IN (SELECT ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] .
570 ' FROM ' . $tableName .
571 ' WHERE ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '>0' .
572 ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '>0';
573
574 // Add delete clause to ensure all entries are loaded
575 if (isset($GLOBALS['TCA'][$tableName]['ctrl']['delete'])) {
576 $additionalWhereClause .= ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['delete'] . '=0';
577 }
578 $additionalWhereClause .= '))';
579 }
580 $sql['additionalWhereClause'][] = '(' . $additionalWhereClause . ')';
581 }
582 }
583 }
584
585 /**
586 * Builds the page ID checking statement
587 *
588 * @param string $tableName The database table name
589 * @param array &$sql The query parts
590 * @param array $storagePageIds list of storage page ids
591 * @return void
592 */
593 protected function addPageIdStatement($tableName, array &$sql, array $storagePageIds) {
594 $tableColumns = $this->tableColumnCache->get($tableName);
595 if ($tableColumns === FALSE) {
596 $tableColumns = $this->databaseHandle->admin_get_fields($tableName);
597 $this->tableColumnCache->set($tableName, $tableColumns);
598 }
599 if (is_array($GLOBALS['TCA'][$tableName]['ctrl']) && array_key_exists('pid', $tableColumns)) {
600 $rootLevel = (int)$GLOBALS['TCA'][$tableName]['ctrl']['rootLevel'];
601 if ($rootLevel) {
602 if ($rootLevel === 1) {
603 $sql['additionalWhereClause'][] = $tableName . '.pid = 0';
604 }
605 } else {
606 if (empty($storagePageIds)) {
607 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InconsistentQuerySettingsException('Missing storage page ids.', 1365779762);
608 }
609 $sql['additionalWhereClause'][] = $tableName . '.pid IN (' . implode(', ', $storagePageIds) . ')';
610 }
611 }
612 }
613
614 /**
615 * Transforms a Join into SQL and parameter arrays
616 *
617 * @param \TYPO3\CMS\Extbase\Persistence\Generic\Qom\JoinInterface $join The join
618 * @param array &$sql The query parts
619 * @return void
620 */
621 protected function parseJoin(\TYPO3\CMS\Extbase\Persistence\Generic\Qom\JoinInterface $join, array &$sql) {
622 $leftSource = $join->getLeft();
623 $leftClassName = $leftSource->getNodeTypeName();
624 $this->addRecordTypeConstraint($leftClassName, $sql);
625 $leftTableName = $leftSource->getSelectorName();
626 $rightSource = $join->getRight();
627 if ($rightSource instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\JoinInterface) {
628 $rightClassName = $rightSource->getLeft()->getNodeTypeName();
629 $rightTableName = $rightSource->getLeft()->getSelectorName();
630 } else {
631 $rightClassName = $rightSource->getNodeTypeName();
632 $rightTableName = $rightSource->getSelectorName();
633 $sql['fields'][$leftTableName] = $rightTableName . '.*';
634 }
635 $this->addRecordTypeConstraint($rightClassName, $sql);
636 $sql['tables'][$leftTableName] = $leftTableName;
637 $sql['unions'][$rightTableName] = 'LEFT JOIN ' . $rightTableName;
638 $joinCondition = $join->getJoinCondition();
639 if ($joinCondition instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\EquiJoinCondition) {
640 $column1Name = $this->dataMapper->convertPropertyNameToColumnName($joinCondition->getProperty1Name(), $leftClassName);
641 $column2Name = $this->dataMapper->convertPropertyNameToColumnName($joinCondition->getProperty2Name(), $rightClassName);
642 $sql['unions'][$rightTableName] .= ' ON ' . $joinCondition->getSelector1Name() . '.' . $column1Name . ' = ' . $joinCondition->getSelector2Name() . '.' . $column2Name;
643 }
644 if ($rightSource instanceof \TYPO3\CMS\Extbase\Persistence\Generic\Qom\JoinInterface) {
645 $this->parseJoin($rightSource, $sql);
646 }
647 }
648
649 /**
650 * Returns a plain value, i.e. objects are flattened out if possible.
651 *
652 * @param mixed $input
653 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnexpectedTypeException
654 * @return mixed
655 */
656 protected function getPlainValue($input) {
657 if (is_array($input)) {
658 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnexpectedTypeException('An array could not be converted to a plain value.', 1274799932);
659 }
660 if ($input instanceof \DateTime) {
661 return $input->format('U');
662 } elseif (TypeHandlingUtility::isCoreType($input)) {
663 return (string) $input;
664 } elseif (is_object($input)) {
665 if ($input instanceof \TYPO3\CMS\Extbase\Persistence\Generic\LazyLoadingProxy) {
666 $realInput = $input->_loadRealInstance();
667 } else {
668 $realInput = $input;
669 }
670 if ($realInput instanceof \TYPO3\CMS\Extbase\DomainObject\DomainObjectInterface) {
671 return $realInput->getUid();
672 } else {
673 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnexpectedTypeException('An object of class "' . get_class($realInput) . '" could not be converted to a plain value.', 1274799934);
674 }
675 } elseif (is_bool($input)) {
676 return $input === TRUE ? 1 : 0;
677 } else {
678 return $input;
679 }
680 }
681
682
683 /**
684 * adds a union statement to the query, mostly for tables referenced in the where condition.
685 *
686 * @param string &$className
687 * @param string &$tableName
688 * @param array &$propertyPath
689 * @param array &$sql
690 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception
691 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InvalidRelationConfigurationException
692 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\MissingColumnMapException
693 */
694 protected function addUnionStatement(&$className, &$tableName, &$propertyPath, array &$sql) {
695 $explodedPropertyPath = explode('.', $propertyPath, 2);
696 $propertyName = $explodedPropertyPath[0];
697 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
698 $tableName = $this->dataMapper->convertClassNameToTableName($className);
699 $columnMap = $this->dataMapper->getDataMap($className)->getColumnMap($propertyName);
700
701 if ($columnMap === NULL) {
702 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\MissingColumnMapException('The ColumnMap for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1355142232);
703 }
704
705 $parentKeyFieldName = $columnMap->getParentKeyFieldName();
706 $childTableName = $columnMap->getChildTableName();
707
708 if ($childTableName === NULL) {
709 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InvalidRelationConfigurationException('The relation information for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1353170925);
710 }
711
712 if ($columnMap->getTypeOfRelation() === \TYPO3\CMS\Extbase\Persistence\Generic\Mapper\ColumnMap::RELATION_HAS_ONE) {
713 if (isset($parentKeyFieldName)) {
714 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $tableName . '.uid=' . $childTableName . '.' . $parentKeyFieldName;
715 } else {
716 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $tableName . '.' . $columnName . '=' . $childTableName . '.uid';
717 }
718 $className = $this->dataMapper->getType($className, $propertyName);
719 } elseif ($columnMap->getTypeOfRelation() === \TYPO3\CMS\Extbase\Persistence\Generic\Mapper\ColumnMap::RELATION_HAS_MANY) {
720 if (isset($parentKeyFieldName)) {
721 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $tableName . '.uid=' . $childTableName . '.' . $parentKeyFieldName;
722 } else {
723 $onStatement = '(FIND_IN_SET(' . $childTableName . '.uid, ' . $tableName . '.' . $columnName . '))';
724 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $onStatement;
725 }
726 $className = $this->dataMapper->getType($className, $propertyName);
727 } elseif ($columnMap->getTypeOfRelation() === \TYPO3\CMS\Extbase\Persistence\Generic\Mapper\ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY) {
728 $relationTableName = $columnMap->getRelationTableName();
729 $sql['unions'][$relationTableName] = 'LEFT JOIN ' . $relationTableName . ' ON ' . $tableName . '.uid=' . $relationTableName . '.' . $columnMap->getParentKeyFieldName();
730 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $relationTableName . '.' . $columnMap->getChildKeyFieldName() . '=' . $childTableName . '.uid';
731 $className = $this->dataMapper->getType($className, $propertyName);
732 } else {
733 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception('Could not determine type of relation.', 1252502725);
734 }
735 // TODO check if there is another solution for this
736 $sql['keywords']['distinct'] = 'DISTINCT';
737 $propertyPath = $explodedPropertyPath[1];
738 $tableName = $childTableName;
739 }
740
741 /**
742 * Returns the SQL operator for the given JCR operator type.
743 *
744 * @param string $operator One of the JCR_OPERATOR_* constants
745 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception
746 * @return string an SQL operator
747 */
748 protected function resolveOperator($operator) {
749 switch ($operator) {
750 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_IN:
751 $operator = 'IN';
752 break;
753 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_EQUAL_TO:
754 $operator = '=';
755 break;
756 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_EQUAL_TO_NULL:
757 $operator = 'IS';
758 break;
759 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_NOT_EQUAL_TO:
760 $operator = '!=';
761 break;
762 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_NOT_EQUAL_TO_NULL:
763 $operator = 'IS NOT';
764 break;
765 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_LESS_THAN:
766 $operator = '<';
767 break;
768 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_LESS_THAN_OR_EQUAL_TO:
769 $operator = '<=';
770 break;
771 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_GREATER_THAN:
772 $operator = '>';
773 break;
774 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_GREATER_THAN_OR_EQUAL_TO:
775 $operator = '>=';
776 break;
777 case \TYPO3\CMS\Extbase\Persistence\QueryInterface::OPERATOR_LIKE:
778 $operator = 'LIKE';
779 break;
780 default:
781 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception('Unsupported operator encountered.', 1242816073);
782 }
783 return $operator;
784 }
785
786 /**
787 * @return \TYPO3\CMS\Frontend\Page\PageRepository
788 */
789 protected function getPageRepository() {
790 if (!$this->pageRepository instanceof \TYPO3\CMS\Frontend\Page\PageRepository) {
791 if ($this->environmentService->isEnvironmentInFrontendMode() && is_object($GLOBALS['TSFE'])) {
792 $this->pageRepository = $GLOBALS['TSFE']->sys_page;
793 } else {
794 $this->pageRepository = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance('TYPO3\\CMS\\Frontend\\Page\\PageRepository');
795 }
796 }
797
798 return $this->pageRepository;
799 }
800 }