Question about SQL query on collection - Magento 1.7

Question

I'm displaying data in a grid using a prepareColumns function.

I have two tables I want to join table "admin_user"

to:

table "customer_group" where both have 'customer_group_id' and I want to display the 'customer_group_code' from the "customer_group" table

I did something similar to this before to display the admin role ID using this code:

$collection = Mage::getResourceModel('admin/user_collection');
 $collection->getSelect()->join(
 array('ar' =>'admin_role'),
 'ar.user_id = main_table.user_id'
 ) ->join(
 array('ar2' => 'admin_role'),
 'ar.parent_id = ar2.role_id',
 array('role_name')
 );

I then tried to add onto the collection using:

$collection->getSelect()->join(
 array('ar' => 'admin_role'),
 'ar.customer_group_id = customer_group.customer_group_id',
 array('customer_group_code')
 );

But this gives me an error :( not sure what's wrong.

Answer 1

Ah foolish of me I realized main_table is the table I'm already currently in. I just had to join them as if they were two separate tables then look at customer_group_code in the second table.

Here is the code I used incase anyone has this problem:

$collection->getSelect()->join(
 array('cg' => 'customer_group'),
 'main_table.customer_group_id = cg.customer_group_id',
 array('customer_group_code')
 );