Zend Framework 1 cannot use where() method when using union()

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);

This work, but when I insert where() method before $select->order(), will throw error.

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

Error is:

<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

I've been frustrated for about two days, please help me. Thank you.

@Claudio Venturini

I tried your answer, still have two questions:

1 The sql printed out are:

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

How to remove the from ``...as?

2 When I remove the from `` ... as and execute the sql, I got error below:

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

But I have OperateTime field! What's wrong?

PS: I've got it, I should query the OperateTime field out too.

Claudio Venturini · Accepted Answer · 2014-07-31 08:10:23Z

1

You cannot use a WHERE condition on the outer UNION query. You can only use WHERE inside any of the subqueries in the UNION.

For example this is valid SQL

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

while the following is not valid:

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

See the documentation on UNION for reference: http://dev.mysql.com/doc/refman/5.5/en/union.html

So to add the same WHERE condition programmatically in each condition you have to create each SELECT statement separately. Try the following:

$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);

edited Jul 31, 2014 at 8:10

answered Jul 31, 2014 at 7:57

Claudio Venturini

6985 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Phoenix Over a year ago

Thanks, but my two where are the same, how could I modify my code to use two where separately using zf1?

Phoenix Over a year ago

So great of you! I'll have your code try later, thank you so much!!

Phoenix Over a year ago

I got it, I should query the field out too.

Collectives™ on Stack Overflow

Zend Framework 1 cannot use where() method when using union()

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related