You don't need a parenthesized join in your example. Do this instead:
$select = $db->select()->from(array('outer'=>'firstTable'));
$select->joinLeft(array('t2'=>'someOtherTable'), 't2.foo = `outer`.foo');
$select->join(array('t1'=>'someTable'), 't1.id = t2.id');
Generates the following valid SQL:
SELECT `outer`.*, `t2`.*, `t1`.*
FROM `firstTable` AS `outer`
LEFT JOIN `someOtherTable` AS `t2` ON t2.foo = `outer`.foo
INNER JOIN `someTable` AS `t1` ON t1.id = t2.id
This works like the parenthesized join you showed, because t1.id = t2.id is
not true where t2.id is NULL.
Using parenthesized join expressions is not in the intended usage of
Zend_Db_Select. Another option might be to use a derived table in a full
subquery:
$subselect = $db->select()->from(array('t1'=>'someTable'))
->join(array('t2'=>'someOtherTable'), 't1.id = t2.id', array());
$select = $db->select()->from(array('outer'=>'firstTable'))
->joinLeft(array('t3'=>new Zend_Db_Expr("({$subselect})")), 't3.foo =
outer.foo');
Generates the following SQL:
SELECT `outer`.*, `t3`.* FROM `firstTable` AS `outer`
LEFT JOIN (SELECT `t1`.* FROM `someTable` AS `t1`
INNER JOIN `someOtherTable` AS `t2` ON t1.id = t2.id) AS `t3` ON t3.foo =
outer.foo
Note I had to suppress columns from t2, or else the derived table would have
duplicate columns.
Regards,
Bill Karwin
--
View this message in context:
http://www.nabble.com/Zend_Db_Select%3A-nested-joins-tp19636627p19637378.html
Sent from the Zend Framework mailing list archive at Nabble.com.
