Filter Product Collection by BOTH multiple catgeories

Question

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

Answer 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; 
}

Answer 2

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

Answer 3

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.