Magento 2 | Item with the same ID already exists when join tables

Question

In my class I have filtered my collection by tier price and quantity.

If I put the display of only 3 products the code works, while if I set the display for all products I get this problem:

Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "42" already exists

This is my code

namespace Vendor\Module\Block;
class ProductCollection extends \Magento\Framework\View\Element\Template
{ 

protected $_productCollectionFactory;
        
    public function __construct(
        \Magento\Backend\Block\Template\Context $context,        
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,        
        array $data = []
    )
    {    
        $this->_productCollectionFactory = $productCollectionFactory;    
        parent::__construct($context, $data);
    }

public function getProductCollectionSearch() {
        $collection = $this->_productCollectionFactory->create();
        $collection->addAttributeToSelect('*');
        $collection->setPageSize(3);

        $collection->getSelect()->join('catalog_product_index_price as price_index', 'e.entity_id = price_index.entity_id'); 
        $collection->getSelect()->join('catalog_product_entity_tier_price as tier', 'price_index.entity_id = tier.entity_id');

        $collection->addFinalPrice()
            ->addMinimalPrice()
            ->getSelect()
            ->where('tier.all_groups = 0')
            ->where('tier.qty = 1000')
            ->where('tier.value <= 0.20');

        return $collection;
    }
}

Where is the issue?

Answer 1

The problem

In a Magento collection you are not allowed to have multiple items with the same id.

This happens when your result set of the database query returns the same item multiple times, most likely because you join a table which contains the same item twice or more.

I would assume that both of these tables contain the same product multiple times:

 $collection->getSelect()->join('catalog_product_index_price as price_index', 'e.entity_id = price_index.entity_id'); 
 $collection->getSelect()->join('catalog_product_entity_tier_price as tier', 'price_index.entity_id = tier.entity_id');

solution

1. rewrite the code, so that the query result does not contain multiple items
2. GROUP BY (doesn't return most of the time what you want without tinkering)
3. use DISTINCT (doesn't work most of the time)

Ideas how to resolve (after comment)

I would assume, that the multiples only differ by qty, so in theory you could filter by qty, something like qty = 10 OR if it is different something like:

qty = (SELECT MIN(qty) FROM catalog_product_entity_tier_price jtp 
    WHERE jtp.entity_id = main_table.entity_id)

Answer 2

My solution was different because I really needed that duplicates.

I removed the original ID field from the request (by removing all the columns from the select):

$select = $collection->getSelect();
$select->reset(Select::COLUMNS);

Then I defined alias to the original ID:

$select->columns(
   [
       'my_awesome_alias' => 'main_table.id',
       // add other fields you need
    ]
 );

In this case, when Magento collection is loaded, it preserves items as a simple array, not an associative array. I mean you won't have item IDs as array keys.

After getting items with $collection->getItems(), you can process retrieved items one by one and set the IDs back, if you need:

$itemId = $item->getData('my_awesome_alias');
$item->setData('id', $itemId);

Yeah, the solution seems not very nice but I haven't found a better one. If you have it, please, share it.

Answer 3

You can try something as below.

 $collection->getSelect()->group('main_table.entity_id');

Answer 4

Run SQL

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 ;

Answer 5

The problem can be in the cataloginventory_stock_status table (see @Msquare answer https://magento.stackexchange.com/a/350958/10018) but potentially also in other tables with product_id duplicated.

In my case, the table was catalog_compare_item

SELECT * FROM catalog_compare_item
WHERE product_id in (
    SELECT product_id 
    FROM catalog_compare_item
    GROUP BY product_id
    HAVING ( COUNT(product_id) > 1 )
)

So I suggest to create a plugin on \Magento\Eav\Model\Entity\Collection\AbstractCollection::addItem to get product duplicate entries without spending too much time (see https://magento.stackexchange.com/a/299081/10018).