Re: Zend_Db_Table_Select - Select query cannot join with another table

> 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.