private function checkAliases($column) { if (Strings::contains($column, ".")) { return $column; } return current($this->data_source->getRootAliases()) . '.' . $column; }
public function createPropertyPathMapping(QueryBuilder $qb, array $fieldMapping, array $existingMapping = []) { if (count($qb->getRootAliases()) > 1) { throw new \UnexpectedValueException("Expected 1 root alias"); } $rootAlias = $qb->getRootAliases()[0]; $aliasMapping = [$rootAlias => '']; foreach ($qb->getDQLPart('join') as $joinFrom => $joins) { foreach ($joins as $join) { $joinParts = explode(".", $join->getJoin()); $aliasMapping[$join->getAlias()] = trim($aliasMapping[$joinParts[0]] . '.' . $joinParts[1], '.'); } } $mapping = []; foreach ($fieldMapping as $key => $field) { if (array_key_exists($key, $existingMapping)) { $mapping[$key] = $existingMapping[$key]; continue; } $fieldParts = explode('.', $field); if (isset($fieldParts[1])) { $alias = $fieldParts[0]; $mapping[$key] = trim($aliasMapping[$fieldParts[0]] . '.' . $fieldParts[1], '.'); } else { $mapping[$key] = trim($fieldParts[0]); } } return $mapping; }
/** * {@inheritdoc} */ public function addFieldFilter($field, $operator, $value) { $field = current($this->qb->getRootAliases()) . '.' . $field; $condition = $this->prepareCriteriaCondition($field, $operator, $value); $this->qb->andWhere($condition); return $this; }
/** * {@inheritdoc} */ public function addFieldSorter($field, $direction, $locale = null, $scope = null) { $field = current($this->qb->getRootAliases()) . '.' . $field; $this->qb->addOrderBy($field, $direction); $idField = $this->qb->getRootAlias() . '.id'; $this->qb->addOrderBy($idField); return $this; }
/** * Set original query builder. * * @param QueryBuilder $originalQb */ protected function setOriginalQueryBuilder(QueryBuilder $originalQb) { $this->originalQb = $originalQb; $this->qbTools->prepareFieldAliases($originalQb->getDQLPart('select')); $this->qbTools->prepareJoinTablePaths($originalQb->getDQLPart('join')); $this->rootAlias = current($this->originalQb->getRootAliases()); $this->initIdFieldName(); }
public function getCount() { if ($this->count === NULL) { $alias = $this->builder->getRootAliases(); $builder = clone $this->builder; $result = $builder->select('COUNT(' . current($alias) . '.id) AS count')->getQuery()->getSingleResult(); $this->count = $result['count']; } return $this->count; }
/** * @param string $column * @return string */ private function checkAliases($column) { if (Strings::contains($column, ".")) { return $column; } if (!isset($this->root_alias)) { $this->root_alias = $this->data_source->getRootAliases(); $this->root_alias = current($this->root_alias); } return $this->root_alias . '.' . $column; }
/** * @param QueryBuilder $qb * @param string $orderStatus */ protected function addFilterByOrderStatus(QueryBuilder $qb, $orderStatus) { $aliases = $qb->getRootAliases(); $subQueryBuilder = $this->customerRepository->createQueryBuilder('c'); $subQueryBuilder->select('IDENTITY(c.account)')->join('c.orders', 'o', 'WITH', $subQueryBuilder->expr()->eq($subQueryBuilder->expr()->lower('o.status'), ':filteredOrderStatus')); $qb->andWhere($qb->expr()->in($aliases[0] . '.account', $subQueryBuilder->getDQL()))->setParameter('filteredOrderStatus', $orderStatus); }
private function autoJoin(QueryBuilder $queryBuilder) { foreach ($this->expectedJoinChains as $tablesToJoin) { // check if the first dimension is a known alias if (isset($this->knownAliases[$tablesToJoin[0]])) { $joinTo = $tablesToJoin[0]; array_pop($tablesToJoin); } else { // if not, it's the root table $joinTo = $queryBuilder->getRootAliases()[0]; } foreach ($tablesToJoin as $table) { $joinAlias = self::ALIAS_PREFIX . $table; $join = sprintf('%s.%s', $joinTo, $table); if (!isset($this->joinMap[$join])) { $this->joinMap[$join] = $joinAlias; $this->knownAliases[$joinAlias] = true; $queryBuilder->join(sprintf('%s.%s', $joinTo, $table), $joinAlias); } else { $joinAlias = $this->joinMap[$join]; } $joinTo = $joinAlias; } } }
/** * @param QueryBuilder $query * @param int $per_page * @param int $current_page * * @return Configuration */ public function paginateQuery(QueryBuilder $query, $per_page, $current_page = 1) { $counter = clone $query; $total = $counter->select(sprintf('COUNT(%s)', current($query->getRootAliases())))->getQuery()->getSingleScalarResult(); $query->setFirstResult(($current_page - 1) * $per_page)->setMaxResults($per_page); return (new Configuration(ceil($total / $per_page), $current_page))->setMaxNavigate($this->max_navigate); }
function it_adds_binary_filter_in_the_query(QueryBuilder $queryBuilder) { $queryBuilder->expr()->willReturn(new Expr()); $queryBuilder->getRootAliases()->willReturn(['p']); $queryBuilder->andWhere('p.id = \'12\'')->shouldBeCalled(); $this->addFieldFilter('id', '=', '12'); }
/** * Sets sorting. * @param array $sorting */ public function sort(array $sorting) { foreach ($sorting as $key => $value) { $column = isset($this->sortMapping[$key]) ? $this->sortMapping[$key] : current($this->qb->getRootAliases()) . '.' . $key; $this->qb->addOrderBy($column, $value); } }
/** * @return mixed */ public function getItems() { $this->qb->setMaxResults($this->getLimit()); $this->qb->setFirstResult($this->getOffset()); if (count($this->getSorting()) > 0) { foreach ($this->getSorting() as $sortColumn => $sortType) { if (isset($this->columnAliases[$sortColumn])) { $sortColumn = $this->columnAliases[$sortColumn]->qbName; } else { $aliases = $this->qb->getRootAliases(); $sortColumn = $aliases[0] . '.' . $sortColumn; } $this->qb->addOrderBy($sortColumn, is_string($sortType) && strncasecmp($sortType, 'd', 1) || $sortType > 0 ? 'ASC' : 'DESC'); } } return $this->qb->getQuery()->getResult(); }
/** * {@inheritdoc} */ public function filter(Request $request, QueryBuilder $queryBuilder) { $order = $request->query->get('order') ?: self::DEFAULT_ORDER; if ($request->query->has('sort')) { $alias = $queryBuilder->getRootAliases()[0]; $queryBuilder->orderBy($alias . '.' . $request->query->get('sort'), $order); } }
/** * {@inheritdoc} */ public function setSourceQueryBuilder(QueryBuilder $queryBuilder) { if ($this->priceListId) { $aliases = $queryBuilder->getRootAliases(); $rootAlias = reset($aliases); $queryBuilder->andWhere($queryBuilder->expr()->eq(sprintf('IDENTITY(%s.priceList)', $rootAlias), ':priceList'))->setParameter('priceList', $this->priceListId); } parent::setSourceQueryBuilder($queryBuilder); }
/** * @param QueryBuilder $qb */ protected function prepareIteratorPart(QueryBuilder $qb) { if (!$this->unsubscribedItemClassName) { throw new \InvalidArgumentException('Unsubscribed Item Class name must be provided'); } $rootAliases = $qb->getRootAliases(); $entityAlias = reset($rootAliases); $qb->leftJoin($this->unsubscribedItemClassName, 'mlu', Join::WITH, "mlu.entityId = {$entityAlias}.id")->andWhere($qb->expr()->isNotNull('mlu.id')); }
/** * Add completeness joins to query builder * * @param string $completenessAlias the join alias * @param string $locale the locale * @param string $scope the scope * * @return CompletenessJoin */ public function addJoins($completenessAlias, $locale, $scope) { $rootAlias = $this->qb->getRootAliases()[0]; $localeAlias = $completenessAlias . 'Locale'; $channelAlias = $completenessAlias . 'Channel'; $rootEntity = $this->qb->getRootEntities()[0]; $completenessMapping = $this->qb->getEntityManager()->getClassMetadata($rootEntity)->getAssociationMapping('completenesses'); $completenessClass = $completenessMapping['targetEntity']; $joinCondition = sprintf('%s.product = %s.id', $completenessAlias, $rootAlias); $this->qb->leftJoin('PimCatalogBundle:Channel', $channelAlias, 'WITH', $channelAlias . '.code = :cScopeCode')->setParameter('cScopeCode', $scope); $joinCondition .= sprintf(' AND %s.channel = %s.id', $completenessAlias, $channelAlias); if (null !== $locale) { $this->qb->leftJoin('PimCatalogBundle:Locale', $localeAlias, 'WITH', sprintf('%s.code = :%scLocaleCode', $localeAlias, $localeAlias))->setParameter($localeAlias . 'cLocaleCode', $locale); $joinCondition .= sprintf(' AND %s.locale = %s.id', $completenessAlias, $localeAlias); } $this->qb->leftJoin($completenessClass, $completenessAlias, 'WITH', $joinCondition); return $this; }
/** * {@inheritdoc} */ public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder, Request $request) { if (!$request->query->has('tags')) { return; } foreach ($request->query->get('tags') as $i => $tag) { $queryBuilder->innerJoin($queryBuilder->getRootAliases()[0] . '.tags', 't' . $i)->andWhere('t' . $i . '.name = :name_' . $i)->setParameter('name_' . $i, $tag); } }
/** * Sets the initial QueryBuilder for this DataGrid * @param QueryBuilder $queryBuilder */ public function initQueryBuilder(QueryBuilder $queryBuilder) { $this->queryBuilder = clone $queryBuilder; //Try to guess the new root alias and apply it to our queries+ //as the external querybuilder almost certainly is not used our default alias $externalTableAliases = $this->queryBuilder->getRootAliases(); if (count($externalTableAliases)) { $this->setTableAlias($externalTableAliases[0]); } }
/** * {@inheritdoc} */ public function addAttributeSorter(AttributeInterface $attribute, $direction, $locale = null, $scope = null) { if (null === $locale) { throw new \InvalidArgumentException(sprintf('Cannot prepare condition on type "%s" without locale', $attribute->getAttributeType())); } $aliasPrefix = 'sorter'; // join to values $joinAlias = $aliasPrefix . 'V' . $attribute->getCode(); $condition = $this->prepareAttributeJoinCondition($attribute, $joinAlias, $locale, $scope); $this->qb->leftJoin(current($this->qb->getRootAliases()) . '.values', $joinAlias, 'WITH', $condition); // join to reference data $joinAliasOpt = $attribute->getCode(); $this->qb->leftJoin($joinAlias . '.' . $attribute->getReferenceDataName(), $joinAliasOpt); $this->qb->addOrderBy($joinAliasOpt . '.code', $direction); $idField = current($this->qb->getRootAliases()) . '.id'; $this->qb->addOrderBy($idField); return $this; }
/** * @param string $field * @param mixed $value * @return array */ protected function setQueryParam($field) { $index = 0; $rootAlias = $this->qb->getRootAliases()[$index]; if (false !== strpos($field, '.')) { list($assoc, $subField) = explode('.', $field); } else { $assoc = $field; } $meta = $this->getClassMetadata($index); if ($meta->hasAssociation($assoc)) { $targetClass = $meta->getAssociationTargetClass($assoc); if ($meta->isCollectionValuedAssociation($assoc)) { $alias = "{$rootAlias}_{$assoc}"; if (!in_array($alias, $this->qb->getAllAliases())) { $this->qb->leftJoin("{$rootAlias}.{$assoc}", $alias); } $assoc = $alias; } else { $alias = "{$rootAlias}_{$assoc}"; if (!in_array($alias, $this->qb->getAllAliases())) { $this->qb->join("{$rootAlias}.{$assoc}", $alias); } $assoc = $alias; } $em = $this->qb->getEntityManager(); $targetMeta = $em->getClassMetadata($targetClass); if (isset($subField) && !$targetMeta->hasField($subField) && $targetMeta->isInheritanceTypeJoined()) { foreach ($targetMeta->discriminatorMap as $alias => $class) { $joinedMeta = $em->getClassMetadata($class); if ($joinedMeta->hasField($subField)) { if (!in_array($alias, $this->qb->getAllAliases())) { $this->qb->leftJoin($joinedMeta->getName(), $alias, 'WITH', "{$alias}.id = {$assoc}.id"); } $assoc = $alias; } } } $alias = isset($subField) ? "{$assoc}.{$subField}" : $assoc; } else { $alias = "{$rootAlias}.{$field}"; } $values = array_slice(func_get_args(), 1); if ($values) { $result = [$alias]; foreach ($values as $value) { $paramName = $this->getParamName($field); $this->qb->setParameter($paramName, $value); $result[] = ":{$paramName}"; } return $result; } return $alias; }
/** * * @return Array */ protected function getRecurringInstances(QueryBuilder $qb, \DateTime $dateFrom, \DateTime $dateTo) { $events = array(); $qb->andWhere($qb->expr()->isNotNull($qb->getRootAliases()[0] . '.recurrenceRule')); $recurringEvents = $qb->getQuery()->getResult(); $eventUtil = new EventUtil($this->getEntityManager()); foreach ($recurringEvents as $recurringEvent) { /* @var $recurringEvent \Xima\ICalBundle\Entity\Component\Event */ $events = array_merge($events, $eventUtil->getInstances($recurringEvent, $dateFrom, $dateTo)); } return $events; }
/** * {@inheritdoc} */ public function applyFilter(QueryBuilder $qb, array $data, array $options) { self::$filterCount++; $alias = $qb->getRootAliases()[0]; if ($options['mode'] === self::MODE_DEFAULT) { $qb->andWhere(FilterOperator::buildExpression($alias . '.' . $data['property_path'], $data['operator'], ':filter_' . self::$filterCount))->setParameter('filter_' . self::$filterCount, FilterOperator::buildParameter($data['operator'], $data['value'])); } else { $value = $options['mode'] === self::MODE_IS_NULL ? $data['value'] : !$data['value']; $operator = $value ? FilterOperator::IS_NULL : FilterOperator::IS_NOT_NULL; $qb->andWhere(FilterOperator::buildExpression($alias . '.' . $data['property_path'], $operator, ':filter_' . self::$filterCount)); } }
/** * @param QueryBuilder $qb * * @return string * * @throws \RuntimeException */ public function getRootAlias(QueryBuilder $qb) { $aliases = $qb->getRootAliases(); if (count($aliases) !== 1) { if (count($aliases) === 0) { throw new \RuntimeException('Cannot get root alias. A query builder has no root entity.'); } else { throw new \RuntimeException('Cannot get root alias. A query builder has more than one root entity.'); } } return $aliases[0]; }
/** * Get ids of entities from the QueryBuilder * * @return array */ protected function getEntitiesIds() { if (null === $this->entitiesIds) { $rootAlias = current($this->queryBuilder->getRootAliases()); $rootIdExpr = sprintf('%s.id', $rootAlias); $from = current($this->queryBuilder->getDQLPart('from')); $this->queryBuilder->select($rootIdExpr)->resetDQLPart('from')->from($from->getFrom(), $from->getAlias(), $rootIdExpr)->groupBy($rootIdExpr); $results = $this->queryBuilder->getQuery()->getArrayResult(); $this->entitiesIds = array_keys($results); } return $this->entitiesIds; }
/** * Get query builder joins * * @param QueryBuilder $qb * * @return array */ protected function getQbJoins(QueryBuilder $qb) { $qbJoin = []; $joins = $qb->getDQLPart('join'); $rootAlias = current($qb->getRootAliases()); if (isset($joins[$rootAlias])) { foreach ($joins[$rootAlias] as $join) { $qbJoin[] = $join->getAlias(); } } return $qbJoin; }
function it_creates_a_cursor(QueryBuilder $queryBuilder, AbstractQuery $query, From $from) { $queryBuilder->getRootAliases()->willReturn(['a']); $queryBuilder->getDQLPart('from')->willReturn([$from]); $queryBuilder->select('a.id')->willReturn($queryBuilder); $queryBuilder->resetDQLPart('from')->willReturn($queryBuilder); $queryBuilder->from(Argument::any(), Argument::any(), 'a.id')->willReturn($queryBuilder); $queryBuilder->groupBy('a.id')->willReturn($queryBuilder); $queryBuilder->getQuery()->willReturn($query); $query->getArrayResult()->willReturn([]); $cursor = $this->createCursor($queryBuilder); $cursor->shouldBeAnInstanceOf('Akeneo\\Component\\StorageUtils\\Cursor\\CursorInterface'); }
/** * Gets the root entity alias of the query. * * @param QueryBuilder $qb * @param bool $triggerException * * @return string|null * * @throws Exception\InvalidEntityException */ public function getSingleRootAlias(QueryBuilder $qb, $triggerException = true) { $rootAliases = $qb->getRootAliases(); $result = null; if (count($rootAliases) !== 1) { if ($triggerException) { $errorReason = count($rootAliases) === 0 ? 'the query has no any root aliases' : sprintf('the query has several root aliases. "%s"', implode(', ', $rootAliases)); throw new Exception\InvalidEntityException(sprintf('Can\'t get single root alias for the given query. Reason: %s.', $errorReason)); } } else { $result = $rootAliases[0]; } return $result; }
public static function setupSearchableEntityQueryBuild(\Doctrine\ORM\QueryBuilder $qb, \Symfony\Component\HttpFoundation\Request $request) { $terms = $request->query->get('terms'); $search = $request->query->get('search'); $baseEntityName = $qb->getRootAliases()[0]; if ($terms) { $termsArray = explode(',', $terms); } if ($search) { $searchArray = explode(',', $search); } $expr = $qb->expr()->orX(); if (!empty($terms)) { foreach ($searchArray as $searchablePropertyPath) { if (!empty($searchablePropertyPath)) { $searchablePropertyName = str_replace('.', '_', $searchablePropertyPath); $searchablePropertyPathArray = explode('.', $searchablePropertyPath); if (count($searchablePropertyPathArray) > 1) { $lastPathPart = $baseEntityName; foreach ($searchablePropertyPathArray as $pathIndex => $pathPart) { if ($pathIndex < count($searchablePropertyPathArray) - 1) { $searchableProperty = $lastPathPart . '.' . $pathPart; if (!self::doesJoinAExistOnQueryBuilder($qb, $searchableProperty)) { if (!self::doesJoinAliasExistOnQueryBuilder($qb, $pathPart)) { $qb->leftJoin($searchableProperty, $pathPart); $lastPathPart = $pathPart; } else { $pathPart = self::incrementQueryAlias($pathPart); $qb->leftJoin($searchableProperty, $pathPart); $lastPathPart = $pathPart; } } } } $searchableProperty = $lastPathPart . '.' . $searchablePropertyPathArray[count($searchablePropertyPathArray) - 1]; } else { $searchableProperty = $baseEntityName . '.' . $searchablePropertyPath; } foreach ($termsArray as $term) { $parameterName = preg_replace('/[^a-zA-Z0-9]+/', '_', $searchablePropertyName . '_' . $term); $expr->add('LOWER(' . $searchableProperty . ') LIKE :' . $parameterName); $qb->setParameter($parameterName, '%' . strtolower($term) . '%'); } } } } if ($expr->count() > 0) { $qb->andWhere($expr); } }
function let(QueryBuilder $queryBuilder, EntityManager $entityManager, AbstractQuery $query, From $from) { $rootIdExpr = 'o.id'; $from->getFrom()->willReturn('Pim\\Bundle\\CatalogBundle\\Model\\Product'); $from->getAlias()->willReturn('o'); $queryBuilder->getRootAliases()->willReturn(['o']); $queryBuilder->getDQLPart('from')->willReturn([$from]); $queryBuilder->select($rootIdExpr)->willReturn($queryBuilder); $queryBuilder->resetDQLPart('from')->willReturn($queryBuilder); $queryBuilder->from(Argument::any(), Argument::any(), $rootIdExpr)->willReturn($queryBuilder); $queryBuilder->groupBy($rootIdExpr)->willReturn($queryBuilder); $queryBuilder->getQuery()->willReturn($query); $query->getArrayResult()->willReturn([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]); $this->beConstructedWith($queryBuilder, $entityManager, self::PAGE_SIZE); }