Query Problem on _prepareCollection()

Question

how to convert mysql query to magento query

mysql query

SELECT  `p`.`product_id` ,  `p`.`name` ,  `o`.`increment_id` 
FROM  `sales_flat_order_item` AS  `p` 
LEFT JOIN  `sales_flat_order` AS  `o` ON  `p`.`order_id` =  `o`.`entity_id` where `o`.`increment_id` = 10000001

magento query

protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel('sales/order_collection');      
        $collection->getSelect()->joinLeft( array('p'
        =>  Mage::getSingleton('core/resource')->getTableName('sales_flat_order_item')),
            array('main_table.entity_id = p.order_id')->where('main_table.increment_id=',$order_id)););
        $this->setCollection($collection);
        parent::_prepareCollection();
        return $this;
    }

I tried below query but it's not getting any result.

how to map above mysql query to $collection;

Thanks.

Answer 1

Maybe something like this:

$collection = Mage::getResourceModel('sales/order_collection');
$collection->addFieldToSelect('increment_id')
    ->getSelect()->joinLeft(array('item' => $collection->getTable('sales/order_item')),
        'item.order_id = main_table.entity_id',
        array('item.product_id', 'item.name'))
    ->where('main_table.increment_id = ?', '100000090');