/**
* @inheritdoc
*/
public function getList(array $valueIds, Struct\ShopContextInterface $context)
{
$query = $this->connection->createQueryBuilder();
$sortMode = $this->getSortMode($valueIds);
$query->addSelect($this->fieldHelper->getPropertySetFields())->addSelect($this->fieldHelper->getPropertyGroupFields())->addSelect($this->fieldHelper->getPropertyOptionFields())->addSelect($this->fieldHelper->getMediaFields());
$query->from('s_filter', 'propertySet');
$query->innerJoin('propertySet', 's_filter_relations', 'relations', 'relations.groupID = propertySet.id');
$query->leftJoin('propertySet', 's_filter_attributes', 'propertySetAttribute', 'propertySetAttribute.filterID = propertySet.id');
$query->innerJoin('relations', 's_filter_options', 'propertyGroup', 'relations.optionID = propertyGroup.id
AND filterable = 1');
$query->innerJoin('propertyGroup', 's_filter_values', 'propertyOption', 'propertyOption.optionID = propertyGroup.id');
$query->leftJoin('propertyOption', 's_media', 'media', 'propertyOption.media_id = media.id');
$query->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id');
$query->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID');
$this->fieldHelper->addAllPropertyTranslations($query, $context);
$query->groupBy('propertyOption.id');
$query->where('propertyOption.id IN (:ids)')->setParameter(':ids', $valueIds, Connection::PARAM_INT_ARRAY);
$query->orderBy('propertySet.position')->addOrderBy('propertySet.id')->addOrderBy('relations.position')->addOrderBy('propertyGroup.name');
switch ($sortMode) {
case self::FILTERS_SORT_NUMERIC:
$query->addOrderBy('propertyOption.value_numeric');
break;
case self::FILTERS_SORT_POSITION:
$query->addOrderBy('propertyOption.position');
break;
default:
$query->addOrderBy('propertyOption.value');
}
$query->addOrderBy('propertyOption.id');
/**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
$statement = $query->execute();
$rows = $statement->fetchAll(\PDO::FETCH_ASSOC);
return $this->propertyHydrator->hydrateValues($rows);
}
/**
* @inheritdoc
*/
public function getList($products, Struct\ShopContextInterface $context)
{
$ids = [];
foreach ($products as $product) {
$ids[] = $product->getId();
}
$ids = array_unique($ids);
$query = $this->connection->createQueryBuilder();
$query->addSelect('products.id as productId')->addSelect($this->fieldHelper->getPropertySetFields())->addSelect($this->fieldHelper->getPropertyGroupFields())->addSelect($this->fieldHelper->getPropertyOptionFields())->addSelect($this->fieldHelper->getMediaFields());
$query->addSelect('
(
CASE
WHEN propertySet.sortmode = 1 THEN propertyOption.value_numeric
WHEN propertySet.sortmode = 3 THEN propertyOption.position
ELSE propertyOption.value
END
) as sortRelevance');
$query->from('s_filter_articles', 'filterArticles');
$query->innerJoin('filterArticles', 's_articles', 'products', 'products.id = filterArticles.articleID');
$query->innerJoin('filterArticles', 's_filter_values', 'propertyOption', 'propertyOption.id = filterArticles.valueID');
$query->innerJoin('products', 's_filter', 'propertySet', 'propertySet.id = products.filtergroupID');
$query->leftJoin('propertySet', 's_filter_attributes', 'propertySetAttribute', 'propertySetAttribute.filterID = propertySet.id');
$query->innerJoin('propertySet', 's_filter_relations', 'relations', 'relations.groupID = propertySet.id');
$query->innerJoin('propertyOption', 's_filter_options', 'propertyGroup', 'propertyGroup.id = propertyOption.optionID AND relations.optionID = propertyGroup.id');
$query->leftJoin('propertyOption', 's_media', 'media', 'propertyOption.media_id = media.id');
$query->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id');
$query->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID');
$this->fieldHelper->addAllPropertyTranslations($query, $context);
$query->where('products.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
$query->orderBy('filterArticles.articleID')->addOrderBy('relations.position')->addOrderBy('propertyGroup.name')->addOrderBy('sortRelevance')->addOrderBy('propertyOption.id');
/**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
$statement = $query->execute();
$data = $statement->fetchAll(\PDO::FETCH_GROUP);
$properties = [];
foreach ($data as $productId => $values) {
$properties[$productId] = $this->propertyHydrator->hydrateValues($values);
}
$result = [];
foreach ($products as $product) {
if (!isset($properties[$product->getId()])) {
continue;
}
$sets = $properties[$product->getId()];
$result[$product->getNumber()] = array_shift($sets);
}
return $result;
}
/**
* @return \Doctrine\DBAL\Query\QueryBuilder
*/
private function getQuery()
{
$query = $this->connection->createQueryBuilder();
$query->select($this->fieldHelper->getMediaFields());
$query->from('s_media', 'media')->innerJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id');
$query->where('media.id IN (:ids)');
return $query;
}
/**
* @param \Shopware\Bundle\StoreFrontBundle\Struct\ShopContextInterface $context
* @return \Doctrine\DBAL\Query\QueryBuilder
*/
private function getQuery(Struct\ShopContextInterface $context)
{
$query = $this->connection->createQueryBuilder();
$query->addSelect('variant.ordernumber as number')->addSelect($this->fieldHelper->getMediaFields())->addSelect($this->fieldHelper->getImageFields());
$this->fieldHelper->addImageTranslation($query, $context);
$query->from('s_articles_img', 'image')->innerJoin('image', 's_media', 'media', 'image.media_id = media.id')->innerJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')->innerJoin('image', 's_articles_img', 'childImage', 'childImage.parent_id = image.id')->innerJoin('image', 's_articles_details', 'variant', 'variant.id = childImage.article_detail_id')->leftJoin('image', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = image.media_id')->leftJoin('image', 's_articles_img_attributes', 'imageAttribute', 'imageAttribute.imageID = image.id');
return $query;
}
/**
* @inheritdoc
*/
public function getList(array $ids, Struct\ShopContextInterface $context)
{
$query = $this->connection->createQueryBuilder();
$query->select($this->fieldHelper->getCategoryFields())->addSelect($this->fieldHelper->getMediaFields())->addSelect("GROUP_CONCAT(customerGroups.customergroupID) as __category_customer_groups");
$query->from('s_categories', 'category');
$query->leftJoin('category', 's_categories_attributes', 'categoryAttribute', 'categoryAttribute.categoryID = category.id')->leftJoin('category', 's_categories_avoid_customergroups', 'customerGroups', 'customerGroups.categoryID = category.id')->leftJoin('category', 's_media', 'media', 'media.id = category.mediaID')->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id');
$query->addOrderBy('category.position')->addOrderBy('category.id')->addGroupBy('category.id');
$query->where('category.id IN (:categories)')->andWhere('category.active = 1');
$query->setParameter(':categories', $ids, Connection::PARAM_INT_ARRAY);
/**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
$statement = $query->execute();
$data = $statement->fetchAll(\PDO::FETCH_ASSOC);
$categories = [];
foreach ($data as $row) {
$id = $row['__category_id'];
$categories[$id] = $this->categoryHydrator->hydrate($row);
}
return $categories;
}
/**
* @param ShopContextInterface $context
* @return \Doctrine\DBAL\Query\QueryBuilder
*/
private function getQuery(ShopContextInterface $context)
{
$query = $this->connection->createQueryBuilder();
$query->addSelect($this->fieldHelper->getPropertyGroupFields())->addSelect($this->fieldHelper->getPropertyOptionFields())->addSelect($this->fieldHelper->getMediaFields());
$query->from('s_filter_options', 'propertyGroup')->innerJoin('propertyGroup', 's_filter_values', 'propertyOption', 'propertyOption.optionID = propertyGroup.id')->leftJoin('propertyOption', 's_media', 'media', 'propertyOption.media_id = media.id')->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id')->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID');
$this->fieldHelper->addPropertyGroupTranslation($query, $context);
$this->fieldHelper->addPropertyOptionTranslation($query, $context);
$query->where('propertyGroup.id = :id');
return $query;
}
/**
* @param int[] $ids
* @return \array[]
*/
private function getShops($ids)
{
$query = $this->connection->createQueryBuilder();
$query->addSelect($this->fieldHelper->getShopFields())->addSelect($this->fieldHelper->getCurrencyFields())->addSelect($this->fieldHelper->getTemplateFields())->addSelect($this->fieldHelper->getLocaleFields())->addSelect($this->fieldHelper->getCustomerGroupFields())->addSelect($this->fieldHelper->getCategoryFields())->addSelect($this->fieldHelper->getMediaFields());
$query->from('s_core_shops', 'shop')->leftJoin('shop', 's_core_currencies', 'currency', 'currency.id = shop.currency_id')->leftJoin('shop', 's_core_templates', 'template', 'shop.template_id = template.id')->leftJoin('shop', 's_core_locales', 'locale', 'locale.id = shop.locale_id')->leftJoin('shop', 's_core_customergroups', 'customerGroup', 'customerGroup.id = shop.customer_group_id')->leftJoin('customerGroup', 's_core_customergroups_attributes', 'customerGroupAttribute', 'customerGroupAttribute.customerGroupID = customerGroup.id')->leftJoin('shop', 's_categories', 'category', 'category.id = shop.category_id')->leftJoin('category', 's_categories_attributes', 'categoryAttribute', 'categoryAttribute.categoryID = category.id')->leftJoin('category', 's_categories_avoid_customergroups', 'customerGroups', 'customerGroups.categoryID = category.id')->leftJoin('category', 's_media', 'media', 'media.id = category.mediaID')->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id');
$query->where('shop.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
$data = $query->execute()->fetchAll(\PDO::FETCH_ASSOC);
$result = [];
foreach ($data as $row) {
$result[$row['__shop_id']] = $row;
}
return $result;
}
/**
* @param ListProduct[] $products
* @param ShopContextInterface $context
* @return \array[]
*/
private function getProperties($products, ShopContextInterface $context)
{
$ids = array_map(function (ListProduct $product) {
return $product->getId();
}, $products);
$query = $this->connection->createQueryBuilder();
$query->addSelect('filterArticles.articleID as productId')->addSelect($this->fieldHelper->getPropertyOptionFields())->addSelect($this->fieldHelper->getMediaFields())->from('s_filter_articles', 'filterArticles')->innerJoin('filterArticles', 's_filter_values', 'propertyOption', 'propertyOption.id = filterArticles.valueID')->leftJoin('propertyOption', 's_media', 'media', 'propertyOption.media_id = media.id')->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id')->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')->where('filterArticles.articleID IN (:ids)')->addOrderBy('filterArticles.articleID')->addOrderBy('propertyOption.value')->addOrderBy('propertyOption.id')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
$this->fieldHelper->addPropertyOptionTranslation($query, $context);
/**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
$statement = $query->execute();
$data = $statement->fetchAll(\PDO::FETCH_GROUP);
$properties = [];
$hydrator = $this->propertyHydrator;
foreach ($data as $productId => $values) {
$options = array_map(function ($row) use($hydrator) {
return $hydrator->hydrateOption($row);
}, $values);
$properties[$productId] = $options;
}
return $properties;
}
