How to set a store ID on Mage_Catalog_Model_Resource_Product_Collection?

What version of Magento is this? These are my results for Magento 1.9:

Flat catalog enabled:

Flat catalog is indexed:

Some data set in a specific store view:

Code used:

<?php

require_once 'app/Mage.php';

Mage::app('admin');

$collection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect('*')                                                                                                                                                                                                                                                 
    ->addFieldToFilter('entity_id', array('eq' => 231))
    ->setStore(2);

var_dump($collection->getFirstItem()->getName());

Result is as expected:

string(18) "But I Am Le French"

edit:

Nevermind, flat catalog is specifically prohibited for admin store:

// Flat Data can be used only on frontend
if (Mage::app()->getStore()->isAdmin()) {
    return false;
}

Investigating...

edit2:

It looks like you are right. _initSelect is called before we are able to modify the storeId which is used to generate the table name.

Of course (if we don't want to go the rewrite route) we can:

• getSelect(), do a reset and set a new from()
• $collection->getEntity()->setStoreId(123) and then use reflection to call _initSelect again
• Just create our own resource model and extend from flat, give some way of inserting storeId at the right time (__construct, delaying _initSelect, etc).
• call setCurrentStore everytime we create the collection.

But these all feel very hacky... Sorry, this may be an unsatisfactory answer :-(

edit3:

So for the sake of providing at least an answer:

// Get collection and update store ID.
$collection = Mage::getResourceModel('catalog/product_collection');
$collection->getEntity()->setStoreId(2);

// Reset the select.
$collection->getSelect()->reset();

// Update table name.
$reflectionMethod = new ReflectionMethod($collection, '_initSelect');
$reflectionMethod->setAccessible(true);
$reflectionMethod->invoke($collection);

// Do any other operations on the collection now.
$collection->addAttributeToSelect('*');

Please don't use that ;-)

So I consider these to be two bugs in Magento.

First one is the fact that you can't set store ID on catalog/product collection. And the second is that you absolutely can't get resource model as non-singleton.

So stupid workaround is to instantiate model twice. The first time the store ID can be set and the second instantiation will use it:

Mage::getResourceModel('catalog/product_collection')->setStore($storeId);

$collection = Mage::getResourceModel('catalog/product_collection')

Interestingly, the used flat table is set once and never changed which works for EAV since table name doesn't change but not for flat since the table name includes the store ID. A workaround would be to make a helper that would swap out the table in the FROM part of the query. Here's an example of such a helper:

class My_Module_Helper_Data extends Mage_Core_Helper_Abstract
{
    public function getProductCollectionForStore($store)
    {
        $collection = Mage::getResourceModel('catalog/product_collection');

        // Change the store on the entity
        // This doesn't change it in the (already constructed) SQL query
        $collection->setStore($store);

        if (! $collection->isEnabledFlat()) {
            return $collection;
        }

        // Change the used table to the $store we want
        $select = $collection->getSelect();
        $from = $select->getPart('from');

        // Here, getFlatTableName() will pick up the store set above
        $from[$collection::MAIN_TABLE_ALIAS]['table'] =
        $from[$collection::MAIN_TABLE_ALIAS]['tableName'] = 
            $collection->getEntity()->getFlatTableName();

        $select->setPart('from', $from);
        return $collection;
    }
}

Then you can use it simply with:

$collection = Mage::helper('my_module')->getProductCollectionForStore('somestore')
    ->addAttributeToSelect('name');

I imagine this would cause no trouble for the SQL since you're fetching all data from a single flat table but since it's a singleton the last used store would be used everywhere else.

Alternative solution would be to make an observer on catalog_product_collection_load_before which does something like this:

class My_Module_Model_Observer
{
    public function setCorrectFlatStore(Varien_Event_Observer $observer)
    {
        $collection = $observer->getCollection();
        if (! $collection->isEnabledFlat()) {
            return;
        }

        $select = $collection->getSelect();
        $from = $select->getPart('from');

        // If somebody called setStore() on the collection make sure
        // to update the used flat table
        $from[$collection::MAIN_TABLE_ALIAS]['table'] =
        $from[$collection::MAIN_TABLE_ALIAS]['tableName'] =
            $collection->getEntity()->getFlatTableName();

        $select->setPart('from', $from);
    }
}

I agree that Magento guys should fix this in the _beforeLoad() method.