Zendframework Union问题

Question

我在运行这段代码

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

当我试图使用union时，问题就会发生。

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

但是单独的$sq和$sqtemp工作得很好

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

不知道我哪里出了问题

如有任何帮助，将不胜感激。

*编辑

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)

合并前的部分是$sq，之后的部分是$sqtemp，两者的结合给出了两者上面的打印和合并在一起的全部内容。

Answer 1

再看一看您的代码，我怀疑联合上的oder()调用。您是按cs.code_id排序的，在任何select语句中都没有提到whic，对此也没有提到c.code_id。

尝试将c.code_id或cs.code_id添加到构成UNION的SELECTt中，可能考虑使用别名，然后可以在order子句中使用该别名。

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

我相信这是可行的。我从不同的地方得到灵感。下面是一些通向我的答案的链接(无法找到所有的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
• union issue：老臭虫报告..。包含一些细节