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

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.

  • 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 '14 at 12:43
  • I am not sure what you mean? You get the same result as in, you still got all three products? – Patrick Steenks Jul 2 '14 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 '14 at 13:07
  • See my edit above. – Patrick Steenks Jul 2 '14 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 '14 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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