PHP QueryBuilder::innerJoin方法代码示例

 public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
 {
     $paramcount = 0;
     if (!empty($this->gIDs)) {
         $validgids = array();
         foreach ($this->gIDs as $gID) {
             if ($gID > 0) {
                 $validgids[] = $gID;
             }
         }
         if (!empty($validgids)) {
             $query->innerJoin('p', 'VividStoreProductGroups', 'g', 'p.pID = g.pID and g.gID in (' . implode(',', $validgids) . ')');
             if (!$this->groupMatchAny) {
                 $query->having('count(g.gID) = ' . count($validgids));
             }
         }
     }
     switch ($this->sortBy) {
         case "alpha":
             $query->orderBy('pName', 'ASC');
             break;
         case "date":
             $query->orderBy('pDateAdded', 'DESC');
             break;
         case "popular":
             $pr = new StoreProductReport();
             $pr->sortByPopularity();
             $products = $pr->getProducts();
             $pIDs = array();
             foreach ($products as $product) {
                 $pIDs[] = $product['pID'];
             }
             foreach ($pIDs as $pID) {
                 $query->addOrderBy("pID = ?", 'DESC')->setParameter($paramcount++, $pID);
             }
             break;
     }
     switch ($this->featured) {
         case "featured":
             $query->andWhere("pFeatured = 1");
             break;
         case "nonfeatured":
             $query->andWhere("pFeatured = 0");
             break;
     }
     if (!$this->showOutOfStock) {
         $query->andWhere("pQty > 0 OR pQtyUnlim = 1");
     }
     if ($this->activeOnly) {
         $query->andWhere("pActive = 1");
     }
     if (is_array($this->cIDs) && !empty($this->cIDs)) {
         $query->innerJoin('p', 'VividStoreProductLocations', 'l', 'p.pID = l.pID and l.cID in (' . implode(',', $this->cIDs) . ')');
     }
     $query->groupBy('p.pID');
     if ($this->search) {
         $query->andWhere('pName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
     }
     return $query;
 }

 /**
  * @test
  */
 public function innerJoinQuotesIdentifiersAndDelegatesToConcreteQueryBuilder()
 {
     $this->connection->quoteIdentifier('fromAlias')->shouldBeCalled()->willReturnArgument(0);
     $this->connection->quoteIdentifier('join')->shouldBeCalled()->willReturnArgument(0);
     $this->connection->quoteIdentifier('alias')->shouldBeCalled()->willReturnArgument(0);
     $this->concreteQueryBuilder->innerJoin('fromAlias', 'join', 'alias', null)->shouldBeCalled()->willReturn($this->subject);
     $this->subject->innerJoin('fromAlias', 'join', 'alias');
 }

 /**
  * {@inheritdoc}
  * @return NativeQueryBuilder
  */
 public function innerJoin($fromAlias, $join, $alias, $condition = null)
 {
     if ($condition !== NULL) {
         list($condition) = array_values(Helpers::separateParameters($this, array_slice(func_get_args(), 3)));
     }
     return parent::innerJoin($fromAlias, $this->addTableResultMapping($join, $alias, $fromAlias), $alias, $condition);
 }

 /**
  * @inheritdoc
  *
  * @SuppressWarnings(PHPMD.CyclomaticComplexity)
  * @SuppressWarnings(PHPMD.ElseExpression)
  * @SuppressWarnings(PHPMD.NPathComplexity)
  * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
  */
 public function applyFilters(ErrorCollection $errors, QueryBuilder $builder, $modelClass, FilterParameterCollection $filterParams)
 {
     if ($filterParams->count() <= 0) {
         return;
     }
     $whereLink = $filterParams->isWithAnd() === true ? $builder->expr()->andX() : $builder->expr()->orX();
     // while joining tables we select distinct rows. this flag used to apply `distinct` no more than once.
     $hasAppliedDistinct = false;
     $table = $this->getTableName($modelClass);
     $quotedTable = $this->buildTableName($table);
     $modelSchemes = $this->getModelSchemes();
     foreach ($filterParams as $filterParam) {
         /** @var FilterParameterInterface $filterParam */
         $filterValue = $filterParam->getValue();
         // if filter value is not array of 'operation' => parameters (string/array) but
         // just parameters we will apply default operation
         // for example instead of `filter[id][in]=1,2,3,8,9,10` we got `filter[id]=1,2,3,8,9,10`
         if (is_array($filterValue) === false) {
             if (empty($filterValue) === true) {
                 $operation = static::DEFAULT_FILTER_OPERATION_EMPTY;
                 $filterIndexes = null;
             } else {
                 $filterIndexes = explode(',', $filterValue);
                 $numIndexes = count($filterIndexes);
                 $operation = $numIndexes === 1 ? static::DEFAULT_FILTER_OPERATION_SINGLE : static::DEFAULT_FILTER_OPERATION;
             }
             $filterValue = [$operation => $filterIndexes];
         }
         foreach ($filterValue as $operation => $params) {
             $filterTable = null;
             $filterColumn = null;
             $lcOp = strtolower((string) $operation);
             if ($filterParam->isForRelationship() === true) {
                 switch ($filterParam->getRelationshipType()) {
                     case RelationshipTypes::BELONGS_TO:
                         if ($filterParam->isForAttributeInRelationship() === true) {
                             $foreignKey = $modelSchemes->getForeignKey($modelClass, $filterParam->getRelationshipName());
                             list($reverseClass) = $modelSchemes->getReverseRelationship($modelClass, $filterParam->getRelationshipName());
                             $reversePk = $modelSchemes->getPrimaryKey($reverseClass);
                             $filterTable = $modelSchemes->getTable($reverseClass);
                             $filterColumn = $filterParam->getAttributeName();
                             $aliased = $filterTable . $this->getNewAliasId();
                             $joinCondition = $this->buildTableColumnName($table, $foreignKey) . '=' . $this->buildTableColumnName($aliased, $reversePk);
                             $builder->innerJoin($quotedTable, $this->buildTableName($filterTable), $aliased, $joinCondition);
                             if ($hasAppliedDistinct === false) {
                                 $this->distinct($builder, $modelClass);
                                 $hasAppliedDistinct = true;
                             }
                             $filterTable = $aliased;
                         } else {
                             $filterTable = $table;
                             $filterColumn = $modelSchemes->getForeignKey($modelClass, $filterParam->getRelationshipName());
                         }
                         break;
                     case RelationshipTypes::HAS_MANY:
                         // here we join hasMany table and apply filter on its primary key
                         $primaryKey = $modelSchemes->getPrimaryKey($modelClass);
                         list($reverseClass, $reverseName) = $modelSchemes->getReverseRelationship($modelClass, $filterParam->getRelationshipName());
                         $filterTable = $modelSchemes->getTable($reverseClass);
                         $reverseFk = $modelSchemes->getForeignKey($reverseClass, $reverseName);
                         $filterColumn = $filterParam->isForAttributeInRelationship() === true ? $filterParam->getAttributeName() : $modelSchemes->getPrimaryKey($reverseClass);
                         $aliased = $filterTable . $this->getNewAliasId();
                         $joinCondition = $this->buildTableColumnName($table, $primaryKey) . '=' . $this->buildTableColumnName($aliased, $reverseFk);
                         $builder->innerJoin($quotedTable, $this->buildTableName($filterTable), $aliased, $joinCondition);
                         if ($hasAppliedDistinct === false) {
                             $this->distinct($builder, $modelClass);
                             $hasAppliedDistinct = true;
                         }
                         $filterTable = $aliased;
                         break;
                     case RelationshipTypes::BELONGS_TO_MANY:
                         // here we join intermediate belongsToMany table and apply filter on its 2nd foreign key
                         list($intermediateTable, $intermediatePk, $intermediateFk) = $modelSchemes->getBelongsToManyRelationship($modelClass, $filterParam->getRelationshipName());
                         $primaryKey = $modelSchemes->getPrimaryKey($modelClass);
                         $aliased = $intermediateTable . $this->getNewAliasId();
                         $joinCondition = $this->buildTableColumnName($table, $primaryKey) . '=' . $this->buildTableColumnName($aliased, $intermediatePk);
                         $builder->innerJoin($quotedTable, $this->buildTableName($intermediateTable), $aliased, $joinCondition);
                         if ($hasAppliedDistinct === false) {
                             $this->distinct($builder, $modelClass);
                             $hasAppliedDistinct = true;
                         }
                         if ($filterParam->isForAttributeInRelationship() === false) {
                             $filterColumn = $intermediateFk;
                             $filterTable = $aliased;
                         } else {
                             // that's a condition on attribute of resources in relationship
                             // so we have to join that table
                             list($reverseClass) = $modelSchemes->getReverseRelationship($modelClass, $filterParam->getRelationshipName());
                             $reverseTable = $modelSchemes->getTable($reverseClass);
                             $reversePk = $modelSchemes->getPrimaryKey($reverseClass);
                             // now join the table with intermediate
                             $aliased2 = $reverseTable . $this->getNewAliasId();
//.........这里部分代码省略.........

 /**
  * Creates and adds a join to the query.
  *
  * <code>
  *     $qb = $conn->getQueryBuilder()
  *         ->select('u.name')
  *         ->from('users', 'u')
  *         ->innerJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');
  * </code>
  *
  * @param string $fromAlias The alias that points to a from clause.
  * @param string $join The table name to join.
  * @param string $alias The alias of the join table.
  * @param string $condition The condition for the join.
  *
  * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance.
  */
 public function innerJoin($fromAlias, $join, $alias, $condition = null)
 {
     $this->queryBuilder->innerJoin($fromAlias, $this->getTableName($join), $alias, $condition);
     return $this;
 }

 protected function prepareQuery(QueryBuilder $queryBuilder)
 {
     $expr = $queryBuilder->expr();
     if (count($this->filteredGroups)) {
         foreach ($this->filteredGroups as $index => $filteredGroup) {
             $condition = $filteredGroup['membersGroupFilter_condition'];
             $group = $filteredGroup['membersGroupFilter_group'];
             switch ($condition) {
                 case 'in':
                     $where = $expr->orX();
                     // HHFIX
                     // $where->add($expr->like('m.groups', ':groupPattern1_' . $index));
                     // HHENDFIX
                     $where->add($expr->like('m.groups', ':groupPattern2_' . $index));
                     break;
                 case 'not in':
                     $where = $expr->andX();
                     // HHFIX
                     // $where->add($expr->notLike('m.groups', ':groupPattern1_' . $index));
                     // ENDFIX
                     $where->add($expr->notLike('m.groups', ':groupPattern2_' . $index));
                     break;
                 default:
                     continue 2;
             }
             $queryBuilder->andWhere($where)->setParameter('groupPattern2_' . $index, sprintf('%%s:%d:"%d";%%', strlen($group), $group));
         }
     }
     if (count($this->filteredMailingLists)) {
         $queryBuilder->innerJoin('m', 'orm_avisota_subscription', 's', 's.recipientType = :recipientType AND s.recipientId = m.id')->setParameter('recipientType', 'member');
         $or = $expr->orX();
         foreach ($this->filteredMailingLists as $index => $mailingList) {
             $or->add($expr->eq('s.mailingList', ':mailingList' . $index));
             $queryBuilder->setParameter('mailingList' . $index, $mailingList->getId());
         }
         $queryBuilder->andWhere($or);
     }
     if (count($this->filteredProperties)) {
         foreach ($this->filteredProperties as $index => $filteredProperty) {
             $property = 'm.' . $filteredProperty['membersPropertyFilter_property'];
             $comparator = $filteredProperty['membersPropertyFilter_comparator'];
             $value = $filteredProperty['membersPropertyFilter_value'];
             switch ($comparator) {
                 case 'empty':
                     $queryBuilder->andWhere($expr->orX($expr->eq($property, ':property' . $index), $expr->isNull($property)));
                     $value = '';
                     break;
                 case 'not empty':
                     $queryBuilder->andWhere($expr->gt($property, ':property' . $index));
                     $value = '';
                     break;
                 case 'eq':
                     $queryBuilder->andWhere($expr->eq($property, ':property' . $index));
                     break;
                 case 'neq':
                     $queryBuilder->andWhere($expr->neq($property, ':property' . $index));
                     break;
                 case 'gt':
                     $queryBuilder->andWhere($expr->gt($property, ':property' . $index));
                     break;
                 case 'gte':
                     $queryBuilder->andWhere($expr->gte($property, ':property' . $index));
                     break;
                 case 'lt':
                     $queryBuilder->andWhere($expr->lt($property, ':property' . $index));
                     break;
                 case 'lte':
                     $queryBuilder->andWhere($expr->lte($property, ':property' . $index));
                     break;
             }
             $queryBuilder->setParameter(':property' . $index, $value);
         }
     }
 }

 /**
  * {@inheritDoc}
  */
 public function innerJoin($fromAlias, $join, $alias, $condition = null)
 {
     $join = $this->getTableName($join);
     return parent::innerJoin($fromAlias, $join, $alias, $condition);
 }

 /**
  * Sort products for current category by passed sort type
  *
  * @param QueryBuilder $builder
  * @param integer $orderBy
  */
 private function sortUnsortedByDefault($builder, $orderBy)
 {
     switch ($orderBy) {
         case 1:
             $builder->addOrderBy('product.datum', 'DESC')->addOrderBy('product.changetime', 'DESC');
             break;
         case 2:
             $builder->leftJoin('product', 's_articles_top_seller_ro', 'topSeller', 'topSeller.article_id = product.id')->addOrderBy('topSeller.sales', 'DESC')->addOrderBy('topSeller.article_id', 'DESC');
             break;
         case 3:
             $builder->addSelect('MIN(ROUND(defaultPrice.price * priceVariant.minpurchase * 1, 2)) as cheapest_price')->leftJoin('product', 's_articles_prices', 'defaultPrice', 'defaultPrice.articleID = product.id')->innerJoin('defaultPrice', 's_articles_details', 'priceVariant', 'priceVariant.id = defaultPrice.articledetailsID')->addOrderBy('cheapest_price', 'ASC')->addOrderBy('product.id', 'DESC');
             break;
         case 4:
             $builder->addSelect('MIN(ROUND(defaultPrice.price * priceVariant.minpurchase * 1, 2)) as cheapest_price')->leftJoin('product', 's_articles_prices', 'defaultPrice', 'defaultPrice.articleID = product.id')->innerJoin('defaultPrice', 's_articles_details', 'priceVariant', 'priceVariant.id = defaultPrice.articledetailsID')->addOrderBy('cheapest_price', 'DESC')->addOrderBy('product.id', 'DESC');
             break;
         case 5:
             $builder->addOrderBy('product.name', 'ASC');
             break;
         case 6:
             $builder->addOrderBy('product.name', 'DESC');
             break;
         case 7:
             $builder->addSelect('(SUM(vote.points) / COUNT(vote.id)) as votes')->leftJoin('product', 's_articles_vote', 'vote', 'product.id = vote.articleID')->addOrderBy('votes', 'DESC')->addOrderBy('product.id', 'DESC')->groupBy('product.id');
             break;
         case 9:
             $builder->innerJoin('product', 's_articles_details', 'variant', 'variant.id = product.main_detail_id')->addOrderBy('variant.instock', 'ASC')->addOrderBy('product.id', 'DESC');
             break;
         case 10:
             $builder->innerJoin('product', 's_articles_details', 'variant', 'variant.id = product.main_detail_id')->addOrderBy('variant.instock', 'DESC')->addOrderBy('product.id', 'DESC');
             break;
     }
 }

 /**
  * Creates and adds a join to the query.
  *
  * @param string $fromAlias The alias that points to a from clause.
  * @param string $join      The table name to join.
  * @param string $alias     The alias of the join table.
  * @param string $condition The condition for the join.
  *
  * @return self
  */
 public function innerJoin($fromAlias, $join, $alias, $condition = null)
 {
     $this->qb->innerJoin($fromAlias, $join, $alias, $condition);
     return $this;
 }

 /**
  * Creates and adds a join to the query.
  *
  * <code>
  *     $qb = $conn->getQueryBuilder()
  *         ->select('u.name')
  *         ->from('users', 'u')
  *         ->innerJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');
  * </code>
  *
  * @param string $fromAlias The alias that points to a from clause.
  * @param string $join The table name to join.
  * @param string $alias The alias of the join table.
  * @param string $condition The condition for the join.
  *
  * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance.
  */
 public function innerJoin($fromAlias, $join, $alias, $condition = null)
 {
     $this->queryBuilder->innerJoin($fromAlias, $this->helper->quoteColumnName($join), $alias, $condition);
     return $this;
 }

 private function applyQueryFilter(QueryBuilder $query, Filter $filter)
 {
     $query->where('u.enabled = 1');
     if ($filter->isEmpty()) {
         return;
     }
     if ($nt = $filter->getNotificationType()) {
         // JOIN NotificationSubscriptions ns ON (u.id = ns.idUser AND ns.idNotificationType = x)
         $query->innerJoin('u', 'NotificationSubscriptions', 'ns', 'u.id = ns.idUser AND ns.idNotificationType = :ntype');
         $query->setParameter('ntype', $nt->getId());
     }
     if ($status = $filter->getUserStatus()) {
         $query->andWhere('u.status = :status');
         $query->setParameter('status', $status);
     }
     $sslist = $filter->getScholarships();
     if (!empty($sslist)) {
         $ssids = [];
         foreach ($sslist as $scholarship) {
             $ssids[] = $scholarship->getId();
         }
         $ssids = implode(',', $ssids);
         $query->innerJoin('u', 'Entries', 'e', 'u.id = e.idUser AND e.idScholarship IN(' . $ssids . ')');
     }
     if (class_exists('GotChosen\\SiteBundle\\Entity\\EGGame') && $filter->hasSubmittedGame()) {
         $query->innerJoin('u', 'Games', 'g', 'u.id = g.user_id');
     }
     if ($lang = $filter->getLanguage()) {
         $query->innerJoin('u', 'UserProfile', 'up', 'u.id = up.user_id AND up.property_id = 19 AND up.propertyValue = :lang');
         $query->setParameter('lang', $lang);
     }
 }

 /**
  * @param $queryBuilder
  * @param $expressionBuilder
  */
 protected function addFilteredMailingLists(QueryBuilder &$queryBuilder, ExpressionBuilder $expressionBuilder)
 {
     if (!count($this->filteredMailingLists)) {
         return;
     }
     $queryBuilder->innerJoin('m', 'orm_avisota_subscription', 's', 's.recipientType = :recipientType AND s.recipientId = m.id');
     $queryBuilder->setParameter('recipientType', 'member');
     $orExpression = $expressionBuilder->orX();
     foreach ($this->filteredMailingLists as $index => $mailingList) {
         $orExpression->add($expressionBuilder->eq('s.mailingList', ':mailingList' . $index));
         $queryBuilder->setParameter('mailingList' . $index, $mailingList->getId());
     }
     $queryBuilder->andWhere($orExpression);
 }