Zend Framework 2 Sql Expression

Hi,

To perform the sql function inside sql query in ZF2 like:

Type 1:
Query: 

 "select count(*) from foo group by name"  

ZF2: 

 $sql = new Sql($adaptor);  
 $select = $sql->select()->from(array('f' => 'foo'));  
 $select = $select->columns(array('count' => new \Zend\Db\Sql\Expression("count(*)")));  
 $select = $select->group(array('name'));  
 $statement = $sql->prepareStatementForSqlObject($select);  
 echo $statement->getSql();  

Type 2:

 Query = "Select * from foo where foo_name = lower('test')";  

ZF2:

  $sql = new SQL($adaptor);   
 $select = $sql->select()->from(array('f'=>'foo'));   
 $select = $select->where('foo_name' => new \Zend\Db\Sql\Expression("LOWER('test')"));  
 $statement = $sql->prepareStatementForSqlObject($select);  
 echo $statement->getSql();  

Type 3: 
Manage sub-query inside query:

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`  

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

Type 4:

Union of Sql Query:

Query:

select 'passport' as type,a.user_id from join_user_passport_office as a where a.user_id=7

 union

select 'embassy' as type,b.user_id from join_user_embassy_office as b where b.user_id=7

 union 

select 'visa' as type,c.user_id from join_user_visa_office as c where c.user_id=7

 union 

select 'ecowas' as type,d.user_id from join_user_ecowas_office as d where d.user_id=7

 union 

select 'freezone' as type,e.user_id from join_user_freezone_office as e where e.user_id=7 ;

 

ZF2:

 $select1 = $this->_sql->select()
        ->from(array('t1' => 'join_user_embassy_office'))
        ->columns(array('type' => new \Zend\Db\Sql\Expression("'embassy'"), 'user_id' => 'user_id'))
        ->where(array('t1.user_id' => $id));
        

        $select2 = $this->_sql->select()
        ->from(array('t2' => 'join_user_passport_office'))
        ->columns(array('type' => new \Zend\Db\Sql\Expression("'passport'") ,'user_id' => 'user_id'))
        ->where(array('t2.user_id' => $id));
        
        $select1->combine($select2);

        
        
        $select3 = $this->_sql->select()
        ->from(array('t3' => 'join_user_visa_office'))
        ->columns(array('type' => new \Zend\Db\Sql\Expression("'visa'") , 'user_id' => 'user_id'))
        ->where(array('t3.user_id' => $id));
        
        $selectall3 = $this->_sql->select();
        $selectall3->from(array('sel1and2' => $select1));
        $selectall3->combine($select3);