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