getSelect and Join: Adding a shipment status to the Invoice Grid.php

Question

I am trying to perform a join in the invoice grid.php.

I am adding a column that I want to show one, of possibly many, shipment created_at rows attached to an Invoice.

The problem is that for many invoices more than one row in sales_flat_shipment_grid exist.

protected function _prepareCollection()
    {    
        $collection = Mage::getResourceModel($this->_getCollectionClass());
        $collection->getSelect()->join('sales_flat_order', 'main_table.order_id = sales_flat_order.entity_id', array('shipping_description', 'shipping_amount'));

        $collection->getSelect()->joinLeft('sales_flat_shipment_grid',
           'main_table.order_increment_id = sales_flat_shipment_grid.order_increment_id', 
            array('shipment_status' => 'created_at',
                  'shipment_entity_id' => 'sales_flat_shipment_grid.entity_id',
                  'shipment_order_id' => 'sales_flat_shipment_grid.order_id'
                  ));

        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

In this code snippet, I have tried assigning the sales_flat_shipment_grid entity id to an alias to avoid conflict with the main table entity id.

The issue is the final $collection->getSelect() line. I cannot get the collection to retrieve just one of the multiple rows that are returned when looking up an order_increment_id in sales_flat_shipment_grid. How can I limit the join to one of the rows that are possibly returned?

Here is the error from Magento:

Item (Mage_Sales_Model_Order_Invoice) with the same id "53249" already exist

Answer 1

I think

$select->groupBy('main_table.entity_id');

will help you out.