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