Join tables to get sold products

Question

Hi,

I am joining two tables 'sales/order_item_collection' and 'sales/orders' by 'order_id', so that afterward i can filter sold products by 'store_id' and 'product_name'

Here is the code:

$orderTable = Mage::getSingleton('core/resource')->getTableName('sales/order');
$itemsCollection= Mage::getResourceModel('sales/order_item_collection')
    ->join(array('ord'=>$orderTable),'e.order_id = ord.entity_id');

Why is this join not working?

Thank you

Answer 1

The order item collection object implements Mage_Core_Model_Mysql4_Collection_Abstract, so looking at that class, the join method doesn't take an array for table (unlike some other collections). Also, you don't need to get the table manually, just specify the model and Magento will take care of the rest. So this works:

$itemsCollection= Mage::getResourceModel('sales/order_item_collection')
    ->join('order', 'order_id=entity_id');

Hope that helps.

Thanks, Joe