Magento Sales Order Grid shows incorrect number of records when added Names and Skus columns

Question

I'm working with Magento version 1.4 and I added extra grid columns (names and skus) to Sales Order Grid, the returned data is correct but I'm having problems with pagination and total number of records, my code as follow:

First I Edited Mage_Adminhtml_Block_Sales_Order_Grid:

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass())
    ->join(
        'sales/order_item',
        '`sales/order_item`.order_id=`main_table`.entity_id',
        array(
            'skus'  => new Zend_Db_Expr('group_concat(`sales/order_item`.sku SEPARATOR ", ")'),
            'names' => new Zend_Db_Expr('group_concat(`sales/order_item`.name SEPARATOR ", ")'),
            )
        );
    $collection->getSelect()->group('entity_id');

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

Then I override this method to return correct results when filter by names or skus

    protected function _addColumnFilterToCollection($column)
{
    if($this->getCollection() && $column->getFilter()->getValue()) 
    {
        if($column->getId() == 'skus'){
            $this->getCollection()->join(
                'sales/order_item',
                '`sales/order_item`.order_id=`main_table`.entity_id',
                array(
                    'skus'  => new Zend_Db_Expr('group_concat(`sales/order_item`.sku SEPARATOR ", ")'),
                )
            )->getSelect()
                ->having('find_in_set(?, skus)', $column->getFilter()->getValue());

            return $this;
        }

        if($column->getId() == 'names'){
            $this->getCollection()->join(
                'sales/order_item',
                '`sales/order_item`.order_id=`main_table`.entity_id',
                array(
                    'names' => new Zend_Db_Expr('group_concat(`sales/order_item`.name SEPARATOR ", ")'),
                )
            )->getSelect()
                ->having('find_in_set(?, names)', $column->getFilter()->getValue());

            return $this;
        }
    }
    return parent::_addColumnFilterToCollection($column);
}

Then I edited this method getSelectCountSql() in Mage_Sales_Model_Mysql4_Order_Collection class:

public function getSelectCountSql()
{
    $countSelect = parent::getSelectCountSql();

    //added 
    $countSelect->reset(Zend_Db_Select::HAVING);
    //end

    $countSelect->resetJoinLeft();
    return $countSelect;
}

How can I calculate number of rows?

score 4 · Answer 1 · answered Apr 02 '12 at 08:39

4

Maybe its a bit to late but in your code try using GROUP insted of HAVING:

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

Because you are using this statemen:

$collection->getSelect()->group('entity_id');

answered Apr 02 '12 at 08:39

James

41
2

But it is expecting the change in the Mage_Sales_Model_Resource_Order_Collection rather than Mage_Sales_Model_Mysql4_Order_Collection for the change to appear. Can you please explain me that? – Ricky Sharma Aug 12 '13 at 04:07

score 2 · Answer 2 · answered Jan 27 '15 at 12:24

$collection->getSelect()->join(array(
            'item'=>$collection->getTable('sales/order_item')),
            'item.order_id=`main_table`.entity_id AND item.product_type="simple"',
            array(
                'skus' => new Zend_Db_Expr('group_concat(item.sku SEPARATOR ", ")'),
                'name' => new Zend_Db_Expr('group_concat(item.name SEPARATOR ", ")')
            ));

$this->addColumn('skus', array(
            'header' => Mage::helper('sales')->__('SKU'),
            'index' => 'skus',
            'type' => 'text',
        ));

        $this->addColumn('name', array(
            'header' => Mage::helper('sales')->__('NAME'),
            'index' => 'name',
            'type' => 'text'
        ));

Meabed · Answer 3 · 2012-03-15T19:37:44.280

I had this issue and i have got it working by implementing custom getSize() function in the collection i am using

public function getSize()
{
    $select = clone $this->getSelect();
    $select->reset();
    $select =  $this->getConnection()->fetchOne('SELECT COUNT(*) FROM Table GROUP BY FIELD'); // or you can use select count(distinct field) from table
    return $select;
}

and to achieve Grid storing i have override

protected function _setCollectionOrder($column)
    {
        $collection = $this->getCollection();
        if ($collection) {
            $columnIndex = $column->getFilterIndex() ?
                $column->getFilterIndex() : $column->getIndex();
            $collection->getSelect()->order(array($columnIndex.' '.$column->getDir()));
        }
        return $this;
    }

and Set filter_index of the columns TO

 in _prepareColumns() function 
    'filter_index' => 'SUM(tablename.field)'

and you can use Callback function on filters for the columns

James' solution is definitely better, Meabed's rewrite of getSize can trigger the loss of the binding. I implemented a complex collection with a "group by" clause and met the "count = 1" bug. Adding `$countSelect->reset(Zend_Db_Select::GROUP);` to getSelectCountSql did the trick in a clean way. — SMASHED, Oct 23 '12 at 13:38

Magento Sales Order Grid shows incorrect number of records when added Names and Skus columns

3 Answers3

Linked