How to join collections in Magento?

Question

I am trying to join a custom collection with products to show the product name (not just the id) in the admin grid widget. So far i can't find the correct syntax.

I am able to retrieve the products with the product name by the following:

Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('name');

and i can retrieve my custom collection with:

Mage::getResourceModel('xyz_mymodule/model_collection');

How do i join the two so that the module collection is the primary collection and the id as returned by $model->getId() is still the id of my custom collection?

Answer 1

Here you have a working example:

$collection = Mage::getModel('sales/order')->getCollection();
$collection->getSelect()->join( array('order_item'=> 'sales_flat_order_item'), 'order_item.order_id = main_table.entity_id', array('order_item.sku'));