Zendframework Union Issue

Question

I have this code running

$sq = $this->_codes->getAdapter()->select()
            ->from (array('cs' => 'code_statuses'), array('total' =>     'count(*)'))
            ->join (
                array ('c' => 'codes'), 'c.code_id = cs.code_id', 
                array ('human_state' => new Zend_Db_Expr("CASE c.state_id WHEN 3 THEN 'active' WHEN 5 THEN 'suspended' ELSE 'inactive' END"), 'c.*')
            )
            ->group('cs.code_id');

$sqtemp = $this->_codes->getAdapter()->select()
            ->from (array('cs' => 'code_statuses'), array('total' => 'count(*)'))
            ->join (
                array ('c' => 'codes'), 'c.code_id = cs.code_id', 
                array ('human_state' => new Zend_Db_Expr("CASE     c.state_id WHEN 3 THEN 'active' WHEN 5 THEN 'suspended' ELSE 'inactive' END"), 'c.*')
            )
            ->group('cs.code_id');

if (!empty($options['state_id'])):
            if (is_array($options['state_id'])):
                $states = 'cs.state_id=' . implode(' OR cs.state_id=', $options['state_id']);
                $sq->where($states)
                                       ->having(total<=4);
                $sqtemp->where ('cs.state_id=5')
                                            ->having(total<4);
            else:
                $sq->where ('cs.state_id=?', $options['state_id']);
            endif;

The issue occurs when i try to use union

$sqfinal=$this->_codes->getAdapter()->select()
                ->union(array($sq,$sqtemp))
                ->order('cs.code_id');

but individually $sq and $sqtemp work fine

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cs.code_id' in 'order clause'

Not sure where I am going wrong

Any help will be appreciated

*edit

SELECT count(*) AS `total`, 
CASE c.state_id 
WHEN 3 THEN 'active' 
WHEN 5 THEN 'suspended' 
ELSE 'inactive' 
END AS `human_state`, `c`.* 
FROM `code_statuses` AS `cs` 
INNER JOIN `codes` AS `c` 
ON c.code_id = cs.code_id 
WHERE (cs.state_id=1 OR cs.state_id=2 OR cs.state_id=4) 
GROUP BY `cs`.`code_id` HAVING (total<=4) 
UNION 
SELECT count(*) AS `total`, 
CASE c.state_id 
WHEN 3 THEN 'active' 
WHEN 5 THEN 'suspended' 
ELSE 'inactive' 
END AS `human_state`, `c`.* 
FROM `code_statuses` AS `cs` 
INNER JOIN `codes` AS `c` 
ON c.code_id = cs.code_id 
WHERE (cs.state_id=5) 
GROUP BY `cs`.`code_id` 
HAVING (total<4)

The part before the union is $sq, the part afterwards is $sqtemp, the combination of the two gives the print out above Both of them with union in is the whole thing

Which version of ZendFW are you using? what is the output of `(string) $sq` and `(string) $sqtemp` respectively? What is the output of `(string) — Elias Van Ootegem, Sep 10 '13 at 14:00
The zend version is 1.12.31 See above for edits with regards to $sq and $sqtemp — user2764582, Sep 11 '13 at 07:09
Union all gives me the same error $sq=$this->_codes->getAdapter()->select() ->union(array( $sq, $sqtemp ),Zend_Db_Select::SQL_UNION_ALL) ->order('cs.code_id'); SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c.code' in 'order clause' — user2764582, Sep 11 '13 at 10:17
Just copy-paste the query in mysql (workbench, or some other tool you have, even cli will do), and break it up, find the syntax error. I'm no free debugger (I, like anybody else, hate debugging, but we all have to do it) — Elias Van Ootegem, Sep 11 '13 at 10:35
The raw SQL works fine in mysql so I suspect it has something to do with zend framework — user2764582, Sep 11 '13 at 11:39

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

After a second look at your code, I suspect the oder() call on the union. You're ordering by cs.code_id, whic is not mentioned in any of the select statements, nor is the c.code_id for that matter.
Try adding either c.code_id or cs.code_id to the SELECT't that make up the UNION, possibly consider using an alias, which you can then use in your order clause.

$sq = $this->_codes->getAdapter()->select()
            ->from(array('cs' => 'code_statuses'),
                   array(
                       'total'     => 'count(*)'
                       'cscodeids' => 'code_ids',
                   ));
//...
$union = $this->_codes->getAdapter()
              ->select()
              ->union(array($sq,$sqtemp))
              ->order('cscodeids');

This, I believe, should work. I've taken inspiration from various places. Here are some of the links that lead up to my answer (can't find all of them ATM):

MySQL union and order by help
Every derived table must have its own alias - error from combination descending MySQL
Using union and order by clause in mysql
How to use union in zend db
Zend_Db union issue: old bug report... contains some details

edited May 23 '17 at 12:05

Community

1
1

answered Sep 11 '13 at 11:58

Elias Van Ootegem

74,482
9
111
149

Thanks, will have a look at those documents The $sq should have been $sqfinal=$this->_codes->getAdapter()->select() ->union(array($sq,$sqtemp)) ->order('cs.code_id'); My apologies, – user2764582 Sep 11 '13 at 12:59
I found some code at the bottom of the page that was doing this $sq->order('c.code ASC'); i commented it out, and my query worked perfectly, but it might have affects for other queries, will look into this Thanks for your help, really appreciate it :) – user2764582 Sep 11 '13 at 13:37
@user2764582: You might try creating the `union` query manually, because, [as the manual says](http://dev.mysql.com/doc/refman/5.0/en/union.html), to use `ORDER BY` in the queries that make up the `UNION`, you have to use parentheses: `$sqfinal = '('.(string)$sq.') UNION ('.$sqtemp.')';`... – Elias Van Ootegem Sep 11 '13 at 13:50

Zendframework Union Issue

1 Answers1