/** * 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; }