/** * @param Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection $collection * @param $categoryId */ protected function _prepareProductCollection($collection, $categoryId) { $res = $collection->getResource(); $db = $res->getReadConnection(); $storeId = Mage::app()->getStore()->getId(); $subSelect = $db->select()->from(array('subcat_index' => $res->getTable('catalog/category_product_index')), new Zend_Db_Expr("`subcat_index`.`product_id`"))->joinInner(array('subcat' => $res->getTable('catalog/category')), "`subcat`.`entity_id` = `subcat_index`.`category_id`", null)->where("`subcat_index`.`store_id`={$storeId} AND `subcat_index`.`visibility` IN(2, 4) AND `subcat`.`parent_id` = ?", $categoryId); $collection->getSelect()->where("`e`.`entity_id` IN ({$subSelect})"); }