Magento – Understanding setSelect()->join to Join Two Custom Collections

collection;join;select

I am confused understanding the correct usage of $collection->getSelect()->join(... to bring two custom model resources together. I tried following other examples using the getSelect method. I also attempted to join the tables in the collection class _afterLoad() method. I'm stuck with errors like:

Column not found: 1054 Unknown column 'machinemanager_machine_metertype.meter_type_id' in 'on clause', query was: SELECT main_table., table_alias. FROM machinemanager_machine_meter AS main_table INNER JOIN machinemanager_machine_metertype AS table_alias ON machinemanager_machine_metertype.meter_type_id = machinemanager_machine_meter.meter_type

Here is my simple request. My model resources to join:

Mage::getModel('machinemanager/machine_meter') //main table 'meter_id' is foreign key
Mage::getModel('machinemanager/machine_metertype') //table to join

So I can understand as a simple example, I'd like to end up with $collection as all fields of both machine_meter and machine_meter_type tables.

Could you please show me how to end up with a correct use of $collection->getSelect()->join(...? Thank you!

Best Answer

This should work for you:

$collection->getSelect()->join(
   array(/* joined table alias = */ 'machine_metertype' => $collection->getTable('machinemanager/machine_metertype')),
   'main_table.meter_id = machine_metertype.meter_type_id',
    /* array of columns */ array()
);

Hope it helps

Related Topic