ZF-4772: Zend_Db_Select - union() doesn't work at all
Description
Zend_Db_Select::union() function does not produce expected result.
$selectA = $db->select()
->from(array('u' => 'user'), 'name')
->where('u.id >= 5');
$selectB = $db->select()
->from(array('u' => 'user'), 'name')
->where('u.id < 5')
->union(array($selectA));
echo $selectB->__toString();
outputs
SELECT `u`.`name`SELECT `u`.`name` FROM `user` AS `u` WHERE (u.id >= 5) FROM `user` AS `u` WHERE (u.id < 5)
which is not a valid query. Expected result:
(SELECT `u`.`name` FROM `user` AS `u` WHERE (u.id < 5)) UNION (SELECT `u`.`name` FROM `user` AS `u` WHERE (u.id >= 5))
Comments
Posted by David Muir (dmuir) on 2009-01-20T17:10:44.000+0000
I was able to get unions working by using this syntax:
$selectA = $db->select() ->from(array('u' => 'user'), 'name') ->where('u.id >= 5'); $selectB = $db->select() ->from(array('u' => 'user'), 'name') ->where('u.id < 5'); $select = $db->select() ->union(array($selectA, $selectB));or alternatively
$select = $db->select() ->union(array( $db->select() ->from(array('u' => 'user'), 'name') ->where('u.id >= 5'), $db->select() ->from(array('u' => 'user'), 'name') ->where('u.id < 5') ));It won't wrap each select clause in parenthesis though. See ZF-4338 for that issue.
Posted by Benjamin Eberlei (beberlei) on 2009-09-17T14:04:58.000+0000
This is mainly a documentation issue. The union() method doc comment described functionality it did not have.
The only working example for union is to pass an array of sql query strings or Zend_Db_Select instances into its first parameter and render that.
I have added documentation on the issue into the Zend_Db_Select manual.