使用Zend生成多个子查询

Question

我正在尝试使用Zend函数调用来构建查询来重现以下SQL：

SELECT `0`.`id`, `0`.`abbrev` FROM 

  (SELECT  `abbreviations`.`id`, `abbreviations`.`abbrev` 
   FROM  `abbreviations` ,  `keywords` 
   WHERE  `keywords`.`keyword` LIKE  'aug%'
   AND  `keywords`.`abbrev_id` =  `abbreviations`.`id`) `0`

INNER JOIN 

  (SELECT  `abbreviations`.`id` 
   FROM  `abbreviations` ,  `keywords` 
   WHERE  `keywords`.`keyword` LIKE  'foo%'
   AND  `keywords`.`abbrev_id` =  `abbreviations`.`id`) `1` 

ON (`0`.`id` =  `1`.`id`) 

INNER JOIN

  (SELECT  `abbreviations`.`id` 
   FROM  `abbreviations` ,  `keywords` 
   WHERE  `keywords`.`keyword` LIKE  'augment%'
   AND  `keywords`.`abbrev_id` =  `abbreviations`.`id`) `2` 

ON (`0`.`id` = `2`.`id`)

ORDER BY `0`.`abbrev`

正如我测试过的那样，我知道这个SQL可以工作。我更喜欢使用SQL "INTERSECT“，但由于MySQL不支持这一点(因此，我不知道Zend是否也支持)，我不得不使用子查询。

我遇到的困难是用"Zend way“来创建带有链式函数调用的查询，比如$this->getDbTable()->select()->from()等。

例如，我已经能够使用以下命令成功创建一个子查询：

public function selectAbbrevIdsByKeyword($keyword, $abbrevFields) {
    return $this->getDbTable()->select()
    ->from(array('a' => 'abbreviations'), $abbrevFields)
    ->from(array('k' => 'keywords'), 'abbrev_id')
    ->where('`k`.`keyword` LIKE ?', $keyword . '%')
    ->where('`k`.`abbrev_id` = `a`.`id`')
    ->setIntegrityCheck(false);

然而，当我尝试将子查询组合到我的整体目标SQL语句中时，这一切都失败了，比如：

$all_abbrev_cols = array('id', 'abbrev');
$first_subselect = $this->selectAbbrevIdsByKeyword('foo', $all_abbrev_cols);
$select = $this->getDbTable()->select();
$select->from(array('0' => $first_subselect), $all_abbrev_cols);
$select->join(array("1" => 
    $this->selectAbbrevIdsByKeyword($keywords[1], 
        array('id'))), "`0`.`id` = `1`.`id`");
$select->setIntegrityCheck(false);
Zend_Debug::dump($select->__toString());

所谓“崩溃”，我的意思是SQL的产生是令人费解的，尤其是所有无关的反引号。

string(1006) 
"SELECT ```id``)`.`id`, ```id``)`.`abbrev`, ```id``)`.`description`, ```id``)`.`status`, ```id``)`.`rec_practice`, ```id``)`.`type`, ```id``)`.`category`, `SELECT ``a``.``id``, ``k``.``abbrev_id`` FROM ``abbreviations`` AS ``a``
 INNER JOIN ``keywords`` AS ``k`` WHERE (``k``.``keyword`` LIKE 'aug%') AND (``k``.``abbrev_id`` = ``a``.``id``)_2`.* FROM (SELECT `a`.`id`, `a`.`abbrev`, `a`.`description`, `a`.`status`, `a`.`rec_practice`, `a`.`type`, `a`.`category`, `k`.`abbrev_id` FROM `abbreviations` AS `a`
 INNER JOIN `keywords` AS `k` WHERE (`k`.`keyword` LIKE 'foo%') AND (`k`.`abbrev_id` = `a`.`id`)) AS ```id``)`
 INNER JOIN (SELECT `a`.`id`, `k`.`abbrev_id` FROM `abbreviations` AS `a`
 INNER JOIN `keywords` AS `k` WHERE (`k`.`keyword` LIKE 'aug%') AND (`k`.`abbrev_id` = `a`.`id`)) AS `SELECT ``a``.``id``, ``k``.``abbrev_id`` FROM ``abbreviations`` AS ``a``
 INNER JOIN ``keywords`` AS ``k`` WHERE (``k``.``keyword`` LIKE 'aug%') AND (``k``.``abbrev_id`` = ``a``.``id``)_2` ON `0`.`id` = `1`.`id`"

有没有办法用"Zend way“通过链接的Zend函数调用来生成SQL，或者我应该放弃，说这个查询太复杂了，而是把查询构建成一个字符串(使用Zend_Db_Expr和/或quoteInto作为参数/引用)？

Answer 1

我最终做了Aaron在他的评论中描述的事情，只是因为使用我已经拥有的代码是最简单的。

我编写了一个帮助器函数，该函数使用标准的Zend方法来构建基于参数的子查询：

public function selectAbbrevIdsByKeyword($keyword, $abbrevFields) {
  return $this->getDbTable()->select()
              ->from(array('a' => 'abbreviations'), $abbrevFields)
              ->from(array('k' => 'keywords'), 'abbrev_id')
              ->where('`k`.`keyword` LIKE ?', $keyword . '%')
              ->where('`k`.`abbrev_id` = `a`.`id`')
              ->setIntegrityCheck(false)
              ->__toString();
}

然后，我使用子查询助手方法将查询构建为一个大字符串，并使用Zend_Db_Adapter的query()函数将其全部提交：

$all_abbrev_cols = array('id', 'abbrev', 'description', 'status', 'rec_practice', 'type', 'category'); 
$sql = 'SELECT DISTINCT ';
foreach ($all_abbrev_cols as $col) {
  $sql .= "`0`.`$col`, ";
}
$sql = substr_replace($sql, '', -2); // remove last comma-space
$sql .= " FROM \n\n";

// first subquery is special, has all cols and is "0"
$sql .= '  (';
$sql .= $this->selectAbbrevIdsByKeyword($keywords[0], $all_abbrev_cols);
$sql .= ") `0`\n\n";

// starting on SECOND element (index 1)
for ($i = 1; $i < count($keywords); $i++) {
  $sql .= "INNER JOIN\n\n  (";
  $sql .= $this->selectAbbrevIdsByKeyword($keywords[$i], array('id'));
  $sql .= ") `$i`\n\nON (`0`.`id` = `$i`.`id`)\n\n";
}

$sql .= 'ORDER BY `0`.`status`, `0`.`abbrev`';

$entities = array();
foreach ($this->getDb()->query($sql)->fetchAll() as $row) {
  $entities[] = $this->_populate($row);
}
return $entities;

生成的查询结果与我原始问题中的SQL语句相匹配。