2

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

2 Answers 2

1

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');
2
  • throw an error : Column not found: 1054 Unknown column 'main_table.entity_id' in 'group statement'
    – senthil
    Jul 7, 2015 at 9:53
  • Add the following line after first statement: Mage::log((string)$collection->getSelect(),null,'test.log'); And then go to /var/log/test.log and check what's the name used by main_table Jul 7, 2015 at 10:02
1

Look like your query is OK. I don't know the structure of table lawer (customer), but the last one should be:

$collection->getSelect()->group('e.userid');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.