php - Zend_DB union() :Zend_Db_Table_Abstract 与 Zend_Db_Table::getDefaultAdapter()

Question

所以我有我想通过 zend 框架调用的原始 SQL

select t.type, t.tid,t.tname,t.cid,t.cname, ls.*
from
(
        select t.type, t.id as tid, t.name as tname, c.id as cid, c.name as cname from team t
        join company c on t.parent=c.id and t.type='C' and c.sector=20 and t.status='ACTIVE'
    union
        select t.type, t.id as tid,t.name as tname, null as cid, null as cname from team t
        join sector s on t.parent=s.id and t.type='S'and s.id=20 and t.status='ACTIVE'
) t
LEFT JOIN leaguesummary ls ON ls.leagueparticipantid=t.tid AND ls.leaguetype='T'
WHERE ls.leagueid = 5
ORDER BY ls.leaguedivision asc, ls.leagueposition asc LIMIT 10;    

我的模型类扩展了 Zend_Db_Table 我有一个简单的方法来构建 SQL 并查询它

class Model_DbTable_LeagueSummary extends Zend_Db_Table_Abstract {

....

public function getTeamLeagueSummayBySector($sectorid,$limit=10)
{
    $select = $this->select()
        ->setIntegrityCheck(false)
        ->from(array('team'=>'team'),array('type','id','name'))
        ->join(array('company'=>'company'),'team.parent=company.id',array())
        ->where('team.type="C"')
        ->where('team.status="ACTIVE"')
        ->where('company.sector=?',$sectorid);

    $select2 = $this->select()
        ->setIntegrityCheck(false)
        ->from(array('team'=>'team'),array('type','id','name'))
        ->join(array('sector'=>'sector'),'team.parent=sector.id',array())
        ->where('team.type="S"')
        ->where('team.status="ACTIVE"')
        ->where('sector.id=?',$sectorid);

    // manually creating the SQL string and calling Zend_Db_Table::getDefaultAdapter() directly
    $STRING = sprintf("select x.*,ls.* from ( %s union %s ) x
        LEFT JOIN leaguesummary ls ON ls.leagueparticipantid=x.id AND ls.leaguetype='T'
        WHERE ls.leagueid = 5
        ORDER BY ls.leaguedivision asc, ls.leagueposition asc LIMIT 10;",$select,$select2);
    $db = Zend_Db_Table::getDefaultAdapter();
    $stmt = $db->query($STRING);
    $stmt->setFetchMode(Zend_Db::FETCH_OBJ);
    $result = $stmt->fetchAll();
    return $result;
}

此查询有效，但不喜欢该解决方案并希望重构代码以更正确地使用 Zend_DB 方法。我已经走到这一步了

    $sql = $this->select()
        ->setIntegrityCheck(false)
        ->from(array('X'=>'X'))
        ->union(array($select,$select2))
        ->joinLeft(array('leaguesummary'=>'leaguesummary'),'leaguesummary.leagueparticipantid=X.id')
        ->where('leaguesummary.leaguetype="T"')
        ->where("leaguesummary.leagueid = ?",5)
        ->order("leaguesummary.leaguedivision asc")
        ->order("leaguesummary.leagueposition asc")
        ->limit($limit);
    return $db->fetchAll($sql);

但是我得到了这个异常(exception)。工会声明有什么问题？

Message: Invalid use of table with UNION

Stack trace:
#0 /home/assure/bhaa/zend/trunk/library/Zend/Db/Select.php(357): Zend_Db_Select->_join('left join', Array, 'leaguesummary.l...', '*', NULL)
#1 /home/assure/bhaa/zend/trunk/application/models/DbTable/LeagueSummary.php(175): Zend_Db_Select->joinLeft(Array, 'leaguesummary.l...')
#2 /home/assure/bhaa/zend/trunk/application/controllers/HousesController.php(110): Model_DbTable_LeagueSummary->getTeamLeagueSummayBySector('20')
#3 /home/assure/bhaa/zend/trunk/library/Zend/Controller/Action.php(513): HousesController->sectorAction()
#4 /home/assure/bhaa/zend/trunk/library/Zend/Controller/Dispatcher/Standard.php(289): Zend_Controller_Action->dispatch('sectorAction')
#5 /home/assure/bhaa/zend/trunk/library/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#6 /home/assure/bhaa/zend/trunk/library/Zend/Application/Bootstrap/Bootstrap.php(97): Zend_Controller_Front->dispatch()
#7 /home/assure/bhaa/zend/trunk/library/Zend/Application.php(366): Zend_Application_Bootstrap_Bootstrap->run()

-- 编辑 2 --

所以我添加了第三步，像这样合并两个 union sql 语句

$union = $this->getDefaultAdapter()->select()->union(array($select,$select2));
$logger->info(sprintf(' (%s) ',$union));

这给了我有效的 SQL

2010-06-04T15:11:55+00:00 INFO (6):  (SELECT `team`.`type`, `team`.`id`, `team`.`name` FROM `team` INNER JOIN `company` ON team.parent=company.id WHERE (team.type="C") AND (team.status="ACTIVE") AND (company.sector='20') UNION SELECT `team`.`type`, `team`.`id`, `team`.`name` FROM `team` INNER JOIN `sector` ON team.parent=sector.id WHERE (team.type="S") AND (team.status="ACTIVE") AND (sector.id='20')) 

下一步是将这个 union 子句集成到主查询中，我像这样使用 from() 子句

->from(array('X'=> '('.$union.')' ) )
->joinLeft(array('leaguesummary'=>'leaguesummary'),'leaguesummary.leagueparticipantid=X.id')
->where('leaguesummary.leaguetype="T"')
->where("leaguesummary.leagueid = ?",5)
->order("leaguesummary.leaguedivision asc")
->order("leaguesummary.leagueposition asc")
->limit($limit);
$logger->info(sprintf('%s',$sql));
return $this->getDefaultAdapter()->fetchAll($sql);

但似乎当“$union”变量转换为字符串时，它以某种方式缩短了，这意味着我的 SQL 无效

SELECT `X`.*, `leaguesummary`.* FROM `(SELECT ``team```.```type``, ``team``` AS `X` LEFT JOIN `leaguesummary` ON leaguesummary.leagueparticipantid=X.id WHERE (leaguesummary.leaguetype="T") AND (leaguesummary.leagueid = 5) ORDER BY `leaguesummary`.`leaguedivision` asc, `leaguesummary`.`leagueposition` asc LIMIT 10

有什么想法吗？

Answer 1

对于像您这样“复杂”的查询，您可能不想以“Zend”方式进行查询。您可以将 query() 函数与原始 SQL 一起使用

$rows = $this->getAdapter()->query("
    select t.type, t.tid,t.tname,t.cid,t.cname, ls.*
    from
    (
        select t.type, t.id as tid, t.name as tname, c.id as cid, c.name as cname from team t
        join company c on t.parent=c.id and t.type='C' and c.sector=20 and t.status='ACTIVE'
    union
        select t.type, t.id as tid,t.name as tname, null as cid, null as cname from team t
        join sector s on t.parent=s.id and t.type='S'and s.id=20 and t.status='ACTIVE'
    ) t
    LEFT JOIN leaguesummary ls ON ls.leagueparticipantid=t.tid AND ls.leaguetype='T'
    WHERE ls.leagueid = 5
    ORDER BY ls.leaguedivision asc, ls.leagueposition asc LIMIT 10;  
");

This bug报告显示了 union() 函数的正确用法:

$selectA = $db->select()
    ->from(array('u' => 'user'), 'name')
    ->where('u.id >= 5');

$selectB = $db->select()
    ->from(array('u' => 'user'), 'name')
    ->where('u.id < 5');

$select = $db->select()
    ->union(array($selectA, $selectB));

或者:

$select = $db->select()
    ->union(array(
        $db->select()
            ->from(array('u' => 'user'), 'name')
            ->where('u.id >= 5'),
        $db->select()
            ->from(array('u' => 'user'), 'name')
            ->where('u.id < 5')
));