MYSql - 在Zend Db中使用相关子查询

Question

我正在尝试使用Zend_Db_Select（ZF 1.12）中的相关子查询来构建一个可用的MySql查询，以便在Zend_Paginator_Adapter中使用。以下是可用的查询语句：

SELECT f.*, (SELECT (COUNT(p.post_id) - 1)
FROM `forum_topic_posts` AS p WHERE f.topic_id = p.topic_id) AS post_count
FROM `forum_topics` AS f WHERE f.forum_id = '2293'
ORDER BY post_count DESC, last_update DESC

因此，我得出：

$subquery = $db->select()
->from(array('p' => 'forum_topic_posts'), 'COUNT(*)')
->where('p.topic_id = f.topic_id');

$this->sql = $db->select()
->from(array('f' => 'forum_topics'), array('*', $subquery . ' as post_count'))
->where('forum_id=?', $forumId, Zend_Db::PARAM_INT)
->order('post_count ' . $orderDirection);

但是当执行查询时，Zend会停止并显示以下异常：

Zend_Db_Statement_Mysqli_Exception: Mysqli准备错误：您的SQL语法有误；请检查与您的MySQL服务器版本相对应的手册以获取正确的语法使用方法，在第1行附近 'SELECT COUNT(*) FROM forum_topic_posts AS p WHERE (p.topic_id = f.to'。

我该如何让子查询正常工作？

- DerFlow

Answer 1

以下是使用Zend_Db面向对象接口编写的查询。

关键点大多使用一些Zend_Db_Expr对象用于子查询和COUNT函数。

$ss = $db->select()
         ->from(array('p' => 'forum_topic_posts'),
                new Zend_Db_Expr('COUNT(p.post_id) - 1'))
         ->where('f.topic_id = p.topic_id');

$s = $db->select()
        ->from(array('f' => 'forum_topics'),
               array('f.*', 'post_count' => new Zend_Db_Expr('(' . $ss . ')')))
        ->where('f.forum_id = ?', 2293)
        ->order('post_count DESC, last_update DESC');

echo $s;
// SELECT `f`.*, SELECT COUNT(p.post_id) - 1 FROM `forum_topic_posts` AS `p` WHERE (f.topic_id = p.topic_id) AS `post_count` FROM `forum_topics` AS `f` WHERE (f.forum_id = 2293) ORDER BY `post_count DESC, last_update` DESC

- drew010

---

谢谢，那很完美地运作了。想要点赞，但是声望不够。 - DerFlow