MYSql - Using correlated subquery in Zend Db

correlated-subquery, mysql, zend-db, zend-framework

Solution

Here is the query written using the `Zend_Db` OO interface.

The key was mostly using some `Zend_Db_Expr` objects for the subquery and `COUNT` function.

$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

Problem

I'm trying to construct a working MySql query with a correlated subquery in zend_db_select (ZF 1.12) to use that in Zend_Paginator_Adapter. The working query is as follows: ``` 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 ``` So i worked out: ``` $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); ``` But Zend stops with the following exception when executing the query: `Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*) FROM forum_topic_posts AS p WHERE (p.topic_id = f.to' at line 1` How could i get the subquery to work?

Original source