Display Gift Messages in Sales Order Grid per Order Magento Admin

Question

Guys I am very new to Magento So can anyone help me? Below is my code to get the Gift message on the Sales Order Grid In the Admin :

  protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());

    $collection->join('sales/order_item','`sales/order_item`.order_id=`main_table`.entity_id',array(
    'giftavail' => new Zend_Db_Expr('group_concat(`sales/order_item`.gift_message_available SEPARATOR ",")'),
    'giftid' => new Zend_Db_Expr('group_concat(`sales/order_item`.gift_message_id SEPARATOR ",")'),
    'skus' => new Zend_Db_Expr('group_concat(`sales/order_item`.sku SEPARATOR ",")'),
    'name' => new Zend_Db_Expr('group_concat(`sales/order_item`.name SEPARATOR ",")'),)

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

    $collection->getSelect()->joinLeft(array('sfog' => 'sales_flat_order_grid'),
    'main_table.entity_id = sfog.entity_id',array('sfog.shipping_name','sfog.billing_name'));

    $collection->getSelect()->joinLeft(array('sfo'=>'sales_flat_order'),
    'sfo.entity_id=main_table.entity_id',array('sfo.customer_email','sfo.weight',
    'sfo.discount_description','sfo.increment_id','sfo.store_id','sfo.created_at','sfo.status',
    'sfo.base_grand_total','sfo.grand_total'));

    $collection->getSelect()->joinLeft(array('sfoa'=>'sales_flat_order_address'),
    'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"',array('sfoa.street',
    'sfoa.city','sfoa.region','sfoa.postcode','sfoa.telephone' , 'sfoa.country_id'));

    $collection->getSelect()->joinLeft(array('gfm' => 'gift_message'),
    'main_table.giftid = gfm.gift_message_id',array('gfm.message'));

    $this->setCollection($collection);

    return parent::_prepareCollection();

}

This is the SQL Query that is generated :

"SELECT main_table.*, group_concat(sales/order_item.gift_message_available SEPARATOR ",") AS giftavail, group_concat(sales/order_item.gift_message_id SEPARATOR ",") AS giftid, group_concat(sales/order_item.sku SEPARATOR ",") AS skus, group_concat(sales/order_item.name SEPARATOR ",") AS name, sfog.shipping_name, sfog.billing_name, sfo.customer_email, sfo.weight, sfo.discount_description, sfo.increment_id, sfo.store_id, sfo.created_at, sfo.status, sfo.base_grand_total, sfo.grand_total, sfoa.street, sfoa.city, sfoa.region, sfoa.postcode, sfoa.telephone, sfoa.country_id, gfm.message FROM sales_flat_order_grid AS main_table INNER JOIN sales_flat_order_item AS sales/order_item ON sales/order_item.order_id=main_table.entity_id LEFT JOIN sales_flat_order_grid AS sfog ON main_table.entity_id = sfog.entity_id LEFT JOIN sales_flat_order AS sfo ON sfo.entity_id=main_table.entity_id LEFT JOIN sales_flat_order_address AS sfoa ON main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping" LEFT JOIN gift_message AS gfm ON main_table.entity_id = gfm.giftid GROUP BY main_table.entity_id"

But this is not working, Can anybody Help??? Thanks in Advance

Before $this->setCollection($collection); line print $collection->getSelect(); exit; and run that sql query manually. Then share, if getting any error. — Rajiv Ranjan
When I run that query in the Database, i get these error :'#1054 - Unknown column 'main_table.giftid' in 'on clause'' — Parker
I am getting the Gift message Id respected to the Order , But I want the Message related to that Gift Message... Has anyone any Idea?? — Parker

Rajiv Ranjan Rajiv Ranjan · Accepted Answer · 2013-11-14T14:12:25

Update you code with below code:

protected function _prepareCollection(){

$collection->join(array('order_item'=>'sales/order_item'),'`order_item`.order_id=`main_table`.entity_id',array(
'giftavail' => new Zend_Db_Expr('group_concat(`order_item`.gift_message_available SEPARATOR ",")'),
'giftid' => new Zend_Db_Expr('group_concat(`order_item`.gift_message_id SEPARATOR ",")'),
'skus' => new Zend_Db_Expr('group_concat(`order_item`.sku SEPARATOR ",")'),
'name' => new Zend_Db_Expr('group_concat(`order_item`.name SEPARATOR ",")'))

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

$collection->getSelect()->joinLeft(array('sfog' => 'sales_flat_order_grid'),
'main_table.entity_id = sfog.entity_id',array('sfog.shipping_name','sfog.billing_name'));

$collection->getSelect()->joinLeft(array('sfo'=>'sales_flat_order'),
'sfo.entity_id=main_table.entity_id',array('sfo.customer_email','sfo.weight',
'sfo.discount_description','sfo.increment_id','sfo.store_id','sfo.created_at','sfo.status',
'sfo.base_grand_total','sfo.grand_total'));

$collection->getSelect()->joinLeft(array('sfoa'=>'sales_flat_order_address'),
'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"',array('sfoa.street',
'sfoa.city','sfoa.region','sfoa.postcode','sfoa.telephone' , 'sfoa.country_id'));

$collection->getSelect()->joinLeft(array('gfm' => 'gift_message'),
'order_item.gift_message_id = gfm.gift_message_id',array('gfm.message'));

$this->setCollection($collection);

return parent::_prepareCollection();

}

Then you will get working sql query

SELECT `main_table`.*, group_concat(`order_item`.gift_message_available SEPARATOR ",") AS `giftavail`,
group_concat(`order_item`.gift_message_id SEPARATOR ",") AS `giftid`, 
group_concat(`order_item`.sku SEPARATOR ",") AS `skus`,
group_concat(`order_item`.name SEPARATOR ",") AS `name`, `sfog`.`shipping_name`,  
`sfog`.`billing_name`, `sfo`.`customer_email`,
`sfo`.`weight`, `sfo`.`discount_description`, `sfo`.`increment_id`, 
`sfo`.`store_id`, `sfo`.`created_at`, `sfo`.`status`, 
`sfo`.`base_grand_total`, `sfo`.`grand_total`, `sfoa`.`street`, 
`sfoa`.`city`, `sfoa`.`region`, `sfoa`.`postcode`,
`sfoa`.`telephone`, `sfoa`.`country_id`, `gfm`.`message` 
FROM `sales_flat_order_grid` AS `main_table` 
INNER JOIN 
   `sales_flat_order_item` AS `order_item` 
ON `order_item`.order_id=`main_table`.entity_id 
LEFT JOIN 
   `sales_flat_order_grid` AS `sfog`
ON main_table.entity_id = sfog.entity_id 
LEFT JOIN 
   `sales_flat_order` AS `sfo` 
ON sfo.entity_id=main_table.entity_id 
LEFT JOIN 
   `sales_flat_order_address` AS `sfoa` 
ON main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping" 
LEFT JOIN 
   `gift_message` AS `gfm` 
ON order_item.gift_message_id = gfm.gift_message_id 
GROUP BY 
   `main_table`.`entity_id`

Issue was with sales_flat_order_item table alise name and in last line with gift message. You are using main_table.giftid in stead of order_item.gift_message_id.

Hope will help!

Display Gift Messages in Sales Order Grid per Order Magento Admin

1 Answers