Below is the code for creating a custom collection, but it is breaking pagination. It is showing only one item in pagination, When I am printing getSize() it is giving one(which is wrong), but on printing count() it is printing correct result.

               $sampleqry = 'select entity_id from catalog_product_entity_int as sample where sample.store_id=0 and sample.attribute_id="169" and sample.value=0';

                $this->_productCollection->getSelect()->joinLeft(array('r' => 'catalog_product_relation'), 'r.parent_id=e.entity_id', array());
                $this->_productCollection->getSelect()->joinLeft(array('s' => 'sales_flat_order_item'), '(r.child_id=s.product_id AND s.product_id IN(' . $sampleqry . ')) OR (s.product_id=e.entity_id)', array('SUM(s.qty_ordered) as qtyordered'));
                $this->_productCollection->getSelect()->order('qtyordered DESC');

Same code is working fine in Magento 1.4 but not in
How can I fix this issue, any help will be appreciated.


when you apply group on collection magento collection counts gets break, this i had fixed by using below code.


protected function _getSelectCountSql($select = null, $resetLeftJoins = true)
       $countSelect = (is_null($select)) ?
           $this->_getClearSelect() :

       if(count($countSelect->getPart(Zend_Db_Select::GROUP)) > 0) {

       $countSelect->columns('COUNT(DISTINCT e.entity_id)');
       if ($resetLeftJoins) {
       return $countSelect;
  • Thanks, it worked. But can you let me know how did you figured it out. – Anshu Mishra Feb 11 '14 at 8:38
  • year back i had created 1 extension for admin and i had did multiple joins and had used group also, at that time i had done extensive research on this and had came across with these solution :) – Deepak Mallah Feb 11 '14 at 9:04
  • welcome buddy.. – Deepak Mallah Feb 11 '14 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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