Custom resource model for Magento product flat tables

Custom resource models in Magento are crucial for optimizing data retrieval, particularly from product flat tables. These models allow for more efficient data access, reducing load times and enhancing user experience on eCommerce platforms. By implementing custom resource models, businesses can address specific data retrieval challenges unique to their operations, leading to improved performance and scalability.

Atwix, with its extensive expertise in Magento, is well-positioned to develop these custom solutions. Their deep understanding of Magento’s architecture and best practices ensures that custom resource models are not only effective but also seamlessly integrated with the overall Magento ecosystem, providing a robust and optimized eCommerce experience.

In this article we will try to explain how to create your own resource model for getting data from the product flat tables. First of all you need to know why we should do this. It’s simple.

Understanding Magento Product Flat Tables

Magento’s Entity-Attribute-Value (EAV) structure is designed for flexible data storage, allowing for the dynamic addition of attributes to entities like products without altering database tables. However, this flexibility can lead to performance issues due to the complex queries required to retrieve data.

Product flat tables, a feature in Magento, address this by consolidating data into a single, flat structure, enhancing query efficiency and site performance. While this improves speed, especially for product listing and filtering, it can limit the dynamic nature of attribute management and increase database size, potentially impacting other areas of performance. This trade-off is important to consider when optimizing Magento stores.

For example, you have a category page with configurable products and you need to get all attribute values on this page.

Can anybody tell me how to do it and avoid using the following code snippet?


The previous example is not good, because it would make category page very slow and grab a ton of unneeded attributes.
We’ve found another way.

Steps to Create a Custom Resource Model for Product Flat Tables

First, you should create your module, Magento geeks know how to do this, newbies can find this info in Google or in the our blog :)
Next step is creating the resource model file, we call it

Below you can see code of this file


class Atwix_Mymodule_Model_Resource_Flat extends Mage_Core_Model_Resource_Db_Abstract
    protected $_storeId;

    protected function _construct()
        $this->_init('catalog/product_flat', 'entity_id');
        $this->_storeId = (int)Mage::app()->getStore()->getId();

    public function getData($entityId)
        $resource = Mage::getSingleton('core/resource');
        $select = $resource->getConnection('core_read')->select();
            ->from($this->getTable(array('catalog/product_flat', $this->_storeId)), '*')
            ->where('entity_id = :entity_id');

        $result = $resource->getConnection('core_read')->fetchAll($select, array('entity_id' => $entityId));

        return $result;

We avoided using magento models or collections for getting data.
The method getData returns array with data selected from catalog_product_flat_n table, you can pass any conditions you want into the where() function. You can add join(), group(), etc.. methods to prepare and sort of your dataset.

What would you like to read in the next article? Please, post in the comments, any your ideas are warmly welcome.

Thanks and enjoy.


  • What benefits can a custom resource model bring to a Magento store?
  • How does a custom resource model impact Magento store maintenance and upgrades?
  • Are there risks or challenges associated with creating custom resource models for Magento?