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