Home

catalog

Bestsellers list vs flat product catalog in Magento

18/02/2010 - 17:54 / Posted by Dominik Andruszak
44 comments

Magento uses the EAV database model by default, but it's also possible to use a flat product catalog in your shop. Enabling this option can result in improved performance, but it also may cause some of the sites to display unproperly. A best selling products list could be an example of that.

A best-seller list can be built using collections:

$collection = Mage::getResourceModel('reports/product_collection');

$collection->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds());

$collection = $this->_addProductAttributesAndPrices($collection)
    ->addOrderedQty()
    ->addStoreFilter()
    ->setOrder('ordered_qty', 'desc')
    ->setPageSize(5)
    ->setCurPage(1);

$this->setProductCollection($collection);

This way we create a collection of products ordered by the quantity of orders. However - when diplaying the collection in the template you may find that some of the data (product name, image) was not properly fetched and are not displayed. This is a result of the addOrderedQty method performing a wrong query. Here's the solution.

The addOrderedQty method is a part of the Mage/Reports module - you can find it in Magento's app/code/core/Mage/Reports/Model/Mysql4/Product/Collection.php. The interesting part lies at the very bottom of this function:

$this->getSelect()
    ->joinInner(
        array('e' => $this->getProductEntityTableName()),
        "e.entity_id = order_items.{$productIdFieldName} AND e.entity_type_id = {$this->getProductEntityTypeId()}{$productTypes}"
    )
    ->group('e.entity_id')
    ->having('ordered_qty > 0');

To resolve the problem with the flat catalog, simply rewrite the model and modify this function by replacing the last part with:

if ($this->isEnabledFlat()) {
    $this->getSelect()->joinInner(
        array('e' => $this->getResource()->getFlatTableName()),
        "e.entity_id = order_items.{$productIdFieldName}{$productTypes}"
    );
}
else {
    $this->getSelect()
        ->joinInner(
            array('e' => $this->getProductEntityTableName()),
            "e.entity_id = order_items.{$productIdFieldName} AND e.entity_type_id = {$this->getProductEntityTypeId()}{$productTypes}"
        );
}

$this->getSelect()
    ->group('e.entity_id')
    ->having('ordered_qty > 0');

That should fix the problem.