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

Apologete

Answer 1

Ok, I've got this solved thanks to https://mcmap.net/q/1604801/-magento-filter-product-collection-by-multiple-categories

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

Apologete

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

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. – Apologete

Answer 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

Aramanta

Answer 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?

Cabbage

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

Answer 4

Filter Product Collection using multiple category ids

$all_categories = array('3','13','113');   
$productCollection = Mage::getModel('catalog/product')->getCollection();
$productCollection->joinField('category_id', 'catalog/category_product', 'category_id', 
                    'product_id = entity_id', null, 'left')
                  ->addAttributeToSelect('*')
                  ->addAttributeToFilter('type_id', array('eq' => 'simple'))
                  ->addAttributeToFilter('category_id', array($all_categories));
foreach($productCollection as $product)
{
    echo $product->getId() .$product->getName() . "<br/>";
}

Dewitt