0

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?

5 Answers 5

2

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)
5
  • Hi! How can I rewrite the code? I need to merge the two tables to filter by tier price and quantity. Any suggestions? Thank you
    – Jackom
    Commented May 20, 2021 at 7:49
  • Man i tried to rewrite code and i discover that the duplicate item id exist in "catalog_product_entity_tier_price". Exist a method for ignore item id duplicate? Otherwise how can i do for filter my collection fro qty, customer group and tier price? Thanks!
    – Jackom
    Commented May 20, 2021 at 9:24
  • Added an idea how to resolve it. Commented May 20, 2021 at 9:47
  • So i can write ->where('tier.qty = 10') ? Because the value of tier.qty will be dynamic and set by an input field of a form
    – Jackom
    Commented May 20, 2021 at 9:52
  • I'm not sure I get your question. Yes you can do that, but it only works if the product has a tier price for qty 10. (And we ignore website settings and customer_groups here!) Commented May 20, 2021 at 16:26
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.

1

You can try something as below.

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

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 ;
0

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

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.