Magento Product Collection query is running multiple times

Question

I am making use of 3rd party free extension for displaying top seller block.

Code is very simple to understand, but when i use getSelect() method to display SQL query i found same query being displayed multiple times instead of once.

I am not sure why its happening.

Here is the below _getProductCollection() function

 protected function _getProductCollection()
    {
        parent::__construct();
        $storeId    = Mage::app()->getStore()->getId();
        $todayDate  = Mage::app()->getLocale()->date()->toString(Varien_Date::DATETIME_INTERNAL_FORMAT);

        $products = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('*')
            ->addAttributeToSort("entity_id","DESC")
            ->addAttributeToSelect(array('name', 'price', 'small_image'))
            ->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInSiteIds())
            ->setOrder($this->get_order(), $this->get_order_dir());
        echo $products->getSelect()."<br>";
        if(Mage::getStoreConfig('catalogextensions/config4/max_product'))
        {
            $products->setPageSize(Mage::getStoreConfig('catalogextensions/config4/max_product'));
        }


        Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($products);
        Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($products);
        Mage::getSingleton('cataloginventory/stock')->addInStockFilterToCollection($products);

        $this->_productCollection = $products;

        return $this->_productCollection;
    }

And below is what i get

same query getting repeated multiple times .

Thank you.

Answer 1

Change the method you posted to

protected function _getProductCollection()
{
    if (!$this->_productCollection) {
        parent::__construct();
        $storeId    = Mage::app()->getStore()->getId();
        $todayDate  = Mage::app()->getLocale()->date()->toString(Varien_Date::DATETIME_INTERNAL_FORMAT);

        $products = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('*')
            ->addAttributeToSort("entity_id","DESC")
            ->addAttributeToSelect(array('name', 'price', 'small_image'))
            ->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInSiteIds())
            ->setOrder($this->get_order(), $this->get_order_dir());
        echo $products->getSelect()."<br>";
        if(Mage::getStoreConfig('catalogextensions/config4/max_product'))
        {
            $products->setPageSize(Mage::getStoreConfig('catalogextensions/config4/max_product'));
        }


        Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($products);
        Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($products);
        Mage::getSingleton('cataloginventory/stock')->addInStockFilterToCollection($products);

        $this->_productCollection = $products;
    }
    return $this->_productCollection;
}

This should make it load the collection only once.
But this is wrong. You should not call parent::__construct(); in a protected method. Unless you really know what you are doing. I think who wrote this, did not really indent for that.