Magento: Wrong count in admin Grid when using GROUP BY clause, overriding lib module

public function getSelectCountSql()

    {

        $this->_renderFilters();

        $countSelect = clone $this->getSelect();

        $countSelect->reset(Zend_Db_Select::ORDER);

        $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);

        $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);

        $countSelect->reset(Zend_Db_Select::COLUMNS);

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

            $countSelect->reset(Zend_Db_Select::GROUP);

            $countSelect->distinct(true);

            $group = $this->getSelect()->getPart(Zend_Db_Select::GROUP);

            $countSelect->columns("COUNT(DISTINCT ".implode(", ", $group).")");

        } else {

            $countSelect->columns('COUNT(*)');

        }

        return $countSelect;

    }

34 Comments

• 
  Meabed March 15, 2012 at 3:07 pm

  Hi,
  Check this solution its much better i think

  http://stackoverflow.com/questions/8764543/magento-sales-order-grid-shows-incorrect-number-of-records-when-added-names-and/9721900#9721900

  thanks

  Reply
  • 
    SU July 3, 2013 at 6:36 am

    I already check it out. It didn’t work for me.

    Reply
• 
  Pravin April 30, 2012 at 12:13 pm

  i need to add ‘company’ attribute of customer to invoice grid. below is the code i change and its showing correct query results in phpmyadmin, but somehow it only shows 2 as a Total records on Invoice grid.

  protected function _prepareCollection()
  {
  $collection = Mage::getResourceModel($this->_getCollectionClass());
  $collection->getSelect()->joinInner(array(‘order_address’ => Mage::getSingleton(‘core/resource’)->getTableName(‘sales_flat_order_address’)),’order_address.parent_id = main_table.order_id’,array(‘company’))->group(‘parent_id’)->order(‘entity_id’, ‘desc’);

  $this->setCollection($collection);
  return parent::_prepareCollection();
  }

  and query is

  SELECT `main_table`.*, `order_address`.`company` FROM `sales_flat_invoice_grid` AS `main_table` INNER JOIN `sales_flat_order_address` AS `order_address` ON order_address.parent_id = main_table.order_id GROUP BY `parent_id`

  if i change the number of records to 200 per page , it shows all that records , but there is some issue in paging and total records.

  It always shows 1 page and ‘Total 2 records found’. So i can not move to next page.

  Anye help please.

  i think i have the same problem of “Wrong count in admin Grid when using GROUP BY clause, overriding lib module”

  i try the above solution , but i have the same result.

  Reply
• 
  kalpesh April 30, 2012 at 1:14 pm

  Okay Pravin I have updated the above method for generic use now. Your purpose will be solved with this fix, update and let me know.

  Reply
• 
  Pravin May 1, 2012 at 10:59 am

  Thanks Kalpesh,
  Its working fine now.

  thanks again.

  Reply
• 
  idzat May 9, 2012 at 9:33 am

  Hi prakash,

  Im having problems regarding to admin grid review count. i have total review like almost 600 something, but on the total records its show only 70 records.its also causing problem for the pagination. i have tried your solution, but its still the same.

  Reply
  • 
    kalpesh May 9, 2012 at 10:45 am

    First of all, who is prakash? 😀
    Can you please share the query you are using for populating the grid to better understand the problem?

    Reply
• 
  idzat May 10, 2012 at 6:04 am

  Hi kalpesh,

  sorry wrong name. XD
  below is the query that im using rite now ( default magento ).

  public function getSelectCountSql()
  {
  $this->_renderFilters();

  $countSelect = clone $this->getSelect();
  $countSelect->reset(Zend_Db_Select::ORDER);
  $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
  $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
  $countSelect->reset(Zend_Db_Select::COLUMNS);

  $countSelect->columns(‘COUNT(*)’);

  return $countSelect;
  }

  Reply
  • 
    kalpesh May 10, 2012 at 6:11 am

    No, actually I am talking about query you wrote on your module’s Grid.php file, where you are joining tables for collection and displaying that in your grid.

    Reply
  • 
    kalpesh May 10, 2012 at 6:13 am

    and also update this method (getSelectCountSql) to the one displayed on this post.

    Reply
• 
  idzat May 10, 2012 at 7:05 am

  i didnt change anything from the grid.php.

  protected function _prepareCollection()
  {
  $model = Mage::getModel(‘review/review’);
  $collection = $model->getProductCollection();

  if ($this->getProductId() || $this->getRequest()->getParam(‘productId’, false)) {
  $this->setProductId(($this->getProductId() ? $this->getProductId() : $this->getRequest()->getParam(‘productId’)));
  $collection->addEntityFilter($this->getProductId());
  }

  if ($this->getCustomerId() || $this->getRequest()->getParam(‘customerId’, false)) {
  $this->setCustomerId(($this->getCustomerId() ? $this->getCustomerId() : $this->getRequest()->getParam(‘customerId’)));
  $collection->addCustomerFilter($this->getCustomerId());
  }

  if (Mage::registry(‘usePendingFilter’) === true) {
  $collection->addStatusFilter($model->getPendingStatus());
  }

  $collection->addStoreData();

  $this->setCollection($collection);
  return parent::_prepareCollection();
  }

  Reply
• 
  Umakanth June 29, 2012 at 4:47 am

  HI Kalpesh,

  I am new to magento, can u help me to get the correct count of the products and categories in the admin panel.

  Thanks in Advance

  Reply
  • 
    kalpesh June 29, 2012 at 5:30 am

    Yes Umakanth, you can see the total products count above the grid in admin panel. You can even filter to show only simple or configurable products and see the count above grid for filtered records. To see total categories, go to Catalog -> Manage Categories in admin panel, you will have total count for each category just beside it in parenthesis.

    Reply
    • 
      speed July 2, 2012 at 7:08 am

      hi Kalpesh,

      we have some 158 products in my store but it showing the wrong count i,e 166 products at the admin side. so can u help me in this issue.

      Reply
• 
  Chitra August 7, 2012 at 6:27 am

  Thanks…
  Following worked for me :

  Copy Db.php file from magento / lib / Varien / Data / Collection / Db.php
  Paste it to your local directory so the resultant folder structure would look like this:
  magento / app / code / local / Varien / Data / Collection / Db.php

  and to do the necessary changes.

  worked for me…

  Reply
• 
  magento800 August 24, 2012 at 6:50 am

  thank you for this ariticle,save my time

  Reply
• 
  magento800 August 24, 2012 at 7:44 am

  i copy file to local but it does’t work at all,so that i have to modify the core file

  Reply
• 
  Gü Bosch September 16, 2012 at 4:16 pm

  This code above didn’t work for me.
  Only the following one solved the issue:

  Change the function getSelectCountSql in \app\code\core\Mage\Catalog\Model\Resource\Product\Collection.php to

  	public function getSelectCountSql()
  	{
  	$this->_renderFilters();
  	$countSelect = $this->_getClearSelect()
  	->reset(Zend_Db_Select::GROUP)
  	->columns('COUNT(DISTINCT e.entity_id)')
  	->resetJoinLeft();
  	return $countSelect;
  	}

  Reply
• 
  Dev January 26, 2013 at 7:10 am

  Hii Kalpesh,

  I am getting correct count but, grid only shows 1 row.
  i.e, it displays Total 2 records found but only display one row in the grid..
  Please help.

  My grid code of custom module is as below:

  public function __construct()
  {
  parent::__construct();
  $this->setId(‘order_tailor’);
  $this->setUseAjax(true);
  }

  /**
  * Retrieve collection class
  *
  * @return string
  */
  protected function _getCollectionClass()
  {
  return ‘sales/order_invoice_grid_collection’;
  }

  protected function _prepareCollection()
  {
  $collection = Mage::getResourceModel($this->_getCollectionClass())
  ->addFieldToSelect(‘entity_id’)
  ->addFieldToSelect(‘order_id’)
  ->setOrderFilter($this->getOrder())
  ;
  $collection->getSelect()->join(‘custom_order_info’, ‘main_table.entity_id = custom_order_info.custom_id’,array(‘tailor_name’,’created_at’,’custom_id’,’id’));
  $collection->getSelect()->group(‘entity_id’);
  $this->setCollection($collection);
  return parent::_prepareCollection();
  }

  protected function _prepareColumns()
  {
  $this->addColumn(‘id’, array(
  ‘header’ => Mage::helper(‘sales’)->__(‘ID’),
  ‘index’ => ‘id’,
  ‘width’ => ‘120px’,
  ));

  $this->addColumn(‘tailor_name’, array(
  ‘header’ => Mage::helper(‘sales’)->__(‘Tailor Name’),
  ‘index’ => ‘tailor_name’,
  ));

  $this->addColumn(‘created_at’, array(
  ‘header’ => Mage::helper(‘sales’)->__(‘Date Sent’),
  ‘index’ => ‘created_at’,
  ‘type’ => ‘datetime’,
  ));

  $this->addColumn(‘delivery_at’, array(
  ‘header’ => Mage::helper(‘sales’)->__(‘Date Delivery Expected’),
  ‘index’ => ‘delivery_at’,
  ‘type’ => ‘datetime’,
  ));

  $this->addColumn(‘status’, array(
  ‘header’ => Mage::helper(‘sales’)->__(‘Status’),
  ‘index’ => ‘status’,
  ‘type’ => ‘options’,
  //’options’ => Mage::getModel(‘sales/order_invoice’)->getStates(),
  ‘options’ => array(
  1 => ‘Draft’,
  2 => ‘Sent’,
  3 => ‘Accepted’,
  ),
  ));

  $this->addColumn(‘base_grand_total’, array(
  ‘header’ => Mage::helper(‘customer’)->__(‘Amount’),
  ‘index’ => ‘base_grand_total’,
  ‘type’ => ‘currency’,
  ‘currency’ => ‘base_currency_code’,
  ));

  return parent::_prepareColumns();
  }

  /**
  * Retrieve order model instance
  *
  * @return Mage_Sales_Model_Order
  */
  public function getOrder()
  {
  return Mage::registry(‘current_order’);
  }

  public function getRowUrl($row)
  {
  return $this->getUrl(‘*/sales_order_test/view’,
  array(
  ‘id’ => $row->getId(),
  ‘order_id’ => $row->getOrderId()
  )
  );
  }

  public function getGridUrl()
  {
  return $this->getUrl(‘*/*/grid’, array(‘_current’ => true));
  }

  Reply
• 
  Rahil April 12, 2013 at 11:51 am

  Thanks, You saved my whole day to figure out solution.

  Reply
• 
  Krzysztof Witkowski May 9, 2013 at 9:22 am

  I have found some troubles in getting this worked because of names of columns in db, ie. “order”, this is my adjustment:

  public function getSelectCountSql()
  {
  $this->_renderFilters();

  $countSelect = clone $this->getSelect();
  $countSelect->reset(Zend_Db_Select::ORDER);
  $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
  $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
  $countSelect->reset(Zend_Db_Select::COLUMNS);

  if(count($this->getSelect()->getPart(Zend_Db_Select::GROUP)) > 0) {
  $countSelect->reset(Zend_Db_Select::GROUP);
  $countSelect->distinct(true);
  $group = $this->getSelect()->getPart(Zend_Db_Select::GROUP);
  $countSelect->columns(“COUNT(DISTINCT `”.implode(“`, `”, $group).”`)”);
  } else {
  $countSelect->columns(‘COUNT(*)’);
  }
  return $countSelect;
  }

  Reply
• 
  Bob May 14, 2013 at 8:39 pm

  Fantastic! Thanks for this fix!

  Reply
• 
  Abhilash May 17, 2013 at 3:29 pm

  Thanks a lot. it worked for me.

  However I got an peculiar issue in magento 1.5.1

  My query was

  $the_collection = Mage::getModel(‘sales/recurring_profile’)->getCollection()
  ->addFieldToFilter(‘state’, array(‘in’ => array(‘active’, ‘pending’)))
  ->addFieldToSelect(‘customer_id’);
  $the_collection->setOrder(‘customer_id’, ‘ASC’);

  $the_collection->getSelect()->join(array(‘c’ => ‘customer_entity’),main_table.customer_id = c.entity_id’, array());
  $the_collection->addFieldToFilter(‘c.group_id’, ‘4’);
  $the_collection->getSelect()->distinct(true);
  $this->setCollection($the_collection);
  return parent::_prepareCollection();

  When I print the record count query in db.php

  I got below.

  SELECT DISTINCT COUNT(*) FROM `sales_recurring_profile` AS `main_table` INNER JOIN `customer_entity` AS `c` ON main_table.customer_id = c.entity_id WHERE (state in (‘active’, ‘pending’)) AND (c.group_id = ‘4’)

  Note that the Distinct field is gone?

  Reply
• 
  SU July 3, 2013 at 6:26 am

  Thanx a lot. You saved my time. It works perfectly. But there’s another issue came. My order collection is as follows.
  $collection = $observer->getOrderGridCollection();
  $collection->getSelect()
  ->joinLeft(‘sales_flat_order_payment’, ‘main_table.entity_id = sales_flat_order_payment.parent_id’,’method’)
  ->joinLeft(‘customer_entity’, ‘main_table.customer_id = customer_entity.entity_id’,’email’)
  ->joinLeft(‘sales_flat_order_item’, ‘main_table.entity_id = sales_flat_order_item.order_id’,’name’);

  $collection->getSelect()->group(‘main_table.entity_id’);

  But when search by ‘Purched On’ date it shows the following error.

  SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘created_at’ in where clause is ambiguous

  Do you have any idea how to solve this?

  Reply
• 
  AK July 12, 2013 at 12:52 am

  Thanks for sharing.Life saver 🙂

  Reply
• 
  SU July 23, 2013 at 11:56 am

  Hi Kalpesh,

  Is it possible to override the classes in lib/Varien folder?

  Reply
  • 
    kalpesh July 23, 2013 at 1:09 pm

    Yes you can do it. It’s the same way you do with other modules. Just create a directory under app/code/local/Varien and you can now override lib/Varien classes inside local Varien directory.

    Reply
    • 
      SU July 24, 2013 at 3:04 am

      Hi,
      I mean we are overriding blocks and models by extending the core class. Likewise can we override the classes in lib/Varien folder within our modules? (Instead of creating the same file path in local folder)

      One more thing I want to mention. In the code above getSelectCountSql() the code in line 13 is not required. I have checked without that line it works.

      Thank you

      Reply
• 
  iqbal September 20, 2013 at 6:38 am

  Great solution!! here ends my day long search for a fix.

  Thanks.

  Reply
• 
  Ankur June 17, 2014 at 10:46 am

  Great fix !! Thank you very much.

  Reply
• 
  Aman August 23, 2014 at 11:40 am

  Hi Kalpesh,

  I i have used group by and having clause in collection, i am getting the problem that i the row count is not correct and pagination also not working.

  $collection = Mage::getModel(‘sales/order’)->getCollection();
  $collection->getSelect()
  ->group(array(‘customer_email’))
  ->having(‘count(“customer_email”)=1’);
  $this->setCollection($collection);

  also i would like to mention you that when i used the above solution record then grid become 0.

  Kindly help me to resolve the issue.

  Thanks.

  Reply
• 
  Srontol May 11, 2016 at 4:37 am

  Thank you, Kalpesh..!

  Reply
• 
  omadaco May 25, 2016 at 9:29 pm

  Excelente solucion, funciono perfecto. gracias.

  Excellent solution , it worked perfect. Thank you.

  Reply
• 
  rach August 14, 2018 at 5:19 am

  Thank you so much for this post.

  Reply