/**
* @param string $emailFromTableAlias EmailAddress table alias of joined Email#fromEmailAddress association
*
* @return string
*/
protected function getFromEmailExpression($emailFromTableAlias)
{
$providers = $this->emailOwnerProviderStorage->getProviders();
if (empty($providers)) {
return sprintf('%s.email', $emailFromTableAlias);
}
$expressionsByOwner = [];
foreach ($providers as $provider) {
$relationAlias = $this->emailOwnerProviderStorage->getEmailOwnerFieldName($provider);
$expressionsByOwner[$relationAlias] = $this->entityNameResolver->getNameDQL($provider->getEmailOwnerClass(), $relationAlias);
}
$expression = '';
foreach ($expressionsByOwner as $alias => $expressionPart) {
$expression .= sprintf('WHEN %s.%s IS NOT NULL THEN %s ', $emailFromTableAlias, $alias, $expressionPart);
}
$expression = sprintf('CASE %sELSE \'\' END', $expression);
// if has owner then use expression to expose formatted name, use email otherwise
return sprintf('CONCAT(\'\', CASE WHEN %1$s.hasOwner = true THEN (%2$s) ELSE %1$s.email END) as fromEmailExpression', $emailFromTableAlias, $expression);
}
public function testGetNameDQLByFallbackFormat()
{
$className = 'Test\\Entity';
$alias = 'entity_alias';
$format = 'full';
$locale = 'en_US';
$expected = $alias . '.field';
$this->provider1->expects($this->once())->method('getNameDQL')->with($format, $locale, $className, $alias)->willReturn(false);
$this->provider2->expects($this->at(0))->method('getNameDQL')->with($format, $locale, $className, $alias)->willReturn(false);
$this->provider2->expects($this->at(1))->method('getNameDQL')->with('short', $locale, $className, $alias)->willReturn($expected);
$result = $this->entityNameResolver->getNameDQL($className, $alias, $format, $locale);
$this->assertEquals($expected, $result);
}
/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
/**
* {@inheritdoc}
*/
public function getListQueryBuilder($limit = 10, $page = 1, $criteria = [], $orderBy = null, $joins = [])
{
$userNameDQL = $this->entityNameResolver->getNameDQL('Oro\\Bundle\\UserBundle\\Entity\\User', 'u');
$criteria = $this->prepareQueryCriteria($limit ?: null, $page, $criteria, $orderBy);
return $this->getRepository()->createQueryBuilder('e')->select('e.id, e.invitationStatus, u.email,' . sprintf('%s AS userFullName', $userNameDQL))->join('e.calendar', 'c')->join('c.owner', 'u')->addCriteria($criteria);
}
