Search code examples
phpsqlzend-frameworkzend-db

Zendframework Union Issue

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

Solution

  • 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):