1

In my magento store i need to filter my product collection by 2 categories.

I have searched online and found ways of doing this (see existing code below) but they filter multiple categories using an OR condition – is there any way to do this with an AND condition so the products have to be found in BOTH categories not just any of them?

I am currently using the code below:

$_productCollection = Mage::getModel('catalog/product')
    ->getCollection()
    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('category_id', array(
        array('finset' => '26'),
        array('finset' => '63'))
    )
    ->addAttributeToSort('created_at', 'desc');

    foreach($_productCollection as $_product){
        echo $_product->getName()."<br/>";
    };

My product/category setup is:

products:

category 1 (ID 26)
category 2 (ID 63)

product A - found in category 1
product B - found in category 1 & 2
product C - found in category 1 & 2

So my current code below would output all 3 products where as i want it to only show products B and C

3 Answers 3

1

Ended up filtering by just the 1 category, and then on the for each output checking if the product id is present in the 2nd category. Not ideal but does the job for now

$_filter = 26;

foreach($_productCollection as $_product){
    $cats = $_product->getCategoryIds();
    if (in_array($_filter, $cats)):
        ....
    endif; 
}
1

You could join the category_product_index table with both category ids and then filter by count()=2 (i.e. only show products that are present in both categories):

$storeId = Mage::app()->getStore()->getId();
$categoryIds = [ /* Category IDs */];
$productCollection = Mage::getModel('catalog/product')->getCollection();

$joinConditions = array(
    'cat_index.product_id=e.entity_id',
    $productCollection->getConnection()->quoteInto('cat_index.category_id IN (?)', $categoryIds),
    $productCollection->getConnection()->quoteInto('cat_index.store_id=?', $storeId)
);
$productCollection->getSelect()->join(
    array('cat_index' => $this->getTable('catalog/category_product_index')),
    $joinConditions,
    array('cat_index_position' => 'position')
);

$productCollection->getSelect()->columns(
    array('category_count' => 'COUNT(cat_index.category_id)')
);
$productCollection->getSelect()->having('COUNT(cat_index.category_id) = ?', count($categoryIds));
0

This can be accomplished by using the following AttributeFilter:

$collection->addAttributeToFilter('category_id', array('in' => array('finset' => array($newArray))));

$newArray is an array containing the category_id's as values.

EDIT What if you tried this: $collection->addAttributeToFilter('category_id', array("in"=>array('26'))); $collection->addAttributeToFilter('category_id', array("in"=>array('63')));

According to Magento documentation this should provide an AND clause. You can always dump $collection->getSelect(); to view the SQL query and debug from there.

11
  • I've just tried the following with your code and i get the same result, is this correct - ? $_productCollection = Mage::getModel('catalog/product') ->getCollection() ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left') ->addAttributeToSelect('*') ->addAttributeToFilter('category_id', array('in' => array('finset' => array($newArray))));
    – odd_duck
    Jul 2, 2014 at 12:43
  • I am not sure what you mean? You get the same result as in, you still got all three products? Jul 2, 2014 at 13:01
  • Yep all 3 are coming up still. My code in the comment above is a little messy as i couldn't format it as a block, are you able to adapt that line into my code so i can check it's not my mistake
    – odd_duck
    Jul 2, 2014 at 13:07
  • See my edit above. Jul 2, 2014 at 15:28
  • I've tried the edit and still no luck. I've tried several variations as shown below in the links, with the outcome/result also below each one, can you show where abouts and how that line fits in with me existing code?: pastebin.com/zGeP9XW5 pastebin.com/Jrnb2bm2
    – odd_duck
    Jul 2, 2014 at 15:59

Your Answer

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

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