How to add SKU in Order Grid Page

Question

I have already seen many examples on this for how to do it. But all the examples are for a different version of Magento. And none of them worked in my case. My version is 1.4.0.0rc-1. Yes I know this should not be in a production environment but unfortunately this is the case in here.

The problem is _prepareCollection() function in Mage/Adminhtml/Block/Sales/Order/Grid.php class is different than all the other example I have seen. So not sure how to do it.

This is how the code for that function:

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel('sales/order_collection')
        ->addAttributeToSelect('*')
        ->joinAttribute('sku','sales_flat_order_item', null, null, 'left')
        ->joinAttribute('billing_firstname', 'order_address/firstname', 'billing_address_id', null, 'left')
        ->joinAttribute('billing_lastname', 'order_address/lastname', 'billing_address_id', null, 'left')
        ->joinAttribute('shipping_firstname', 'order_address/firstname', 'shipping_address_id', null, 'left')
        ->joinAttribute('shipping_lastname', 'order_address/lastname', 'shipping_address_id', null, 'left')
        ->addExpressionAttributeToSelect('billing_name',
            'CONCAT({{billing_firstname}}, " ", {{billing_lastname}})',
            array('billing_firstname', 'billing_lastname'))
        ->addExpressionAttributeToSelect('shipping_name',
            'CONCAT({{shipping_firstname}},  IFNULL(CONCAT(\' \', {{shipping_lastname}}), \'\'))',
            array('shipping_firstname', 'shipping_lastname'));
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

I tried to add a left join but it didn't work. This is what I have tried right before the setCollection statement:

$collection->getSelect()->join('sales_flat_order_item', 'order_id=entity_id',    array('name'=>'name', 'sku' =>'sku'), null,'left');

But it is printing out the following error:

SELECT `e`.*, `_table_billing_address_id`.`value` AS `billing_address_id`, `_table_billing_firstname`.`value` AS `billing_firstname`, `_table_billing_lastname`.`value` AS `billing_lastname`, `_table_shipping_address_id`.`value` AS `shipping_address_id`, `_table_shipping_firstname`.`value` AS `shipping_firstname`, `_table_shipping_lastname`.`value` AS `shipping_lastname`, CONCAT(_table_billing_firstname.value, " ", _table_billing_lastname.value) AS `billing_name`, CONCAT(_table_shipping_firstname.value,  IFNULL(CONCAT(' ', _table_shipping_lastname.value), '')) AS `shipping_name`, `sales_flat_order_item`.`name`, `sales_flat_order_item`.`sku` FROM `sales_order` AS `e`
 LEFT JOIN `sales_order_int` AS `_table_billing_address_id` ON (_table_billing_address_id.entity_id = e.entity_id) AND (_table_billing_address_id.attribute_id='112')
 LEFT JOIN `sales_order_entity_varchar` AS `_table_billing_firstname` ON (_table_billing_firstname.entity_id = _table_billing_address_id.value) AND (_table_billing_firstname.attribute_id='202')
 LEFT JOIN `sales_order_entity_varchar` AS `_table_billing_lastname` ON (_table_billing_lastname.entity_id = _table_billing_address_id.value) AND (_table_billing_lastname.attribute_id='204')
 LEFT JOIN `sales_order_int` AS `_table_shipping_address_id` ON (_table_shipping_address_id.entity_id = e.entity_id) AND (_table_shipping_address_id.attribute_id='113')
 LEFT JOIN `sales_order_entity_varchar` AS `_table_shipping_firstname` ON (_table_shipping_firstname.entity_id = _table_shipping_address_id.value) AND (_table_shipping_firstname.attribute_id='202')
 LEFT JOIN `sales_order_entity_varchar` AS `_table_shipping_lastname` ON (_table_shipping_lastname.entity_id = _table_shipping_address_id.value) AND (_table_shipping_lastname.attribute_id='204')
 INNER JOIN `sales_flat_order_item` ON order_id=entity_id WHERE (e.entity_type_id = '11') ORDER BY `e`.`created_at` desc, `e`.`created_at` desc LIMIT 20

Can someone please help me on how to solve this? And for your info, the SKU field is in the "Sales_flat_order_item" table.

What do you plan to do where there's multiple sku's / products per order? — Andrew, Sep 25 '12 at 22:20
@Andrew: Thanks for the reply. But I have already solved the problem. :) — blackRider, Sep 27 '12 at 02:03
@meraz please post your solution as an answer then accept it for others to see. — clockworkgeek, Sep 27 '12 at 18:41

Nikhil_K_R · Answer 1 · 2012-10-11T07:32:15.990

$collection = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('sku')
        ->addAttributeToSelect('name')
        ->addAttributeToSelect('attribute_set_id')
        ->addAttributeToSelect('type_id')
        ->joinField('qty',
            'cataloginventory/stock_item',
            'qty',
            'product_id=entity_id',
            '{{table}}.stock_id=1',
            'left')
        ->joinAttribute('status', 'catalog_product/status',
                        'entity_id', null, 'inner', $store->getId());

like getId() you can go for getSku(); --->I hope this will help you.

score 0 · Answer 2 · answered Oct 16 '12 at 05:58

0

For anyone having problem with this one I have found a good solution for this one in here:

http://www.magentocommerce.com/boards/viewthread/18362/

answered Oct 16 '12 at 05:58

blackRider

143
2
17

How to add SKU in Order Grid Page

2 Answers2