0

I have been stuck for hours trying to add both Product Name and SKU to the Sales Order Grid in Magento.

I have created the joins and added the columns but the no data is being displayed and can't figure out why???

Any help would be nice...

Inside _prepareCollection

protected function _prepareCollection()
{
          $collection->getSelect()->joinLeft(array('sfoi' => 'sales_flat_order_item'),
            'order_id=main_table.entity_id = quote_item_id', array('sfoi.sku',
            'sfoi.name'));
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

And my two columns

$this->addColumn('sku', array(
    'header' => Mage::helper('sales')->__('SKU'),
    'align' => 'left',
    'sortable' => true,
    'index' => 'sku'
));

$this->addColumn('name', array(
    'header' => Mage::helper('sales')->__('Item Name'),
    'align' => 'left',
    'sortable' => true,
    'index' => 'name'
));

$this->addColumn('total_item_count', array(
    'header' => Mage::helper('sales')->__('Total Items'),
    'width' => '80px',
    'index' => 'total_item_count',
    'type' => 'text',
    'filter_index' => 'total_item_count'
));

I am able to get number of items into the Grid but not name and SKU is just empty

Could do with a heads up here!

  • Rather then use join you can do if very simple manner create one render file and in that file you got the order id based on that you got the items of that order as well as sku. – Keyul Shah Feb 3 '14 at 17:32
3

Rather then put complex query you can just use the Renderer in Magento Grid

 $this->addColumn('protect_code', array(
        'header' => Mage::helper('sales')->__('Bought Games'),           
        'index' => 'protect_code',
        'type'  => 'text',      
        'width' => '250px',
        'sortable'  =>false,
        'filter' => false,                                         
        'renderer' => 'NameSpace_ModuleName_Block_Adminhtml_Renderer_Productssku',          
    ));

Then you have to create the Renderer File NameSpace\ModuleName\Block\Adminhtml\Renderer\Productssku.php

Write down below code in you this file

<?php
class NameSpace_ModuleName_Block_Adminhtml_Renderer_Productssku extends     Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
{
    public function render(Varien_Object $row)
    {       
        //$getData = $row->getData();               
        $order=Mage::getModel('sales/order')->load($row['entity_id']);              
        $str="";
        $i=1;
        foreach($order->getAllItems() as $_order){                      
            $str.=$_order->getSku();
            if($i!=count($order->getAllItems()))
                $str.=", ";
                $i++;
    }       
        unset($order);
        return $str;
    }
}
1

I always seem to do this, as soon as I post I realise the answer:

This is the correct JOIN

      $collection->getSelect()->joinLeft(array('sfoi' => 'sales_flat_order_item'),
        'main_table.entity_id = item_id', array('sfoi.sku',
        'sfoi.name'));

For times where there are more than one item in the order you can use the CONCAT and GROUPBY methods in your prepare collection

$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 ", ")'),
        'brand' => new Zend_Db_Expr('group_concat(item.brand SEPARATOR ", ")')
    )
);

    $collection->getSelect()->group('main_table.entity_id');
  • 1
    What happens if there are more than one item on the order, though? – philwinkle Feb 3 '14 at 17:33
  • @philwinkle you're right, do I need some CONCAT to the join? – user1704524 Feb 4 '14 at 10:56
0

try the below codes

Inside _prepareCollection

$collection->getSelect()
            ->join(array('sfoi'=>'sales_flat_order_item'),
                       'sfoi.item_id = main_table.entity_id',
                   array('')
                 )
            ->join(array('cpe'=>'catalog_product_entity'),
                       "cpe.entity_id = sfoi.product_id",
                   array('sku')
                 )
                ->join(array('ea'=>'eav_attribute'),
                       "ea.attribute_code = 'name'",
                   array('ea.attribute_id')
                 )
            ->join(array('cpev'=>'catalog_product_entity_varchar'),
                       "cpev.entity_id = sfoi.product_id AND cpev.attribute_id = ea.attribute_id",
                   array('cpev.value as productname')
                 );

for columns use these codes

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

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

these should work as i have tested it.

0
$collection->join(array('sfoi' => 'sales/order_item'),
'main_table.entity_id =   sfoi.order_id',

array('sfoi.name'=> 'name', 'sfoi.sku'=> 'sku', ));


 $this->addColumn('sfoi.name', array(
                'header'       => $helper->__('Product Name'),
                'index'        => 'sfoi.name',
                'type' => 'text',
           ));
    $this->addColumn('sfoi.sku', array(
              'header'       => $helper->__('Product SKU'),
              'index'        => 'sfoi.sku',
               'type' => 'text',
          ));               
0

For those who are getting duplicate item names/sku's when you also join the sales_flat_order_address table is because there are most likely two entires (rows) for every order in sales_flat_order_address table.

One for a billing address and the other for a shipping address.

Simply add sales_flat_order_address.address_type = 'shipping' in the on clause (second parameter)

->joinLeft('sales_flat_order_address', "sales_flat_order.entity_id = sales_flat_order_address.parent_id AND sales_flat_order_address.address_type = 'shipping'", array('sales_flat_order_address.region', 'sales_flat_order_address.country_id'))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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