0

Whenever i try to filter by a custom attribute (type = price) i get the error :

Exception #0 (Exception): Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "871" already exists

I also have the SQl but im not that familier with SQL code. Can someone see an error here (it is a multisite)

SQL that caused this :SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `stock_status_index`.`stock_status` AS `is_salable`, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name` FROM `eh_catalog_product_entity` AS `e`  INNER JOIN `eh_catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='20'  INNER JOIN `eh_catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = '19'  INNER JOIN `eh_catalog_product_index_eav_decimal` AS `adviced_price_idx` ON adviced_price_idx.entity_id = e.entity_id AND adviced_price_idx.attribute_id = '170' AND adviced_price_idx.store_id
= 1  LEFT JOIN `eh_cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id
= 1  INNER JOIN `eh_search_tmp_5a1be69bc93529_67461608` AS `search_result` ON e.entity_id = search_result.entity_id  LEFT JOIN `eh_catalog_product_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = '73') AND `at_name_default`.`store_id` = 0  LEFT JOIN `eh_catalog_product_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = '73') AND (`at_name`.`store_id` = 1) WHERE (adviced_price_idx.value >= 0) AND (adviced_price_idx.value < 10) ORDER BY `name` ASC  LIMIT 9

Anyone any idea?

The problem is caused by duplicate entries in the decimal index table 'product_index_eav_decimal. Does anyone know how this gets generated?

code :

protected function _renderFiltersBefore()
    {
        $this->getCollectionClone();

        $this->getSearchCriteriaBuilder();
        $this->getFilterBuilder();
        $this->getSearch();

        if ($this->queryText) {
            $this->filterBuilder->setField('search_term');
            $this->filterBuilder->setValue($this->queryText);
            $this->searchCriteriaBuilder->addFilter($this->filterBuilder->create());
        }

        $priceRangeCalculation = $this->_scopeConfig->getValue(
            \Magento\Catalog\Model\Layer\Filter\Dynamic\AlgorithmFactory::XML_PATH_RANGE_CALCULATION,
            \Magento\Store\Model\ScopeInterface::SCOPE_STORE
        );
        if ($priceRangeCalculation) {
            $this->filterBuilder->setField('price_dynamic_algorithm');
            $this->filterBuilder->setValue($priceRangeCalculation);
            $this->searchCriteriaBuilder->addFilter($this->filterBuilder->create());
        }

        $searchCriteria = $this->searchCriteriaBuilder->create();
        $searchCriteria->setRequestName($this->searchRequestName);

        try {
            $this->searchResult = $this->getSearch()->search($searchCriteria);
        } catch (\Exception $e) {
            throw new LocalizedException(__('Sorry, something went wrong. You can find out more in the error log.'));
        }

        $temporaryStorage = $this->temporaryStorageFactory->create();
        $table            = $temporaryStorage->storeDocuments($this->searchResult->getItems());

        $this->getSelect()->joinInner(
            [
                'search_result' => $table->getName(),
            ],
            'e.entity_id = search_result.' . TemporaryStorage::FIELD_ENTITY_ID,
            []
        );


        if ($this->order && 'relevance' === $this->order['field']) {
            $this->getSelect()->order('search_result.' . TemporaryStorage::FIELD_SCORE . ' ' . $this->order['dir']);
        }

        parent::_renderFiltersBefore();
    }

This is the result in the database : enter image description here

Remove one of the 2 child (so its a unique row) and there is no problem anymore

  • You have an duplicated product, if you can add distinct to your query ou grouped by entity_id – Mohamed El Mrabet Nov 27 '17 at 10:30
  • The query is generated by Magento itself since its their layered navigation. Its default. I have a new question that i will update in my queston – CompactCode Nov 27 '17 at 10:38
1

My problem solved using Group By in my bestsellers collection.

$collection->setVisibility($this->_catalogProductVisibility->getVisibleInCatalogIds());
$collection = $this->_addProductAttributesAndPrices($collection);
$collection->getSelect()->join(['bestsellers' => $collection->getTable('sales_bestsellers_aggregated_yearly')],
                                    'e.entity_id = bestsellers.product_id AND bestsellers.store_id = '.$this->getStoreId(),
                                    ['qty_ordered','rating_pos'])
                        ->order('qty_ordered DESC');
$collection->addStoreFilter($this->getStoreId())
                        ->setPageSize($this->getProductsCount())
                        ->setCurPage(1);
$collection->getSelect()->group('e.entity_id')->limit(15);
            return $collection;
  • is that the part : getSelect()->group('e.entity_id')->limit(15);? – CompactCode Nov 28 '17 at 14:07
  • I accepted this but other problems occur now like invalid count of products and things like that. Maybe i'm placing it wrong – CompactCode Nov 28 '17 at 14:27
  • Thanks for accept. Can you please paste your query here? – P_U Nov 29 '17 at 5:21
  • the query is at the top. I now even have this issue when just loading a category page – CompactCode Nov 29 '17 at 13:53
  • the problem seems to be that a grouped product has multiple entries within eh_catalog_product_index_eav_decimal since the simple products attached to them each have a different price. – CompactCode Nov 29 '17 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.