1

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.*');
4

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

  • Well that makes sense. So I have var_dump() as you suggest and returned, string 'SELECT main_table.*, table2.unsubscribed FROM table1 AS main_table INNER JOIN table1 AS table2 ON table1_id = table2_id' (length=179) so I tried testdb_table1 and testdb_table2 also tried with a . between both threw an error, of can't retrieve entity config – tony09uk Sep 16 '13 at 12:20
  • Sorry, i just noticed a mistake in the code, please check the update :) – Sander Mangel Sep 16 '13 at 12:30
  • +1 for the magento wiki article, I had seen it before but it didnt make sense until now – tony09uk Sep 16 '13 at 12:47
  • Awesome, I hope this will help you solve your join issues. – Sander Mangel Sep 16 '13 at 12:52
  • Following the wiki article that you have shared all I can achieve is an empty sting for my query, any suggestions on what I may be doing wrong? I will add it as an EDIT in my original question – tony09uk Sep 16 '13 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.