I have a report to list all orders that are part shipped but the data does not match what I am seeing in the pages Magento generates itelf. My code is below...

 $collection = Mage::getModel('sales/order')->getCollection()
    ->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_COMPLETE))
    ->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED))
    ->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CLOSED));        

    ->joinLeft(array('sfsg' => $collection->getTable('sales/shipment')), 'main_table.increment_id = sfsg.increment_id', array('shipped'=>'sfsg.created_at'))
    ->where('sfsg.created_at IS NOT NULL');        
    return parent::_prepareCollection();        

So the first part defines that no orders with a status of complete, canceled or closed are selected eg orders that are part shipped and the leftJoin bit joins the sales_flat_shipment table on increment_id (order id). This then pulls out created_at which is when the shipment was created/sent.

This produces my report no bother but the data inside it does not match what I see if I look at a report using Magento's built in facilities.

Where I am going wrong here? It must be the leftJoin that is not right I'm thinking. Is there any useful documentation on this that would explain the relationship between an orders first shipment or earliest shipment?


The above will work if you replace the line

->joinLeft(array('sfsg' => $collection->getTable('sales/shipment')), 'main_table.increment_id = sfsg.increment_id', array('shipped'=>'sfsg.created_at'))


->joinLeft(array('sfsg' => $collection->getTable('sales/shipment')), 'main_table.entity_id = sfsg.order_id', array('shipped'=>'sfsg.created_at'))

Weirdly, Magento has decided to name the columns that match the 2 tables by order entity_id and order_id which, in my opinion, is a real naff choice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.