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?

Magento requires a unique order id for each record displayed on the grid to handle mass updates from the grid etc, but your query will be doing a one to many database join. With this, when you have multiple shipments, you will now be trying to display the order twice in the grid which is why get the error as you cannot have the order id twice. A simple solution may be to add a group by order id clause to ensure you only get a one record back per order. Question is, what shipment date do you want to display for the multiples? The first shipment or the last shipment against the order? — Ashley Swatton
Also, it's far safer to extend the original magento file as opposed to copying it and all it's contents into the local namespace. Reason being, if you upgrade and magento change significant parts of the file, then you will have to merge in the core changes into your fully overridden file again for every upgrade. A good guide can be found here: inchoo.net/magento/… — Ashley Swatton
@AshleySwatton thanks. I would like to get the last shipment. — James

Javier C. H. Javier C. H. · Accepted Answer · 2014-12-08T12:00:59

As mentioned in the comments, you need to group the results by the entity_id:

$collection
->getSelect()
->joinLeft('sales_flat_shipment_grid',
    'sales_flat_shipment_grid.order_id=main_table.entity_id', 
    array('shipped_date' => 'MAX(sales_flat_shipment_grid.created_at)') // Since you want the most recent one
);

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

Couldn't test it but something like that should work.

Regards, Javier

How to add shipped date to admin sales order grid

1 Answers