/**
* @param string $nameFormat Localized name format string
* @param array $nameParts Parts array
*
* @throws \LogicException
* @return string
*/
private function buildExpression($nameFormat, array $nameParts)
{
$parts = [];
preg_match_all('/%(\\w+)%([^%]*)/', $nameFormat, $matches);
if (!empty($matches[0])) {
foreach ($matches[0] as $idx => $match) {
$key = $matches[1][$idx];
$prependSeparator = isset($matches[2], $matches[2][$idx]) ? $matches[2][$idx] : '';
$lowerCaseKey = strtolower($key);
if (isset($nameParts[$lowerCaseKey])) {
$value = $nameParts[$lowerCaseKey];
if ($key !== $lowerCaseKey) {
$value = sprintf('UPPER(%s)', $nameParts[$lowerCaseKey]);
}
$parts[] = $value;
if (strlen($prependSeparator) !== 0) {
$parts[] = sprintf("'%s'", $prependSeparator);
}
}
}
} else {
throw new \LogicException('Unexpected name format given');
}
return QueryUtils::buildConcatExpr($parts);
}
/**
* Calculates total count of query records
* Notes: this method do not make any modifications of the given query
*
* @param Query|SqlQuery $query
*
* @return integer
*/
public function getCount($query)
{
if ($this->useWalker($query)) {
if (!$query->contains('DISTINCT')) {
$query->setHint(CountWalker::HINT_DISTINCT, false);
}
$paginator = new Paginator($query);
$paginator->setUseOutputWalkers(false);
$result = $paginator->count();
} else {
if ($query instanceof Query) {
$parserResult = QueryUtils::parseQuery($query);
$parameterMappings = $parserResult->getParameterMappings();
list($params, $types) = QueryUtils::processParameterMappings($query, $parameterMappings);
$statement = $query->getEntityManager()->getConnection()->executeQuery('SELECT COUNT(*) FROM (' . $query->getSQL() . ') AS e', $params, $types);
} elseif ($query instanceof SqlQuery) {
$countQuery = clone $query->getQueryBuilder();
$statement = $countQuery->resetQueryParts()->select('COUNT(*)')->from('(' . $query->getSQL() . ')', 'e')->execute();
} else {
throw new \InvalidArgumentException(sprintf('Expected instance of Doctrine\\ORM\\Query or Oro\\Bundle\\EntityBundle\\ORM\\SqlQuery, "%s" given', is_object($query) ? get_class($query) : gettype($query)));
}
$result = $statement->fetchColumn();
}
return $result ? (int) $result : 0;
}
/**
* @param Query $query
*
* @return string
*
* @throws QueryException
*/
public static function getExecutableSql(Query $query)
{
$parserResult = static::parseQuery($query);
$sql = $parserResult->getSqlExecutor()->getSqlStatements();
list($params, $types) = QueryUtils::processParameterMappings($query, $parserResult->getParameterMappings());
list($sql, $params, $types) = SQLParserUtils::expandListParameters($sql, $params, $types);
$paramPos = SQLParserUtils::getPlaceholderPositions($sql);
for ($i = count($paramPos) - 1; $i >= 0; $i--) {
$sql = substr_replace($sql, $query->getEntityManager()->getConnection()->quote($params[$i], $types[$i]), $paramPos[$i], 1);
}
return $sql;
}
/**
* {@inheritdoc}
*/
public function getListQueryBuilder($limit = 10, $page = 1, $criteria = [], $orderBy = null, $joins = [])
{
$currentUser = $this->securityTokenStorage->getToken()->getUser();
$userClass = ClassUtils::getClass($currentUser);
return $this->activityManager->getActivityTargetsQueryBuilder($this->class, $criteria, $joins, $limit, $page, $orderBy, function (QueryBuilder $qb, $targetEntityClass) use($currentUser, $userClass) {
if ($targetEntityClass === $userClass) {
// Need to exclude current user from result because of email context
// @see Oro\Bundle\EmailBundle\Entity\Manager\EmailApiEntityManager::getEmailContext
$qb->andWhere($qb->expr()->neq(QueryUtils::getSelectExprByAlias($qb, 'entityId'), $currentUser->getId()));
}
});
}
/**
* Returns an DQL expression the filter should be applied to
*
* @param int $comparisonType
*
* @return string
*/
protected function getFieldExpr($comparisonType)
{
$dataName = array_map('trim', preg_split('/,/', $this->get(FilterUtility::DATA_NAME_KEY), -1, PREG_SPLIT_NO_EMPTY));
switch ($comparisonType) {
case TextFilterType::TYPE_CONTAINS:
case TextFilterType::TYPE_NOT_CONTAINS:
// CONTAINS and NOT CONTAINS should search in all field
return QueryUtils::buildConcatExpr($dataName);
default:
// other comparisons should work only for the first column
return reset($dataName);
}
}
/**
* Calculates total count of query records
* Notes: this method do not make any modifications of the given query
*
* @param Query|SqlQuery $query
*
* @return integer
*/
public function getCount($query)
{
if ($this->useWalker($query)) {
if (!$query->contains('DISTINCT')) {
$query->setHint(CountWalker::HINT_DISTINCT, false);
}
// fix of doctrine count walker bug
// TODO revert changes when doctrine version >= 2.5 in scope of BAP-5577
/* @var $countQuery Query */
$countQuery = clone $query;
$countQuery->setParameters(clone $query->getParameters());
foreach ($query->getHints() as $name => $value) {
$countQuery->setHint($name, $value);
}
if (!$countQuery->hasHint(CountWalker::HINT_DISTINCT)) {
$countQuery->setHint(CountWalker::HINT_DISTINCT, true);
}
$this->appendTreeWalker($countQuery, 'Oro\\Bundle\\BatchBundle\\ORM\\Query\\Walker\\CountWalker');
$countQuery->setFirstResult(null)->setMaxResults(null);
try {
$data = $countQuery->getScalarResult();
$data = array_map('current', $data);
$result = array_sum($data);
} catch (NoResultException $e) {
$result = 0;
}
} else {
if ($query instanceof Query) {
$parserResult = QueryUtils::parseQuery($query);
$parameterMappings = $parserResult->getParameterMappings();
list($params, $types) = QueryUtils::processParameterMappings($query, $parameterMappings);
$statement = $query->getEntityManager()->getConnection()->executeQuery('SELECT COUNT(*) FROM (' . $query->getSQL() . ') AS e', $params, $types);
} elseif ($query instanceof SqlQuery) {
$countQuery = clone $query->getQueryBuilder();
$statement = $countQuery->resetQueryParts()->select('COUNT(*)')->from('(' . $query->getSQL() . ')', 'e')->execute();
} else {
throw new \InvalidArgumentException(sprintf('Expected instance of Doctrine\\ORM\\Query or Oro\\Bundle\\EntityBundle\\ORM\\SqlQuery, "%s" given', is_object($query) ? get_class($query) : gettype($query)));
}
$result = $statement->fetchColumn();
}
return $result ? (int) $result : 0;
}
/**
* Returns the context for the given activity class and id
*
* @param string $class The FQCN of the activity entity
* @param $id
*
* @return array
*/
public function getActivityContext($class, $id)
{
$criteria = Criteria::create();
$criteria->andWhere(Criteria::expr()->eq('id', $id));
$currentUser = $this->securityTokenStorage->getToken()->getUser();
$userClass = ClassUtils::getClass($currentUser);
$queryBuilder = $this->activityManager->getActivityTargetsQueryBuilder($class, $criteria, null, null, null, null, function (QueryBuilder $qb, $targetEntityClass) use($currentUser, $userClass) {
if ($targetEntityClass === $userClass) {
// Exclude current user from result
$qb->andWhere($qb->expr()->neq(QueryUtils::getSelectExprByAlias($qb, 'entityId'), $currentUser->getId()));
}
});
if (null === $queryBuilder) {
return [];
}
$result = $queryBuilder->getQuery()->getResult();
if (empty($result)) {
return $result;
}
$entityProvider = $this->configManager->getProvider('entity');
foreach ($result as &$item) {
$config = $entityProvider->getConfig($item['entity']);
$metadata = $this->configManager->getEntityMetadata($item['entity']);
$safeClassName = $this->entityClassNameHelper->getUrlSafeClassName($item['entity']);
$link = null;
if ($metadata) {
$link = $this->router->generate($metadata->getRoute(), ['id' => $item['id']]);
} elseif ($link === null && ExtendHelper::isCustomEntity($item['entity'])) {
// Generate view link for the custom entity
$link = $this->router->generate('oro_entity_view', ['id' => $item['id'], 'entityName' => $safeClassName]);
}
$item['activityClassAlias'] = $this->entityAliasResolver->getPluralAlias($class);
$item['entityId'] = $id;
$item['targetId'] = $item['id'];
$item['targetClassName'] = $safeClassName;
$item['icon'] = $config->get('icon');
$item['link'] = $link;
unset($item['id'], $item['entity']);
}
return $result;
}
/**
* Returns a query builder that could be used for fetching the list of owner side entities
* the specified $associationTargetClass associated with.
* The $filters and $joins could be used to filter entities
*
* The resulting query would be something like this:
* <code>
* SELECT entity.entityId AS id, entity.entityClass AS entity, entity.entityTitle AS title FROM (
* SELECT [DISTINCT]
* target.id AS id,
* e.id AS entityId,
* {first_owner_entity_class} AS entityClass,
* {first_owner_title} AS entityTitle
* FROM {first_owner_entity_class} AS e
* INNER JOIN e.{target_field_name_for_first_owner} AS target
* {joins}
* WHERE {filters}
* UNION ALL
* SELECT [DISTINCT]
* target.id AS id,
* e.id AS entityId,
* {second_owner_entity_class} AS entityClass,
* {second_owner_title} AS entityTitle
* FROM {second_owner_entity_class} AS e
* INNER JOIN e.{target_field_name_for_second_owner} AS target
* {joins}
* WHERE {filters}
* UNION ALL
* ... select statements for other owners
* ) entity
* ORDER BY {orderBy}
* LIMIT {limit} OFFSET {(page - 1) * limit}
* </code>
*
* @param string $associationTargetClass The FQCN of the entity that is the target side of the association
* @param mixed|null $filters Criteria is used to filter entities which are association owners
* e.g. ['age' => 20, ...] or \Doctrine\Common\Collections\Criteria
* @param array|null $joins Additional associations required to filter owning side entities
* @param array $associationOwners The list of fields responsible to store associations between
* the given target and association owners
* Array format: [owner_entity_class => field_name]
* @param int|null $limit The maximum number of items per page
* @param int|null $page The page number
* @param string|null $orderBy The ordering expression for the result
* @param callable|null $callback A callback function which can be used to modify child queries
* function (QueryBuilder $qb, $ownerEntityClass)
*
* @return SqlQueryBuilder
*/
public function getMultiAssociationOwnersQueryBuilder($associationTargetClass, $filters, $joins, $associationOwners, $limit = null, $page = null, $orderBy = null, $callback = null)
{
$em = $this->doctrineHelper->getEntityManager($associationTargetClass);
$criteria = QueryUtils::normalizeCriteria($filters);
$selectStmt = null;
$subQueries = [];
$targetIdFieldName = $this->doctrineHelper->getSingleEntityIdentifierFieldName($associationTargetClass);
foreach ($associationOwners as $ownerClass => $fieldName) {
$nameExpr = $this->entityNameResolver->getNameDQL($ownerClass, 'e');
$subQb = $em->getRepository($ownerClass)->createQueryBuilder('e')->select(sprintf('target.%s AS id, e.id AS entityId, \'%s\' AS entityClass, ' . ($nameExpr ?: '\'\'') . ' AS entityTitle', $targetIdFieldName, str_replace('\'', '\'\'', $ownerClass)))->innerJoin('e.' . $fieldName, 'target');
QueryUtils::applyJoins($subQb, $joins);
$subQb->addCriteria($criteria);
if (null !== $callback && is_callable($callback)) {
call_user_func($callback, $subQb, $ownerClass);
}
$subQuery = $this->getAclHelper()->apply($subQb);
$subQueries[] = QueryUtils::getExecutableSql($subQuery);
if (empty($selectStmt)) {
$mapping = QueryUtils::parseQuery($subQuery)->getResultSetMapping();
$selectStmt = sprintf('entity.%s AS id, entity.%s AS entity, entity.%s AS title', QueryUtils::getColumnNameByAlias($mapping, 'entityId'), QueryUtils::getColumnNameByAlias($mapping, 'entityClass'), QueryUtils::getColumnNameByAlias($mapping, 'entityTitle'));
}
}
$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id', Type::INTEGER)->addScalarResult('entity', 'entity')->addScalarResult('title', 'title');
$qb = new SqlQueryBuilder($em, $rsm);
$qb->select($selectStmt)->from('(' . implode(' UNION ALL ', $subQueries) . ')', 'entity');
if (null !== $limit) {
$qb->setMaxResults($limit);
if (null !== $page) {
$qb->setFirstResult(QueryUtils::getPageOffset($page, $limit));
}
}
if ($orderBy) {
$qb->orderBy($orderBy);
}
return $qb;
}
/**
* Checks the given criteria and converts them to Criteria object if needed
*
* @param Criteria|array|null $criteria
*
* @return Criteria
*
* @deprecated since 1.9. Use QueryUtils::normalizeCriteria instead
*/
public function normalizeCriteria($criteria)
{
return QueryUtils::normalizeCriteria($criteria);
}
/**
* @param string $entityClass
* @param integer $entityId
* @param array $filter
*
* @return ActivityList[]
*/
public function getListCount($entityClass, $entityId, $filter)
{
$qb = $this->getBaseQB($entityClass, $entityId);
$this->activityListFilterHelper->addFiltersToQuery($qb, $filter);
$this->activityListAclHelper->applyAclCriteria($qb, $this->chainProvider->getProviders());
$qb->resetDQLPart('orderBy');
$query = $qb->getQuery();
$parserResult = QueryUtils::parseQuery($query);
$parameterMappings = $parserResult->getParameterMappings();
list($params, $types) = QueryUtils::processParameterMappings($query, $parameterMappings);
$statement = $query->getEntityManager()->getConnection()->executeQuery('SELECT COUNT(*) FROM (' . $query->getSQL() . ') AS e', $params, $types);
$result = $statement->fetchColumn();
return $result;
}
/**
* @dataProvider getSelectExprByAliasProvider
*
* @param QueryBuilder $qb
* @param string $alias
* @param string $expectedExpr
*/
public function testGetSelectExprByAlias($qb, $alias, $expectedExpr)
{
$this->assertEquals($expectedExpr, QueryUtils::getSelectExprByAlias($qb, $alias));
}
/**
* Returns a query builder that contains entities from the search result in which titles replaced with
* text representation of appropriate entities.
*
* @todo: This functionality should be removed in the BAP-8995.
*
* @param SearchResult $searchResult
*
* @return SqlQueryBuilder
*/
protected function getEmailAssociatedEntitiesQueryBuilder(SearchResult $searchResult)
{
/** @var EntityManager $em */
$em = $this->getObjectManager();
$selectStmt = null;
$subQueries = [];
foreach ($this->getAssociatedEntitiesFilters($searchResult) as $entityClass => $ids) {
$nameExpr = $this->entityNameResolver->getNameDQL($entityClass, 'e');
/** @var QueryBuilder $subQb */
$subQb = $em->getRepository($entityClass)->createQueryBuilder('e')->select(sprintf('e.id AS id, \'%s\' AS entityClass, ' . ($nameExpr ?: '\'\'') . ' AS entityTitle', str_replace('\'', '\'\'', $entityClass)));
$subQb->where($subQb->expr()->in('e.id', $ids));
$subQuery = $subQb->getQuery();
$subQueries[] = QueryUtils::getExecutableSql($subQuery);
if (empty($selectStmt)) {
$mapping = QueryUtils::parseQuery($subQuery)->getResultSetMapping();
$selectStmt = sprintf('entity.%s AS id, entity.%s AS entity, entity.%s AS title', QueryUtils::getColumnNameByAlias($mapping, 'id'), QueryUtils::getColumnNameByAlias($mapping, 'entityClass'), QueryUtils::getColumnNameByAlias($mapping, 'entityTitle'));
}
}
$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id', Type::INTEGER)->addScalarResult('entity', 'entity')->addScalarResult('title', 'title');
$qb = new SqlQueryBuilder($em, $rsm);
$qb->select($selectStmt)->from('(' . implode(' UNION ALL ', $subQueries) . ')', 'entity');
return $qb;
}
/**
* @param string $entityClass
* @param integer $entityId
* @param array $filter
*
* @return ActivityList[]
*/
public function getListCount($entityClass, $entityId, $filter)
{
$qb = $this->prepareQB($entityClass, $entityId, $filter);
$qb->resetDQLPart('orderBy');
$query = $qb->getQuery();
$parserResult = QueryUtils::parseQuery($query);
$parameterMappings = $parserResult->getParameterMappings();
list($params, $types) = QueryUtils::processParameterMappings($query, $parameterMappings);
$statement = $query->getEntityManager()->getConnection()->executeQuery('SELECT COUNT(*) FROM (' . $query->getSQL() . ') AS e', $params, $types);
$result = $statement->fetchColumn();
return $result;
}
/**
* @dataProvider concatExprProvider
*
* @param string[] $parts
* @param string $expectedExpr
*/
public function testBuildConcatExpr($parts, $expectedExpr)
{
$this->assertEquals($expectedExpr, QueryUtils::buildConcatExpr($parts));
}
/**
* Calculates the page offset
*
* @param int $page The page number
* @param int $limit The maximum number of items per page
*
* @return int
*/
protected function getOffset($page, $limit)
{
return QueryUtils::getPageOffset($page, $limit);
}
/**
* Returns a query builder that could be used for fetching the list of the Customer entities
* filtered by ids.
*
* @param SearchResult $searchResult
*
* @return SqlQueryBuilder
*/
protected function getCustomerListQueryBuilder(SearchResult $searchResult)
{
/** @var EntityManager $em */
$em = $this->getObjectManager();
$selectStmt = null;
$subQueries = [];
foreach ($this->getCustomerListFilters($searchResult) as $customerClass => $customerIds) {
$subQb = $em->getRepository($customerClass)->createQueryBuilder('e')->select(sprintf('channel.id AS channelId, e.id AS entityId, \'%s\' AS entityClass', str_replace('\'', '\'\'', $customerClass)))->innerJoin('e.' . $this->getChannelFieldName($customerClass), 'channel');
$subQb->where($subQb->expr()->in('e.id', $customerIds));
$subQuery = $subQb->getQuery();
$subQueries[] = QueryUtils::getExecutableSql($subQuery);
if (empty($selectStmt)) {
$mapping = QueryUtils::parseQuery($subQuery)->getResultSetMapping();
$selectStmt = sprintf('entity.%s AS channelId, entity.%s as entityId, entity.%s AS entityClass', QueryUtils::getColumnNameByAlias($mapping, 'channelId'), QueryUtils::getColumnNameByAlias($mapping, 'entityId'), QueryUtils::getColumnNameByAlias($mapping, 'entityClass'));
}
}
$rsm = new ResultSetMapping();
$rsm->addScalarResult('channelId', 'channelId', 'integer')->addScalarResult('entityId', 'id', 'integer')->addScalarResult('entityClass', 'entity');
$qb = new SqlQueryBuilder($em, $rsm);
$qb->select($selectStmt)->from('(' . implode(' UNION ALL ', $subQueries) . ')', 'entity');
return $qb;
}
public function testNormalizeCriteriaArray()
{
$expectedCriteria = new Criteria();
$expectedCriteria->andWhere(Criteria::expr()->eq('field', 'value'));
$this->assertEquals($expectedCriteria, QueryUtils::normalizeCriteria(['field' => 'value']));
}
/**
* @param string[] $parts
* @param string $prefix
* @param string[] $separators
*
* @return string
*/
protected function buildConcatExpression($parts, $prefix, $separators)
{
$count = count($parts);
if ($count > 1 || !empty($prefix) && $count === 1) {
$items = [];
if (!empty($prefix)) {
$items[] = sprintf('\'%s\'', $prefix);
}
for ($i = 0; $i < $count; $i++) {
if (empty($separators[$i])) {
$items[] = sprintf('CASE WHEN NULLIF(%1$s, \'\') IS NULL THEN \'\' ELSE %1$s END', $parts[$i]);
} else {
$items[] = sprintf('CASE WHEN NULLIF(%1$s, \'\') IS NULL THEN \'\' ELSE CONCAT(%1$s, \'%2$s\') END', $parts[$i], $separators[$i]);
}
}
return QueryUtils::buildConcatExpr($items);
} elseif ($count === 1) {
return reset($parts);
} else {
return '';
}
}
/**
* Returns a query builder that could be used for fetching the list of entities
* associated with $associationOwnerClass entities found by $filters and $joins
*
* @param string $associationTargetClass The FQCN of the entity that is the owning side of the association
* @param mixed|null $filters Criteria is used to filter entities which are association owners
* e.g. ['age' => 20, ...] or \Doctrine\Common\Collections\Criteria
* @param array|null $joins Additional associations required to filter owning side entities
* @param array $associationOwners The list of fields responsible to store associations between
* the given target and association owners
* Array format: [owner_entity_class => field_name]
* @param int $limit The maximum number of items per page
* @param int $page The page number
* @param string|null $orderBy The ordering expression for the result
*
* @return SqlQueryBuilder
*/
public function getMultiAssociationOwnersQueryBuilder($associationTargetClass, $filters, $joins, $associationOwners, $limit = null, $page = null, $orderBy = null)
{
$em = $this->doctrineHelper->getEntityManager($associationTargetClass);
$criteria = $this->doctrineHelper->normalizeCriteria($filters);
$selectStmt = null;
$subQueries = [];
$targetIdFieldName = $this->doctrineHelper->getSingleEntityIdentifierFieldName($associationTargetClass);
foreach ($associationOwners as $ownerClass => $fieldName) {
// dispatch oro_api.request.get_list.before event
$event = new GetListBefore($this->cloneCriteria($criteria), $associationTargetClass);
$this->eventDispatcher->dispatch(GetListBefore::NAME, $event);
$subCriteria = $event->getCriteria();
$nameExpr = $this->entityNameResolver->getNameDQL($ownerClass, 'e');
$subQb = $em->getRepository($ownerClass)->createQueryBuilder('e')->select(sprintf('target.%s AS id, e.id AS entityId, \'%s\' AS entityClass, ' . ($nameExpr ?: '\'\'') . ' AS entityTitle', $targetIdFieldName, str_replace('\'', '\'\'', $ownerClass)))->innerJoin('e.' . $fieldName, 'target');
$this->doctrineHelper->applyJoins($subQb, $joins);
// fix of doctrine error with Same Field, Multiple Values, Criteria and QueryBuilder
// http://www.doctrine-project.org/jira/browse/DDC-2798
// TODO revert changes when doctrine version >= 2.5 in scope of BAP-5577
QueryBuilderHelper::addCriteria($subQb, $subCriteria);
// $subQb->addCriteria($criteria);
$subQuery = $subQb->getQuery();
$subQueries[] = QueryUtils::getExecutableSql($subQuery);
if (empty($selectStmt)) {
$mapping = QueryUtils::parseQuery($subQuery)->getResultSetMapping();
$selectStmt = sprintf('entity.%s AS id, entity.%s AS entity, entity.%s AS title', QueryUtils::getColumnNameByAlias($mapping, 'entityId'), QueryUtils::getColumnNameByAlias($mapping, 'entityClass'), QueryUtils::getColumnNameByAlias($mapping, 'entityTitle'));
}
}
$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id', Type::INTEGER)->addScalarResult('entity', 'entity')->addScalarResult('title', 'title');
$qb = new SqlQueryBuilder($em, $rsm);
$qb->select($selectStmt)->from('(' . implode(' UNION ALL ', $subQueries) . ')', 'entity');
if (null !== $limit) {
$qb->setMaxResults($limit);
if (null !== $page) {
$qb->setFirstResult($this->doctrineHelper->getPageOffset($page, $limit));
}
}
if ($orderBy) {
$qb->orderBy($orderBy);
}
return $qb;
}
