How to select from magento EAV tables when flat catalog product data is on

Question

I have this code for selecting best selling products from Magento:

$productCollection = Mage::getResourceModel('reports/product_collection')
            ->addOrderedQty($startTime, $currentTime)
            ->addAttributeToSelect('*')
            ->setStoreId($storeId)
            ->addStoreFilter($storeId)
            ->setOrder('ordered_qty', 'desc')
            ->setPageSize($this->limit());
    }

and it works fine, until I set "use flat catalog product" in backend to yes. Is there any way to tell magento to not use flat tables, and use EAV instead?
Can any one help me with this.

Aerotherapeutics

Answer 1

Create a new model class ('mycatalog/product') that extends the original product class but hard code it to use the EAV resource model and EAV resource collection, and then use that model in your query code.

Anarchist

I struggled for the last few hours trying to figure out why my module works perfectly on one installation and not on another. It created a collection of products and optionally applied a category filter. Problem is, Mage_Catalog_Model_Resource_Product addCategoryFilter() relies upon $category->getId().... Id is not present in a flat category model , whose brilliant idea... I am going to attempt the above and see what I can come up with. Thanks as always Alan. – Tucky

Answer 2

I'd been running my code from a stand alone php file, as soon as i moved my code into an admin module it stopped using the flat_file and went back to eav.

If you look at: Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection

There's a method:

public function isEnabledFlat()
{
    if (Mage::app()->getStore()->isAdmin()) {
        return false;
    }
    if (!isset($this->_flatEnabled[$this->getStoreId()])) {
        $this->_flatEnabled[$this->getStoreId()] = $this->getFlatHelper()
            ->isEnabled($this->getStoreId());
    }
    return $this->_flatEnabled[$this->getStoreId()];
}

You could modify this to add an extra condition that returns false based on your own criteria.

BTW, The reports collection mentioned in the first post by Blazo is an extension of this collection.

Annoying

Answer 3

To expand on Alan's answer:

class Namespace_Module_Model_Category extends Mage_Catalog_Model_Category
{
    protected function _construct()
    {
        $this->_init('catalog/category');

    }

}

The above removes the check to see if flat was enabled and only inits the standard eav verson of the catalog/category resource.

And then when you wish to load your model ensuring that you get the eav model regardless of wither the flat data is enabled:

$category = Mage::getModel('namespace_module/category')->load($id)

Tucky

Answer 4

I have use

Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

before

Mage::getModel('catalog/product')->getCollection()

And it start fetching data from eav based system.

Timbre

This worked for me, I did $storeId = Mage::app()->getStore()->getStoreId(); before so I could get back to my preferred store front afterwards. – Agnola

Answer 5

This is an old post but I thought one important point was not stated. 1. Once you set flat catalog to on you need to run indexer via cron or via admin/shell so that flat catalog tables get populated.

1. If you do have many products in your search bypassing flat catalog table will slow down your site and each search code will consume lots of resources.

Cyndycynera

Answer 6

I found that the easiest solution was to turn off the flat tables and then get the SQL query that magento executes using the ->load(true) parameter

e.g.

$collection = Mage::getModel('catalog/category')->getCollection();
$collection
    ->setStoreId($store->getId())
    ->addAttributeToSelect('*')
    ->addAttributeToFilter(array(array('attribute'=>'ig_unifeed_ids', 'like'=>"%:".$this->getId().":%")))
    ->load(true);

then turn flat tables back on and replace this code with:

$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_read');
$query = "SELECT `e`.*, `at_ig_unifeed_ids`.`value` AS `ig_unifeed_ids` FROM `catalog_category_entity` AS `e` INNER JOIN `catalog_category_entity_varchar` AS `at_ig_unifeed_ids` ON (`at_ig_unifeed_ids`.`entity_id` = `e`.`entity_id`) AND (`at_ig_unifeed_ids`.`attribute_id` = '139') AND (`at_ig_unifeed_ids`.`store_id` = 0) WHERE (`e`.`entity_type_id` = '3') AND ((at_ig_unifeed_ids.value LIKE '%:".$this->getId().":%'))";
$collection = $readConnection->fetchAll($query);

From this point on you will probably need to change other code like replacing

$category->getId()

with

$category["entity_id"]

I hope this helps a bit...

NOTE: this is a real solution for the IG_Unifeed module magento bug that disregards category filtering when using flat tables.

Symbolic

This already had 2 downvotes when I found it, but no explanation for why. I'm guessing it's because this will break when you run the code in a different store. It could only work if that other store had the exact same attribute IDs and was not using table name prefixes. Even if you were to resolve these issues, this solution still seems very fragile and hacky at best. Then again, many such hacky solutions manage to find their way into production code. Use your own wisdom to decide whether it's right for your use-case. – Vincevincelette