StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POZendframework Union Issue
text
Body
copied!<p>I have this code running</p> <pre><code>$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'); </code></pre> <hr> <pre><code>$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'); </code></pre> <hr> <pre><code>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; </code></pre> <p>The issue occurs when i try to use union </p> <pre><code>$sqfinal=$this->_codes->getAdapter()->select() ->union(array($sq,$sqtemp)) ->order('cs.code_id'); </code></pre> <p>but individually <code>$sq</code> and <code>$sqtemp</code> work fine</p> <p><code>SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cs.code_id' in 'order clause'</code></p> <p>Not sure where I am going wrong</p> <p>Any help will be appreciated</p> <p>*edit</p> <pre><code>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) </code></pre> <p>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</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload