/** * @inheritdoc */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { if (!$query->hasState(self::STATE_ESD_INCLUDED)) { $query->innerJoin('product', 's_articles_esd', 'esd', 'esd.articleID = product.id'); $query->addState(self::STATE_ESD_INCLUDED); } }
/** * Handles the passed condition object. * Extends the provided query builder with the specify conditions. * Should use the andWhere function, otherwise other conditions would be overwritten. * * @param MinStockCondition $condition * @param QueryBuilder $query * @param ShopContextInterface $context * @return void */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { if (!$condition->hasMinStock()) { return; } $query->andWhere('variantFilterArticleDetails.instock >= :minStock')->setParameter(':minStock', $condition->getMinStock()); }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { $query->innerJoin('product', 's_articles_details', 'allVariants', 'allVariants.articleID = product.id AND allVariants.active = 1 AND allVariants.instock >= allVariants.minpurchase'); $query->addState(self::STATE_INCLUDES_ALL_VARIANTS); }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { $query->innerJoin('product', 's_articles_supplier', 'manufacturer', 'manufacturer.id = product.supplierID AND product.supplierID IN (:manufacturer)'); /** @var ManufacturerCondition $condition */ $query->setParameter(':manufacturer', $condition->getManufacturerIds(), Connection::PARAM_INT_ARRAY); }
/** * {@inheritdoc} */ public function generateSorting(SortingInterface $sorting, QueryBuilder $query, ShopContextInterface $context) { /** @var ProductAttributeSorting $sorting */ if (!$sorting->getField()) { throw new \Exception('ProductAttributeSorting class requires a defined attribute field!'); } $query->addOrderBy('productAttribute.' . $sorting->getField(), $sorting->getDirection()); }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { $query->leftJoin('product', 's_articles_avoid_customergroups', 'avoidCustomerGroup', 'avoidCustomerGroup.articleID = product.id AND avoidCustomerGroup.customerGroupId IN (:customerGroupIds)'); /** @var CustomerGroupCondition $condition */ $query->setParameter(':customerGroupIds', $condition->getCustomerGroupIds(), Connection::PARAM_INT_ARRAY); $query->andWhere('avoidCustomerGroup.articleID IS NULL'); }
/** * Extends the query and returns the alias to bind the definition to. * * @param QueryBuilder $queryBuilder * @param AbstractSortDefinition $definition * * @return string */ public function extendQuery(QueryBuilder $queryBuilder, AbstractSortDefinition $definition) { $alias = $this->createAlias('Variant'); if ($queryBuilder->hasState($alias)) { return $alias; } $queryBuilder->addState($alias); return 'variant'; }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { /**@var CreateDateCondition $condition */ $date = new \DateTime(); $intervalSpec = 'P' . $condition->getDays() . 'D'; $interval = new \DateInterval($intervalSpec); $date->sub($interval); $query->andWhere('product.datum >= :createDateFrom')->setParameter(':createDateFrom', $date->format('Y-m-d')); }
/** * {@inheritdoc} */ public function generateSorting(SortingInterface $sorting, QueryBuilder $query, ShopContextInterface $context) { if (!$query->hasState(SearchTermConditionHandler::STATE_INCLUDES_RANKING)) { return; } /** @var SearchRankingSorting $sorting */ $query->addOrderBy('searchTable.ranking', $sorting->getDirection()); $query->addOrderBy('product.id', $sorting->getDirection()); }
/** * {@inheritdoc} */ public function generateSorting(SortingInterface $sorting, QueryBuilder $query, ShopContextInterface $context) { if (!$query->hasState(SalesConditionHandler::STATE_INCLUDES_TOPSELLER_TABLE)) { $query->leftJoin('product', 's_articles_top_seller_ro', 'topSeller', 'topSeller.article_id = product.id'); $query->addState(SalesConditionHandler::STATE_INCLUDES_TOPSELLER_TABLE); } /** @var PopularitySorting $sorting */ $query->addOrderBy('topSeller.sales', $sorting->getDirection())->addOrderBy('topSeller.article_id', $sorting->getDirection()); }
/** * Extends the query and returns the alias to bind the definitition to. * * @param QueryBuilder $queryBuilder * * @return string */ public function extendQuery(QueryBuilder $queryBuilder, AbstractSortDefinition $definition) { $alias = $this->createAlias('Variant'); if ($queryBuilder->hasState($alias)) { return $alias; } $queryBuilder->leftJoin('product', $this->getTableName(), $alias, $alias . '.articleID = product.id'); $queryBuilder->addState($alias); return $alias; }
public function extendQuery($alias, AbstractSortDefinition $definition, RuleVo $rule, QueryBuilder $queryBuilder) { $sort = $this->getOrderByProvider()->getSort($alias, $definition); $order = $this->getOrderByProvider()->getOrder($rule); foreach ($this->getDataFilters() as $filter) { $filter->setUp($alias, $definition, $rule); $sort = $filter->filterSort($sort); $order = $filter->filterOrder($order); } $queryBuilder->addOrderBy($sort, $order); }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { if ($this->counter++ === 0) { $suffix = ''; } else { $suffix = $this->counter; } $query->innerJoin('product', 's_articles_categories_ro', "productCategory{$suffix}", "productCategory{$suffix}.articleID = product.id\n AND productCategory{$suffix}.categoryID IN (:category{$suffix})"); /** @var CategoryCondition $condition */ $query->setParameter(":category{$suffix}", $condition->getCategoryIds(), Connection::PARAM_INT_ARRAY); }
/** * Extends the query and returns the alias to bind the definition to. * * @param QueryBuilder $queryBuilder * @param AbstractSortDefinition $definition * * @return string join alias */ public function extendQuery(QueryBuilder $queryBuilder, AbstractSortDefinition $definition) { $alias = $this->createAlias('Select'); if ($queryBuilder->hasState($alias)) { return $alias; } $subQueryAlias = $this->createAlias('Inner'); $subQueryBuilder = Shopware()->Models()->getDBALQueryBuilder(); $subQueryBuilder->select($this->getSelectWithExpression($definition, $subQueryAlias))->from($this->getTableName(), $subQueryAlias)->where('product.id = ' . $subQueryAlias . '.articleID'); $queryBuilder->addSelect('(' . $subQueryBuilder->getSQL() . ') AS ' . $alias . '_' . $definition->getFieldName()); return $alias; }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { $table = ' SELECT SUM(vote.points) / COUNT(vote.id) AS average, vote.articleID AS product_id FROM s_articles_vote vote GROUP BY vote.articleID'; $query->innerJoin('product', '(' . $table . ')', 'voteAverage', 'voteAverage.product_id = product.id AND voteAverage.average >= :average'); /** @var VoteAverageCondition $condition */ $query->setParameter(':average', (double) $condition->getAverage()); $query->addSelect('voteAverage.average'); $query->addState(VoteAverageCondition::STATE_INCLUDES_VOTE_TABLE); }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { $ids = $condition->getProductVariantIds(); if (!$ids) { return; } $query->innerJoin('product', 's_articles_details', 'variantFilterArticleDetails', 'variantFilterArticleDetails.articleID = product.id'); foreach ($ids as $groupId => $variantOptions) { $tableAlias = 'variantFilterArticleDetails' . $groupId; $paramAlias = ':options' . $groupId; $query->innerJoin('variantFilterArticleDetails', 's_article_configurator_option_relations', $tableAlias, 'variantFilterArticleDetails.id = ' . $tableAlias . '.article_id AND ' . $tableAlias . '.option_id IN (' . $paramAlias . ')')->setParameter($paramAlias, $variantOptions, Connection::PARAM_INT_ARRAY); } }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { $tableKey = $condition->getName(); $where = []; /** @var PropertyCondition $condition */ foreach ($condition->getValueIds() as $valueId) { $valueKey = $tableKey . '_' . $valueId; $where[] = $tableKey . '.valueID = :' . $valueKey; $query->setParameter(':' . $valueKey, $valueId); } $where = implode(' OR ', $where); $query->innerJoin('product', 's_filter_articles', $tableKey, 'product.id = ' . $tableKey . '.articleID AND (' . $where . ')'); }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { /** @var SearchTermCondition $condition */ $searchQuery = $this->searchTermQueryBuilder->buildQuery($condition->getTerm()); //no matching products found by the search query builder. //add condition that the result contains no product. if ($searchQuery == null) { $query->andWhere('0 = 1'); return; } $queryString = $searchQuery->getSQL(); $query->addSelect('searchTable.*'); $query->addState(self::STATE_INCLUDES_RANKING); $query->innerJoin('product', '(' . $queryString . ')', 'searchTable', 'searchTable.product_id = product.id'); }
/** * @param SortingInterface $sorting * @param QueryBuilder $query * @param ShopContextInterface $context * @throws \Exception */ public function generateSorting(SortingInterface $sorting, QueryBuilder $query, ShopContextInterface $context) { /** @var Listing $categoryComponent */ $categoryComponent = Shopware()->Container()->get('swagcustomsort.listing_component'); $categoryId = Shopware()->Front()->Request()->getParam('sCategory'); $linkedCategoryId = $categoryComponent->getLinkedCategoryId($categoryId); $hasCustomSort = $categoryComponent->hasCustomSort($categoryId); $baseSort = $categoryComponent->getCategoryBaseSort($categoryId); if ($hasCustomSort || $baseSort > 0) { $baseSorting = $categoryComponent->getCategoryBaseSort($categoryId); } else { $baseSorting = Shopware()->Config()->get('defaultListingSorting'); } //apply 'plugin' order if ($linkedCategoryId) { $query->leftJoin('productCategory', 's_articles_sort', 'customSort', 'customSort.articleId = productCategory.articleID AND (customSort.categoryId = :sortCategoryId OR customSort.categoryId IS NULL)'); $query->setParameter('sortCategoryId', $linkedCategoryId); } else { $query->leftJoin('productCategory', 's_articles_sort', 'customSort', 'customSort.articleId = productCategory.articleID AND (customSort.categoryId = productCategory.categoryID OR customSort.categoryId IS NULL)'); } //exclude passed products ids from result $sortedProductsIds = $this->sortingComponent->getSortedProductsIds(); if ($sortedProductsIds) { $query->andWhere($query->expr()->notIn("product.id", $sortedProductsIds)); } //for records with no 'plugin' order data use the default shopware order $handlerData = $this->getDefaultData($baseSorting); if ($handlerData) { $sorting->setDirection($handlerData['direction']); $handlerData['handler']->generateSorting($sorting, $query, $context); } }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { /** @var ProductAttributeCondition $condition */ if (!$condition->getField()) { throw new \Exception('ProductAttributeCondition class requires a defined attribute field!'); } if (!$condition->getOperator()) { throw new \Exception('ProductAttributeCondition class requires a defined operator!'); } $placeholder = ':' . $condition->getField(); $field = 'productAttribute.' . $condition->getField(); switch (true) { case $condition->getValue() === null: if ($condition->getOperator() === ProductAttributeCondition::OPERATOR_EQ) { $query->andWhere($field . ' IS NULL'); } else { $query->andWhere($field . ' IS NOT NULL'); } break; case $condition->getOperator() === ProductAttributeCondition::OPERATOR_IN: $query->andWhere($field . ' IN (' . $placeholder . ')'); $query->setParameter($placeholder, $condition->getValue(), Connection::PARAM_STR_ARRAY); break; case $condition->getOperator() === ProductAttributeCondition::OPERATOR_CONTAINS: $query->andWhere($field . ' LIKE ' . $placeholder); $query->setParameter($placeholder, '%' . $condition->getValue() . '%'); break; case $condition->getOperator() === ProductAttributeCondition::OPERATOR_BETWEEN: $value = $condition->getValue(); if (isset($value['min'])) { $query->andWhere($field . ' >= ' . $placeholder . 'Min')->setParameter($placeholder . 'Min', $value['min']); } if (isset($value['max'])) { $query->andWhere($field . ' <= ' . $placeholder . 'Max')->setParameter($placeholder . 'Max', $value['max']); } break; case $condition->getOperator() === ProductAttributeCondition::OPERATOR_STARTS_WITH: $query->andWhere($field . ' LIKE ' . $placeholder); $query->setParameter($placeholder, $condition->getValue() . '%'); break; case $condition->getOperator() === ProductAttributeCondition::OPERATOR_ENDS_WITH: $query->andWhere($field . ' LIKE ' . $placeholder); $query->setParameter($placeholder, '%' . $condition->getValue()); break; default: $query->andWhere($field . ' ' . $condition->getOperator() . ' ' . $placeholder); $query->setParameter($placeholder, $condition->getValue()); break; } }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { if (!$query->hasState(self::STATE_INCLUDES_TOPSELLER_TABLE)) { $query->leftJoin('product', 's_articles_top_seller_ro', 'topSeller', 'topSeller.article_id = product.id'); $query->addState(self::STATE_INCLUDES_TOPSELLER_TABLE); } $query->andWhere('topSeller.sales > :sales'); /** @var SalesCondition $condition */ $query->setParameter('sales', $condition->getMinSales()); }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { $date = new \DateTime(); /** @var ReleaseDateCondition $condition */ $intervalSpec = 'P' . $condition->getDays() . 'D'; $interval = new \DateInterval($intervalSpec); $dateNow = new \DateTime(); switch ($condition->getDirection()) { case ReleaseDateCondition::DIRECTION_FUTURE: $date->add($interval); $query->andWhere('variant.releasedate <= :releaseDateFrom'); $query->andWhere('variant.releasedate > :dateNow'); $query->setParameter(':releaseDateFrom', $date->format('Y-m-d')); $query->setParameter(':dateNow', $dateNow->format('Y-m-d')); break; case ReleaseDateCondition::DIRECTION_PAST: $date->sub($interval); $query->andWhere('variant.releasedate >= :releaseDateTo'); $query->andWhere('variant.releasedate <= :dateNow'); $query->setParameter(':releaseDateTo', $date->format('Y-m-d')); $query->setParameter(':dateNow', $dateNow->format('Y-m-d')); break; } }
/** * {@inheritdoc} */ public function generateSorting(SortingInterface $sorting, QueryBuilder $query, ShopContextInterface $context) { $query->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'); }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { $query->andWhere('variant.ordernumber IN (:ordernumbers)'); /** @var OrdernumberCondition $condition */ $query->setParameter(':ordernumbers', $condition->getOrdernumbers(), Connection::PARAM_STR_ARRAY); }
/** * @param QueryBuilder $query * @param Struct\ShopContextInterface $context */ private function addTranslations($query, $context) { if ($context->getShop()->isDefault()) { return; } $query->addSelect('attributeTranslations.objectdata as __attribute_translation')->leftJoin('product', 's_core_translations', 'attributeTranslations', 'attributeTranslations.objectkey = product.id AND attributeTranslations.objecttype = "article" AND attributeTranslations.objectlanguage = :language'); $query->setParameter(':language', $context->getShop()->getId()); if (!$context->getShop()->getFallbackId() || $context->getShop()->getFallbackId() === $context->getShop()->getId()) { return; } $query->addSelect('attributeTranslations_fallback.objectdata as __attribute_translation_fallback')->leftJoin('product', 's_core_translations', 'attributeTranslations_fallback', 'attributeTranslations_fallback.objectkey = product.id AND attributeTranslations_fallback.objecttype = "article" AND attributeTranslations_fallback.objectlanguage = :languageFallback'); $query->setParameter(':languageFallback', $context->getShop()->getFallbackId()); }
/** * Calculated the total count of the whole search result. * * @param QueryBuilder $query * @return int */ private function getTotalCount($query) { return $query->getConnection()->fetchColumn('SELECT FOUND_ROWS()'); }
/** * {@inheritdoc} */ public function generateCondition(ConditionInterface $condition, QueryBuilder $query, ShopContextInterface $context) { $query->andWhere('variant.shippingfree = 1'); }
/** * {@inheritdoc} */ public function generateSorting(SortingInterface $sorting, QueryBuilder $query, ShopContextInterface $context) { /** @var ProductNameSorting $sorting */ $query->addOrderBy('product.name', $sorting->getDirection())->addOrderBy('product.id', $sorting->getDirection()); }
/** * @inheritdoc */ public function joinAvailableVariant(QueryBuilder $query) { if ($query->hasState(self::STATE_INCLUDES_AVAILABLE_VARIANT)) { return; } $stockCondition = ''; if ($this->config->get('hideNoInstock')) { $stockCondition = 'AND (product.laststock * availableVariant.instock) >= (product.laststock * availableVariant.minpurchase)'; } $query->innerJoin('product', 's_articles_details', 'availableVariant', 'availableVariant.articleID = product.id AND availableVariant.active = 1 ' . $stockCondition); $query->addState(self::STATE_INCLUDES_AVAILABLE_VARIANT); }
/** * {@inheritdoc} */ public function generateSorting(SortingInterface $sorting, QueryBuilder $query, ShopContextInterface $context) { /** @var ReleaseDateSorting $sorting */ $query->addOrderBy('product.datum', $sorting->getDirection())->addOrderBy('product.changetime', $sorting->getDirection())->addOrderBy('product.id', $sorting->getDirection()); }