Magento Front End Pagination Not Working for Custom Table Join Query Collection

magento-1.9paginationPHP

i working with the custom collection pagination, its working much good for single table collection, but for the join query collection, the pagination is not working, below i give as much a information for your further consideration.

i having two table

flquestion(table 1):
-------------------
question_id
question_title
question_description
question_category

flquestion_answer(table 2) (one question have a multiple answers)
-------------------------
answer_id
quesiton_id
answer_description

my query using query buider :

SELECT `main_table`.*, COUNT(main_table.question_id) AS `total_answer` FROM `flquestion` AS `main_table` LEFT JOIN `flquestion_answer` AS `answer` ON main_table.question_id = answer.question_id where main_table.status=1 GROUP BY `main_table`.`question_id` ORDER BY `main_table`.`question_id` desc

when i run this query i got the full result (nearly 7 records having), but the pagination not working properly , for more information see below my screen shot and the block file code

code in block file

public function __construct()
{
    parent::__construct();
    $collection = Mage::getModel('flquestion/question')->getAllQuestion();
    $this->setCollection($collection);
}

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

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

screen shot

enter image description here

Best Answer

the problem is the group by statement.
Magento's pagination system does not play well withe group by statements. You will always get a wrong result for the number of items.

You need to reset the group by in the select that calculates the total number of items.
This can be achieved by adding the following method in the collection resource model for your main entity (question in this case). 

public function getSelectCountSql()
{
    $countSelect = parent::getSelectCountSql();
    $countSelect->reset(Zend_Db_Select::GROUP);
    return $countSelect;
}

Actually you can add this method for all your collection resource models.
You will find the collection resource model in [Namespace]/[Module]/Model/Resource/[Entity]/Collection.php.

Related Topic