Magento 1.7 – Fix Pagination Broken by Custom Collection

collection;magento-1.7

Below is the code for creating a custom collection, but it is breaking pagination. It is showing only one item in pagination, When I am printing getSize() it is giving one(which is wrong), but on printing count() it is printing correct result.

               $sampleqry = 'select entity_id from catalog_product_entity_int as sample where sample.store_id=0 and sample.attribute_id="169" and sample.value=0';

                $this->_productCollection->getSelect()->joinLeft(array('r' => 'catalog_product_relation'), 'r.parent_id=e.entity_id', array());
                $this->_productCollection->getSelect()->joinLeft(array('s' => 'sales_flat_order_item'), '(r.child_id=s.product_id AND s.product_id IN(' . $sampleqry . ')) OR (s.product_id=e.entity_id)', array('SUM(s.qty_ordered) as qtyordered'));
                $this->_productCollection->getSelect()->group('e.entity_id');
                $this->_productCollection->getSelect()->order('qtyordered DESC');

Same code is working fine in Magento 1.4 but not in 1.7.0.2
How can I fix this issue, any help will be appreciated.

Best Answer

when you apply group on collection magento collection counts gets break, this i had fixed by using below code.

app/code/local/Mage/Catalog/Model/Resource/Product/Collection.php

protected function _getSelectCountSql($select = null, $resetLeftJoins = true)
    {
       $this->_renderFilters();
       $countSelect = (is_null($select)) ?
           $this->_getClearSelect() :
           $this->_buildClearSelect($select);


       if(count($countSelect->getPart(Zend_Db_Select::GROUP)) > 0) {
           $countSelect->reset(Zend_Db_Select::GROUP);
       }


       $countSelect->columns('COUNT(DISTINCT e.entity_id)');
       if ($resetLeftJoins) {
           $countSelect->resetJoinLeft();
       }
       return $countSelect;
    }

Related Solutions

Magento Collection – Difference Between getSize() and count()

Most (if not all) the collections extend Varien_Data_Collection_Db. Here are the 2 methods from this class

public function getSize()
{
    if (is_null($this->_totalRecords)) {
        $sql = $this->getSelectCountSql();
        $this->_totalRecords = $this->getConnection()->fetchOne($sql, $this->_bindParams);
    }
    return intval($this->_totalRecords);
} 

public function count() //inherited from Varien_Data_Collection
{
    $this->load();
    return count($this->_items);
}

There is a difference. For getSize() the collection is not loaded. For count() it is. Usually collection models use the same getSize() method as above and only override getSelectCountSql().
In getSelectCountSql() the limit is reset in order to get the total number of records available for the set filters (where statement). See how the getSelectCountSql() works

public function getSelectCountSql()
{
    $this->_renderFilters();
    $countSelect = clone $this->getSelect();
    $countSelect->reset(Zend_Db_Select::ORDER);
    $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
    $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
    $countSelect->reset(Zend_Db_Select::COLUMNS);
    $countSelect->columns('COUNT(*)');
    return $countSelect;
}

Magento – Add pagination to any block’s collection

It's actually quite simple.

In the main block that calls your collection to render, add the following:

protected function _prepareLayout()
{
    parent::_prepareLayout();

    $pager = $this->getLayout()->createBlock('page/html_pager', 'your.custom.blockname.pager')
        ->setCollection($this->getCollection()); //call your own collection getter here, name it something better than getCollection, please; *or* your call to getResourceModel()
    $this->setChild('pager', $pager);
    return $this;
}

public function getPagerHtml()
{
    return $this->getChildHtml('pager');
}

By default this will limit your collection to 10-at-a-time! Sweet!!

Then, from your template phtml associated to your collection, add the pager controls by echoing the output of our other method, getPagerHtml:

<?php echo $this->getPagerHtml(); ?>

_{Source: Experience. And also, the sales/order_history block in Magento.}

Best Answer

Related Solutions

Magento Collection – Difference Between getSize() and count()

Magento – Add pagination to any block’s collection

Related Topic