Magento – How to get collection object from custom query result in pagination

magento-1.9pagination

I have created a custom table in my magento store and wants to show its data on frontend.

For this I have created a block file in my custom module like : 

class Custom_Module_Block_Pagination extends Mage_Core_Block_Template
{

    public function __construct()
    {
        parent::__construct();
        $customer = Mage::getSingleton('customer/session')->getCustomer();
        $readConnection = Mage::getSingleton('core/resource')->getConnection('core_read');
        $rows = $readConnection->fetchAll('Select * from customer_table where c_id = '.$customer->getId().'');

        $collection = new Varien_Data_Collection();
        foreach($rows as $row){
            $rowObj = new Varien_Object();
            $rowObj->setData($row);
            $collection->addItem($rowObj);
        }
        $this->setCollection($collection);


        /* // this collection works perfect with pagination 
        $collection = Mage::getModel('customer/customer')->getCollection()->addAttributeToSelect('*');
        $this->setCollection($collection);
        */
    }

     protected function _prepareLayout()
    {
        parent::_prepareLayout();
        $pager = $this->getLayout()->createBlock('page/html_pager', 'custom.pager');
        $pager->setAvailableLimit(array(10=>10,20=>20));
        $pager->setCollection($this->getCollection());
        $this->setChild('pager', $pager);
        $this->getCollection()->load();
        return $this;
    }

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

On frontend the collection not working with pagination.

For example : the collection return 43 rows and pagination showing number of 5 pages in toolbar with Row per page is 10 but collection showing all 43 rows on all pages. but it should showing only 10 rows on all pages.

Why this is happened? How to resolve this?

EDIT :
My template file :

$collection = $this->getCollection();
echo $this->getToolbarHtml();
foreach($collection as $row)
{
    // here tables's tbody
}

Best Answer

The issue is related to Magento's Varien_Data_Collection It does not support pagination. As you can see an answer here and also a possible solution here
You can always use a Model for getting collection.
EDIT
One more reference to look at.

Related Topic