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

 
 

6 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

Anonymous 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.

Aapthi Technologies said...

Hi,

good information

Web application development services will successfully change the execution of your online business So, hire our skilled Ecommerce website company to get customized applications for your business.

Best Website Designing and Development Company
"
Ecommerce Website Development Company
"
Zend Framework