Abdul Ghaffar Abdul Ghaffar - 9 months ago 46
SQL Question

How to use join in magento to get colloection

I need to convert following sql query into magento to get collection.

SELECT * FROM sales_flat_order LEFT JOIN sales_flat_order_address ON sales_flat_order.entity_id=sales_flat_order_address.parent_id And sales_flat_order_address.store_id!=x;

I tried this, I know it's wrong, just for idea what am I trying to do, where "555" is x in sql query

$ordercollection = Mage::getModel('sales/order')->getCollection();
$ordercollection->getSelect()->joinLeft(array('sfoa' => 'sales_flat_order_address'),'main_table.entity_id = sfoa.parent_id',array(,=>'sfoa.store_id'));


You're almost there:

$orderCollection = Mage::getModel('sales/order')->getCollection();
$orderCollection->getSelect()->joinLeft(array('sfoa' => 'sales_flat_order_address'),'main_table.entity_id = sfoa.parent_id', "");


SELECT `main_table`.* FROM `sales_flat_order` AS `main_table` LEFT JOIN `sales_flat_order_address` AS `sfoa` ON main_table.entity_id = sfoa.parent_id WHERE (main_table.store_id != '555')

SFOA doesn't include a store_id column, so I've updated my collection query above.

foreach($orderCollection as $collection) {
    // do something