Magento - joinField() query to add store_id to products in collection

I've been trying for months now to filter a product collection by it's store root category. I've tried numerous different ways ( Magento - addStoreFilter not working? , Magento ->addCategoryFilter - filtering product collection by root category , Magento - Loading root collection loads all products ) but still haven't found anything that works.

So I want to try adding in the store_id to the products in a collection. I can call the collection using the following, but I need to join some fields in order to add the store_id:

$_testproductCollection = Mage::getResourceModel('catalog/product_collection');
$_testproductCollection->getSelect()->distinct(true);
$_testproductCollection->addAttributeToSelect('*')->load();

The joinField I need is something like the following (which was given in a post linked above) but the store_id=1 part changes every products store_id to 1, which I don't want, I just want a way to add it's designated store so I can then filter by it.

$_testproductCollection-开发者_如何学JAVA>joinField('store_id', 'catalog_category_product_index', 'store_id', 'product_id=entity_id', '{{table}}.store_id = 1', 'left');

Can anyone point me in the right direction? Cheers!

I can think of a way with subqueries. The following excerpts are taken from a library I'm working on. They are placed in the lib/ dir so are accessible to the autoloader. I haven't yet profiled the performance but my gut tells me MySQL will cache the subqueries separately so when reused their results are nice and fast. I may have to implement these as database views if the performance isn't so good.

The key to this technique is understanding that Zend's selects can be used directly as SELECT, FROM or WHERE clauses and automatically become a sub-query.

// A shortened example, not tested directly
class Example_Product_List extends Varien_Db_Select {

    protected $_resource;

    public function __construct($storeId) {
        // Since this isn't a collection we need to get the resource ourselves
        $this->_resource = Mage::getSingleton('core/resource');
        $adapter = $this->_resource->getConnection('core_read');
        parent::__construct($adapter);

        $this->from(
            array('catprod'=>$this->getTable('catalog/category_product')),
           'product_id'
        );
        $this->joinInner(
            array('catprodin'=>$this->getTable('catalog/category_product_index'),
            '(catprodin.category_id=catprod.category_id) AND (catprodin.product_id=catprod.product_id)',
            'store_id'
        );
        $this->where('store_id = ?', $storeId);
        $this->group('product_id');
    }

    public function getTable($modelEntity)
    {
        return $this->_resource->getTableName($modelEntity);
    }

}

// Using it in a collection
class Example_Module_Model_Resource_Product_Collection extends Mage_Catalog_Model_Resource_Eav_Mysql4 {

    protected function setStoreId($store) {
        $this->getSelect()->where(array(
            'attribute'=>'product_id',
            'in'=>new Example_Product_list($store->getId())
        );
        return parent::setStoreId($store);
    }
}

// Putting it to use, '1' can be any store ID.
$_testproductCollection = Mage::getResourceModel('examplemodule/product_collection');
$_testproductCollection->addStoreFilter(1);
$_testproductCollection->addAttributeToSelect('*')->load();

Near the end I use the inherited addStoreFilter() because I want to not only include the correct products but use the store's appropriate values too. If your stores have translated product names this will adopt the correct one.

Basically the entire thing is constructing a list of all products for the store (quite big which is why I hope it caches well) then effectively does "WHERE product_id IN (list_of_product_ids)".

search in core code:

grep '>addStoreFilter' app/code -rsn 

and observe usages in existing code:

Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('*')->addStoreFilter(Mage::app()->getStore()->getId());

OK, I think this works, haven't tested too much but seems to have done the trick. You need to first get your stores root category id, then join some fields so you have access to the products "category_id", then filter using that:

$_rootcatID = Mage::app()->getStore()->getRootCategoryId();

$_testproductCollection = Mage::getResourceModel('catalog/product_collection')
->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left')
->addAttributeToFilter('category_id', array('in' => $_rootcatID))
->addAttributeToSelect('*');
$_testproductCollection->load();

foreach($_testproductCollection as $_testproduct){ 
    echo $this->htmlEscape($_testproduct->getName())."<br/>"; 
};