Grid pagination does not work in magento

Question

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`');

Answer 1

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:

• https://stackoverflow.com/questions/20197518/varien-data-collection-pagination-not-working
• https://stackoverflow.com/questions/8937714/magento-custom-collection-breaks-pagination