How to add shipped date to admin sales order grid

Question

I am stuck with adding new column(Shipped date) to sales order admin grid. I copied the file Grid.php from app/code/core/Mage/Adminhtml/Block/Sales/Order to app/code/local/Mage/Adminhtml/Block/Sales/Order and in the _prepareCollection() function added the code

$collection->getSelect()->joinLeft('sales_flat_shipment_grid','sales_flat_shipment_grid.order_id=main_table.entity_id',array('shipped_date' => 'sales_flat_shipment_grid.created_at'));

and in _prepareColumns() added this

$this->addColumn('shipped_date', array(
      'header'    => Mage::helper('sales')->__('Shipped Date'),
      'index'     => 'shipped_date',
      'type'      => 'datetime',
      'filter_index'=>'sales_flat_shipment_grid.created_at',
));

My problem is when there are orders with multiple shipments and when I go to the sales order grid I get the error There has been an error processing your request which says there is a duplicate order id. If there are no multiple shipments for orders, then the sales order grid is fine and shows the shipped date for the corresponding orders.

Could someone help me with this?

Answer 1

As the order can has multiple shipments, you may use sub-query:

1. in the _prepareCollection()

   $table = Mage::getSingleton('core/resource')
       ->getTableName('sales_flat_shipment_grid');
   $shipping = "((select max(t.created_at) from `$table` as t where t.order_id=main_table.entity_id))";
   $collection->getSelect()->from('', "$shipping as shipped_date");   
   

2. in the _prepareColumns():

   $this->addColumn('shipped_date', array( 'header' => Mage::helper('sales')->__('Shipped Date'), 'index' => 'shipped_date', 'type' => 'datetime', ));

Answer 2

You could use the renderer instead, and this without joining with the shipment grid.

$this->addColumn('shipped_date', array(
      'header'    => Mage::helper('sales')->__('Shipped Date'),
      'index'     => 'entity_id',
      'type'      => 'datetime',
      'renderer'  => 'Mage_Adminhtml_Block_Sales_Order_Renderer_ShippedDate',
      'filter_condition_callback'
                                       => array($this, '_filterShippedDate'),

));

You should create the renderer in your module, and to define your filter function, and you are gonna have the shipped dates, no matter how much shipment you have.