Filter a product collection by two categories in Magento

Question

I'm trying to find products that are in two categories. I've found an example to get products that are in category1 OR category2. http://www.alphadigital.cl/blog/lang/en-us/magento-filter-by-multiple-categories.html I need products that are in category1 AND category2.

The example in the blog is:

class ModuleName_Catalog_Model_Resource_Eav_Mysql4_Product_Collection
  extends Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection{

  public function addCategoriesFilter($categories){

  $alias = 'cat_index';
  $categoryCondition = $this->getConnection()->quoteInto(
    $alias.'.product_id=e.entity_id AND '.$alias.'.store_id=? AND ',
    $this->getStoreId()
  );

  $categoryCondition.= $alias.'.category_id IN ('.$categories.')';

  $this->getSelect()->joinInner(
    array($alias => $this->getTable('catalog/category_product_index')),
    $categoryCondition,
    array('position'=>'position')
  );

  $this->_categoryIndexJoined = true;
  $this->_joinFields['position'] = array('table'=>$alias, 'field'=>'position' );

  return $this;

  }
}

When I'm using this filter alone it perform OR query on several categories. When I combine this filter with prepareProductCollection of Mage_Catalog_Model_Layer it somehow remove the filter effect.

How can I change the filter to AND and combine it with prepareProductCollection?

Thanks

Thanks

Answer 1

This code will allow you to filter by multiple categories but avoid completely killing performance if you had to perform multiple collection loads:

$iNumberFeaturedItems = 4;
$oCurrentCategory = Mage::registry('current_category');
$oFeaturedCategory = Mage::getModel('catalog/category')->getCollection()
        ->addAttributeToFilter('name','Featured')
        ->getFirstItem();
$aFeaturedCollection = Mage::getResourceModel('catalog/product_collection')
        ->addAttributeToSelect(array('name', 'price', 'small_image', 'url_key'), 'inner')
        ->addStoreFilter()
        ->addCategoryFilter($oFeaturedCategory)
        ->addCategoryIds();

The first step is to get a collection of products for one category (in this case, a Featured category). Next step is to get the IDs of the products, notice that this does NOT perform a load (ref Mage_Core_Model_Mysql4_Collection_Abstract::getAllIds())

    $aFeaturedProdIds = $aFeaturedCollection->getAllIds();
    shuffle($aFeaturedProdIds);  //randomize the order of the featured products

Then get the IDs for a second category:

    $aCurrentCatProdIds = $oCurrentCategory->getProductCollection()->getAllIds();

And intersect the arrays to find product IDs that exist in both categories:

    $aMergedProdIds = array_intersect($aFeaturedProdIds,$aCurrentCatProdIds);

For this particular use case, we loop until we have sufficient intersecting products, traversing up the category tree until we find a large enough match (but stopping at root category!):

    while(count($aMergedProdIds) < $iNumberFeaturedItems && $oCurrentCategory->getId() != Mage::app()->getStore()->getRootCategoryId()): 
        $oCurrentCategory = $oCurrentCategory->getParentCategory();
        $aParentCatProdIds = $oCurrentCategory->getProductCollection()->getAllIds();
        $aMergedProdIds = array_intersect($aFeaturedProdIds,$aParentCatProdIds);
    endwhile;

Finally, filter our initial collection by the IDs of the intersecting products, and return.

    $aFeaturedItems = $aFeaturedCollection->addIdFilter(array_slice($aMergedProdIds,0,$iNumberFeaturedItems))->getItems();
    return $aFeaturedItems;