/** * @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($this->fieldHelper->getVoteFields()); $query->from('s_articles_vote', 'vote')->where('vote.articleID IN (:ids)')->andWhere('vote.active = 1')->orderBy('vote.articleID', 'DESC')->addOrderBy('vote.datum', 'DESC')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_ASSOC); $votes = []; foreach ($data as $row) { $id = $row['__vote_articleID']; $votes[$id][] = $this->voteHydrator->hydrate($row); } $result = []; foreach ($products as $product) { $id = $product->getId(); if (!isset($votes[$id])) { continue; } $number = $product->getNumber(); $result[$number] = $votes[$id]; } return $result; }
/** * @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->select(['relation.article_id']); $query->addSelect($this->fieldHelper->getRelatedProductStreamFields()); $query->from('s_product_streams_articles', 'relation'); $query->innerJoin('relation', 's_product_streams', 'stream', 'stream.id = relation.stream_id'); $this->fieldHelper->addProductStreamTranslation($query, $context); $query->where('relation.article_id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_GROUP); $related = []; foreach ($data as $productId => $data) { $related[$productId] = []; foreach ($data as $row) { $related[$productId][] = $this->hydrator->hydrate($row); } } return $related; }
/** * @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->select($this->fieldHelper->getDownloadFields()); $query->from('s_articles_downloads', 'download')->leftJoin('download', 's_articles_downloads_attributes', 'downloadAttribute', 'downloadAttribute.downloadID = download.id'); $query->where('download.articleID IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_ASSOC); $downloads = []; foreach ($data as $row) { $key = $row['__download_articleID']; $download = $this->downloadHydrator->hydrate($row); $downloads[$key][] = $download; } $result = []; foreach ($products as $product) { if (isset($downloads[$product->getId()])) { $result[$product->getNumber()] = $downloads[$product->getId()]; } } return $result; }
/** * @inheritdoc */ public function get(Struct\BaseProduct $product, Struct\ShopContextInterface $context) { $query = $this->getQuery(); $query->addSelect($this->fieldHelper->getConfiguratorSetFields())->addSelect($this->fieldHelper->getConfiguratorGroupFields())->addSelect($this->fieldHelper->getConfiguratorOptionFields()); $this->fieldHelper->addConfiguratorTranslation($query, $context); $query->where('products.id = :id')->setParameter(':id', $product->getId()); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_ASSOC); return $this->configuratorHydrator->hydrate($data); }
/** * @inheritdoc */ public function getList(array $keys) { $query = $this->connection->createQueryBuilder(); $query->select($this->fieldHelper->getCustomerGroupFields()); $query->from('s_core_customergroups', 'customerGroup')->leftJoin('customerGroup', 's_core_customergroups_attributes', 'customerGroupAttribute', 'customerGroupAttribute.customerGroupID = customerGroup.id'); $query->where('customerGroup.groupkey IN (:keys)')->setParameter(':keys', $keys, Connection::PARAM_STR_ARRAY); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_ASSOC); $customerGroups = []; foreach ($data as $group) { $key = $group['__customerGroup_groupkey']; $customerGroups[$key] = $this->customerGroupHydrator->hydrate($group); } return $customerGroups; }
/** * @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; }
/** * @inheritdoc */ public function getList(array $ids, Struct\ShopContextInterface $context) { $query = $this->connection->createQueryBuilder(); $query->addSelect($this->fieldHelper->getManufacturerFields()); $query->from('s_articles_supplier', 'manufacturer')->leftJoin('manufacturer', 's_articles_supplier_attributes', 'manufacturerAttribute', 'manufacturerAttribute.supplierID = manufacturer.id'); $query->where('manufacturer.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY); $this->fieldHelper->addManufacturerTranslation($query, $context); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_ASSOC); $manufacturers = []; foreach ($data as $row) { $id = $row['__manufacturer_id']; $manufacturers[$id] = $this->manufacturerHydrator->hydrate($row); } return $manufacturers; }
/** * @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; }
/** * @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; }
/** * @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 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 Struct\Customer\Group $customerGroup * @param Struct\ShopContextInterface $context * @return array|Struct\Product\PriceGroup[] */ public function getPriceGroups(Struct\Customer\Group $customerGroup, Struct\ShopContextInterface $context) { $query = $this->connection->createQueryBuilder(); $query->addSelect('priceGroupDiscount.groupID')->addSelect($this->fieldHelper->getPriceGroupDiscountFields())->addSelect($this->fieldHelper->getPriceGroupFields()); $query->from('s_core_pricegroups_discounts', 'priceGroupDiscount')->innerJoin('priceGroupDiscount', 's_core_pricegroups', 'priceGroup', 'priceGroup.id = priceGroupDiscount.groupID'); $query->andWhere('priceGroupDiscount.customergroupID = :customerGroup'); $query->groupBy('priceGroupDiscount.id'); $query->orderBy('priceGroupDiscount.groupID')->addOrderBy('priceGroupDiscount.discountstart'); $query->setParameter(':customerGroup', $customerGroup->getId()); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_GROUP); $priceGroups = []; foreach ($data as $row) { $priceGroup = $this->priceHydrator->hydratePriceGroup($row); foreach ($priceGroup->getDiscounts() as $discount) { $discount->setCustomerGroup($customerGroup); } $priceGroups[$priceGroup->getId()] = $priceGroup; } return $priceGroups; }
/** * @inheritdoc */ public function getList($products, Struct\Customer\Group $customerGroup) { $ids = []; foreach ($products as $product) { $ids[] = $product->getVariantId(); } $ids = array_unique($ids); $query = $this->connection->createQueryBuilder(); $query->select($this->fieldHelper->getPriceFields()); $query->addSelect('variants.ordernumber as number'); $query->from('s_articles_prices', 'price')->innerJoin('price', 's_articles_details', 'variants', 'variants.id = price.articledetailsID')->leftJoin('price', 's_articles_prices_attributes', 'priceAttribute', 'priceAttribute.priceID = price.id'); $query->where('price.articledetailsID IN (:products)')->andWhere('price.pricegroup = :customerGroup')->setParameter(':products', $ids, Connection::PARAM_INT_ARRAY)->setParameter(':customerGroup', $customerGroup->getKey()); $query->orderBy('price.articledetailsID', 'ASC')->addOrderBy('price.from', 'ASC'); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_ASSOC); $prices = []; foreach ($data as $row) { $product = $row['number']; $prices[$product][] = $this->priceHydrator->hydratePriceRule($row); } return $prices; }
/** * @inheritdoc */ public function getStates(array $ids, Struct\ShopContextInterface $context) { $query = $this->connection->createQueryBuilder(); $query->select($this->fieldHelper->getStateFields()); $query->from('s_core_countries_states', 'countryState')->leftJoin('countryState', 's_core_countries_states_attributes', 'countryStateAttribute', 'countryStateAttribute.stateID = countryState.id'); $this->fieldHelper->addCountryStateTranslation($query, $context); $query->where('countryState.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_ASSOC); $states = []; foreach ($data as $row) { $states[] = $this->countryHydrator->hydrateState($row); } return $states; }
/** * @param Struct\Customer\Group $customerGroup * @param Struct\Country\Area $area * @param Struct\Country $country * @param Struct\Country\State $state * @return \Doctrine\DBAL\Query\QueryBuilder */ private function getAreaQuery(Struct\Customer\Group $customerGroup = null, Struct\Country\Area $area = null, Struct\Country $country = null, Struct\Country\State $state = null) { $query = $this->connection->createQueryBuilder(); $query->select($this->fieldHelper->getTaxRuleFields()); $query->from('s_core_tax_rules', 'taxRule'); $areaId = $area ? $area->getId() : null; $countryId = $country ? $country->getId() : null; $stateId = $state ? $state->getId() : null; $query->andWhere('(taxRule.areaID = :area OR taxRule.areaID IS NULL)')->setParameter(':area', $areaId); $query->andWhere('(taxRule.countryID = :country OR taxRule.countryID IS NULL)')->setParameter(':country', $countryId); $query->andWhere('(taxRule.stateID = :state OR taxRule.stateID IS NULL)')->setParameter(':state', $stateId); $query->andWhere('(taxRule.customer_groupID = :customerGroup OR taxRule.customer_groupID IS NULL)')->setParameter(':customerGroup', $customerGroup->getId()); $query->andWhere('taxRule.groupID = :taxId')->andWhere('taxRule.active = 1'); $query->orderBy('taxRule.customer_groupID', 'DESC')->addOrderBy('taxRule.areaID', 'DESC')->addOrderBy('taxRule.countryID', 'DESC')->addOrderBy('taxRule.stateID', 'DESC'); $query->setFirstResult(0)->setMaxResults(1); return $query; }
/** * @param array $numbers * @param Struct\ShopContextInterface $context * @return \Doctrine\DBAL\Query\QueryBuilder */ protected function getQuery(array $numbers, Struct\ShopContextInterface $context) { $esdQuery = $this->getEsdQuery(); $customerGroupQuery = $this->getCustomerGroupQuery(); $availableVariantQuery = $this->getHasAvailableVariantQuery(); $fallbackPriceQuery = $this->getPriceCountQuery(':fallback'); $query = $this->connection->createQueryBuilder(); $query->select($this->fieldHelper->getArticleFields())->addSelect($this->fieldHelper->getTopSellerFields())->addSelect($this->fieldHelper->getVariantFields())->addSelect($this->fieldHelper->getUnitFields())->addSelect($this->fieldHelper->getTaxFields())->addSelect($this->fieldHelper->getPriceGroupFields())->addSelect($this->fieldHelper->getManufacturerFields())->addSelect($this->fieldHelper->getEsdFields())->addSelect('(' . $esdQuery->getSQL() . ') as __product_has_esd')->addSelect('(' . $customerGroupQuery->getSQL() . ') as __product_blocked_customer_groups')->addSelect('(' . $availableVariantQuery->getSQL() . ') as __product_has_available_variants')->addSelect('(' . $fallbackPriceQuery->getSQL() . ') as __product_fallback_price_count'); $query->setParameter(':fallback', $context->getFallbackCustomerGroup()->getKey()); if ($context->getCurrentCustomerGroup()->getId() !== $context->getFallbackCustomerGroup()->getId()) { $customerPriceQuery = $this->getPriceCountQuery(':current'); $query->addSelect('(' . $customerPriceQuery->getSQL() . ') as __product_custom_price_count'); $query->setParameter(':current', $context->getCurrentCustomerGroup()->getKey()); } $query->from('s_articles_details', 'variant')->innerJoin('variant', 's_articles', 'product', 'product.id = variant.articleID')->innerJoin('product', 's_core_tax', 'tax', 'tax.id = product.taxID')->leftJoin('variant', 's_core_units', 'unit', 'unit.id = variant.unitID')->leftJoin('product', 's_articles_supplier', 'manufacturer', 'manufacturer.id = product.supplierID')->leftJoin('product', 's_core_pricegroups', 'priceGroup', 'priceGroup.id = product.pricegroupID')->leftJoin('variant', 's_articles_attributes', 'productAttribute', 'productAttribute.articledetailsID = variant.id')->leftJoin('product', 's_articles_supplier_attributes', 'manufacturerAttribute', 'manufacturerAttribute.supplierID = product.supplierID')->leftJoin('product', 's_articles_top_seller_ro', 'topSeller', 'topSeller.article_id = product.id')->leftJoin('variant', 's_articles_esd', 'esd', 'esd.articledetailsID = variant.id')->leftJoin('esd', 's_articles_esd_attributes', 'esdAttribute', 'esdAttribute.esdID = esd.id')->where('variant.ordernumber IN (:numbers)')->andWhere('variant.active = 1')->andWhere('product.active = 1')->setParameter(':numbers', $numbers, Connection::PARAM_STR_ARRAY); $this->fieldHelper->addProductTranslation($query, $context); $this->fieldHelper->addVariantTranslation($query, $context); $this->fieldHelper->addManufacturerTranslation($query, $context); $this->fieldHelper->addUnitTranslation($query, $context); return $query; }
/** * @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; }
/** * @inheritdoc */ public function getList($products, Struct\ShopContextInterface $context, Struct\Customer\Group $customerGroup) { /** * Contains the cheapest price logic which product price should be selected. */ $ids = $this->getCheapestPriceIds($products, $customerGroup); $query = $this->connection->createQueryBuilder(); $query->select($this->fieldHelper->getPriceFields())->addSelect($this->fieldHelper->getUnitFields()); $query->from('s_articles_prices', 'price')->innerJoin('price', 's_articles_details', 'variant', 'variant.id = price.articledetailsID')->innerJoin('variant', 's_articles', 'product', 'product.id = variant.articleID')->leftJoin('variant', 's_core_units', 'unit', 'unit.id = variant.unitID')->leftJoin('price', 's_articles_prices_attributes', 'priceAttribute', 'priceAttribute.priceID = price.id'); $this->fieldHelper->addUnitTranslation($query, $context); $this->fieldHelper->addVariantTranslation($query, $context); $query->andWhere('price.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_ASSOC); $prices = []; foreach ($data as $row) { $product = $row['__price_articleID']; $prices[$product] = $this->priceHydrator->hydrateCheapestPrice($row); } return $prices; }
/** * @inheritdoc */ public function getList($products, Struct\ShopContextInterface $context) { if (empty($products)) { return []; } $ids = []; foreach ($products as $product) { $ids[] = $product->getVariantId(); } $ids = array_unique($ids); $query = $this->getQuery()->select('variants.ordernumber as number')->addSelect($this->fieldHelper->getConfiguratorGroupFields())->addSelect($this->fieldHelper->getConfiguratorOptionFields()); $this->fieldHelper->addConfiguratorTranslation($query, $context); $query->where('relations.article_id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY); $query->addOrderBy('configuratorGroup.id'); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_GROUP); $result = []; foreach ($data as $key => $groups) { $result[$key] = $this->configuratorHydrator->hydrateGroups($groups); } return $result; }
/** * @param int[] $ids * @return \array[] */ private function getCurrencies($ids) { $query = $this->connection->createQueryBuilder(); $query->addSelect($this->fieldHelper->getCurrencyFields())->from('s_core_currencies', 'currency')->where('currency.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY); return $query->execute()->fetchAll(\PDO::FETCH_ASSOC); }