« Return to Thread: Zend_Db_Table_Select - Select query cannot join with another table

Zend_Db_Table_Select - Select query cannot join with another table

by Michele Brodoloni :: Rate this Message:

Reply to Author | View in Thread

Hello,
i'm having troubles with table joins within Zend_Db_Table_Select.
I'm trying this code on my model:

class MyTable extends Zend_Db_Table
{
    protected $_name     = 'my_table';
    protected $_primary  = 'id';
    protected $_sequence = false;

    protected $_cols = array(
        'id',
        'descrizione',
        'indice'
    );

    public function listByCategory($cat_id)
    {
       $select = $this->select();
       $select->from(array('t1' => $this->_name),
                     array('id', 'descrizione'))
               ->join(array('t2' => 'my_table_2'),
                      't2.foreignKey = t1.id')
               ->where('t2.category_id = ?', $cat_id)
               ->order('indice ASC');

       // Which should translate into something like:
       // SELECT t1.id, t1.descrizione FROM my_table AS t1
       // LEFT JOIN my_table_2 AS t2 ON (t2.foreignKey = t1.id)
       // WHERE t2.category_id = {$cat_id} ORDER BY indice ASC;
    }
}

If i try to print out the SQL query using Zend_Db_Select::__toString()
method, i get this error:
Warning: Select query cannot join with another table in
/var/www/zendFramework/library/Zend/Db/Table/Select.php on line 191

Perhaps a db scheme may help understand better:

Table: my_table
+------+-------------+--------+
| id   | descrizione | indice |
+------+-------------+--------+
| VAL1 | desc. 1     | 0      |
+------+-------------+--------+
| VAL2 | desc. 2     | 1      |
+------+-------------+--------+

Table: my_table_2 (the table i'm trying to have a join with)
+-----+-------------+-------------+
| id  | foreignKey  | category_id |
+-----+-------------+-------------+
|   1 | VAL1        | CAT1        |
+-----+-------------+-------------+
|   2 | VAL2        | CAT1        |
+-----+-------------+-------------+
|   3 | VAL1        | CAT2        |
+-----+-------------+-------------+


I can't figure out where's the problem :(
Maybe i should use the $_referenceMap array in some way?

Hope you get the point.

Thank you all.

 « Return to Thread: Zend_Db_Table_Select - Select query cannot join with another table