Jan 5, 2012
kalpesh

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

If you have noticed or not, in version 1.5 of Magento when you use GROUP BY clause in any Grid.php file in admin, the count always display wrong. Many times it displays 1 even if there are hundreds of records. Due to this your pagination also doesn’t work. This is a bug in Magento. Your getSize() always returns wrong count whereas total records in grid are proper.

To fix this, you need to edit one of your core file. As it’s not a good practice to edit core file, we will here override the core file.
Overriding LIB module

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

Now open this file to edit and replace getSelectCountSql function with below one

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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;
    }

Updated the method for generic use

33 Comments

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

  • 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.

  • 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.

  • Thanks Kalpesh,
    Its working fine now.

    thanks again.

  • 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.

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

  • 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;
    }

    • 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.

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

  • 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();
    }

  • 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

    • 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.

      • 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.

  • 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…

  • thank you for this ariticle,save my time

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

  • 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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    
    public function getSelectCountSql()
        {
            $this->_renderFilters();
    
            $countSelect = $this->_getClearSelect()
               ->reset(Zend_Db_Select::GROUP)
                ->columns('COUNT(DISTINCT e.entity_id)')
                ->resetJoinLeft();
    
            return $countSelect;
        }
  • 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));
    }

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

  • 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;
    }

  • Fantastic! Thanks for this fix!

  • 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?

  • 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?

  • Thanks for sharing.Life saver 🙂

  • Hi Kalpesh,

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

    • 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.

      • 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

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

    Thanks.

  • Great fix !! Thank you very much.

  • 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.

  • Thank you, Kalpesh..!

  • Excelente solucion, funciono perfecto. gracias.

    Excellent solution , it worked perfect. Thank you.

Leave a comment

 

Welcome to my Blog

Kalpesh MehtaHelping Magento developers in their day-to-day development problems since 2011. Most of the problems and solutions here are my own experiences while working on different projects. Enjoy the blog and don't forget to throw comments and likes/+1's/tweets on posts you like. Thanks for visiting!

Certifications