Tuesday, November 12, 2013

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

 
 

5 comments :

Anonymous said...

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`

This is my test result, 'comment.id' not like `comment`.id

Nick Mallare said...

Where did you use $predicate in your last example?

Tarun Kumar Singhal said...

Hey Nick, You can omit the $predicate line.

Anonymous said...

$select = $select->where('foo_name' => new \Zend\Db\Sql\Expression("LOWER('test')")); - See more at: http://tarunlinux.blogspot.ro/2013/11/zf2-zenddbsqlexpression-usage.html#sthash.joqK04Na.dpuf



you forgot to put array('foo_name' => new \Zend\Db\Sql\Expression("LOWER('test')")))

Tarun Kumar Singhal said...

Soory, Unable to get you..
i think you posted your comment at wrong plane, as you are giving reference link of correction of the same page where you commented.