0

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?

JasonMArcher
  • 14,195
  • 22
  • 56
  • 52
James
  • 4,540
  • 1
  • 18
  • 34
  • 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 Dec 05 '14 at 14:42
  • 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: http://inchoo.net/magento/overriding-magento-blocks-models-helpers-and-controllers/ – Ashley Swatton Dec 05 '14 at 14:44
  • @AshleySwatton thanks. I would like to get the last shipment. – James Dec 07 '14 at 16:22

1 Answers1

1

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

Javier C. H.
  • 2,124
  • 2
  • 19
  • 30
  • Thanks a ton Javier. The only thing is, in the order panel it shows the incorrect number of records found. I have selected 20 items in the view per page dropdown and it displays 20 items, but it shows Total 1 records found. Do you know why this is happening? Also I was wondering is there a way to show the shipment dates for multiple shipments for an order? – James Dec 08 '14 at 12:18
  • To show all the shipments, you can replace MAX by GROUP_CONCAT and that should show them comma separated. The other issue I know exactly what you are talking about, as I experienced it as well, but I don't remember how I fixed it. Will try to check it this evening and get back to you :) – Javier C. H. Dec 08 '14 at 12:21
  • I tried GROUP_CONCAT, but it didn't work. It would be great if you could let me know how you fixed the incorrect number of records found. :) – James Dec 08 '14 at 12:29
  • You have to reset the GROUP in the count select: http://stackoverflow.com/questions/8764543/magento-sales-order-grid-shows-incorrect-number-of-records-when-added-names-and – Javier C. H. Dec 08 '14 at 12:30
  • Did you got an error or something? try this: `array('shipped_date' => new Zend_Db_Expr('group_concat(sales_flat_shipment_grid.created_at SEPARATOR ", ")'))` – Javier C. H. Dec 08 '14 at 12:33
  • I tried with your code to concatenate, but it didn't work and also i didn't get any errors. I checked the link you gave to reset the group, but that too didn't work. I found the function in `app\code\core\Mage\Sales\Model\Resource\Order\Grid\Collection.php` where I added the code `$unionSelect->reset(Zend_Db_Select::GROUP);` in `getSelectCountSql()` function. After doing this also it was showing Total 1 records found. – James Dec 09 '14 at 09:56
  • Hi, I solved the incorrect records issue with the code change from this link http://ka.lpe.sh/2012/01/05/magento-wrong-count-in-admin-grid-when-using-group-by-clause-overriding-lib-module/. But now I am facing another problem. In the order grid if I filter with the shipment date it is fine, but then I filter with the order date I am getting the error `Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous`. – James Dec 09 '14 at 11:26
  • Search for $this->addColumn('created_at', array( ... and add this: 'filter_index' => 'main_table.created_at' )); – Javier C. H. Dec 09 '14 at 11:33
  • Glad it worked :). By the way I think the GROUP_CONCAT didn't work because your column type => 'datetime', if you change it by 'text' it should show the comma separated values, but then you wouldn't be able to filter by date, so it probably doesn't worth it... – Javier C. H. Dec 09 '14 at 13:03