/** * {@inheritDoc} */ public function buildQuery(QueryBuilder $qb, array $query) { if (isset($query['owner_id'])) { $userAlias = $this->createAlias(); $qb->innerJoin('Omeka\\Entity\\Property.owner', $userAlias); $qb->andWhere($qb->expr()->eq("{$userAlias}.id", $this->createNamedParameter($qb, $query['owner_id']))); } if (isset($query['vocabulary_id'])) { $vocabularyAlias = $this->createAlias(); $qb->innerJoin('Omeka\\Entity\\Property.vocabulary', $vocabularyAlias); $qb->andWhere($qb->expr()->eq("{$vocabularyAlias}.id", $this->createNamedParameter($qb, $query['vocabulary_id']))); } if (isset($query['vocabulary_namespace_uri'])) { $vocabularyAlias = $this->createAlias(); $qb->innerJoin('Omeka\\Entity\\Property.vocabulary', $vocabularyAlias); $qb->andWhere($qb->expr()->eq("{$vocabularyAlias}.namespaceUri", $this->createNamedParameter($qb, $query['vocabulary_namespace_uri']))); } if (isset($query['vocabulary_prefix'])) { $vocabularyAlias = $this->createAlias(); $qb->innerJoin('Omeka\\Entity\\Property.vocabulary', $vocabularyAlias); $qb->andWhere($qb->expr()->eq("{$vocabularyAlias}.prefix", $this->createNamedParameter($qb, $query['vocabulary_prefix']))); } if (isset($query['local_name'])) { $qb->andWhere($qb->expr()->eq("Omeka\\Entity\\Property.localName", $this->createNamedParameter($qb, $query['local_name']))); } if (isset($query['term']) && $this->isTerm($query['term'])) { list($prefix, $localName) = explode(':', $query['term']); $vocabularyAlias = $this->createAlias(); $qb->innerJoin('Omeka\\Entity\\Property.vocabulary', $vocabularyAlias); $qb->andWhere($qb->expr()->eq("{$vocabularyAlias}.prefix", $this->createNamedParameter($qb, $prefix))); $qb->andWhere($qb->expr()->eq("Omeka\\Entity\\Property.localName", $this->createNamedParameter($qb, $localName))); } }
/** * @param string $column * @param mixed $value * @return void */ public function whereRootFieldEq($column, $value) { $qualified = $this->aliases->get($this->getRootId()) . '.' . $column; $param = ':' . $this->aliases->get($this->getRootId()) . $column; $this->builder->andWhere($this->builder->expr()->eq($qualified, $param)); $this->builder->setParameter($param, $value); }
/** * Build the query. * * @param QueryBuilder &$qb * @param array $parameters * * @return QueryBuilder */ public function buildQuery(QueryBuilder $qb, array $parameters) { //Handle single tag if (!is_array($parameters['tags'])) { $parameters['tags'] = [$parameters['tags']]; } //clean the parameters from the blank value foreach ($parameters['tags'] as $index => $parameter) { //the blank value is removed if ($parameter === '') { unset($parameters['tags'][$index]); } } if (count($parameters['tags']) > 0) { if (array_key_exists('strict', $parameters)) { $repository = $this->getEntityManager()->getRepository('VictoireBlogBundle:Article'); foreach ($parameters['tags'] as $index => $tag) { $parameter = ':tag' . $index; $subquery = $repository->createQueryBuilder('article_' . $index)->join('article_' . $index . '.tags', 'tag_' . $index)->where('tag_' . $index . ' = ' . $parameter); $qb->andWhere($qb->expr()->in('main_item', $subquery->getDql()))->setParameter($parameter, $tag); } } else { $qb = $qb->join('main_item.tags', 't')->andWhere('t.id IN (:tags)')->setParameter('tags', $parameters['tags']); } } return $qb; }
protected function assertValidQueryBuilder(QueryBuilder $qb, $expectedDql) { $dql = $qb->getDql(); $q = $qb->getQuery(); $this->assertEquals($expectedDql, $dql); }
/** * Determines whether the query builder has ORDER BY on entity joined through * to-many association. * * @param QueryBuilder $queryBuilder * @param ManagerRegistry $managerRegistry * * @return bool */ public static function hasOrderByOnToManyJoin(QueryBuilder $queryBuilder, ManagerRegistry $managerRegistry) { if (empty($orderByParts = $queryBuilder->getDQLPart('orderBy')) || empty($joinParts = $queryBuilder->getDQLPart('join'))) { return false; } $orderByAliases = []; foreach ($orderByParts as $orderBy) { $parts = QueryNameGenerator::getOrderByParts($orderBy); foreach ($parts as $part) { if (false !== ($pos = strpos($part, '.'))) { $alias = substr($part, 0, $pos); $orderByAliases[$alias] = true; } } } if (!empty($orderByAliases)) { foreach ($joinParts as $rootAlias => $joins) { foreach ($joins as $join) { $alias = QueryNameGenerator::getJoinAlias($join); if (isset($orderByAliases[$alias])) { $relationship = QueryNameGenerator::getJoinRelationship($join); $relationshipParts = explode('.', $relationship); $parentAlias = $relationshipParts[0]; $association = $relationshipParts[1]; $parentMetadata = QueryNameGenerator::getClassMetadataFromJoinAlias($parentAlias, $queryBuilder, $managerRegistry); if ($parentMetadata->isCollectionValuedAssociation($association)) { return true; } } } } } return false; }
/** * @param QueryBuilder $qb * @param string $dqlAlias */ public function modify(QueryBuilder $qb, $dqlAlias) { if ($this->dqlAlias !== null) { $dqlAlias = $this->dqlAlias; } $qb->groupBy(sprintf('%s.%s', $dqlAlias, $this->field)); }
/** * {@inheritdoc} * * Orders collection by properties. The order of the ordered properties is the same as the order specified in the * query. * For each property passed, if the resource does not have such property or if the order value is different from * `asc` or `desc` (case insensitive), the property is ignored. */ public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder) { $request = $this->requestStack->getCurrentRequest(); if (null === $request) { return; } $properties = $this->extractProperties($request); foreach ($properties as $property => $order) { if (!$this->isPropertyEnabled($property) || !$this->isPropertyMapped($property, $resource)) { continue; } if (empty($order) && isset($this->properties[$property])) { $order = $this->properties[$property]; } $order = strtoupper($order); if (!in_array($order, ['ASC', 'DESC'])) { continue; } $alias = 'o'; $field = $property; if ($this->isPropertyNested($property)) { $propertyParts = $this->splitPropertyParts($property); $parentAlias = $alias; foreach ($propertyParts['associations'] as $association) { $alias = QueryNameGenerator::generateJoinAlias($association); $queryBuilder->leftJoin(sprintf('%s.%s', $parentAlias, $association), $alias); $parentAlias = $alias; } $field = $propertyParts['field']; } $queryBuilder->addOrderBy(sprintf('%s.%s', $alias, $field), $order); } }
/** * get the search dql. * * @return string */ protected function _addSearch(\Doctrine\ORM\QueryBuilder $queryBuilder) { if ($this->search == true) { $request = $this->request; $search_fields = array_values($this->fields); foreach ($search_fields as $i => $search_field) { $search_param = $request->get("sSearch_{$i}"); if ($request->get("sSearch_{$i}") !== false && !empty($search_param)) { $field = explode(' ', trim($search_field)); $search_field = $field[0]; if (preg_match('/~/', $search_param)) { $search_params = explode('~', $search_param); $from_date = date('Y-m-d 00:00:00', strtotime($search_params[0])); $to_date = date('Y-m-d 23:59:59', strtotime($search_params[1])); if (($search_params[0] == '' || $from_date < '2000-01-01 00:00:00') && ($search_params[1] == '' || $to_date < '2000-01-01 00:00:00')) { // both date fields are empty/invalid -- do nothing } elseif ($search_params[0] == '' || $from_date < '2000-01-01 00:00:00') { $queryBuilder->andWhere(" {$search_field} <= '{$to_date}' "); } elseif ($search_params[1] == '' || $to_date < '2000-01-01 00:00:00') { $queryBuilder->andWhere(" {$search_field} >= '{$from_date}' "); } else { $queryBuilder->andWhere(" {$search_field} between '{$from_date}' and '{$to_date}' "); } } else { $queryBuilder->andWhere(" {$search_field} like '%{$request->get("sSearch_{$i}")}%' "); } } } } }
/** * Adds additional category-related conditions to query * * @param QueryBuilder $queryBuilder */ private function addCategoryConditions(QueryBuilder $queryBuilder) { // show products from enabled categories $expression = $queryBuilder->expr()->eq('categories.enabled', ':enabled2'); $queryBuilder->andWhere($expression); $queryBuilder->setParameter('enabled2', true); }
/** * Clones a query. * * @param QueryBuilder $query The query. * * @return QueryBuilder The cloned query. */ public static function cloneQuery($query) { /* @var $cloneQuery QueryBuilder */ $cloneQuery = clone $query; $cloneQuery->setParameters(clone $query->getParameters()); return $cloneQuery; }
/** * @param FilterInterface $filter * @param FormInterface $form * @param QueryBuilder $qb * @param string $alias */ public function handleForm(FilterInterface $filter, FormInterface $form, QueryBuilder $qb, $alias) { $data = $form->getData(); if (!$data) { return; } $columns = $filter->getFullAttributeReferences($alias); if (!empty($data[DateRangeType::START_NAME])) { $startDate = $data[DateRangeType::START_NAME]; $dql = []; foreach ($columns as $column) { $uid = uniqid('fromDate'); $dql[] = "{$column} >= :{$uid}"; $qb->setParameter($uid, $startDate); } $qb->andWhere(implode(' OR ', $dql)); } if (!empty($data[DateRangeType::END_NAME])) { $endDate = $data[DateRangeType::END_NAME]; $dql = []; foreach ($columns as $column) { $uid = uniqid('endDate'); $dql[] = "{$column} <= :{$uid}"; $qb->setParameter($uid, $endDate); } $qb->andWhere(implode(' OR ', $dql)); } }
/** * {@inheritdoc} * * Order collection by properties. The order of the ordered properties is the same as the order specified in the * query. */ public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder, Request $request) { $metadata = $this->getClassMetadata($resource); $fieldNames = $metadata->getFieldNames(); foreach ($request->query->all() as $filter => $values) { if (!is_array($values)) { continue; } if (null !== $this->properties) { if (false === in_array($filter, $this->properties)) { continue; } } if (in_array($filter, $fieldNames)) { foreach ($values as $period => $date) { $period = strtolower($period); $date = new \DateTime($date); if ('before' === $period) { $parameter = sprintf('%s%s', $period, $filter); $queryBuilder->andWhere(sprintf('o.%s <= :%s', $filter, $parameter))->setParameter($parameter, $date); } if ('after' === $period) { $parameter = sprintf('%s%s', $period, $filter); $queryBuilder->andWhere(sprintf('o.%s >= :%s', $filter, $parameter))->setParameter($parameter, $date); } } } } }
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; }
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 $qb * @param string $dqlAlias * * @return string */ public function getFilter(QueryBuilder $qb, $dqlAlias) { if ($this->dqlAlias !== null) { $dqlAlias = $this->dqlAlias; } return (string) $qb->expr()->isNull(sprintf('%s.%s', $dqlAlias, $this->field)); }
/** * @param array $extra * @param QueryBuilder $builder */ protected function queryExtraConditions(array $extra, QueryBuilder $builder) { foreach ($extra as $key => $extraValue) { $builder->andWhere("e.{$key} = :{$key}"); $builder->setParameter($key, $extraValue); } }
/** * Build the query. * * @param QueryBuilder &$qb * @param array $parameters * * @return QueryBuilder */ public function buildQuery(QueryBuilder $qb, array $parameters) { if (!is_array($parameters['category'])) { $parameters['category'] = [$parameters['category']]; } $childrenArray = []; //clean the parameters from the blank value foreach ($parameters['category'] as $index => $parameter) { //the blank value is removed if ($parameter === '') { unset($parameters['category'][$index]); } else { $parentCategory = $this->getEntityManager()->getRepository('VictoireBlogBundle:Category')->findOneById($parameter); $childrenArray = array_merge($childrenArray, $this->getCategoryChildrens($parentCategory, [])); } } if (count($childrenArray) > 0) { if (array_key_exists('strict', $parameters)) { $repository = $this->getEntityManager()->getRepository('VictoireBlogBundle:Article'); foreach ($childrenArray as $index => $category) { $parameter = ':category' . $index; $subquery = $repository->createQueryBuilder('article_' . $index)->join('article_' . $index . '.category', 'category_' . $index)->where('category_' . $index . ' = ' . $parameter); $qb->andWhere($qb->expr()->in('main_item', $subquery->getDql()))->setParameter($parameter, $category); } } else { $qb = $qb->join('main_item.category', 'c')->andWhere('c.id IN (:category)')->setParameter('category', $childrenArray); } } return $qb; }
public function manipulateQuery(QueryBuilder $query, $prefix = 'hv') { $query->where("{$prefix}.createdAt >= :startDate"); $query->andWhere("{$prefix}.createdAt <= :endDate"); $query->setParameters(['startDate' => $this->period->getStartDate(), 'endDate' => $this->period->getEndDate()]); return $query; }
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'); }
/** * {@inheritdoc} */ public function filter(Request $request, QueryBuilder $queryBuilder) { if ($request->query->has('per_page') && $request->query->has('page')) { $queryBuilder->setMaxResults($request->query->get('per_page')); $queryBuilder->setFirstResult(($request->query->get('page') - 1) * $request->query->get('per_page')); } }
/** * Apply the ACL constraints to the specified query builder, using the permission definition * * @param QueryBuilder $queryBuilder The query builder * @param PermissionDefinition $permissionDef The permission definition * * @return Query */ public function apply(QueryBuilder $queryBuilder, PermissionDefinition $permissionDef) { $whereQueryParts = $queryBuilder->getDQLPart('where'); if (empty($whereQueryParts)) { $queryBuilder->where('1 = 1'); // this will help in cases where no where query is specified } $query = $this->cloneQuery($queryBuilder->getQuery()); $builder = new MaskBuilder(); foreach ($permissionDef->getPermissions() as $permission) { $mask = constant(get_class($builder) . '::MASK_' . strtoupper($permission)); $builder->add($mask); } $query->setHint('acl.mask', $builder->get()); $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'Kunstmaan\\AdminBundle\\Helper\\Security\\Acl\\AclWalker'); $rootEntity = $permissionDef->getEntity(); $rootAlias = $permissionDef->getAlias(); // If either alias or entity was not specified - use default from QueryBuilder if (empty($rootEntity) || empty($rootAlias)) { $rootEntities = $queryBuilder->getRootEntities(); $rootAliases = $queryBuilder->getRootAliases(); $rootEntity = $rootEntities[0]; $rootAlias = $rootAliases[0]; } $query->setHint('acl.root.entity', $rootEntity); $query->setHint('acl.extra.query', $this->getPermittedAclIdsSQLForUser($query)); $classMeta = $this->em->getClassMetadata($rootEntity); $entityRootTableName = $this->quoteStrategy->getTableName($classMeta, $this->em->getConnection()->getDatabasePlatform()); $query->setHint('acl.entityRootTableName', $entityRootTableName); $query->setHint('acl.entityRootTableDqlAlias', $rootAlias); return $query; }
/** * @dataProvider orderByProvider */ public function testAddOrderBy($alias, $orderBy, $expectedOrderBy) { foreach ($expectedOrderBy as $field => $order) { $this->queryBuilder->addOrderBy($field, $order)->shouldBeCalledTimes(1); } $this->addOrderByFunction->invokeArgs($this->orderByTrait, [$this->queryBuilder->reveal(), $alias, $orderBy]); }
/** * @param Request $request * @param QueryBuilder $queryBuilder * * @return QueryBuilder */ public function execute(Request $request, QueryBuilder $queryBuilder) { $filteredProperties = array(); $sort = $request->get('sort'); if (!is_null($sort) && !is_array($sort)) { $elements = explode(',', $sort, 99); foreach ($elements as $element) { if ($element) { $sort = 'asc'; if (substr($element, 0, 1) == '+') { $element = substr($element, 1); } else { if (substr($element, 0, 1) == '-') { $element = substr($element, 1); $sort = 'desc'; } } if (array_key_exists($element, $this->orderProperties)) { $filteredProperties[] = $element; $queryBuilder->addOrderBy($this->orderProperties[$element][0], $sort); } } } } foreach ($this->orderProperties as $property => $value) { if (!array_key_exists($property, $filteredProperties)) { if (!is_null($value[1])) { $queryBuilder->addOrderBy($value[0], $value[1]); } } } return $queryBuilder; }
/** * {@inheritdoc} */ public function addFieldSorter($field, $direction, $locale = null, $scope = null) { $this->qb->addOrderBy($field, $direction); $idField = $this->qb->getRootAlias() . '.id'; $this->qb->addOrderBy($idField); return $this; }
public function implement(DoctrineQueryBuilder $queryBuilder) { if (!$this->getValue()) { return $queryBuilder; } $queryBuilder->select('COUNT(' . QueryBuilder::DEFAULT_ALIAS . '.id)'); }
/** * @param array $extra * @param QueryBuilder $builder */ protected function queryExtraConditions(array $extra, QueryBuilder $builder) { foreach ($extra as $key => $extraValue) { $builder->andWhere("e.{$key} = :" . $this->prepareParam($key)); $builder->setParameter($this->prepareParam($key), $extraValue); } }
/** * Add order rules to the query builder * NB! The alias of the main table must be "e". * @param QueryBuilder $qb * @throws Exception\InvalidArgument */ public function applyToQueryBuilder(QueryBuilder $qb, &$parameterOffset = 0) { $orderRules = $this->orderRules; foreach ($orderRules as $orderRule) { $field = $orderRule[self::FIELD_POS]; switch ($field) { case self::LEFT_FIELD: case self::RIGHT_FIELD: case self::LEVEL_FIELD: break; default: throw new Exception\InvalidArgument("Field {$field} is not recognized"); } $field = "e.{$field}"; $directionSQL = null; $direction = $orderRule[self::DIRECTION_POS]; switch ($direction) { case self::DIRECTION_ASCENDING: $directionSQL = 'ASC'; break; case self::DIRECTION_DESCENDING: $directionSQL = 'DESC'; break; default: throw new Exception\InvalidArgument("Direction {$direction} is not recognized"); } $qb->addOrderBy($field, $directionSQL); } }
/** * This method alters the query to return a clean set of object with a working * set of Object * * @param \Doctrine\ORM\QueryBuilder $queryBuilder * @return void */ private function getFixedQueryBuilder(QueryBuilder $queryBuilder) { $queryBuilderId = clone $queryBuilder; // step 1 : retrieve the targeted class $from = $queryBuilderId->getDQLPart('from'); $class = $from[0]->getFrom(); // step 2 : retrieve the column id $idName = current($queryBuilderId->getEntityManager()->getMetadataFactory()->getMetadataFor($class)->getIdentifierFieldNames()); // step 3 : retrieve the different subjects id $select = sprintf('%s.%s', $queryBuilderId->getRootAlias(), $idName); $queryBuilderId->resetDQLPart('select'); $queryBuilderId->add('select', 'DISTINCT '.$select); $results = $queryBuilderId->getQuery()->execute(array(), Query::HYDRATE_ARRAY); $idx = array(); $connection = $queryBuilder->getEntityManager()->getConnection(); foreach($results as $id) { $idx[] = $connection->quote($id[$idName]); } // step 4 : alter the query to match the targeted ids if (count($idx) > 0) { $queryBuilder->andWhere(sprintf('%s IN (%s)', $select, implode(',', $idx))); $queryBuilder->setMaxResults(null); $queryBuilder->setFirstResult(null); } return $queryBuilder; }
/** * Builds the query for the given data request object * * @access public * @param \Zepi\DataSource\Core\Entity\DataRequest $dataRequest * @param \Doctrine\ORM\QueryBuilder $queryBuilder * @param string $entity * @param string $tableCode */ public function buildDataRequestQuery(DataRequest $dataRequest, QueryBuilder $queryBuilder, $entity, $tableCode) { $queryBuilder->select($tableCode)->from($entity, $tableCode); $hasWhere = false; $i = 1; foreach ($dataRequest->getFilters() as $filter) { $whereQuery = $tableCode . '.' . $filter->getFieldName() . ' ' . $filter->getMode() . ' :' . $i; if ($hasWhere) { $queryBuilder->andWhere($whereQuery); } else { $queryBuilder->where($whereQuery); $hasWhere = true; } $queryBuilder->setParameter($i, $filter->getNeededValue()); $i++; } // Sorting if ($dataRequest->hasSorting()) { $mode = 'ASC'; if (in_array($dataRequest->getSortByDirection(), array('ASC', 'DESC'))) { $mode = $dataRequest->getSortByDirection(); } $queryBuilder->orderBy($tableCode . '.' . $dataRequest->getSortBy(), $mode); } // Offset if ($dataRequest->hasRange()) { $queryBuilder->setFirstResult($dataRequest->getOffset()); $queryBuilder->setMaxResults($dataRequest->getNumberOfEntries()); } }
/** * Adds conditions to the query builder related to the specification. The * specification should add parameters as required and return the expression to be * added to the QueryBuilder. * * @param \Doctrine\ORM\QueryBuilder $qb * @param string $dqlAlias * @return \Doctrine\ORM\Query\Expr */ public function match(QueryBuilder $qb, $dqlAlias) { foreach ($this->sortFields as $field => $direction) { $qb->addOrderBy(sprintf('%s.%s', $dqlAlias, $field), $direction); } return parent::match($qb, $dqlAlias); }