/**
* adds a union statement to the query, mostly for tables referenced in the where condition.
* The property for which the union statement is generated will be appended.
*
* @param string &$className The name of the parent class, will be set to the child class after processing.
* @param string &$tableName The name of the parent table, will be set to the table alias that is used in the union statement.
* @param array &$propertyPath The remaining property path, will be cut of by one part during the process.
* @param string $fullPropertyPath The full path the the current property, will be used to make table names unique.
* @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception
* @throws InvalidRelationConfigurationException
* @throws MissingColumnMapException
*/
protected function addUnionStatement(&$className, &$tableName, &$propertyPath, &$fullPropertyPath)
{
$explodedPropertyPath = explode('.', $propertyPath, 2);
$propertyName = $explodedPropertyPath[0];
$columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
$realTableName = $this->dataMapper->convertClassNameToTableName($className);
$tableName = isset($this->tablePropertyMap[$fullPropertyPath]) ? $this->tablePropertyMap[$fullPropertyPath] : $realTableName;
$columnMap = $this->dataMapper->getDataMap($className)->getColumnMap($propertyName);
if ($columnMap === null) {
throw new MissingColumnMapException('The ColumnMap for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1355142232);
}
$parentKeyFieldName = $columnMap->getParentKeyFieldName();
$childTableName = $columnMap->getChildTableName();
if ($childTableName === null) {
throw new InvalidRelationConfigurationException('The relation information for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1353170925);
}
$fullPropertyPath .= $fullPropertyPath === '' ? $propertyName : '.' . $propertyName;
$childTableAlias = $this->getUniqueAlias($childTableName, $fullPropertyPath);
// If there is already exists a union with the current identifier we do not need to build it again and exit early.
if (in_array($childTableAlias, $this->unionTableAliasCache, true)) {
return;
}
if ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_ONE) {
if (isset($parentKeyFieldName)) {
// @todo: no test for this part yet
$joinConditionExpression = $this->queryBuilder->expr()->eq($tableName . '.uid', $childTableAlias . '.' . $parentKeyFieldName);
} else {
$joinConditionExpression = $this->queryBuilder->expr()->eq($tableName . '.' . $columnName, $childTableAlias . '.uid');
}
$this->queryBuilder->leftJoin($tableName, $childTableName, $childTableAlias, $joinConditionExpression);
$this->unionTableAliasCache[] = $childTableAlias;
$this->queryBuilder->andWhere($this->getAdditionalMatchFieldsStatement($this->queryBuilder->expr(), $columnMap, $childTableAlias, $realTableName));
} elseif ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_MANY) {
// @todo: no tests for this part yet
if (isset($parentKeyFieldName)) {
$joinConditionExpression = $this->queryBuilder->expr()->eq($tableName . '.uid', $childTableAlias . '.' . $parentKeyFieldName);
} else {
$joinConditionExpression = $this->queryBuilder->expr()->inSet($tableName . '.' . $columnName, $childTableAlias . '.uid');
}
$this->queryBuilder->leftJoin($tableName, $childTableName, $childTableAlias, $joinConditionExpression);
$this->unionTableAliasCache[] = $childTableAlias;
$this->queryBuilder->andWhere($this->getAdditionalMatchFieldsStatement($this->queryBuilder->expr(), $columnMap, $childTableAlias, $realTableName));
} elseif ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY) {
$relationTableName = $columnMap->getRelationTableName();
$relationTableAlias = $relationTableAlias = $this->getUniqueAlias($relationTableName, $fullPropertyPath . '_mm');
$joinConditionExpression = $this->queryBuilder->expr()->eq($tableName . '.uid', $relationTableAlias . '.' . $columnMap->getParentKeyFieldName());
$this->queryBuilder->leftJoin($tableName, $relationTableName, $relationTableAlias, $joinConditionExpression);
$joinConditionExpression = $this->queryBuilder->expr()->eq($relationTableAlias . '.' . $columnMap->getChildKeyFieldName(), $childTableAlias . '.uid');
$this->queryBuilder->leftJoin($relationTableAlias, $childTableName, $childTableAlias, $joinConditionExpression);
$this->queryBuilder->andWhere($this->getAdditionalMatchFieldsStatement($this->queryBuilder->expr(), $columnMap, $relationTableAlias, $realTableName));
$this->unionTableAliasCache[] = $childTableAlias;
$this->queryBuilder->addGroupBy($this->tableName . '.uid');
} else {
throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception('Could not determine type of relation.', 1252502725);
}
$propertyPath = $explodedPropertyPath[1];
$tableName = $childTableAlias;
$className = $this->dataMapper->getType($className, $propertyName);
}
/**
* Prepares the clause by which the result elements are sorted. See description of ORDER BY in
* SQL standard for reference.
*
* @return void
*/
protected function prepareOrderByStatement()
{
if (empty($this->config['orderBy'])) {
$this->queryBuilder->addOrderBy($GLOBALS['TCA'][$this->table]['ctrl']['label']);
} else {
foreach (QueryHelper::parseOrderBy($this->config['orderBy']) as $orderPair) {
list($fieldName, $order) = $orderPair;
$this->queryBuilder->addOrderBy($fieldName, $order);
}
}
}
/**
* @test
*/
public function createPositionalParameterDelegatesToConcreteQueryBuilder()
{
$this->concreteQueryBuilder->createPositionalParameter(5, Argument::cetera())->shouldBeCalled()->willReturn('?');
$this->subject->createPositionalParameter(5);
}
/**
* Sets the Doctrine where clause for fetching pages
*
* @param QueryBuilder $queryBuilder
* @param int $id
* @param string $searchFilter
* @return QueryBuilder
*/
protected function setWhereClause(QueryBuilder $queryBuilder, $id, $searchFilter = '') : QueryBuilder
{
$expressionBuilder = $queryBuilder->expr();
$queryBuilder->where(QueryHelper::stripLogicalOperatorPrefix($GLOBALS['BE_USER']->getPagePermsClause(1)));
if (is_numeric($id) && $id >= 0) {
$queryBuilder->andWhere($expressionBuilder->eq('pid', $queryBuilder->createNamedParameter($id, \PDO::PARAM_INT)));
}
$excludedDoktypes = $GLOBALS['BE_USER']->getTSConfigVal('options.pageTree.excludeDoktypes');
if (!empty($excludedDoktypes)) {
$queryBuilder->andWhere($expressionBuilder->notIn('doktype', $queryBuilder->createNamedParameter(GeneralUtility::intExplode(',', $excludedDoktypes, true), Connection::PARAM_INT_ARRAY)));
}
if ($searchFilter !== '') {
$searchParts = $expressionBuilder->orX();
if (is_numeric($searchFilter) && $searchFilter > 0) {
$searchParts->add($expressionBuilder->eq('uid', $queryBuilder->createNamedParameter($searchFilter, \PDO::PARAM_INT)));
}
$searchFilter = '%' . $queryBuilder->escapeLikeWildcards($searchFilter) . '%';
$useNavTitle = $GLOBALS['BE_USER']->getTSConfigVal('options.pageTree.showNavTitle');
$useAlias = $GLOBALS['BE_USER']->getTSConfigVal('options.pageTree.searchInAlias');
$aliasExpression = '';
if ($useAlias) {
$aliasExpression = $expressionBuilder->like('alias', $queryBuilder->createNamedParameter($searchFilter, \PDO::PARAM_STR));
}
if ($useNavTitle) {
$searchWhereAlias = $expressionBuilder->orX($expressionBuilder->like('nav_title', $queryBuilder->createNamedParameter($searchFilter, \PDO::PARAM_STR)), $expressionBuilder->andX($expressionBuilder->eq('nav_title', $queryBuilder->createNamedParameter('', \PDO::PARAM_STR)), $expressionBuilder->like('title', $queryBuilder->createNamedParameter($searchFilter, \PDO::PARAM_STR))));
if (strlen($aliasExpression)) {
$searchWhereAlias->add($aliasExpression);
}
$searchParts->add($searchWhereAlias);
} else {
$searchParts->add($expressionBuilder->like('title', $queryBuilder->createNamedParameter($searchFilter, \PDO::PARAM_STR)));
if (strlen($aliasExpression)) {
$searchParts->add($aliasExpression);
}
}
$queryBuilder->andWhere($searchParts);
}
return $queryBuilder;
}
/**
* Build the MySql where clause by table.
*
* @param QueryBuilder $queryBuilder
* @param string $tableName Record table name
* @param array $fieldsToSearchWithin User right based visible fields where we can search within.
* @return CompositeExpression
*/
protected function makeQuerySearchByTable(QueryBuilder &$queryBuilder, $tableName, array $fieldsToSearchWithin)
{
$constraints = [];
// If the search string is a simple integer, assemble an equality comparison
if (MathUtility::canBeInterpretedAsInteger($this->queryString)) {
foreach ($fieldsToSearchWithin as $fieldName) {
if ($fieldName !== 'uid' && $fieldName !== 'pid' && !isset($GLOBALS['TCA'][$tableName]['columns'][$fieldName])) {
continue;
}
$fieldConfig = $GLOBALS['TCA'][$tableName]['columns'][$fieldName]['config'];
$fieldType = $fieldConfig['type'];
$evalRules = $fieldConfig['eval'] ?: '';
// Assemble the search condition only if the field is an integer, or is uid or pid
if ($fieldName === 'uid' || $fieldName === 'pid' || $fieldType === 'input' && $evalRules && GeneralUtility::inList($evalRules, 'int')) {
$constraints[] = $queryBuilder->expr()->eq($fieldName, $queryBuilder->createNamedParameter($this->queryString, \PDO::PARAM_INT));
} elseif ($fieldType === 'text' || $fieldType === 'flex' || $fieldType === 'input' && (!$evalRules || !preg_match('/date|time|int/', $evalRules))) {
// Otherwise and if the field makes sense to be searched, assemble a like condition
$constraints[] = $constraints[] = $queryBuilder->expr()->like($fieldName, $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards((int) $this->queryString) . '%', \PDO::PARAM_STR));
}
}
} else {
$like = '%' . $queryBuilder->escapeLikeWildcards($this->queryString) . '%';
foreach ($fieldsToSearchWithin as $fieldName) {
if (!isset($GLOBALS['TCA'][$tableName]['columns'][$fieldName])) {
continue;
}
$fieldConfig =& $GLOBALS['TCA'][$tableName]['columns'][$fieldName]['config'];
$fieldType = $fieldConfig['type'];
$evalRules = $fieldConfig['eval'] ?: '';
// Check whether search should be case-sensitive or not
$searchConstraint = $queryBuilder->expr()->andX($queryBuilder->expr()->comparison('LOWER(' . $queryBuilder->quoteIdentifier($fieldName) . ')', 'LIKE', $queryBuilder->createNamedParameter(strtolower($like), \PDO::PARAM_STR)));
if (is_array($fieldConfig['search'])) {
if (in_array('case', $fieldConfig['search'], true)) {
// Replace case insensitive default constraint
$searchConstraint = $queryBuilder->expr()->andX($queryBuilder->expr()->like($fieldName, $queryBuilder->createNamedParameter($like, \PDO::PARAM_STR)));
}
// Apply additional condition, if any
if ($fieldConfig['search']['andWhere']) {
$searchConstraint->add(QueryHelper::stripLogicalOperatorPrefix($fieldConfig['search']['andWhere']));
}
}
// Assemble the search condition only if the field makes sense to be searched
if ($fieldType === 'text' || $fieldType === 'flex' || $fieldType === 'input' && (!$evalRules || !preg_match('/date|time|int/', $evalRules))) {
if ($searchConstraint->count() !== 0) {
$constraints[] = $searchConstraint;
}
}
}
}
// If no search field conditions have been build ensure no results are returned
if (empty($constraints)) {
return '0=1';
}
return $queryBuilder->expr()->orX(...$constraints);
}
/**
* Helper to transform a QueryBuilder object into a queryParts array that can be used
* with exec_SELECT_queryArray
*
* @param \TYPO3\CMS\Core\Database\Query\QueryBuilder $queryBuilder
* @return array
* @throws \RuntimeException
*/
protected function getQueryArray(QueryBuilder $queryBuilder)
{
$fromClauses = [];
$knownAliases = [];
$queryParts = [];
// Loop through all FROM clauses
foreach ($queryBuilder->getQueryPart('from') as $from) {
if ($from['alias'] === null) {
$tableSql = $from['table'];
$tableReference = $from['table'];
} else {
$tableSql = $from['table'] . ' ' . $from['alias'];
$tableReference = $from['alias'];
}
$knownAliases[$tableReference] = true;
$fromClauses[$tableReference] = $tableSql . $this->getQueryArrayJoinHelper($tableReference, $queryBuilder->getQueryPart('join'), $knownAliases);
}
$queryParts['SELECT'] = implode(', ', $queryBuilder->getQueryPart('select'));
$queryParts['FROM'] = implode(', ', $fromClauses);
$queryParts['WHERE'] = (string) $queryBuilder->getQueryPart('where') ?: '';
$queryParts['GROUPBY'] = implode(', ', $queryBuilder->getQueryPart('groupBy'));
$queryParts['ORDERBY'] = implode(', ', $queryBuilder->getQueryPart('orderBy'));
if ($queryBuilder->getFirstResult() > 0) {
$queryParts['LIMIT'] = $queryBuilder->getFirstResult() . ',' . $queryBuilder->getMaxResults();
} elseif ($queryBuilder->getMaxResults() > 0) {
$queryParts['LIMIT'] = $queryBuilder->getMaxResults();
}
return $queryParts;
}
