Magento – displaying data from two tables in one grid

admindatabase

I have a grid for my admin module and it works fine, but I now would like to add more data onto that grid, but the additional data is held in a second table in my database.

To display this additional data I have tried

protected function _prepareCollection()
{
    $collection = Mage::getModel('modulename/modelname')->getCollection();
    $collection->join(array('table2' => 'table1'), 'table1_id = table2_id', 'unsubscribed');//additional line
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

protected function _prepareColumns()
{
    $this->addColumn('table1_id', array(
        'header'    =>  $this->__('ID'),
        'align'     => 'left',
        'width'     => '50px',
        'index'     => 'table1_id',
    ));

    $this->addColumn('salutation', array(
        'header'    => $this->__('Title'),
        'align'     => 'left',
        'index'     => 'salutation',
        'width'     => '80px',
    ));

    $this->addColumn('f_name', array(
        'header'    => $this->__('First Name'),
        'align'     => 'left',
        'index'     => 'f_name',
        'width'     => '100px',
    ));

    $this->addColumn('l_name', array(
        'header'    => $this->__('Last Name'),
        'align'     => 'left',
        'index'     => 'l_name',
        'width'     => '100px',
    ));

    $this->addColumn('subscribed', array(
        'header'    =>  $this->__('Subscribed'),
        'align'     => 'left',
        'width'     => '80px',
        'index'     => 'Subscribed',
        'type'      => 'options',
        'options'   => array(
            0 => 'Subscribed',
            1 => 'Not Subscribed',
        ),
    ));//additional column

table1 has table1_id, salutation, f_name, l_name

table2 has table2_id, subscribed

First I added the new column and that displayed fine, then I added the join statement, but that returned an

Integrity constraint violation: 1052 Column 'table1_id' in on clause is ambiguous

So its obviously something to do with $collection->join() but what have I done wrong? I wasn't sure if it was due to the order I place the tables but reordering them failed, i'm obviously missing something

===EDIT===

$collection = Mage::getModel('module/model')->getCollection();
$collection->getSelect()->join( array('options'=>$this->getTable('module/table2')),'main_table.table1_id = options.table2_id','options.*');

Best Answer

When joining a table to a collection you'll have to use a prefix for the fields so every field is unique in the query.

$collection->getSelect()->join( array('options'=>'table2'),'`main_table`.`table1_id` = `options`.`table2_id`','`options`.*');

tbl2 is the Alias of table2 in this case which will give you a query like:

SELECT main_table.*, tbl2.unsubscribed FROM table1 AS main_table INNER JOIN table2 AS tbl2 ON main_table.table1_id = tbl2.table2_id

Print the query to check if prefixes are added on all the fields and to check for any other issues. 

var_dump((string)$collection->getSelect());

You can check this Magento wiki page for a brief explanation on joining

Related Topic