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