Zend Framework 1在使用union()时不能使用where()方法

Question

$select = $logAdapter->select(); // an instance of Zend_Db_Select class
$select->union(array(
         "SELECT Country,Name
          FROM ManualLog_TheNew",
         "SELECT Country,Name
          FROM ManualLog_TheOld"),Zend_Db_Select::SQL_UNION_ALL);
$select->order("$param->orderBy")
         ->limit($param->length,$param->offset);

这个工作，但当我插入where()方法之前$select->order()，将抛出错误。

$select->where('ManualLog_TheOld.OperateTime >= ?' => "2014-06-30");

错误是:

<b>Fatal error</b>:  Uncaught exception 'Zend_Db_Select_Exception' with message 'Invalid use of where clause with UNION' in /xxx/code/ZendFramework/Zend/Db/Select.php:880

我已经沮丧了两天了，请帮帮我。谢谢你。

@Claudio Venturini

我试了你的答案，还是有两个问题:

1输出的sql语句如下:

Warning: strpos() expects parameter 1 to be string, array given in /code/ZendFramework/Zend/Db/Select.php on line 739
Warning: explode() expects parameter 2 to be string, array given in /code/ZendFramework/Zend/Db/Select.php on line 740
Warning: strpos() expects parameter 1 to be string, array given in /code/ZendFramework/Zend/Db/Select.php on line 739
Warning: explode() expects parameter 2 to be string, array given in /code/ZendFramework/Zend/Db/Select.php on line 740
SELECT `ManualLog_TheOld`.`Country` 
FROM `` AS `ManualLog_TheOld` 
WHERE (OperateTime >= '2014-06-30') 
UNION ALL 
SELECT `ManualLog_TheNew`.`Country` 
FROM `` AS `ManualLog_TheNew` 
WHERE (OperateTime >= '2014-06-30') 
ORDER BY `OperateTime` desc LIMIT 200

如何移除from ``...as ?

2当我删除from `` ... as并执行sql时，我得到以下错误:

Error Code: 1054
Unknown column 'ManualLog_TheNew.OperateTime' in 'order clause'

但是我有OperateTime字段!怎么了?

PS:我知道了，我也应该查询OperateTime字段。

Answer 1

不能在外部UNION查询中使用WHERE条件。您只能在UNION中的任何子查询中使用WHERE。

例如:SQL

(SELECT Country,Name
    FROM ManualLog_TheNew
    WHERE condition1)
UNION
(SELECT Country,Name
    FROM ManualLog_TheOld
    WHERE condition2)

，而以下内容无效:

(SELECT Country,Name FROM ManualLog_TheNew)
UNION
(SELECT Country,Name FROM ManualLog_TheOld)
WHERE condition

参考UNION的文档:http://dev.mysql.com/doc/refman/5.5/en/union.html

因此，要在每个条件中以编程方式添加相同的WHERE条件，您必须分别创建每个SELECT语句。试试以下命令:

$condition = 'OperateTime >= ?';
$conditionValue = '2014-06-30';
$selectOld = $logAdapter->select();
$selectOld->from(array('ManualLog_TheOld'), array('Country', 'Name'))
$selectOld->where($condition, $conditionValue);
$selectNew = $logAdapter->select();
$selectNew->from(array('ManualLog_TheNew'), array('Country', 'Name'))
$selectNew->where($condition, $conditionValue);
$select = $logAdapter->select();
$select->union(array($selectOld, $selectNew), Zend_Db_Select::SQL_UNION_ALL);
$select->order($param->orderBy)->limit($param->length, $param->offset);