CODEWITHSUNDEEP
phpzend-framework2
Anjith K P
Anjith K P

Reputation: 2158

ZF2 - subqueries

Subquery in Zend Framework 2

My required query:

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

Upvotes: 2

Views: 6060

Answers (3)

Tarun Singhal
Tarun Singhal

Reputation: 1007

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 output :

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"

Upvotes: 3

Anjith K P
Anjith K P

Reputation: 2158

My required Query:

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`

How 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 responses.

Upvotes: 16

웃웃웃웃웃
웃웃웃웃웃

Reputation: 11984

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

Upvotes: 0

Related Questions