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