Magento – Grid pagination does not work in magento

collection;gridmagento-1

Pagination is not working in my grid. All records are being shown on same page.
All though Pagination is being shown at the top but all pages shows same records.
Here is the code–

protected function _prepareCollection()
    {
         if ($this->getRequest()->getParam('website')) {
            $storeIds = Mage::app()->getWebsite($this->getRequest()->getParam('website'))->getStoreIds();
            $storeId = array_pop($storeIds);
        } else if ($this->getRequest()->getParam('group')) {
            $storeIds = Mage::app()->getGroup($this->getRequest()->getParam('group'))->getStoreIds();
            $storeId = array_pop($storeIds);
        } else if ($this->getRequest()->getParam('store')) {
            $storeId = (int)$this->getRequest()->getParam('store');
        } else {
            $storeId = '';
        }


        $query="SELECT `e`.*, 
                    `at_status`.`value` AS `status`,
                    `at_name`.`value` AS `name`,
                    `at_taq`.`value` AS `total_available_qty`,
                    `at_price`.`value` AS `price`,
                    `at_notify_stock_qty`.`notify_stock_qty` 
                    FROM `catalog_product_entity` AS `e` 

                    INNER JOIN `catalog_product_entity_int` AS `at_status`
                    ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '273') AND (`at_status`.`store_id` = 0) 

                    INNER JOIN `catalog_product_entity_varchar` AS `at_name` 
                    ON (`at_name`.`entity_id` = `e`.`entity_id`) AND 
                    (`at_name`.`attribute_id` = '96')

                    INNER JOIN `catalog_product_entity_varchar` AS `at_taq` 
                    ON (`at_taq`.`entity_id` = `e`.`entity_id`) AND 
                    (`at_taq`.`attribute_id` = '987')

                    INNER JOIN `catalog_product_entity_decimal` AS `at_price` 
                    ON (`at_price`.`entity_id` = `e`.`entity_id`) AND 
                    (`at_price`.`attribute_id` = '99') 

                    INNER JOIN `cataloginventory_stock_item` AS `at_notify_stock_qty`
                    ON (at_notify_stock_qty.`product_id`=e.entity_id) AND 
                    (at_notify_stock_qty.stock_id=1) WHERE (at_status.value = '1') AND (at_notify_stock_qty.notify_stock_qty > at_taq.value)";

        $connectionRead = Mage::getSingleton('core/resource')->getConnection('core_read'); 
        $collection2=$connectionRead->fetchAll($query);

        $collection = new Varien_Data_Collection();             
        foreach ($collection2 as $item) {
            $varienObject = new Varien_Object();
            $varienObject->setData($item);
            $collection->addItem($varienObject);
        }

        if( $storeId ) {
            $collection->addStoreFilter($storeId);
        }                                              

        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

Can any one help me what I am doing wrong ?

Solution–

I think the main problem was in collection which is mase by custom mysql query, so Now I used below code that worked fine--

$collection = Mage::getModel('catalog/product')->getCollection()
                                                       ->addFieldToFilter('status',1)
                                                        ->addAttributeToSelect('total_available_qty')
                                                       ->addFieldToFilter('total_available_qty',array('gteq'=>0))
                                                       ->addAttributeToSelect('*')
                                                       ->joinField('notify_stock_qty',
                                                         'cataloginventory/stock_item',
                                                         'notify_stock_qty',
                                                         'product_id=entity_id',
                                                         '{{table}}.stock_id=1',
                                                         'left');
        $collection ->getSelect()->where('`at_notify_stock_qty`.`notify_stock_qty` > `at_total_available_qty`.`value`');

Best Answer

The problem is the following:

The class Varien_Data_Collection implements pagination methods like setPageSize and setCurPage but the implementation of IteratorAggregate does not reflect when pagination has been enabled. It always returns an ArrayIterator over all items, not reflecting the pagination (no provider for pagination).

I suggest you look at the following resources to fix your problem:

Collections and Lazy Loading in Magento

The reason pagination does not work is because of how collections are counted and how lazy loading works with collections.

Collections in Magento implement the class Countable. Due to lazy loading of collections in Magento, whenever the method count() is called, the data has to be loaded. As a workaround of this, collections implement a method called getSize(). It will clone your SQL statement, wrap it in a COUNT() and return the result. This allowed a collection to get a total count without loading all the data. This allows for things such as filters to be added at the last minute.

This is what Varien_Data_Collection_Db::getSize() and it's partner getSelectCountSql() looks like:

/**
     * Get collection size
     *
     * @return int
     */
    public function getSize()
    {
        if (is_null($this->_totalRecords)) {
            $sql = $this->getSelectCountSql();
            $this->_totalRecords = $this->getConnection()->fetchOne($sql, $this->_bindParams);
        }
        return intval($this->_totalRecords);
    }

    /**
     * Get SQL for get record count
     *
     * @return Varien_Db_Select
     */
    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;
    }

Basically, it drops limits, columns, ordering, etc and leaves the filters behind. Then it adds a MySQL COUNT() to the columns.

The Problem

Normally, on one table, this would return one row with the total count. This is why getSize() does a fetchOne() against the query. However, when doing things such as table joins, group bys, and the like, you will not return one row, you will return multiple. It is because of this that you need to alter the getSize() method in your collection.

The Solution

This is what your method should look like now:

public function getSize() {

        if ( is_null( $this->_totalRecords ) ) {
            $sql = $this->getSelectCountSql();
            // fetch all rows since it's a joined table and run a count against it.
            $this->_totalRecords = count( $this->getConnection()->fetchall( $sql, $this->_bindParams ) );
        }

        return intval( $this->_totalRecords );
    }

Instead of a fetchOne(), we ran a fetchAll() wrapped in a count() PHP function. Now your totals will return appropriately.

Best Answer

Related Solutions

Magento – FireGento_GermanSetup inline translation does not work

Magento1.9 Grid Pagination – Fix Group Clause Issue

Collections and Lazy Loading in Magento

The Problem

The Solution

Related Topic