Untitled

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

$_testproductCollection = Mage::getResourceModel('catalog/product_collection');

$_testproductCollection->getSelect()->distinct(true);

$_testproductCollection->addAttributeToSelect('*')->load();

       

$_testproductCollection->joinField('store_id', 'catalog_category_product_index', 'store_id', 'product_id=entity_id', '{{table}}.store_id = 1', 'left');

       

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

       

grep '>addStoreFilter' app/code -rsn

       

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

       

$_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/>";

};