$qb = $conn->createQueryBuilder()
->select('u.name')
->from('users', 'u')
->innerJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');
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(); $joinCondition = $this->buildTableColumnName($aliased, $intermediateFk) . '=' . $this->buildTableColumnName($aliased2, $reversePk); $builder->innerJoin($aliased, $this->buildTableName($reverseTable), $aliased2, $joinCondition); $filterColumn = $filterParam->getAttributeName(); $filterTable = $aliased2; } break; } } else { // param for attribute $filterTable = $table; $filterColumn = $filterParam->getAttributeName(); } // here $filterTable and $filterColumn should always be not null (if not it's a bug in logic) $this->applyFilterToQuery($errors, $builder, $whereLink, $filterParam, $filterTable, $filterColumn, $lcOp, $params); } } $builder->andWhere($whereLink); }
/** * 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; }
/** * 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 QueryBuilder This QueryBuilder instance. */ public function innerJoin(string $fromAlias, string $join, string $alias, string $condition = null) : QueryBuilder { $this->concreteQueryBuilder->innerJoin($this->quoteIdentifier($fromAlias), $this->quoteIdentifier($join), $this->quoteIdentifier($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); }