ZF2 – подзапросы

Мой требуемый запрос:

 SELECT `comment`.`id` AS `commentId`, `comment`.`comment` AS `comment`, (SELECT COUNT(comment_vote.id) AS `negativeVote` FROM `comment_vote` WHERE vote = -1 AND comment_vote.commentId = comment.id) AS `nagetiveVoteCount` FROM `comment` 

Как я создал с помощью Zend Framework 2:

 $sql = new Sql($this->_adapter); $mainSelect = $sql->select()->from('comment'); $selectPost = $sql->select() ->from('comment_vote') ->columns(array('negativeVote' => new \Zend\Db\Sql\Expression('COUNT(comment_vote.id)'))) ->where('vote = -1') ->where('comment_vote.commentId = comment.id'); $mainSelect->columns( array( 'commentId' => 'id', 'comment', 'nagetiveVoteCount' => new \Zend\Db\Sql\Expression('?', array($selectPost)), ) ); $statement = $sql->prepareStatementForSqlObject($mainSelect); $comments = $statement->execute(); $resultSet = new ResultSet(); $resultSet->initialize($comments); return $resultSet->toArray(); 

Refrence: http://eltonminetto.net/blog/2013/03/21/subqueries-no-zend-framework-2/

Спасибо за все ответы.

 Solution for the above query in ZF2: $sub = new Select('comment_vote'); $sub->columns(array('negativeVote' => new \Zend\Db\Sql\Expression('COUNT(comment_vote.id)')), FALSE)->where(array('vote' => -1 , 'comment_vote.commentId' => 'comment.id')); $subquery = new \Zend\Db\Sql\Expression("({$sub->getSqlString()})"); $predicate = new \Zend\Db\Sql\Predicate\Expression("({$sub->getSqlString()})"); $sql = new Sql($this->adapter); $select = $sql->select()->from('comment'); $select->columns(array('commentId','comment', 'nagetiveVoteCount' => $subquery)); echo $select->getSqlString(); 

он вернет результат:

 SELECT "comment"."commentId" AS "commentId", "comment"."comment" AS "comment", (SELECT COUNT(comment_vote.id) AS "negativeVote" FROM "comment_vote" WHERE "vote" = '-1' AND "comment_vote"."commentId" = 'comment.id') AS "nagetiveVoteCount" FROM "comment" 

 $db->select() ->from ('table1', array('t1_label')) ->joinInner( array('T2' => new Zend_Db_Expr ( '('. $db->select() ->from('table2', array('t2_label')) ->where('condition') .')' )), 'table1.t2_id = T2.t2_id', array('t2_label') )