Left join a table on a collection query

To change the query you can do

$collection->getSelect()->doWhateverYouWantWithSelectObject();
foreach($collection as $order) {} // order collection

But to join a table, you can just use $collection->joinTable()

joinTable only exists on Mage_Eav_Model_Entity_Collection, this means: products, categories, orders, invoices, creditmemo, shipping, customers. (Thanks @Fra for the comment)

joinTable()

But magento collections have a method joinTable() it took me 45min to fizzle out how it is used. To avoid this for you, I share it.

$productCollection->joinTable(
    array('bonus' => 'mycompany/bonus'), 'product_id=entity_id',
    array('bonus_id' => 'bonus_id')
);

The parameters are:

public function joinTable($table, $bind, $fields = null, $cond = null, $joinType = 'inner')

1. Table is easy, it is the magento namespace/entity format, which you use in your configuration, resource models and the collection. You can use an array of the format array('alias' => 'namespace/entity')
2. Bind means, the ON statement in your SQL. This was the hardest part and I will explain it in details later. It is important to have your flat table BEFORE and your EAV table AFTER the equal sign. Don't use main_table. before the attribute. Magento will do this for you. More on this later.
3. Fields is an array. If you use a string instead, you get this: Warning: Invalid argument supplied for foreach() in /var/www/magento-1.6.2.0/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php on line 775. You can use an array of the format array('field1', 'field2', '...') or array('alias' => 'field1', '...')
4. Condition is a ** WHERE ON condition** in the SQL.
5. joinType. I hope you know what it is. But you have only the choice between LEFT and INNER. app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php:793

More on this topic