0

This question stems from my other recent question on here where I was trying to create a best sellers block on the Magento homepage.

I'm not sure if there is a best way of handling this.

I have the following function to get a list of the best sellers:-

public function getBestsellerProducts()
{
    $storeId = (int) Mage::app()->getStore()->getId();

    // Date
    $date = new Zend_Date();
    $toDate = $date->setDay(1)->getDate()->get('Y-MM-dd');
    $fromDate = $date->subMonth(1)->getDate()->get('Y-MM-dd');

    $collection = Mage::getResourceModel('catalog/product_collection')
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addStoreFilter()
        ->addPriceData()
        ->addTaxPercents()
        ->addUrlRewrite()
        ->setPageSize(3);

    $collection->getSelect()
        ->joinLeft(
            array('aggregation' => $collection->getResource()->getTable('sales/bestsellers_aggregated_monthly')),
            "e.entity_id = aggregation.product_id AND aggregation.store_id={$storeId} AND aggregation.period BETWEEN '{$fromDate}' AND '{$toDate}'",
            array('SUM(aggregation.qty_ordered) AS sold_quantity')
        )
        ->group('e.entity_id')
        ->order(array('sold_quantity DESC', 'e.created_at'));

    Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
    //Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);

    return $collection;
}

You will notice that the filter for visible products only is commented out. Many websites show configurable products in the frontend that have many associated products set to not visible individually. All the sales reported on the configurable products report NULL because the sales are actually associated to the simple products and these simple products can only be shown with that line above commented out. And if this line is commented out, then the best sellers block will show simple products that aren't meant to be visible in the frontend.

Is there a way of associating the sales from a parent products simple products to the configurable by modifying the above select in the function?

1

I have solved this by modify the select with a prepending reset, a new from and additional joinLeft.

This is only with configurable products tested, i don't know what happens if you have only simple products without parent.

    $collection = Mage::getResourceModel('catalog/product_collection');
    $collection->getSelect()
        ->reset()
        ->from(array('aggregation' => $collection->getResource()->getTable('sales/bestsellers_aggregated_monthly')), null)
        ->joinLeft(
            array('pr' => $collection->getResource()->getTable('catalog/product_relation')),
            'aggregation.product_id = pr.child_id',
            array('SUM(aggregation.qty_ordered) AS sold_quantity')
        )
        ->joinLeft(
            array($collection::MAIN_TABLE_ALIAS => $collection->isEnabledFlat() ? $collection->getEntity()->getFlatTableName() : $this->getEntity()->getEntityTable()),
            'e.entity_id = pr.parent_id',
            array('entity_id', 'type_id', 'attribute_set_id')
        )
        ->where("aggregation.store_id={$storeId} AND aggregation.period BETWEEN '{$fromDate}' AND '{$toDate}'")
        ->group('e.entity_id')
        ->order(array('sold_quantity DESC', 'e.created_at'));

    $collection
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addStoreFilter()
        ->addPriceData()
        ->addTaxPercents()
        ->addUrlRewrite()
        ->setPageSize($this->getProductsCount());

    Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
    Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);
| improve this answer | |
  • Hey great work, thanks for posting it, I will give it a go and see how I get on - I suppose we might be able to come up with an if statement to fire this only for configurable products else the original...? – zigojacko Jan 12 '15 at 8:35
  • Finally got round to working with your code snippet and it works great - thanks very much! – zigojacko Feb 1 '15 at 17:03
  • This does indeed exclude products that do not have a parent/configurable. Trying to work out how to modify this to include plain simple products. – zigojacko May 24 '16 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.