PHP ORM\QueryBuilder类代码示例

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

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