Magento1.9 Product SKU in Sales order grid

Question

i have use default collection and change this file

/app/code/core/Mage/Adminhtml/Block/Sales/Order/grid.php

Now replace your _prepareCollection() function to this following code

 protected function _getCollectionClass()
{
    return 'sales/order_grid_collection';
}

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

and add skus field in grid

$this->addColumn('skus', array(
            'header'    => Mage::helper('Sales')->__('Skus'),
            'width'     => '100px',
            'index'     => 'skus',
            'filter_index' => 'sku',
            'type'        => 'text',

        ));

but order count showing wrong

how can solve this issue
Thanks!!

Answer 1

If you join sales_flat_order_item to sales_flat_order you get as many rows in your grid as you have order items.

You are using group_concatthat shouldn't give duplicates in the grid I used that also in another context, but the count SQL removes that part.

Here is the code I used for getting the correct count in an similar case. Basically it uses the original SQL including the columns and creates a count() arround it. The default count sql removes the columns and just add count() - in your case the nice effect of group_concat is gone with that. Just replace the getSelectCountSql()method in the resource collection and it should work.

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

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

    $countSelect->from(array('tmp' => $select));
    $countSelect->reset(Zend_Db_Select::COLUMNS);
    $countSelect->columns(array('result' => 'COUNT(*)'));

    return $countSelect;
}

Since you are in the order collection there might be also an easier way to achieve this, whithout overwriten the getSelectCountSql() method: The order collection resets by default all left joins, so if you change ->join( with joinLeft( in your code that should also work. join or left join should give the same result since there are always order items for an order.

Answer 2

app/code/local/SR/MagentoCommunity/Block/Adminhtml/Sales/Order/Grid.php

<?php
class SR_MagentoCommunity_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid
{

    public function setCollection($collection)
    {
        parent::setCollection($collection);

        if ($this->_isExport) {
            return $this;
        }

        // Get the size (gets pagination working correctly)
        $collection->getSize();

        // Add the sales/order_item model to this collection
        $collection->join(
            'sales/order_item',
            '`sales/order_item`.order_id=`main_table`.entity_id',
            []
        );

        // Group by the order id, which is initially what this grid is id'd by
        $collection->getSelect()->group('entity_id');
    }

    protected function _prepareCollection()
    {
        parent::_prepareCollection();

        if ($this->_isExport) {
            return $this;
        };

        $collection = $this->getCollection();

        $orderIds = [];
        foreach ($collection as $order) {
            $orderIds[] = $order->getEntityId();
        }

        $itemCollection = new Varien_Data_Collection_Db(Mage::getSingleton('core/resource')->getConnection('read'));

        $itemCollection->getSelect()
            ->from(['sales/order_item' => $collection->getTable('sales/order_item')], [
                'order_id',
                'product_skus'  => new Zend_Db_Expr('GROUP_CONCAT(`sales/order_item`.sku SEPARATOR ", ")')
            ])
            ->where('order_id IN (?)', $orderIds)
            ->where('parent_item_id IS NULL')
            ->group('order_id');

        foreach ($itemCollection as $object) {
            $order = $collection->getItemById($object->getOrderId());
            $order->setProductSkus($object->getProductSkus());
        }
    }

    protected function _addColumnFilterToCollection($column)
    {
        $collection = $this->getCollection();
        $filterText = $column->getFilter()->getValue();

        if ($filterText && $column->getId() === 'products') {
            $collection->addFieldToFilter(
                [
                    'sku',
                ],
                [
                    ['like' => "%$filterText%"]
                ]
            );
        } else {
            parent::_addColumnFilterToCollection($column);
        }

        return $this;
    }

    protected function _prepareColumns()
    {
        parent::_prepareColumns();

        if ($this->_isExport) {
            return $this;
        }

        $this->addColumn('products', [
            'header' => Mage::helper('sales')->__('Products'),
            'index' => 'product_skus',
            'sortable'  => false
        ]);
        $this->addColumnsOrder('products', 'shipping_name')->sortColumnsByOrder();
    }
}