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.