5

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!

Improve this question

1 Answer 1

Reset to default
3

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

Improve this answer
3
  • Thank you. How do I '->getData()' of all the fields across the joined table? 'var_dump($collection->getData())' results in only the fields of machine_meter and not the meter types. Thanks for your efforts to help me.
    – STWilson
    Commented Jan 21, 2016 at 23:25
  • 1
    you have to specify the columns you want from joined table in the array of columns that I left empty in the example; if you want all the columns, use 'array('*')`
    – Alessandro Ronchi
    Commented Jan 21, 2016 at 23:30
  • Great. I found too I am able to assign field names to the joined fields this way, e.g. array('newfieldname' => 'sort_order'). Thanks.
    – STWilson
    Commented Jan 21, 2016 at 23:56

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.