Filter product collection on two categories Magento 1.7

Question

I want to get a product collection with products in Category A or Category B. I have been able to succesfully get these products with the following php-code:

$collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
    ->addAttributeToFilter('category_id', array('in' => array('finset' => 4,19)))
    ->addAttributeToSelect('*');

However, if the product is in both category 4 AND 19, then an error is displayed:

Item (Mage_Catalog_Model_Product) with the same id "173" already exist

This is because the collection has a duplicate row in it. I'm struggling to find the right code to filter out any duplicate rows in the collection. The solution must be to group the values, or use distinct, but I'm not sure how to go forth.

See also Filter Magento collection but not products, using distinct

Solution 1

Ok, I've got this solved thanks to https://stackoverflow.com/a/13291759/991491

$collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
    ->addAttributeToFilter('category_id', array('in' => array('finset' => 3,4)))
    ->addAttributeToSelect('*');
$collection->getSelect()->group('e.entity_id');

The group clause does the trick in overcoming duplicate product id's that get returned.

Solution 2

same error links suggestion http://www.magentocommerce.com/boards/m/viewthread/245112/

this will happen when in collection you have same id

so i used below code at the end of collection

$collection->getSelect()->distinct(true);

this will make select distinct

Solution 3

I got this error and what Magento reported via '/var/reports/xxx' was:

a:5:{i:0;s:71:"Item (Mage_Catalog_Model_Product) with the same id "xxx"

What I come up with was deactivating product with this id and it was fixed. Then I deleted this product and re-create it. Not a perfect solution, but works for now. But I still wonder what was the problem there? In our case, this error came suddenly. We haven't changed or developed anything new recently on which we could put the blame. Does anybody have any idea why this happened out of the blue?

Comments

[Damodar Bashyal]

this fixed for me $collection->distinct(true);

[Patrick Steenks]

I'm not sure what you mean. There is not really a path to the file you want to edit, as this is a file in my own code, in my own plugin.

[Patrick Steenks]

Can you put your SQL query here? That might give some insight