PHP QueryBuilder::leftJoin方法代码示例

 /**
  * método que adiciona a clausula where
  * @param query
  * @return query
  */
 public function addWhere(\Doctrine\ORM\QueryBuilder &$query, \Core_Dto_Search $search, $nuCpfCnpjPassaporte)
 {
     $sqPessoa = $search->getSqPessoaCorporativo() ?: $search->getSqPessoa();
     if ($sqPessoa) {
         $query->andWhere('p.sqPessoa = :sqPessoa')->setParameter('sqPessoa', $sqPessoa);
     }
     switch ($search->getSqTipoPessoa()) {
         case \Core_Configuration::getSgdoceTipoPessoaPessoaFisica():
             $passaporte = $this->getDocumento();
             $query->leftJoin('p.sqPessoaFisica', 'pf');
             //passar cpf ou passaporte , passa parametro
             if ($nuCpfCnpjPassaporte != '') {
                 if ($search->getSqNacionalidade() == '1') {
                     $query->andWhere('pf.nuCpf = :nuCpf')->setParameter('nuCpf', $nuCpfCnpjPassaporte);
                 } else {
                     $query->leftJoin("p.sqPessoaDocumento", "d", "WITH", "d.sqAtributoTipoDocumento = :sqAtributoTipoDocumento")->setParameter("sqAtributoTipoDocumento", $passaporte['sqAtributoTipoDocumento'])->andWhere('pf.sqNacionalidade <> :sqNacionalidade')->setParameter('sqNacionalidade', '1')->andWhere('d.txValor = :txValor')->setParameter('txValor', $nuCpfCnpjPassaporte);
                 }
             }
             break;
         case \Core_Configuration::getSgdoceTipoPessoaPessoaJuridica():
             $query->leftJoin('p.sqPessoaJuridica', 'pj');
             if ($nuCpfCnpjPassaporte != '') {
                 $query->andWhere('pj.nuCnpj = :nuCnpj')->setParameter('nuCnpj', $nuCpfCnpjPassaporte);
             }
             break;
         case \Core_Configuration::getSgdoceTipoPessoaMinisterioPublico():
             $query->leftJoin('p.sqUnidadeOrgInterna', 'ui');
             $query->leftJoin('p.sqRppn', 'rpn');
             break;
         case \Core_Configuration::getSgdoceTipoPessoaOutrosOrgaos():
             $query->leftJoin('p.sqUnidadeOrgExterna', 'ue');
             break;
     }
 }

 public function alterQueryBuilder(QueryBuilder $queryBuilder)
 {
     if ($this->year !== null) {
         $queryBuilder->andWhere('YEAR(transaction.date) = :year');
         $queryBuilder->setParameter(':year', $this->year);
     }
     if ($this->month && in_array($this->month, array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))) {
         $queryBuilder->andWhere('MONTH(transaction.date) = :month');
         $queryBuilder->setParameter(':month', $this->month);
     }
     if ($this->processed != null && in_array($this->processed, array(0, 1))) {
         $queryBuilder->andWhere('transaction.is_processed = :processed');
         $queryBuilder->setParameter(':processed', $this->processed ? true : false);
     }
     if ($this->tag != null) {
         $queryBuilder->leftJoin('transaction.tags', 'tag');
         $queryBuilder->andWhere('tag.slug = :tag');
         $queryBuilder->setParameter(':tag', $this->tag);
     }
     if ($this->category !== null) {
         if ($this->category == 'empty') {
             $queryBuilder->andWhere('transaction.category IS NULL');
         } else {
             $queryBuilder->leftJoin('transaction.category', 'category');
             $queryBuilder->leftJoin('category.parent', 'parent');
             $queryBuilder->andWhere('category.slug = :category or parent.slug = :category');
             $queryBuilder->setParameter(':category', $this->category);
         }
     }
     return $queryBuilder;
 }

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

 /**
  * {@inheritdoc}
  */
 protected function addJoinStatement($joinType, $join, $joinAlias, $joinConditionType, $joinCondition)
 {
     if ('left' === $joinType) {
         $this->qb->leftJoin($join, $joinAlias, $joinConditionType, $joinCondition);
     } else {
         $this->qb->innerJoin($join, $joinAlias, $joinConditionType, $joinCondition);
     }
 }

 /**
  * Add completeness joins to query builder
  *
  * @param string $completenessAlias the join alias
  * @param string $locale            the locale
  * @param sting  $scope             the scope
  *
  * @return CompletenessJoin
  */
 public function addJoins($completenessAlias, $locale, $scope)
 {
     $rootAlias = $this->qb->getRootAlias();
     $localeAlias = $completenessAlias . 'Locale';
     $channelAlias = $completenessAlias . 'Channel';
     $rootEntity = current($this->qb->getRootEntities());
     $completenessMapping = $this->qb->getEntityManager()->getClassMetadata($rootEntity)->getAssociationMapping('completenesses');
     $completenessClass = $completenessMapping['targetEntity'];
     $this->qb->leftJoin('PimCatalogBundle:Locale', $localeAlias, 'WITH', $localeAlias . '.code = :cLocaleCode')->leftJoin('PimCatalogBundle:Channel', $channelAlias, 'WITH', $channelAlias . '.code = :cScopeCode')->leftJoin($completenessClass, $completenessAlias, 'WITH', $completenessAlias . '.locale = ' . $localeAlias . '.id AND ' . $completenessAlias . '.channel = ' . $channelAlias . '.id AND ' . $completenessAlias . '.product = ' . $rootAlias . '.id')->setParameter('cLocaleCode', $locale)->setParameter('cScopeCode', $scope);
     return $this;
 }

 /**
  * {@inheritdoc}
  */
 public function addFieldSorter($field, $direction)
 {
     $rootAlias = $this->qb->getRootAlias();
     $prefix = 'sorter';
     $field = $prefix . 'familyLabel';
     $family = $prefix . 'family';
     $trans = $prefix . 'familyTranslations';
     $this->qb->leftJoin($rootAlias . '.family', $family)->leftJoin($family . '.translations', $trans, 'WITH', $trans . '.locale = :dataLocale');
     $this->qb->addSelect('COALESCE(' . $trans . '.label, CONCAT(\'[\', ' . $family . '.code, \']\')) as ' . $field);
     $this->qb->addOrderBy($field, $direction);
     return $this;
 }

 /** ************************************************************************
  * Filter the result which are after startDate and/or before endDate
  * @param QueryBuilder $qb
  * @param array $parameters
  **************************************************************************/
 protected function filterByAccount(QueryBuilder $qb, array $parameters)
 {
     if (isset($parameters['account'])) {
         if ($parameters['account'] == NULL) {
             $qb->leftJoin('FinanceOperationBundle:Operation', 'o', 'WITH', $qb->expr()->eq('ao.id', 'o.id'));
             $qb->leftJoin('FinanceOperationBundle:TransferBetweenAccount', 'it', 'WITH', $qb->expr()->eq('ao.id', 'it.id'));
             $qb->leftJoin('FinanceOperationBundle:TransferBetweenAccount', 'ot', 'WITH', $qb->expr()->eq('ao.id', 'ot.id'));
         } else {
             $qb->leftJoin('FinanceOperationBundle:Operation', 'o', 'WITH', $qb->expr()->andX($qb->expr()->eq('ao.id', 'o.id'), $qb->expr()->eq('o.account', ':account')))->setParameter('account', $parameters['account'])->leftJoin('FinanceOperationBundle:TransferBetweenAccount', 'it', 'WITH', $qb->expr()->andX($qb->expr()->eq('ao.id', 'it.id'), $qb->expr()->eq('it.destinationAccount', ':account')))->setParameter('account', $parameters['account'])->leftJoin('FinanceOperationBundle:TransferBetweenAccount', 'ot', 'WITH', $qb->expr()->andX($qb->expr()->eq('ao.id', 'ot.id'), $qb->expr()->eq('ot.sourceAccount', ':account')))->setParameter('account', $parameters['account']);
         }
     }
 }

 /**
  * {@inheritdoc}
  */
 public function addAttributeFilter(AbstractAttribute $attribute, $operator, $value)
 {
     $joinAlias = 'filter' . $attribute->getCode() . $this->aliasCounter++;
     $backendField = sprintf('%s.%s', $joinAlias, $attribute->getBackendType());
     if ($operator === 'EMPTY') {
         $this->qb->leftJoin($this->qb->getRootAlias() . '.values', $joinAlias, 'WITH', $this->prepareAttributeJoinCondition($attribute, $joinAlias));
         $this->qb->andWhere($this->prepareCriteriaCondition($backendField, $operator, $value));
     } else {
         $condition = $this->prepareAttributeJoinCondition($attribute, $joinAlias);
         $condition .= ' AND ' . $this->prepareCriteriaCondition($backendField, $operator, $value);
         $this->qb->innerJoin($this->qb->getRootAlias() . '.values', $joinAlias, 'WITH', $condition);
     }
     return $this;
 }

 /**
  * Adds joins and conditions to the QueryBuilder in order to only return entities the given user is allowed to see.
  *
  * @param QueryBuilder $queryBuilder The instance of the QueryBuilder to adjust
  * @param UserInterface $user The user for which the access control is checked
  * @param int $permission The permission mask for which is checked
  * @param string $entityClass The class of the entity of which the access control is checked
  * @param string $entityAlias The alias of the entity used in the query builder
  */
 protected function addAccessControl(QueryBuilder $queryBuilder, UserInterface $user, $permission, $entityClass, $entityAlias)
 {
     $queryBuilder->leftJoin(AccessControl::class, 'accessControl', 'WITH', 'accessControl.entityClass = :entityClass AND accessControl.entityId = ' . $entityAlias . '.id');
     $queryBuilder->leftJoin('accessControl.role', 'role');
     $queryBuilder->andWhere('BIT_AND(accessControl.permissions, :permission) = :permission OR accessControl.permissions IS NULL');
     $roleIds = [];
     foreach ($user->getRoleObjects() as $role) {
         $roleIds[] = $role->getId();
     }
     $queryBuilder->andWhere('role.id IN(:roleIds) OR role.id IS NULL');
     $queryBuilder->setParameter('roleIds', $roleIds);
     $queryBuilder->setParameter('entityClass', $entityClass);
     $queryBuilder->setParameter('permission', $permission);
 }

 /**
  * {@inheritdoc}
  */
 public function addAttributeSorter(AttributeInterface $attribute, $direction, $locale = null, $scope = null)
 {
     $aliasPrefix = 'sorter';
     $joinAlias = $aliasPrefix . 'V' . $attribute->getCode();
     $backendType = $attribute->getBackendType();
     // join to value
     $condition = $this->prepareAttributeJoinCondition($attribute, $joinAlias, $locale, $scope);
     $this->qb->leftJoin($this->qb->getRootAlias() . '.values', $joinAlias, 'WITH', $condition);
     $joinAliasMetric = $aliasPrefix . 'M' . $attribute->getCode();
     $this->qb->leftJoin($joinAlias . '.' . $backendType, $joinAliasMetric);
     $this->qb->addOrderBy($joinAliasMetric . '.baseData', $direction);
     $idField = $this->qb->getRootAlias() . '.id';
     $this->qb->addOrderBy($idField);
     return $this;
 }

 /**
  * {@inheritdoc}
  */
 public function entityJoin(array $associationMappings)
 {
     $alias = $this->queryBuilder->getRootAlias();
     $newAlias = 's';
     $joinedEntities = $this->queryBuilder->getDQLPart('join');
     foreach ($associationMappings as $associationMapping) {
         // Do not add left join to already joined entities with custom query
         foreach ($joinedEntities as $joinExprList) {
             foreach ($joinExprList as $joinExpr) {
                 $newAliasTmp = $joinExpr->getAlias();
                 if (sprintf('%s.%s', $alias, $associationMapping['fieldName']) === $joinExpr->getJoin()) {
                     $this->entityJoinAliases[] = $newAliasTmp;
                     $alias = $newAliasTmp;
                     continue 3;
                 }
             }
         }
         $newAlias .= '_' . $associationMapping['fieldName'];
         if (!in_array($newAlias, $this->entityJoinAliases)) {
             $this->entityJoinAliases[] = $newAlias;
             $this->queryBuilder->leftJoin(sprintf('%s.%s', $alias, $associationMapping['fieldName']), $newAlias);
         }
         $alias = $newAlias;
     }
     return $alias;
 }

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

 /**
  * (non-PHPdoc)
  * @see \PartKeepr\Manager\AbstractManager::applyCustomQuery()
  */
 protected function applyCustomQuery(QueryBuilder $qb, ManagerFilter $filter)
 {
     /**
      * Pull in additional tables
      */
     $qb->leftJoin("q.target", "ta")->leftJoin("q.owner", "ow")->leftJoin("q.data", "da");
 }

 /**
  * @param ClientConditionInterface $condition
  */
 protected function prepareTagsCondition(ClientConditionInterface $condition)
 {
     if ($condition->getTags() && count($condition->getTags())) {
         $this->qb->leftJoin('c.tags', 't');
         $this->qb->andWhere($this->qb->expr()->in('t.id', ':tags'))->setParameter('tags', $condition->getTags());
     }
 }

 /**
  * @param QueryBuilder $qb
  * @return $this
  */
 public function prepareQuery(QueryBuilder $qb)
 {
     foreach ($this->emailOwnerFieldNames as $fieldName) {
         $qb->leftJoin('a.' . $fieldName, $fieldName);
     }
     return $this;
 }