sql - joinLeft collection sales_flat_order_grid

Question

I have this SQL query: select main_table.*, (select nexway_status from nexway_order where order_id=main_table.entity_id ORDER BY id DESC LIMIT 1) as nexway_status from sales_flat_order_grid as main_table;

I need a collection for show the rows in grid orders..

I try this:

    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $collection
        ->getSelect()
        ->joinLeft(
            array('n'=> 'nexway_order'),
            'n.order_id = main_table.entity_id',
            array('n.nexway_status')
        );
    $this->setCollection($collection);

But in that case it ignores the subselect, and I need it to do exactly what the above SQL query does, pick up a field with subselect..

Can you help me?

Thanks

Answer 1

Try following way:

$collection = Mage::getModel('sales/order')->getCollection();

$sub = $collection->getConnection()->select()->from(
    'nexway_order',
    ['nexway_status']

)->where('order_id=main_table.entity_id')
->order('id DESC')->limit(1);

$collection->getSelect()->columns(
    ['nexway_status' => $sub]
);

echo  $collection->getSelectSql();