PHP QueryBuilder::join方法代码示例

 private function applyJoins(QueryBuilderFilter $filter, QueryBuilder $queryBuilder)
 {
     /** @var Join[] $previousJoins */
     $previousJoins = array();
     foreach ($filter->getJoins() as $join) {
         $joinEntity = $join->getEntityClassName();
         $joinAlias = $join->getAlias();
         if (!array_key_exists($joinEntity, $previousJoins)) {
             $previousJoins[$joinEntity] = $join;
             switch (get_class($join)) {
                 case LeftJoinWith::class:
                     /** @var LeftJoinWith $join */
                     $queryBuilder->leftJoin($joinEntity, $joinAlias, \Doctrine\ORM\Query\Expr\Join::WITH, $join->getWithCondition());
                     break;
                 case LeftJoin::class:
                     $queryBuilder->leftJoin($joinEntity, $joinAlias);
                     break;
                 case JoinWith::class:
                     /** @var JoinWith $join */
                     $queryBuilder->join($joinEntity, $joinAlias, \Doctrine\ORM\Query\Expr\Join::WITH, $join->getWithCondition());
                     break;
                 case Join::class:
                     $queryBuilder->join($joinEntity, $joinAlias);
                     break;
                 default:
                     throw new Exception('Invalid JOIN type: ' . get_class($join));
             }
         } else {
             if ($previousJoins[$joinEntity]->getAlias() !== $joinAlias) {
                 throw new InconsistentJoinsException($joinEntity, $previousJoins[$joinEntity]->getAlias(), $joinAlias);
             }
         }
     }
 }

 /**
  * Applies a custom query to the QueryBuilder
  * 
  * @param QueryBuilder $qb The query builder
  * @param ManagerFilter $filter The query filter
  */
 protected function applyCustomQuery(QueryBuilder $qb, ManagerFilter $filter)
 {
     // Apply special handling for non-direct fields in relations, where the frontend has no idea about.
     foreach ($filter->getSorters() as $sorter) {
         switch ($sorter->getSortField()) {
             case "q.part_name":
                 $qb->join("q.part", "p");
                 $sorter->setSortField("p.name");
                 break;
             case "q.user_id":
                 $qb->leftJoin("q.user", "u");
                 $sorter->setSortField("u.username");
                 break;
             case "q.direction":
                 $sorter->setSortField("q.dateTime");
                 break;
             case "q.storageLocation_name":
                 $qb->join("q.part", "p")->join("p.storageLocation", "st");
                 $sorter->setSortField("st.name");
                 break;
             default:
                 break;
         }
     }
 }

 /**
  * @param array $joins
  * @return AbstractQuery
  */
 public function join(array $joins)
 {
     $this->assertCanBeModified();
     foreach ($joins as $attribute => $prefix) {
         $this->queryBuilder->join($attribute, $prefix);
     }
     return $this;
 }

 /**
  * @param QueryBuilder $qb
  * @param array $criteria
  * @param array $orderBy
  * @param int $limit
  * @param int $offset
  *
  * @return QueryBuilder
  */
 protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
 {
     if (array_key_exists('cohort', $criteria)) {
         $criteria['cohorts'][] = $criteria['cohort'];
         unset($criteria['cohort']);
     }
     if (array_key_exists('cohorts', $criteria)) {
         $ids = is_array($criteria['cohorts']) ? $criteria['cohorts'] : [$criteria['cohorts']];
         $qb->join('l.cohort', 'l_cohort');
         $qb->andWhere($qb->expr()->in('l_cohort.id', ':cohorts'));
         $qb->setParameter(':cohorts', $ids);
     }
     if (array_key_exists('parent', $criteria)) {
         $criteria['parents'][] = $criteria['parent'];
         unset($criteria['parent']);
     }
     if (array_key_exists('parents', $criteria)) {
         $ids = is_array($criteria['parents']) ? $criteria['parents'] : [$criteria['parents']];
         if (in_array(null, $ids)) {
             $ids = array_diff($ids, [null]);
             $qb->andWhere('l.parent IS NULL');
         }
         if (count($ids)) {
             $qb->join('l.parent', 'l_parent');
             $qb->andWhere($qb->expr()->in('l_parent.id', ':parents'));
             $qb->setParameter(':parents', $ids);
         }
     }
     //cleanup all the possible relationship filters
     unset($criteria['cohorts']);
     unset($criteria['parents']);
     if (count($criteria)) {
         foreach ($criteria as $key => $value) {
             $values = is_array($value) ? $value : [$value];
             $qb->andWhere($qb->expr()->in("l.{$key}", ":{$key}"));
             $qb->setParameter(":{$key}", $values);
         }
     }
     if (empty($orderBy)) {
         $orderBy = ['id' => 'ASC'];
     }
     if (is_array($orderBy)) {
         foreach ($orderBy as $sort => $order) {
             $qb->addOrderBy('l.' . $sort, $order);
         }
     }
     if ($offset) {
         $qb->setFirstResult($offset);
     }
     if ($limit) {
         $qb->setMaxResults($limit);
     }
     return $qb;
 }

 /**
  * Will do JOIN only if there is no such join already.
  *
  * @param QueryBuilder $queryBuilder
  * @param string $join
  * @param string $alias
  *
  * @return QueryBuilder
  */
 protected function join(QueryBuilder $queryBuilder, $join, $alias)
 {
     list($entity) = explode('.', $join);
     $joinParts = $queryBuilder->getDQLPart('join');
     if (!array_key_exists($entity, $joinParts)) {
         return $queryBuilder->join($join, $alias);
     }
     $joinParts = $joinParts[$entity];
     $existingJoin = array_filter($joinParts, function (Join $joinObj) use($alias, $join) {
         return $joinObj->getAlias() == $alias && $joinObj->getJoin() == $join;
     });
     if ([] != $existingJoin) {
         return $queryBuilder;
     }
     return $queryBuilder->join($join, $alias);
 }

 public function implement(QueryBuilder $queryBuilder)
 {
     foreach ($this->getValue() as $key => $alias) {
         $key = Utils::parseKey($key);
         $queryBuilder->join($key, $alias);
     }
 }

 /**
  * {@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->join(sprintf('%s.%s', $parentAlias, $association), $alias);
                 $parentAlias = $alias;
             }
             $field = $propertyParts['field'];
         }
         $queryBuilder->addOrderBy(sprintf('%s.%s', $alias, $field), $order);
     }
 }

 /**
  * @param QueryBuilder $qb
  * @param array $criteria
  * @param array $orderBy
  * @param int $limit
  * @param int $offset
  *
  * @return QueryBuilder
  */
 protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
 {
     if (array_key_exists('alerts', $criteria)) {
         $ids = is_array($criteria['alerts']) ? $criteria['alerts'] : [$criteria['alerts']];
         $qb->join('x.alerts', 'al');
         $qb->andWhere($qb->expr()->in('al.id', ':alerts'));
         $qb->setParameter(':alerts', $ids);
     }
     //cleanup all the possible relationship filters
     unset($criteria['alerts']);
     if (count($criteria)) {
         foreach ($criteria as $key => $value) {
             $values = is_array($value) ? $value : [$value];
             $qb->andWhere($qb->expr()->in("x.{$key}", ":{$key}"));
             $qb->setParameter(":{$key}", $values);
         }
     }
     if (empty($orderBy)) {
         $orderBy = ['id' => 'ASC'];
     }
     if (is_array($orderBy)) {
         foreach ($orderBy as $sort => $order) {
             $qb->addOrderBy('x.' . $sort, $order);
         }
     }
     if ($offset) {
         $qb->setFirstResult($offset);
     }
     if ($limit) {
         $qb->setMaxResults($limit);
     }
     return $qb;
 }

 /**
  * {@inheritdoc}
  */
 public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder)
 {
     $request = $this->requestStack->getCurrentRequest();
     if (null === $request) {
         return;
     }
     foreach ($this->extractProperties($request) as $property => $values) {
         // Expect $values to be an array having the period as keys and the date value as values
         if (!$this->isPropertyEnabled($property) || !$this->isPropertyMapped($property, $resource) || !$this->isDateField($property, $resource) || !is_array($values)) {
             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->join(sprintf('%s.%s', $parentAlias, $association), $alias);
                 $parentAlias = $alias;
             }
             $field = $propertyParts['field'];
         }
         $nullManagement = isset($this->properties[$property]) ? $this->properties[$property] : null;
         if (self::EXCLUDE_NULL === $nullManagement) {
             $queryBuilder->andWhere($queryBuilder->expr()->isNotNull(sprintf('%s.%s', $alias, $field)));
         }
         if (isset($values[self::PARAMETER_BEFORE])) {
             $this->addWhere($queryBuilder, $alias, $field, self::PARAMETER_BEFORE, $values[self::PARAMETER_BEFORE], $nullManagement);
         }
         if (isset($values[self::PARAMETER_AFTER])) {
             $this->addWhere($queryBuilder, $alias, $field, self::PARAMETER_AFTER, $values[self::PARAMETER_AFTER], $nullManagement);
         }
     }
 }

 /**
  * @param QueryBuilder $qb
  * @param array $criteria
  * @param array $orderBy
  * @param int $limit
  * @param int $offset
  *
  * @return QueryBuilder
  */
 protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
 {
     if (array_key_exists('courses', $criteria)) {
         $ids = is_array($criteria['courses']) ? $criteria['courses'] : [$criteria['courses']];
         $qb->join('o.courses', 'course');
         $qb->andWhere($qb->expr()->in('course.id', ':courses'));
         $qb->setParameter(':courses', $ids);
     }
     if (array_key_exists('programYears', $criteria)) {
         $ids = is_array($criteria['programYears']) ? $criteria['programYears'] : [$criteria['programYears']];
         $qb->join('o.programYears', 'programYear');
         $qb->andWhere($qb->expr()->in('programYear.id', ':programYears'));
         $qb->setParameter(':programYears', $ids);
     }
     if (array_key_exists('sessions', $criteria)) {
         $ids = is_array($criteria['sessions']) ? $criteria['sessions'] : [$criteria['sessions']];
         $qb->join('o.sessions', 'session');
         $qb->andWhere($qb->expr()->in('session.id', ':sessions'));
         $qb->setParameter(':sessions', $ids);
     }
     unset($criteria['courses']);
     unset($criteria['programYears']);
     unset($criteria['sessions']);
     if (count($criteria)) {
         foreach ($criteria as $key => $value) {
             $values = is_array($value) ? $value : [$value];
             $qb->andWhere($qb->expr()->in("o.{$key}", ":{$key}"));
             $qb->setParameter(":{$key}", $values);
         }
     }
     if (empty($orderBy)) {
         $orderBy = ['id' => 'ASC'];
     }
     if (is_array($orderBy)) {
         foreach ($orderBy as $sort => $order) {
             $qb->addOrderBy('o.' . $sort, $order);
         }
     }
     if ($offset) {
         $qb->setFirstResult($offset);
     }
     if ($limit) {
         $qb->setMaxResults($limit);
     }
     return $qb;
 }

 /**
  * @param QueryBuilder $queryBuilder
  * @param array        $joinParts
  * @param string       $alias
  * @param string       $join
  * @param string       $joinType
  *
  * @return QueryBuilder
  */
 protected function filterExistingJoins(QueryBuilder $queryBuilder, $joinParts, $alias, $join, $joinType)
 {
     $existingJoin = array_filter($joinParts, function (Join $joinObj) use($alias, $join, $joinType) {
         return $joinObj->getJoinType() == $joinType && $joinObj->getAlias() == $alias && $joinObj->getJoin() == $join;
     });
     if ([] != $existingJoin) {
         return $queryBuilder;
     }
     return $queryBuilder->join($join, $alias);
 }

 protected function order()
 {
     $column = $this->getParamAdapter()->getColumn();
     $order = $this->getParamAdapter()->getOrder();
     if (!$column) {
         return;
     }
     $header = $this->getTable()->getHeader($column);
     $tableAlias = $header ? $header->getTableAlias() : 'q';
     if (false === strpos($tableAlias, '.')) {
         $tableAlias = $tableAlias . '.' . $column;
     }
     if ($header->getOrderJoin()) {
         $joinAlias = 'j' . str_replace('.', '_', $tableAlias);
         $this->query->join($tableAlias, $joinAlias);
         $this->query->orderBy($joinAlias . '.' . $header->getOrderJoin(), $order);
     } else {
         $this->query->orderBy($tableAlias, $order);
     }
 }

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

 /**
  * Reapply joins from a set of joins got from getDQLPart('join')
  *
  * @param array $joinsSet
  */
 protected function applyJoins($joinsSet)
 {
     foreach ($joinsSet as $joins) {
         foreach ($joins as $join) {
             if ($join->getJoinType() === Join::LEFT_JOIN) {
                 $this->qb->leftJoin($join->getJoin(), $join->getAlias(), $join->getConditionType(), $join->getCondition(), $join->getIndexBy());
             } else {
                 $this->qb->join($join->getJoin(), $join->getAlias(), $join->getConditionType(), $join->getCondition(), $join->getIndexBy());
             }
         }
     }
 }

 /**
  * {@inheritdoc}
  *
  * @throws InvalidArgumentException When a comparison expression is not supported.
  */
 public function visitComparison(Expr\Comparison $comparison)
 {
     $property = $comparison->getProperty();
     $path = explode('.', $property->getPath());
     $expr = $this->builder->expr();
     $aliases = $this->builder->getAllAliases();
     $prefix = reset($aliases);
     foreach (array_slice($path, 0, -1) as $alias) {
         if (!in_array($alias, $aliases)) {
             $this->builder->join(sprintf('%s.%s', $prefix, $alias), $alias);
         }
         $prefix = $alias;
     }
     $field = sprintf('%s.%s', $prefix, end($path));
     $value = $comparison->getValue();
     switch (true) {
         case $comparison instanceof Expr\IdenticalTo:
         case $comparison instanceof Expr\EqualTo:
             return $expr->eq($field, $expr->literal($value));
         case $comparison instanceof Expr\NotIdenticalTo:
         case $comparison instanceof Expr\NotEqualTo:
             return $expr->neq($field, $expr->literal($value));
         case $comparison instanceof Expr\LessThan:
             return $expr->lt($field, $expr->literal($value));
         case $comparison instanceof Expr\LessThanOrEqualTo:
             return $expr->lte($field, $expr->literal($value));
         case $comparison instanceof Expr\GreaterThan:
             return $expr->gt($field, $expr->literal($value));
         case $comparison instanceof Expr\GreaterThanOrEqualTo:
             return $expr->gte($field, $expr->literal($value));
         case $comparison instanceof Expr\In:
             return $expr->in($field, $expr->literal($value));
         case $comparison instanceof Expr\NotIn:
             return $expr->notIn($field, $expr->literal($value));
         case $comparison instanceof Expr\Contains:
             return $expr->like(sprintf('LOWER(%s)', $field), $expr->literal('%' . strtolower($value) . '%'));
     }
     throw new InvalidArgumentException(sprintf('Unsupported comparison operator "%s".', get_class($comparison)));
 }