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()


        if ($this->queryText) {

        $priceRangeCalculation = $this->_scopeConfig->getValue(
        if ($priceRangeCalculation) {

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

        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());

                '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']);


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 Nov 27, 2017 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 Nov 27, 2017 at 10:38

2 Answers 2


My problem solved using Group By in my bestsellers collection.

$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(),
                        ->order('qty_ordered DESC');
            return $collection;
  • is that the part : getSelect()->group('e.entity_id')->limit(15);? Nov 28, 2017 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 Nov 28, 2017 at 14:27
  • Thanks for accept. Can you please paste your query here?
    – P_U
    Nov 29, 2017 at 5:21
  • the query is at the top. I now even have this issue when just loading a category page Nov 29, 2017 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. Nov 29, 2017 at 13:58


SELECT product_id ,COUNT(*) FROM cataloginventory_stock_status GROUP BY product_id, product_id HAVING COUNT(*) > 1

After run above sql you can get all duplicate ids then run below SQL query.

delete from cataloginventory_stock_status where  product_id = {AddDuplicateId} and website_id != 0 ;

update cataloginventory_stock_status set stock_status = 1 where  product_id = {AddDuplicateId} and qty >= 1 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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