Magento – magento customer table join query group by make problem in pagination

customerjoin-tablemagento-1.9PHP

I wrote the join query with customer table. the query is, when I execute this query they will return 5 records:

$collection = $this->getCollection()
    ->addAttributeToSelect('userid')
    ->addAttributeToSelect('firstname')
    ->addAttributeToSelect('lastname')
    ->addAttributeToSelect('profile_image')
    ->addAttributeToSelect('specialist')
    ->addAttributeToFilter('group_id', 5);
$collection->getSelect()
    ->joinLeft(
        array('rating' => 'flrating'), 
        'e.entity_id = rating.reviewed_user_id', 
        array('total_rating' => 'count(rating.review_id)')
    );
$collection->getSelect()
    ->group('e.entity_id');

But when I execute the simple query they will return correct result (6 records):

$collection = $this->getCollection()
    ->addAttributeToSelect('userid')
    ->addAttributeToSelect('firstname')
    ->addAttributeToSelect('lastname')
    ->addAttributeToSelect('profile_image')
    ->addAttributeToSelect('specialist')
    ->addAttributeToFilter('group_id', 5);
return $collection;

Even I put the following code in collection table also the code is:

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

Can you please explain what is my problem and how to overcome the pagination issue, for your further information see the below screen:

Result with join query:
Result with join query

Result with simple query:

Result with simple query

Best Answer

Please try the following code:

$collection = $this ->getCollection()
                    ->addAttributeToSelect(array('userid', 'firstname', 'lastname', 'profile_image', 'specialist'))
                    ->addAttributeToFilter('group_id', array('eq' => 5));
$collection->getSelect()->joinLeft(array('rating'=>'flrating'),'main_table.entity_id = rating.reviewed_user_id',array('total_rating'=>'count(rating.review_id)'));
$collection->getSelect()->group('main_table.entity_id');
Related Topic