1

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!!

enter image description here

2 Answers 2

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.

7
  • i have tried sales_flat_order but sometime Column not found: 1054 Unknown column 'sku' in 'where clause', query was: SELECT COUNT(*) FROM sales_flat_order_grid` AS main_table WHERE (sku LIKE '%NEMCB-01%')"` also sku filter not working Feb 28, 2019 at 10:12
  • I look in my code maybe I find the handling of the count sql to avoid that problem, I'll update you if I find the approach
    – HelgeB
    Feb 28, 2019 at 10:15
  • i have use this link ranasohel.me/2014/11/05/… but order count showing wrong Feb 28, 2019 at 10:27
  • but where can i add your code? mate Feb 28, 2019 at 10:38
  • I've just added a second possibilty, try that first: change join to leftJoin
    – HelgeB
    Feb 28, 2019 at 10:39
0

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();
    }
}
4
  • Still problem with counting this answer too
    – Sohel Rana
    Feb 28, 2019 at 10:33
  • Thanks for quick replay but i have use default order grid collection, please check my question Feb 28, 2019 at 10:35
  • i use your module but order count showing wrong ranasohel.me/2014/11/05/… Feb 28, 2019 at 10:51
  • ok thanks, currently wrong order count showing like:prnt.sc/mra30k Feb 28, 2019 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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