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)););
        return $this;

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

how to map above mysql query to $collection;


1 Answer 1


Maybe something like this:

$collection = Mage::getResourceModel('sales/order_collection');
    ->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');
  • i will come back after I tried..
    – M_N
    Commented Dec 24, 2014 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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