CODEWITHSUNDEEP
phpmagento
ben
ben

Reputation: 1936

Can I add other attributes to magento's flat product catalog table?

I am in the process of optimizing magento store, and I've run across a couple of posts that recommend using the Flat Product Catalog for stores with a large amount of SKUs.

As I have over 10K products I thought I'd give it a try. However, when using the Flat Product Catalog only a select few attributes are loaded in product object (e.g. SKU, name, short description). And my template displays a few other attributes in the search/browse view, such as Manufacturer and Color.

Is there a way to add these attributes to the flat product catalog table so they too can be accessed?

Upvotes: 19

Views: 19504

Answers (4)

Fiasco Labs
Fiasco Labs

Reputation: 6457

1.4.x.x, just go into the attributes you want to be used in the "Flat Product Catalog" and make sure the property "Used in Product Listing" is set to Yes. Upon making changes, reindex "Flat Product Data"

The following properties cause the attribute to be included in the "Flat Product Catalog":

"Use in Layered Navigation" = Yes
"Used in Product Listing" = Yes
"Used for Sorting in Product Listing" = Yes

Upvotes: 45

Tom
Tom

Reputation: 3331

If you want to make use of an attribute in a module while using the flat product table (filter a collection of products for example) you could add it to a module's Module_File.xml

<?xml version="1.0"?>
<config>
    <modules>
        <Your_Module>
            <active>true</active>
            <codePool>local</codePool>
        </Your_Module>
    </modules>
    <frontend>
        <product>
            <collection>
                <attributes>
                    <your_custom_attribute /> <!-- This is the attribute name -->
                </attributes>
            </collection>
        </product>
    </frontend>
</config>

Upvotes: 2

Kyle Peacock
Kyle Peacock

Reputation: 61

Others have provided the usually sufficient answer (setting Used In Product Listing to Yes), but I found an edge case that would have been useful to know when I was searching for this same thing.

If you are using a custom source model for the custom attribute you would like to include in the flat product table, you will have to override getFlatColums() in the source model class. Yes, I know it's misspelled, but that's native Magento for you.

Sample:

public function getFlatColums() {
    return array($this->getAttribute()->getAttributeCode() => array(
        'type'      => 'tinyint',
        'unsigned'  => true,
        'is_null'   => true,
        'default'   => null,
        'extra'     => null
    ));
}

Source: http://www.dconstructing.com/2012/03/14/custom-product-attributes-and-flat-database-tables-in-magento

Upvotes: 5

johnorourke
johnorourke

Reputation: 151

I have been working through this issue too, which I would describe as "cannot access product collection attribute in flat mode" or "addAttributeToSelect not working in flat mode".

I found a "clean" solution which:

  • does not require the attribute to have specific settings in admin (it might be added by a user, or hidden on the front end)
  • works for both flat and non-flat mode

Please note - in the code below I have used the associated product collection, but this applies to any product collection (specifically, anything inheriting from Mage_Eav_Model_Entity_Collection_Abstract)

Failing code:

$_product = Mage::getModel('catalog/product')->loadByAttribute( 'sku', 'ABC123' );
$coll = $_product->getTypeInstance()->getAssociatedProductCollection()
    ->addAttributeToSelect( 'my_custom_attribute' )
;

In flat mode, the above code silently fails to add the attribute if it happens not to be in the flat table.

Working code:

$_product = Mage::getModel('catalog/product')->loadByAttribute( 'sku', 'ABC123' );
$coll = $_product->getTypeInstance()->getAssociatedProductCollection()
    ->joinAttribute( 'my_custom_attribute', 'catalog_product/my_custom_attribute', 'entity_id', null, 'left' )
    ->addAttributeToSelect( 'my_custom_attribute' )
;

The joinAttribute method adds a join to the query. It works even if this duplicates an attribute that's already in the flat table.

Note that I've used a left join there, to ensure that it fetches products if my_custom_attribute is not set on those products. You can change that for inner if you're only interested in rows where my_custom_attribute is set.

(tested in CE 1.6.2.0)

Upvotes: 11

Related Questions