ZF2 - Subqueries

Question

Subquery in Zend Framework 2

My required request:

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` 

Please, help.

Thanks Anjith

Answer 1

My required request:

 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` 

As I created using 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/

Thanks for all the answers.

Answer 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(); 

it will return the result:

 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" 

Answer 3

 $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') )